RESTful API Overview

Representational State Transfer (REST) ​​is a type of software architecture style that consists of guidelines and best practices for interfaces provided by scalable Web services.

The four methods defined in the HTTP protocol define the CRUD for the resource.

HTTP Method

Meaning

POST

Create

GET

Select

PUT

Update

DELETE

Delete

Machbase is not a standard RESTful API, but rather a RESTful API that handles CRUD using only POST and GET methods.

That is, the POST method is used for data input and the rest is transmitted as a GET Method parameter to the SQL query so that all the operations can be performed.


Index


Using Embedded web server (version 6.5 and after)

From version 6.5, MachBase supports convenient and fast Rest API functions through the web server built into the server. 

Machbase Edition support embedded web server

All type of machbase editions are supported. (Edge / Fog / Cluster)

Location of version-specific .conf files 
 

Edge/Fog version

$MACHBASE_HOME/conf/machbase.conf $MACHBASE_HOME/http/conf/http.conf

Cluster version

$EACH_BROKER_HOME/conf/machbase.conf (Modify by Broker) $EACH_BROKER_HOME/http/conf/http.conf (modify all per Broker)

Added properties for embedded web server

machbase.conf (set as PROPERTY = VALUE)

Property

Description

HTTP_ENABLE

Whether to run the embedded web server 0: not driven, 1: driven

HTTP_PORT_NOEmbedded web server connection port number Port range: 0 ~ 65535 Default : 5657
HTTP_MAX_MEM Maximum memory used by one Web Session Min: 1048576 (1MB) Default : 536870912 (512MB
HTTP_AUTH Whether to use Basic authentication when using the Embedded Web Server 0: Authentication not used, 1: Authentication enabled


http.conf (set in JSON format)

Property

Description

document_root 

html file location based on $MACHBASE_HOME

Default : http/html ($MACHBASE_HOME/http/html) 

max_request_size Limit the maximum request byte size for one request
request_timeout_ms Maximum response latency for one request (millisecond) 
enable_auth_domain_checkWhether to enable domain authentication Set to "yes" or "no" value Default: "no" 
reverse_proxychange request url to specific url



sample conf files

machbase.conf

machbase.conf
#################################################################################
# Rest-API port
#################################################################################
HTTP_PORT_NO = 5657
  
#################################################################################
# Maximum memory per web session.
# Default Value: 536870912 (512MB)
#################################################################################
HTTP_MAX_MEM = 536870912
  
#################################################################################
# Min Value:     0
# Max Value:     1
# Default Value: 0
#
# Enable REST-API service.
#################################################################################
HTTP_ENABLE = 0
  
#################################################################################
# Min Value:     0
# Max Value:     1
# Default Value: 0
#
# Enable Basic Authentication for Rest-API service
#################################################################################
HTTP_AUTH = 0


http.conf

http.conf
{
    "document_root":"http/html/",
    "max_request_size": "100000",
    "request_timeout_ms": "10000",
    "enable_auth_domain_check": "no",
    "reverse_proxy" : [["/machbase/tables", "http://127.0.0.1:55657/machbase"],
        ["/self_machbase_proxy", "http://127.0.0.1:55657/machbase"],
        ["/dead_proxy", "http://127.0.0.0/machbase"]]
}

DDL / DML / AppendUsing REST API

Basic request format
 
http://addr:port/machbase?q=query&f=dateformat
 
Response DDL / Append / DML (except Select)
{"error_code":0, "error_message" :"Message", "data":[]}
 
Response DML (Select)
{"error_code":0, "error_message" :"Message", "columns":[Columns], "data":[Data]}


DDL Sample

## Request of creating a table
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=create table test_table (name varchar(20), time datetime, value double)'
  
## Normal response
{"error_code":0, "error_message" :"No Error", "data":[]}
  
## Request of dropping a table
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=drop table test_table'
  
## Normal response
{"error_code":0, "error_message" :"No Error", "data":[]}


DML Sample

## Request Log table data insert
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=insert into test_table values ("test", "1999-01-01 00:00:00", 0)'
  
## Response
{"error_code":0, "error_message" :"No Error", "data":[]}
  
## Request Log table select
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from test_table'
  
## Response
{"error_code":0, "error_message": "", "columns" : [{"name":"NAME", "type":5, "length":20},{"name":"TIME", "type":6, "length":8},{"name":"VALUE", "type":20, "length":8}],"data" :[{"NAME":"test", "TIME":"1999-01-01 00:00:00 000:000:000", "VALUE":0.000}]}


Append Sample

## Append some data to log table
curl -X POST -H "Content-Type: application/json" "http://127.0.0.1:5657/machbase" -d '{"name":"test_table", "date_format":"YYYY-MM-DD","values":[["test", "1999-01-01 00:00:01", 1], ["test", "1999-01-01 00:00:02", 2], ["test", "1999-01-01 00:00:03", 3]]}'
  
## Response
{"error_code":0, "error_message" :"No Error", "data":[], "append_success":3, "append_failure":0}



In the case of Binary Append, if binary data is encoded in Base64 and transmitted, the server will decode it and store it. When outputting, binary data is returned after being encoded in Base64.

Input : Binary Data >> Base64 Encoding >> HTTP(POST) >> Base64 Decoding >> Append(BLOB Binary)

Output : BLOB Binary >> Base64 Encoding >> HTTP (GET) >> Base64 Decoding >> Save or View Binary

Binary Append Sample

## Example of sending binary data. data should be encoded by Base64.
  
## Request append to log table
curl  -X POST -H "Content-Type: application/json" "http://127.0.0.1:5657/machbase" -d '{"name":"test_table", "date_format":"YYYY-MM-DD","values":[["AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh8gISIjJCUmJygpKissLS4vMDEyMzQ1Njc4OTo7PD0+P0BBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWltcXV5fYGFiY2RlZmdoaWprbG1ub3BxcnN0dXZ3eHl6e3x9fn+AgYKDhIWGh4iJiouMjY6PkJGSk5SVlpeYmZqbnJ2en6ChoqOkpaanqKmqq6ytrq+wsbKztLW2t7i5uru8vb6/wMHCw8TFxsfIycrLzM3Oz9DR0tPU1dbX2Nna29zd3t/g4eLj5OXm5+jp6uvs7e7v8PHy8/T19vf4+fr7/P3+/w=="]]}'
  
## Result
{"error_code":0, "error_message" :"No Error", "data":[], "append_success":1, "append_failure":0}
  
## Get data from log table
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from test_table';
  
## The Base64 encoded data are displaied
{"error_code" :0, "error_message": "No Error", "columns" : [{"name":"V1", "type":57, "length":67108864}],"data" :[{"V1":"AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh8gISIjJCUmJygpKissLS4vMDEyMzQ1Njc4OTo7PD0+P0BBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWltcXV5fYGFiY2RlZmdoaWprbG1ub3BxcnN0dXZ3eHl6e3x9fn+AgYKDhIWGh4iJiouMjY6PkJGSk5SVlpeYmZqbnJ2en6ChoqOkpaanqKmqq6ytrq+wsbKztLW2t7i5uru8vb6/wMHCw8TFxsfIycrLzM3Oz9DR0tPU1dbX2Nna29zd3t/g4eLj5OXm5+jp6uvs7e7v8PHy8/T19vf4+fr7/P3+/w=="}]}
  
## Can check data using machsql
select to_hex(v1) from test_table;
to_hex(v1)                                                                      
------------------------------------------------------------------------------------
000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F2021222324252627
28292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F
505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F7071727374757677
78797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F
A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7
C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF
F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF                                                
[1] row(s) selected.

Using HTTP Auth Property

This is an option to set authentication as a normal user by including the string 'Authorization: Basic Base64String' in the Request Header. Base64 string is written in ID@Host:Password structure. (However, the host name does not need to be correct. ID and password must be entered in Machbase user information.)

How to create a Basic Base64String for authorize

## In case of ID: sys, Password: manager , creating a Base64String
echo -n "sys@localhost:manager" | base64
  
## Result
c3lzQGxvY2FsaG9zdDptYW5hZ2Vy

Using Auth Sample (HTTP_AUTH = 1)

## Request of result withoutauthorization clause
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from v$stmt'
  
## Error occurred
{"error_code":3118, "error_message" :"There is No Authorization Header.", "data":[]}
  
## Adding'Authorization:Base64String' at the request header
curl -H "Authorization: Basic c3lzQGxvY2FsaG9zdDptYW5hZ2Vy"  -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from v$stmt'
  
## Normal response
{"error_code":0, "error_message": "No Error", "columns" : [{"name":"ID", "type":8, "length":4},{"name":"SESS_ID", "type":8, "length":4},{"name":"STATE", "type":5, "length":64},{"name":"RECORD_SIZE", "type":8, "length":4},{"name":"QUERY", "type":5, "length":32767}],"data" :[{"ID":0, "SESS_ID":52, "STATE":"Fetch prepared", "RECORD_SIZE":0, "QUERY":"select * from v$stmt"}]}


Changing floating point precision with s option

Specify how many decimal places of response data to output Set to a value from 0 to 9 (If it is not a range value, it operates as 3)

Sample (s=5)

## display result 5 decimal places
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from test_table' --data-urlencode 's=5';
  
## Normal response
{"error_code" :0, "error_message": "", "columns" : [{"name":"C1", "type":16, "length":4},{"name":"C2", "type":20, "length":8}],"data" :[{"C1":12345.00000, "C2":1234.01235}]}

Changing data Fetch mode (m option)

Decide whether to always display column names in response data (0: display, 1: do not display)

Default Fetch mode Sample (m=0)

## Requestfetch mode (m=0)
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from tag limit 2' --data-urlencode 'm=0';
  
## Normal response (ConatainsColumn Name in result data)
{"error_code" :0, "error_message": "", "columns" : [{"name":"NAME", "type":5, "length":20},{"name":"TIME", "type":6, "length":8},{"name":"VALUE", "type":20, "length":8}],
"data" :[{"NAME":"tag1", "TIME":"2001-09-09 10:46:40 000:000:000", "VALUE":1000000000.000}, {"NAME":"tag1", "TIME":"2001-09-09 10:46:41 000:000:000", "VALUE":1000000001.000}]}


Advanced Fetch mode Sample (m=1)

## Requestfetch mode (m=1)
curl -G "http://127.0.0.1:5657/machbase" --data-urlencode 'q=select * from tag limit 2' --data-urlencode 'm=1';
  
## Normal response (Column Namesare not included in results)
{"error_code" :0, "error_message": "", "columns" : [{"name":"NAME", "type":5, "length":20},{"name":"TIME", "type":6, "length":8},{"name":"VALUE", "type":20, "length":8}],
"data" :[["tag1", "2001-09-09 10:46:40 000:000:000", 1000000000.000], ["tag1", "2001-09-09 10:46:41 000:000:000", 1000000001.000]]}
Handling of NULL values
When inserting or appending during DML processing, a NULL value can be entered as null as it is.

JSON Sample for appending null values

[["data1", "data2", "data3"],["data11", "data12", "data13"],["data21", "data22", "data23"],[null,null,null]]



Sample result containing null values from SELECT

[{"C1":null, "C2":null, "C3":null, "C4":null, "C5":null, "C6":null, "C7":null, "C8":null, "C9":null, "C10":null, "C11":null, "C12":null}]




How To Use : Use MWA as a REST gateway(before 6.5 version)



Preferences


If $MACHBASE_HOME/webadmin/flask/MWAserver start is used to run Machbase Web Analytics (MWA), you can access the Machbase server using the Restful API.

[mach@localhost]$ cd $MACHBASE_HOME/bin
[mach@localhost]$ MWAserver start

The Machbase RESTful API can be used in conjunction with the MWA server configuration. When using the RESTful API, specify the server ID or Server name to use as the 's' parameter. 
ex) The following example sends the query to the server whose ID is 3 (or server name is sample_server) among the servers set in MWA and receives the result

curl -G "http://127.0.0.1:5001/machbase" --data-urlencode 'q=select * from m$sys_tables' --data 's=3'
# or
curl -G "http://127.0.0.1:5001/machbase" --data-urlencode 'q=select * from m$sys_tables' --data 's=sample_server'


If it can not find the ID or name of the specified server, it returns the following json with a 404 error.

curl -G "http://127.0.0.1:5001/machbase" --data-urlencode 'q=select * from m$sys_tables' --data 's=not_exist'
{
  "error": "Cannot found server."
}


Default URL


The default URL for the Restful API is:

http://hostname: port can be found by running the MWAserver start command to run MWA with the url to connect to the MWA. 


Data extraction


Retrieves the data using the HTTP GET method. The return value is a json type. You can pass the query statement you want to execute using the 'q' parameter. The query below brings up a list of tables.

curl -G "http://127.0.0.1:5001/machbase" --data-urlencode 'q=select * from m$sys_tables'


Data Input

You can use the HTTP POST method to send the input value of the json type as a parameter. Below is an example of entering data into a test_table with three columns.
First, we execute a query that creates a table using the curl GET method.

curl -X GET "http://127.0.0.1:5001/machbase" --data-urlencode 'q=create table test_table(c1 short, c2 integer, c3 varchar(20))'


Enter data using the POST method using the curl command. The data input json can use four keys, and the 'name' and 'values' keys must be entered.

curl -X POST -H "Content-Type: application/json" "http://127.0.0.1:5001/machbase" -d '{"name":"test_table", "values":[[1,2,"aaa"],[3,4,"bbb"]]}'


Key

Description

Remarks

name

Input table name

Required

values

Input data to be input as a 2-dimensional array

Required

date_format

Date format to be used

If data type is used

s

Server ID or name

Server defined in MWA


If you enter a date type, you must specify the date format. The date format must use the pattern used in Machbase. If not specified otherwise, YYYY-MM-DD is set to HH24: MI: SS mmm: uuu: nnn Machbase default format and the result value will not be returned properly if date format is not correct. When specifying date and time pattern, you should be careful of case sensitive.

curl -X GET "http://127.0.0.1:5001/machbase" --data-urlencode 'q=create table test_date(c1 datetime)'

curl -X POST -H "Content-Type: application/json" "http://127.0.0.1:5001/machbase" -d '{"name":"test_date",  "date_format":"YYYY-MM-DD HH24:MI:SS", "values":[["2015-02-01 12:13:14"],["2015-02-11 07:08:09"]]}'


Deleting Data

Standard RESTful APIs should use the HTTP DELETE method, but Machbase methods use the HTTP GET Method to send a delete query. Below is an example of deleting data from test_table.

curl -G "http://127.0.0.1:5001/machbase" --data-urlencode 'q=delete from test_table except 1 rows'


Creating, Altering, and Dropping Table

In Machbase, any query can be performed using the HTTP GET method. Therefore, the TABLE CREATE, ALTER, and DROP commands are also available.

curl -G "http://127.0.0.1:5001/machbase" --data-urlencode 'q=drop table test_table'



  • No labels