singa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Moaz Reyad (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (SINGA-404) Call Rafiki from SQL user-defined functions
Date Thu, 15 Nov 2018 14:58:01 GMT

    [ https://issues.apache.org/jira/browse/SINGA-404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16688173#comment-16688173
] 

Moaz Reyad commented on SINGA-404:
----------------------------------

There is also a C++ implementation which can be used instead of python. This example implementation
inserts the UDF inside SQLite code, so each new database will have the Rafiki prediction function
available by default.

Here are the steps to use this implementation:

1. Download SQLite. We will use [SQLite Amalgamation|https://www.sqlite.org/amalgamation.html].
{code:java}
wget https://www.sqlite.org/2018/sqlite-amalgamation-3250300.zip
unzip sqlite-amalgamation-3250300.zip
cd sqlite-amalgamation-3250300/
{code}
2. Download this patch file as rafiki.patch in the same folder
{code:java}
151897a151898,151967
> 
> #include <curl/curl.h>
> 
> 
> struct MemoryStruct {
>   char *memory;
>   size_t size;
> };
> 
> static size_t
> WriteMemoryCallback(void *contents, size_t size, size_t nmemb, void *userp)
> {
>   size_t realsize = size * nmemb;
>   struct MemoryStruct *mem = (struct MemoryStruct *)userp;
> 
>   mem->memory = realloc(mem->memory, mem->size + realsize + 1);
>   if(mem->memory == NULL) {
>     /* out of memory! */
>     printf("not enough memory (realloc returned NULL)\n");
>     return 0;
>   }
> 
>   memcpy(&(mem->memory[mem->size]), contents, realsize);
>   mem->size += realsize;
>   mem->memory[mem->size] = 0;
> 
>   return realsize;
> }
> 
> static void predict(sqlite3_context *context, int argc, sqlite3_value **argv)
> {
>     if (argc == 1) {
>         const char *text = sqlite3_value_text(argv[0]);
>         if (text) {
>                   CURL *curl = curl_easy_init();
> 
>                   if(curl) {                 
>                       curl_easy_setopt(curl, CURLOPT_URL, "http://127.0.0.1:30000/predict");
>                      
>                       curl_easy_setopt(curl, CURLOPT_POSTFIELDSIZE, strlen(text));
>                       curl_easy_setopt(curl, CURLOPT_POSTFIELDS, text);
> 
>                       struct curl_slist *hchunk = NULL;
>                   
>                       hchunk = curl_slist_append(hchunk, "content-type: application/json");
>                       hchunk = curl_slist_append(hchunk, "Accept-Charset: UTF-8");
>                     
>                       /* set our custom set of headers */ 
>                       curl_easy_setopt(curl, CURLOPT_HTTPHEADER, hchunk);
> 
>                       struct MemoryStruct chunk;
> 
>                       chunk.memory = malloc(1);  /* will be grown as needed by the realloc
above */
>                       chunk.size = 0; /* no data at this point */
> 
>                       curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, WriteMemoryCallback);
>                       curl_easy_setopt(curl, CURLOPT_WRITEDATA, (void *)&chunk);
>                     
>                       curl_easy_perform(curl);
>    
>                       sqlite3_result_text(context, chunk.memory, -1, SQLITE_TRANSIENT);
>                       
>                       return;
>                   }  
>         }
>     }
>     sqlite3_result_null(context);
> }
> 
154879a154950,154952
> 
>   /* Add the Rafiki predict function */
>   sqlite3_create_function(db, "ImageClass", 1, SQLITE_UTF8, NULL, &predict, NULL,
NULL);

{code}
3. Use the patch and compile SQLite

If you don't have curl development library installed you may need to install it before compiling,
for example:
{code:java}
apt-get install libcurl4-openssl-dev
{code}
To patch and compile sqlite-rafiki:
{code:java}
patch sqlite3.c < rafiki.patch 

gcc shell.c sqlite3.c -lpthread -ldl -lcurl -o sqlite-rafiki
{code}
4. Create a test SQL file (test.sql). This data is taken from the [Rafiki documentation|https://nginyc.github.io/rafiki/docs/docs/user/quickstart.html]
example.
{code:sql}
create table test (image varchar(255), author varchar(255));
insert into test values ('{"query": [[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 1, 0, 0, 7, 0, 37, 0, 0], [0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 0, 27, 84, 11, 0, 0, 0, 0, 0, 0, 119, 0, 0], [0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 88, 143, 110, 0, 0, 0, 0, 22, 93, 106, 0, 0], [0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 0, 53, 129, 120, 147, 175, 157, 166, 135, 154, 168,
140, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 11, 137, 130, 128, 160, 176, 159, 167,
178, 149, 151, 144, 0, 0], [0, 0, 0, 0, 0, 0, 1, 0, 2, 1, 0, 3, 0, 0, 115, 114, 106, 137,
168, 153, 156, 165, 167, 143, 157, 158, 11, 0], [0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 3, 0, 0, 89,
139, 90, 94, 153, 149, 131, 151, 169, 172, 143, 159, 169, 48, 0], [0, 0, 0, 0, 0, 0, 2, 4,
1, 0, 0, 0, 98, 136, 110, 109, 110, 162, 135, 144, 149, 159, 167, 144, 158, 169, 119, 0],
[0, 0, 2, 2, 1, 2, 0, 0, 0, 0, 26, 108, 117, 99, 111, 117, 136, 156, 134, 154, 154, 156, 160,
141, 147, 156, 178, 0], [3, 0, 0, 0, 0, 0, 0, 21, 53, 92, 117, 111, 103, 115, 129, 134, 143,
154, 165, 170, 154, 151, 154, 143, 138, 150, 165, 43], [0, 0, 23, 54, 65, 76, 85, 118, 128,
123, 111, 113, 118, 127, 125, 139, 133, 136, 160, 140, 155, 161, 144, 155, 172, 161, 189,
62], [0, 68, 94, 90, 111, 114, 111, 114, 115, 127, 135, 136, 143, 126, 127, 151, 154, 143,
148, 125, 162, 162, 144, 138, 153, 162, 196, 58], [70, 169, 129, 104, 98, 100, 94, 97, 98,
102, 108, 106, 119, 120, 129, 149, 156, 167, 190, 190, 196, 198, 198, 187, 197, 189, 184,
36], [16, 126, 171, 188, 188, 184, 171, 153, 135, 120, 126, 127, 146, 185, 195, 209, 208,
255, 209, 177, 245, 252, 251, 251, 247, 220, 206, 49], [0, 0, 0, 12, 67, 106, 164, 185, 199,
210, 211, 210, 208, 190, 150, 82, 8, 0, 0, 0, 178, 208, 188, 175, 162, 158, 151, 11], [0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]]}', 'Someone');
select author, ImageClass(image) from test;
{code}
5. Call the SQLite+Rafiki with the test SQL statements. Assuming Rafiki is running and the
steps in the quickstart example are done to create an inference job.
{code:java}
./sqlite-rafiki < test.sql 
{code}
The result should be:
{code:java}
Someone|{
  "prediction": 9
}
{code}

May be we can add this to Rafiki code or documentation as an example for calling prediction
functions from SQL.

> Call Rafiki from SQL user-defined functions
> -------------------------------------------
>
>                 Key: SINGA-404
>                 URL: https://issues.apache.org/jira/browse/SINGA-404
>             Project: Singa
>          Issue Type: Improvement
>            Reporter: Moaz Reyad
>            Priority: Major
>
> This ticket is to propose a new implementation of the case study defined in the [Rafiki
paper|https://arxiv.org/pdf/1804.06087.pdf]. The case study presented a user-defined function
(UDF) which is called from SQL to send a request to Rafiki and return the prediction. The
SQL user does not need to know how Rafiki or deep learning works.
> Here is a Python implementation of the case study for prediction that works with the
new Rafiki API. The goal is to support the prediction function first, then later we can add
the training function also.
> The user defined function in Python:
> {code:python}
> import json
> def predict(query):
>         url = 'http://127.0.0.1:30000/predict'
>         headers = {'content-type': 'application/json', 'Accept-Charset': 'UTF-8'}
>         r = requests.post(url, data=query, headers=headers)
>         return json.loads(r.text)['prediction']
> {code}
> Next we add the UDF to SQLite (in memory) database. Let's call it ImageClass because
it returns the class of the image.
> {code:python}
> import sqlite3
> con = sqlite3.connect(":memory:")
> con.create_function("ImageClass", 1, predict)
> {code}
> An example of calling the UDF. Let's create a table and insert a row of data. This data
is taken from the [Rafiki documentation.|https://nginyc.github.io/rafiki/docs/docs/user/quickstart.html]
> {code:python}
> cur = con.cursor()
> cur.execute("create table test (image varchar(255), author varchar(255))")
> cur.execute("insert into test values ('{\"query\": [[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 1, 0, 0, 7, 0,
37, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 0, 27, 84, 11, 0, 0, 0, 0, 0, 0,
119, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 88, 143, 110, 0, 0, 0, 0, 22,
93, 106, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 0, 53, 129, 120, 147, 175, 157,
166, 135, 154, 168, 140, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 11, 137, 130, 128,
160, 176, 159, 167, 178, 149, 151, 144, 0, 0], [0, 0, 0, 0, 0, 0, 1, 0, 2, 1, 0, 3, 0, 0,
115, 114, 106, 137, 168, 153, 156, 165, 167, 143, 157, 158, 11, 0], [0, 0, 0, 0, 1, 0, 0,
0, 0, 0, 3, 0, 0, 89, 139, 90, 94, 153, 149, 131, 151, 169, 172, 143, 159, 169, 48, 0], [0,
0, 0, 0, 0, 0, 2, 4, 1, 0, 0, 0, 98, 136, 110, 109, 110, 162, 135, 144, 149, 159, 167, 144,
158, 169, 119, 0], [0, 0, 2, 2, 1, 2, 0, 0, 0, 0, 26, 108, 117, 99, 111, 117, 136, 156, 134,
154, 154, 156, 160, 141, 147, 156, 178, 0], [3, 0, 0, 0, 0, 0, 0, 21, 53, 92, 117, 111, 103,
115, 129, 134, 143, 154, 165, 170, 154, 151, 154, 143, 138, 150, 165, 43], [0, 0, 23, 54,
65, 76, 85, 118, 128, 123, 111, 113, 118, 127, 125, 139, 133, 136, 160, 140, 155, 161, 144,
155, 172, 161, 189, 62], [0, 68, 94, 90, 111, 114, 111, 114, 115, 127, 135, 136, 143, 126,
127, 151, 154, 143, 148, 125, 162, 162, 144, 138, 153, 162, 196, 58], [70, 169, 129, 104,
98, 100, 94, 97, 98, 102, 108, 106, 119, 120, 129, 149, 156, 167, 190, 190, 196, 198, 198,
187, 197, 189, 184, 36], [16, 126, 171, 188, 188, 184, 171, 153, 135, 120, 126, 127, 146,
185, 195, 209, 208, 255, 209, 177, 245, 252, 251, 251, 247, 220, 206, 49], [0, 0, 0, 12, 67,
106, 164, 185, 199, 210, 211, 210, 208, 190, 150, 82, 8, 0, 0, 0, 178, 208, 188, 175, 162,
158, 151, 11], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]]}', 'Someone')")
> {code}
>  
> If Rafiki prediction service is running on the specified address, we can call the Select
statement and use Rafiki prediction to return the image class of the given image.
> {code:python}
> cur.execute("select author, ImageClass(image) from test")
> print(cur.fetchone())
> {code}
> The result is: 
> {code:bash}
> ('Someone', 9)
> {code}
> More complex Select statements can be written with Joins, Group BY and Order By, ...
etc.
> If this implementation is useful, we may add it to Rafiki client as an experimental feature.




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message