As Writing API

ℹ️

For the examples, create a table with the following SQL statements.

CREATE TAG TABLE IF NOT EXISTS EXAMPLE (
    NAME VARCHAR(20) PRIMARY KEY,
    TIME DATETIME BASETIME,
VALUE DOUBLE SUMMARIZED);

Write CSV with `INSERT`

Save tql file

Save the code below as input-csv.tql.

CSV(payload(), 
    field(0, stringType(), 'name'),
    field(1, datetimeType('ns'), 'time'),
    field(2, doubleType(), 'value'),
    header(false)
)
INSERT("name", "time", "value", table("example"))

Client POST request

Prepare data file as input-csv.csv

TAG0,1628866800000000000,12
TAG0,1628953200000000000,13

Invoke input-csv.tql with the data file with curl command

curl -X POST http://127.0.0.1:5654/db/tql/input-csv.tql \
    -H "Content-Type: application/csv" \
    --data-binary "@input-csv.csv"

Write CSV with `APPEND`

Save tql file

Save the code below as append-csv.tql.

CSV(payload(), 
    field(0, stringType(), 'name'),
    field(1, datetimeType('ns'), 'time'),
    field(2, doubleType(), 'value'),
    header(false)
)
APPEND(table('example'))

Client POST request

Prepare data file as append-csv.csv

TAG0,1628866800000000000,12
TAG0,1628953200000000000,13

Invoke append-csv.tql with the data file with curl command

curl -X POST http://127.0.0.1:5654/db/tql/append-csv.tql \
    -H "Content-Type: application/csv" \
    --data-binary "@append-csv.csv"

Write JSON in custom format

Save tql file

Use SCRIPT() function to parse a custom format JSON.

BYTES(payload())
SCRIPT({
  json := import("json")
  ctx := import("context")
  val := ctx.value()
  obj := json.decode(val[0])
  for i := 0; i < len(obj.data.rows); i++ {
    ctx.yield(obj.data.rows[i][0], obj.data.rows[i][1], obj.data.rows[i][2])
  }
})
INSERT("name", "time", "value", table("example"))

Client POST request

Prepare data file as input-json.json

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      [ "TAG0", 1628866800000000000, 12 ],
      [ "TAG0", 1628953200000000000, 13 ]
    ]
  }
}

Invoke input-csv.tql with the data file with curl command

curl -X POST http://127.0.0.1:5654/db/tql/input-json.tql \
    -H "Content-Type: application/json" \
    --data-binary "@input-json.json"

Parse & write custom text data via MQTT & HTTP-POST

When the data transforming is required for writing to the database, prepare the proper tql script and publish the data to the topic named db/tql/+{tql_file.tql}.

The example code below shows how to handle multi-lines text data for writing into a table.

Transforming using MAP functions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// payload() returns the payload that arrived via HTTP-POST or MQTT,
// The ?? operator means that if tql is called without content,
//        the right side value is applied
// It is a good practice while the code is being developed on the tql editor of web-ui.
STRING(payload() ?? ` 12345
                     23456
                     78901
                     89012
                     90123
                  `, separator('\n'), trimspace(true))
FILTER( len(value(0)) > 0 )   // filter empty line
// transforming data
MAPVALUE(-1, time("now"))     // equiv. PUSHVALUE(0, time("now"))
MAPVALUE(-1, "text_"+key())   // equiv. PUSHVALUE(0, "text_"+key())
MAPVALUE(2, strSub( value(2), 0, 2 ) )

// Run this code in the tql editor of web-ui for testing
CSV( timeformat("DEFAULT") )
// Use APPEND(table('example')) for the real action
// APPEND(table('example'))

Alternative version of tranforming using SCRIPT()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
STRING(payload() ?? ` 12345
                     23456
                     78901
                     89012
                     90123
                  `, separator('\n'), trimspace(true))
FILTER( len(value(0)) > 0) // filter empty line
// transforming data
SCRIPT({
  text := import("text")
  times := import("times")
  ctx := import("context")
  key := ctx.key()
  values := ctx.value()
  str := text.trim_space(values[0]) // trim spaces
  str = text.substr(str, 0, 2)      // takes the first 2 letters of the line
  ctx.yieldKey(
      "text_"+key,                // compose new key (which is NAME column of the table)
      times.now(),                // current time
      text.parse_int(str, 10, 64) // convert to number type 
  )
})
CSV()

text_1,2023-12-02 11:03:36.054,12
text_2,2023-12-02 11:03:36.054,23
text_3,2023-12-02 11:03:36.054,78
text_4,2023-12-02 11:03:36.054,89
text_5,2023-12-02 11:03:36.054,90

Run the code above and if there is no error and works as expected, then replace the last line CSV() with APPEND(table('example')).

Save the code as “script-post-lines.tql”, then send some test data to the topic db/tql/script-post-lines.tql.

cat lines.txt

mosquitto_pub -h 127.0.0.1 -p 5653 \
    -t db/tql/script-post-lines.tql \
    -f lines.txt

Then find if the data was successfuly tranformed and stored.

$ machbase-neo shell "select * from example where name like 'text_%'"
 ROWNUM  NAME    TIME(LOCAL)              VALUE     
────────────────────────────────────────────────────
      1  text_3  2023-07-14 08:51:10.926  44.000000 
      2  text_0  2023-07-14 08:51:10.925  11.000000 
      3  text_1  2023-07-14 08:51:10.926  22.000000 
      4  text_2  2023-07-14 08:51:10.926  33.000000 
4 rows fetched.

For the note, the same tql file also works with HTTP POST.

curl -H "Content-Type: text/plain" \
    --data-binary @lines.txt \
    http://127.0.0.1:5654/db/tql/script-post-lines.tql

Last updated on Dec 2, 2023

Basics As Reading API

As Writing API

Write CSV with INSERT

Save tql file

Client POST request

Write CSV with APPEND

Save tql file

Client POST request

Write JSON in custom format

Save tql file

Client POST request

Parse & write custom text data via MQTT & HTTP-POST

Write CSV with `INSERT`

Write CSV with `APPEND`