Query via http

There are three different ways of executing SQL statement via HTTP. Those api support not only “SELECT” but also “CREATE TABLE”, “ALTER TABLE”, “INSERT”… all other SQL statements.

The query API supports GET, POST JSON and POST form-data. all those methods have the same parameters.

For example the parameter format can be specified in query parameter in GET method like GET /db/query?format=csv, or be a JSON field in POST-JSON method as { "format": "csv" }.

Query Example

http://127.0.0.1:5654/db/query?q=select%20*%20from%20EXAMPLE%20limit%202

Query Parameters

param	default	description
q	required	SQL query string
format	`json`	Result data format: json, csv, box
timeformat	`ns`	Time format: s, ms, us, ns
tz	`UTC`	Time Zone: UTC, Local and location spec
compress	no compression	compression method: gzip
rownum	`false`	including rownum: true, false
heading	`true`	showing heading: true, false
precision	`-1`	precision of float value, -1 for no round, 0 for int

Available parameters with format=json Since v8.0.12

The options are only available when format=json. Those options are exclusive each other, applicable only one of them per a request.

param	default	description
transpose	false	produce cols array instead of rows.
rowsFlatten	false	reduce the array dimension of the rows field in the JSON object.
rowsArray	false	produce JSON that contains only array of object for each record.

Available timeformat

timeformat	result
`Default`	2006-01-02 15:04:05.999
`Numeric`	01/02 03:04:05PM ‘06 -0700
`Ansic`	Mon Jan _2 15:04:05 2006
`Unix`	Mon Jan _2 15:04:05 MST 2006
`Ruby`	Mon Jan 02 15:04:05 -0700 2006
`RFC822`	02 Jan 06 15:04 MST
`RFC822Z`	02 Jan 06 15:04 -0700
`RFC850`	Monday, 02-Jan-06 15:04:05 MST
`RFC1123`	Mon, 02 Jan 2006 15:04:05 MST
`RFC1123Z`	Mon, 02 Jan 2006 15:04:05 -0700
`RFC3339`	2006-01-02T15:04:05Z07:00
`RFC3339Nano`	2006-01-02T15:04:05.999999999Z07:00
`Kitchen`	3:04:05PM
`Stamp`	Jan _2 15:04:05
`StampMili`	Jan _2 15:04:05.000
`StampMicro`	Jan _2 15:04:05.000000
`StampNano`	Jan _2 15:04:05.000000000

GET

Response in JSON format (default)

Set query param format=json or omit it for the default value.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2"

The server responses in Content-Type: application/json.

name	type	description
success	bool	`true` if query execution succeed
reason	string	execution result message, this will contains error message if `success` is `false`
elapse	string	elapse time of the query execution
data		exists only when execution succeed
data.columns	array of strings	represents columns of result
data.types	array of strings	represents data types of result
data.rows	array of records	array represents the result set records. This field will be replaced with `cols` if `transpose` is `true`
data.cols	array of series	array represents the result set column-series. This element exists when `transpose` is `true`

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      [ "wave.sin", 1705381958775759000, 0.8563571936170834 ],
      [ "wave.sin", 1705381958785759000, 0.9011510331449053 ],
      [ "wave.sin", 1705381958795759000, 0.9379488170706388 ]
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "1.887042ms"
}

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE" \
      --data-urlencode "transpose=true"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "cols": [
      [ "wave.sin", "wave.sin", "wave.sin" ],
      [ 1705381958775759000, 1705381958785759000, 1705381958795759000 ],
      [ 0.8563571936170834, 0.9011510331449053, 0.9379488170706388 ]
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "4.090667ms"
}

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE" \
      --data-urlencode "rowsFlatten=true"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      "wave.sin", 1705381958775759000, 0.8563571936170834,
      "wave.sin", 1705381958785759000, 0.9011510331449053,
      "wave.sin", 1705381958795759000, 0.9379488170706388
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "2.255625ms"
}

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE" \
      --data-urlencode "rowsArray=true"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      { "NAME": "wave.sin", "TIME": 1705381958775759000, "VALUE": 0.8563571936170834 },
      { "NAME": "wave.sin", "TIME": 1705381958785759000, "VALUE": 0.9011510331449053 },
      { "NAME": "wave.sin", "TIME": 1705381958795759000, "VALUE": 0.9379488170706388 }
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "3.178458ms"
}

Response in BOX format

Set query param format=box in the request.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2" --data-urlencode "format=box"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2" --data-urlencode "format=box"

The result data in plain text with ascii box. The Content-Type of the response is plain/text

+----------+---------------------+----------+
| NAME     | TIME(UTC)           | VALUE    |
+----------+---------------------+----------+
| wave.sin | 1676337568          | 0.000000 |
| wave.sin | 1676337569          | 0.406736 |
+----------+---------------------+----------+

Response in CSV format

Set query param format=csv in the request.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2" --data-urlencode "format=csv"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2" --data-urlencode "format=csv"

The response comes with Content-Type: text/csv

NAME,TIME,VALUE
wave.sin,1676337568,0.000000
wave.sin,1676337569,0.406736

Use machbase-neo help tz and machbase-neo help timeformat to see more available options for timezone and timeformat options

machbase-neo» help timeformat;
  timeformats:
    epoch
      ns             nanoseconds
      us             microseconds
      ms             milliseconds
      s              seconds
    abbreviations
      Default,-      2006-01-02 15:04:05.999
      Numeric        01/02 03:04:05PM '06 -0700
      Ansic          Mon Jan _2 15:04:05 2006
      Unix           Mon Jan _2 15:04:05 MST 2006
      Ruby           Mon Jan 02 15:04:05 -0700 2006
      RFC822         02 Jan 06 15:04 MST
      RFC822Z        02 Jan 06 15:04 -0700
      RFC850         Monday, 02-Jan-06 15:04:05 MST
      RFC1123        Mon, 02 Jan 2006 15:04:05 MST
      RFC1123Z       Mon, 02 Jan 2006 15:04:05 -0700
      RFC3339        2006-01-02T15:04:05Z07:00
      RFC3339Nano    2006-01-02T15:04:05.999999999Z07:00
      Kitchen        3:04:05PM
      Stamp          Jan _2 15:04:05
      StampMili      Jan _2 15:04:05.000
      StampMicro     Jan _2 15:04:05.000000
      StampNano      Jan _2 15:04:05.000000000
    custom format
      year           2006
      month          01
      day            02
      hour           03 or 15
      minute         04
      second         05 or with sub-seconds '05.999999999'

List of Time Zones - wikipedia.org

machbase-neo» help tz;
  timezones:
    abbreviations
      UTC
      Local
      Europe/London
      America/New_York
      ...
    location examples
      America/Los_Angeles
      Europe/Paris
      ...
    Time Coordinates examples
      UTC+9

POST JSON

It is also possible to request query in JSON form as below example.

Request JSON message

curl -o - -X POST http://127.0.0.1:5654/db/query \
    -H 'Content-Type: application/json' \
    -d '{ "q":"select * from EXAMPLE limit 2" }'

curl -o - -X POST http://127.0.0.1:5654/db/query ^
    -H "Content-Type: application/json" ^
    -d "{ \"q\":\"select * from EXAMPLE limit 2\" }"

POST Form

HTML Form data format is available too. HTTP header Content-type should be application/x-www-form-urlencoded in this case.

curl -o - -X POST http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2"

curl -o - -X POST http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2"

Examples

Please refer to the detail of the API

For this tutorials, pre-write data below.

Data file

Copy and paste on a new file data-nano-1.json.

{
    "data":{
      "columns":["NAME","TIME","VALUE"],
      "rows":[
          ["wave.sin",1676432361,0],
          ["wave.sin",1676432362,0.406736],
          ["wave.sin",1676432363,0.743144],
          ["wave.sin",1676432364,0.951056],
          ["wave.sin",1676432365,0.994522]
      ]
    }
}

Or download it from here.

Create table

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode \
    "q=create tag table EXAMPLE (name varchar(40) primary key, time datetime basetime, value double)"

Write data

curl -X POST http://127.0.0.1:5654/db/write/EXAMPLE?timeformat=ns \
    -H "Content-Type: application/json"                           \
    --data-binary "@data-nano-1.json"

Select in CSV

Request

Set the format=csv query param for CSV format.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=csv"

Response

NAME,TIME,VALUE
wave.sin,1676432361000000000,0.111111
wave.sin,1676432362111111111,0.222222
wave.sin,1676432363222222222,0.333333
wave.sin,1676432364333333333,0.444444
wave.sin,1676432365444444444,0.555555

Select in BOX

Request

Set the format=box query param for BOX format.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"

Response

+----------+---------------------+----------+
| NAME     | TIME                | VALUE    |
+----------+---------------------+----------+
| wave.sin | 1676432361000000000 | 0        |
| wave.sin | 1676432362111111111 | 0.406736 |
| wave.sin | 1676432363222222222 | 0.743144 |
| wave.sin | 1676432364333333333 | 0.951056 |
| wave.sin | 1676432365444444444 | 0.994522 |
+----------+---------------------+----------+

Select in BOX with rownum

Request

Set the format=box query param for BOX format.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "rownum=true"

Response

+--------+----------+---------------------+----------+
| ROWNUM | NAME     | TIME                | VALUE    |
+--------+----------+---------------------+----------+
|      1 | wave.sin | 1676432361000000000 | 0.111111 |
|      2 | wave.sin | 1676432362111111111 | 0.222222 |
|      3 | wave.sin | 1676432363222222222 | 0.333333 |
|      4 | wave.sin | 1676432364333333333 | 0.444444 |
|      5 | wave.sin | 1676432365444444444 | 0.555555 |
+--------+----------+---------------------+----------+

Select in BOX without heading

Request

Set the format=box and heading=false query param for BOX format without header.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "heading=false"

Response

+----------+---------------------+----------+
| wave.sin | 1676432361000000000 | 0        |
| wave.sin | 1676432362111111111 | 0.406736 |
| wave.sin | 1676432363222222222 | 0.743144 |
| wave.sin | 1676432364333333333 | 0.951056 |
| wave.sin | 1676432365444444444 | 0.994522 |
+----------+---------------------+----------+

Select in BOX value in INTEGER

Request

Set the format=box and precision=0 query param for BOX format with integer precision.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "precision=0"

Response

+----------+---------------------+-------+
| NAME     | TIME                | VALUE |
+----------+---------------------+-------+
| wave.sin | 1676432361000000000 | 0     |
| wave.sin | 1676432362111111111 | 0     |
| wave.sin | 1676432363222222322 | 0     |
| wave.sin | 1676432364333333233 | 0     |
| wave.sin | 1676432365444444444 | 1     |
+----------+---------------------+-------+

Select in Default Time

Request

Set query param timeformat=Default

 curl -o - http://127.0.0.1:5654/db/query      \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=Default"

Response

+----------+-------------------------+----------+
| NAME     | TIME                    | VALUE    |
+----------+-------------------------+----------+
| wave.sin | 2023-02-15 03:39:21     | 0.111111 |
| wave.sin | 2023-02-15 03:39:22.111 | 0.222222 |
| wave.sin | 2023-02-15 03:39:23.222 | 0.333333 |
| wave.sin | 2023-02-15 03:39:24.333 | 0.444444 |
| wave.sin | 2023-02-15 03:39:25.444 | 0.555555 |
+----------+-------------------------+----------+

Select rows in default time format with `Asia/Seoul`

Request Set query param timeformat=Default and tz=Asia/Seoul

 curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=Default"      \
    --data-urlencode "tz=Asia/Seoul"

Response

 +----------+-------------------------+----------+
| NAME     | TIME                    | VALUE    |
+----------+-------------------------+----------+
| wave.sin | 2023-02-15 12:39:21     | 0.111111 |
| wave.sin | 2023-02-15 12:39:22.111 | 0.222222 |
| wave.sin | 2023-02-15 12:39:23.222 | 0.333333 |
| wave.sin | 2023-02-15 12:39:24.333 | 0.444444 |
| wave.sin | 2023-02-15 12:39:25.444 | 0.555555 |
+----------+-------------------------+----------+

Select rows in `RFC3339`

Request

Set query param timeformat=Default

 curl -o - http://127.0.0.1:5654/db/query      \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=RFC3339"

Response

+----------+----------------------+----------+
| NAME     | TIME                 | VALUE    |
+----------+----------------------+----------+
| wave.sin | 2023-02-15T03:39:21Z | 0.111111 |
| wave.sin | 2023-02-15T03:39:22Z | 0.222222 |
| wave.sin | 2023-02-15T03:39:23Z | 0.333333 |
| wave.sin | 2023-02-15T03:39:24Z | 0.444444 |
| wave.sin | 2023-02-15T03:39:25Z | 0.555555 |
+----------+----------------------+----------+

Select in RFC3339 with Nano precision

Request

Set query param timeformat=Default

 curl -o - http://127.0.0.1:5654/db/query      \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=RFC3339Nano"

Response

 +----------+--------------------------------+----------+
| NAME     | TIME                           | VALUE    |
+----------+--------------------------------+----------+
| wave.sin | 2023-02-15T03:39:21Z           | 0.111111 |
| wave.sin | 2023-02-15T03:39:22.111111111Z | 0.222222 |
| wave.sin | 2023-02-15T03:39:23.222222322Z | 0.333333 |
| wave.sin | 2023-02-15T03:39:24.333333233Z | 0.444444 |
| wave.sin | 2023-02-15T03:39:25.444444444Z | 0.555555 |
+----------+--------------------------------+----------+

Select in RFC3339 with Nano precision in America/New_York Time Zone

Request

Set query param timeformat=Default

 curl -o - http://127.0.0.1:5654/db/query      \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=RFC3339Nano"  \
    --data-urlencode "tz=America/New_York"

Response

+----------+-------------------------------------+----------+
| NAME     | TIME                                | VALUE    |
+----------+-------------------------------------+----------+
| wave.sin | 2023-02-14T22:39:21-05:00           | 0.111111 |
| wave.sin | 2023-02-14T22:39:22.111111111-05:00 | 0.222222 |
| wave.sin | 2023-02-14T22:39:23.222222222-05:00 | 0.333333 |
| wave.sin | 2023-02-14T22:39:24.333333333-05:00 | 0.444444 |
| wave.sin | 2023-02-14T22:39:25.444444444-05:00 | 0.555555 |
+----------+-------------------------------------+----------+

Select in Custom Time Format

In Machbase Neo Rest API, specific numbers are used to replace the format of certain time. Set query param timeformat=<custom-format-as-below>

 custom format
      year           2006
      month          01
      day            02
      hour           03 or 15
      minute         04
      second         05 or with sub-seconds '05.999999999'

Typical Custom Time Format

Request

 curl -o - http://127.0.0.1:5654/db/query      \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=2006-01-02 03:04:05.999999999"

Response

+----------+-------------------------------+----------+
| NAME     | TIME                          | VALUE    |
+----------+-------------------------------+----------+
| wave.sin | 2023-02-15 03:39:21           | 0.111111 |
| wave.sin | 2023-02-15 03:39:22.111111111 | 0.222222 |
| wave.sin | 2023-02-15 03:39:23.222222222 | 0.333333 |
| wave.sin | 2023-02-15 03:39:24.333333333 | 0.444444 |
| wave.sin | 2023-02-15 03:39:25.444444444 | 0.555555 |
+----------+-------------------------------+----------+

Typical Re-ordered Custom Time Format

Request

 curl -o - http://127.0.0.1:5654/db/query      \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "timeformat=03:04:05.999999999-ReOrder-2006-01-02 "

Response

+----------+----------------------------------------+----------+
| NAME     | TIME                                   | VALUE    |
+----------+----------------------------------------+----------+
| wave.sin | 03:39:21-ReOrder-2023-02-15            | 0.111111 |
| wave.sin | 03:39:22.111111111-ReOrder-2023-02-15  | 0.222222 |
| wave.sin | 03:39:23.222222222-ReOrder-2023-02-15  | 0.333333 |
| wave.sin | 03:39:24.333333333-ReOrder-2023-02-15  | 0.444444 |
| wave.sin | 03:39:25.444444444-ReOrder-2023-02-15  | 0.555555 |
+----------+----------------------------------------+----------+

Typical Re-ordered Custom Time Format in America/New_York Time Zone

Request

 curl -o - http://127.0.0.1:5654/db/query                                 \
    --data-urlencode "q=select * from EXAMPLE"                            \
    --data-urlencode "format=box"                                         \
    --data-urlencode "timeformat=03:04:05.999999999-ReOrder-2006-01-02 "  \
    --data-urlencode "tz=America/New_York"

Response

+----------+----------------------------------------+----------+
| NAME     | TIME                                   | VALUE    |
+----------+----------------------------------------+----------+
| wave.sin | 10:39:21-ReOrder-2023-02-14            | 0.111111 |
| wave.sin | 10:39:22.111111111-ReOrder-2023-02-14  | 0.222222 |
| wave.sin | 10:39:23.222222222-ReOrder-2023-02-14  | 0.333333 |
| wave.sin | 10:39:24.333333333-ReOrder-2023-02-14  | 0.444444 |
| wave.sin | 10:39:25.444444444-ReOrder-2023-02-14  | 0.555555 |
+----------+----------------------------------------+----------+

Last updated on Apr 15, 2024

Create, Drop table Write via http

Query via http

GET

POST JSON

POST Form

Examples

Data file

Create table

Write data

Select in CSV

Select in BOX

Select in BOX with rownum

Select in BOX without heading

Select in BOX value in INTEGER

Select in Default Time

Select rows in default time format with Asia/Seoul

Select rows in RFC3339

Select in RFC3339 with Nano precision

Select in RFC3339 with Nano precision in America/New_York Time Zone

Select in Custom Time Format

Typical Custom Time Format

Typical Re-ordered Custom Time Format

Typical Re-ordered Custom Time Format in America/New_York Time Zone

Select rows in default time format with `Asia/Seoul`

Select rows in `RFC3339`