machbase-neo
Tutorials
Import
Import data via TQL

Import data via TQL

Convert any file to TQL and enter it into the table.

📌

For smooth practice, the following query should be run to prepare tables and data.

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

Import Text

Make test data in import-data.csv.

1,100,value,10
2,200,value,11
3,140,value,12

Copy the code below into TQL editor and save import-tql-csv.tql.

STRING(payload() ?? `1,100,value,10
2,200,value,11
3,140,value,12`, separator('\n'))

SCRIPT({
    text := import("text")
    ctx := import("context")

    key := ctx.key()
    values := ctx.value()
    str :=  text.trim_space(values[0])

    str = text.split(str, ',')

    ctx.yield(
        "tag-" + str[0],
        text.parse_int(str[1], 10, 64),
        text.parse_int(str[3], 10, 64)
    )
})
APPEND(table("example"))

Post the test data CSV to the tql.

curl -o - --data-binary @import-data.csv http://127.0.0.1:5654/db/tql/import-tql-csv.tql

append 3 rows (success 3, fail 0).

Select data

machbase-neo shell "select * from example";

 ROWNUM  NAME   TIME(LOCAL)          VALUE 
───────────────────────────────────────────
      1  tag-1  1970-01-01 09:00:00  10    
      2  tag-2  1970-01-01 09:00:00  11    
      3  tag-3  1970-01-01 09:00:00  12    
3 rows fetched.

Import JSON

Prepare test data saved in import-data.json.

{
  "tag": "pump",
  "data": {
    "string": "Hello TQL?",
    "number": "123.456",
    "time": 1687405320,
    "boolean": true
  },
  "array": ["elements", 234.567, 345.678, false]
}

Copy the code below into TQL editor and save import-tql-json.tql.

BYTES(payload())
SCRIPT({
  json := import("json")
  ctx := import("context")
  val := ctx.value()
  obj := json.decode(val[0])
  // parse a value from json, yield multiple records
  ctx.yield(obj.tag+"_0", obj.data.time*1000000000, obj.data.number)
  ctx.yield(obj.tag+"_1", obj.data.time*1000000000, obj.array[1])
  ctx.yield(obj.tag+"_2", obj.data.time*1000000000, obj.array[2])
})
APPEND(table("example"))

Post the test data JSON to the tql.

curl -o - --data-binary @import-data.json http://127.0.0.1:5654/db/tql/import-tql-json.tql

append 2 rows (success 2, fail 0).

Select data

machbase-neo shell "select * from example";

 ROWNUM  NAME    TIME(LOCAL)          VALUE   
──────────────────────────────────────────────
      1  tag-1   1970-01-01 09:00:00  10      
      2  pump_2  2023-06-22 12:42:00  345.678 
      3  tag-2   1970-01-01 09:00:00  11      
      4  tag-3   1970-01-01 09:00:00  12      
      5  pump_1  2023-06-22 12:42:00  234.567 
5 rows fetched.
Last updated on
Import data from bridge