SINK

All tql scripts must end with one of the sink functions.

The basic SINK function might be INSERT() which write the incoming records onto machbase-neo database. CHART() function can render various charts with incoming records. JSON() and CSV() encode incoming data into proper formats.

INSERT()

Syntax: INSERT( [bridge(),] columns..., table() [, tag()] )

INSERT() stores incoming records into specified database table by an ‘INSERT’ statement for each record.

• bridge() bridge(’name’) optional.
• columns string column list.
• table() table(’name’) specify the destination table name.
• tag() tag(’name’) optional, applicable only to tag tables.

1
2
3
4
5
6

FAKE(json({
    ["temperature", 1708582790, 23.45],
    ["temperature", 1708582791, 24.56]
}))
MAPVALUE(1, value(1)*1000000000) // convert epoch sec to nanosec
INSERT("name", "time", "value", table("example"))

1
2
3
4
5
6
7

FAKE(json({
    [1708582792, 32.34],
    [1708582793, 33.45]
}))
PUSHVALUE(0, "temperature")
MAPVALUE(1, value(1)*1000000000) // convert epoch sec to nanosec
INSERT("name","time", "value", table("example"))

1
2
3
4
5
6

FAKE(json({
    [1708582792, 32.34],
    [1708582793, 33.45]
}))
MAPVALUE(0, value(0)*1000000000) // convert epoch sec to nanosec
INSERT("time", "value", table("example"), tag('temperature'))

Insert records into bridged database.

1
2
3
4

INSERT(
    bridge("sqlite"),
    "company", "employee", "created_on", table("mem_example")
)

APPEND()

Syntax: APPEND( table() )

APPEND() stores incoming records into specified database table via the ‘append’ method of machbase-neo.

• table() table(string) specify destination table

1
2
3
4
5
6

FAKE(json({
    ["temperature", 1708582794, 12.34],
    ["temperature", 1708582795, 13.45]
}))
MAPVALUE(1, value(1)*1000000000 ) // convert epoch sec to nanosec
APPEND( table("example") )

CSV()

Syntax: CSV( [tz(), timeformat(), precision(), rownum(), heading(), delimiter(), nullValue() ] )

Makes the records of the result in CSV format. The values of the records become the fields of the CSV lines. The end of the data is identified by the last two consecutive newline characters (\n\n).

For example, if a record was {key: k, value:[v1,v2]}, it generates an CSV records as v1,v2.

• tz tz(name) time zone, default is tz('UTC')
• timeformat timeformat(string) specify the format how represents datetime fields, default is timeformat('ns')
• rownum rownum(boolean) adds rownum column
• precision precision(int) specify precision of float fields, precision(-1) means no restriction, precision(0) converts to integer
• heading heading(boolean) add fields names as the first row
• delimiter delimiter(string) specify fields separator other than the default comma(,).
• nullValue() specify substitution string for the NULL value, default is nullValue('NULL'). Since v8.0.14
• substituteNull substitute(string) specify substitution string for the NULL value, default is substituteNull('NULL'). (deprecated, replaced by nullValue())
• cache() cache result data. see Cache Result Data for details. Since v8.0.43

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10)
CSV()

1,10
2,20
3,30

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10, "x10")
CSV( heading(true) )

x,x10
1,10
2,20
3,30

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10, "x10")
CSV( heading(true), delimiter("|") )

x|x10
1|10
2|20
3|30

1
2

FAKE( json({ ["A", 123], ["B", null], ["C", 234] }) )
CSV( nullValue("***") )

A|123
B|***
C|234

JSON()

Syntax: JSON( [transpose(), tz(), timeformat(), precision(), rownum(), rowsFlatten(), rowsArray() ] )

Generates JSON results from the values of the records.

• transpose transpose(boolean) transpose rows and columns, it is useful that specifying transpose(true) for the most of chart libraries.
• tz tz(name) time zone, default is tz('UTC').
• timeformat timeformat(string) specify the format how represents datetime fields, default is timeformat('ns').
• rownum *rownum(boolean)` adds rownum column.
• precision precision(int) specify precision of float fields, precision(-1) means no restriction, precision(0) converts to integer.
• rowsFlatten rowsFlatten(boolean) reduces the array dimension of the rows field in the JSON object. If JSON() has transpose(true) and rowsFlatten(true) together, it ignores rowsFlatten(true) and only transpose(true) affects on the result. Since v8.0.12
• rowsArray rowsArray(boolean) produces JSON that contains only array of object for each record. The rowsArray(true) has higher priority than transpose(true) and rowsFlatten(true). Since v8.0.12
• cache() cache result data. see Cache Result Data for details. Since v8.0.43

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10)
JSON()

{
    "data": {
        "columns": [ "x" ],
        "types": [ "double" ],
        "rows": [ [ 1, 10 ], [ 2, 20 ], [ 3, 30 ] ]
    },
    "success": true,
    "reason": "success",
    "elapse": "228.541µs"
}

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10, "x10")
JSON( transpose(true) )

{
    "data": {
        "columns": [ "x", "x10" ],
        "types": [ "double", "double" ],
        "cols": [ [ 1, 2, 3 ], [ 20, 30, 40 ] ]
    },
    "success": true,
    "reason": "success",
    "elapse": "121.375µs"
}

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10, "x10")
JSON( rowsFlatten(true) )

{
    "data": {
        "columns": [ "x", "x10" ],
        "types": [ "double", "double" ],
        "rows": [ 1, 10, 2, 20, 3, 30 ]
    },
    "success": true,
    "reason": "success",
    "elapse": "130.916µs"
}

1
2
3

FAKE( arrange(1, 3, 1))
MAPVALUE(1, value(0)*10, "x10")
JSON( rowsArray(true) )

{
    "data": {
        "columns": [ "x", "x10" ],
        "types": [ "double", "double" ],
        "rows": [ { "x": 1, "x10": 10 }, { "x": 2, "x10": 20 }, { "x": 3, "x10": 30 } ]
    },
    "success": true,
    "reason": "success",
    "elapse": "549.833µs"
}

NDJSON()

Syntax: NDJSON( [tz(), timeformat(), rownum()] ) Since v8.0.33

Generates NDJSON results from the values of the records.

NDJSON (Newline Delimited JSON) is a format for streaming JSON data where each line is a valid JSON object. This is useful for processing large datasets or streaming data because it allows you to handle one JSON object at a time. The end of the data is identified by the last two consecutive newline characters (\n\n).

• tz tz(name) time zone, default is tz('UTC').
• timeformat timeformat(string) specify the format how represents datetime fields, default is timeformat('ns').
• rownum *rownum(boolean)` adds rownum column.
• cache() cache result data. see Cache Result Data for details. Since v8.0.43

1
2

SQL(`select * from example where name = 'neo_load1' limit 3`)
NDJSON(timeformat('Default'), tz('local'), rownum(true))

{"NAME":"neo_load1","ROWNUM":1,"TIME":"2024-09-06 14:46:19.852","VALUE":4.58}
{"NAME":"neo_load1","ROWNUM":2,"TIME":"2024-09-06 14:46:22.853","VALUE":4.69}
{"NAME":"neo_load1","ROWNUM":3,"TIME":"2024-09-06 14:46:25.852","VALUE":4.69}

MARKDOWN()

Generates a table in markdown format or HTML.

Syntax: MARKDOWN( [ options... ] )

• tz(string) time zone, default is tz('UTC')
• timeformat(string) specify the format how represents datetime fields, default is timeformat('ns')
• html(boolean) produce result by HTML renderer, default false
• rownum(boolean) show rownum column
• precision precision(int) specify precision of float fields, precision(-1) means no restriction, precision(0) converts to integer.
• brief(boolean) omit result rows, brief(true) is equivalent with briefCount(5)
• briefCount(limit int) omit result rows if the records exceeds the given limit, no omission if limit is 0

1
2
3
4
5
6
7
8

FAKE( csv(`
10,The first line 
20,2nd line
30,Third line
40,4th line
50,The last is 5th
`))
MARKDOWN()

|column0 |	column1 |
|:-------|:---------|
| 10     | The first line |
| 20     | 2nd line |
| 30     | Third line |
| 40     | 4th line |
| 50     | The last is 5th |

1
2
3
4
5
6
7
8

FAKE( csv(`
10,The first line 
20,2nd line
30,Third line
40,4th line
50,The last is 5th
`))
MARKDOWN( briefCount(2) )

|column0 |	column1 |
|:-------|:---------|
| 10     | The first line |
| 20     | 2nd line |
| ...    | ...      |

> Total 5 records

1
2
3
4
5
6
7
8

FAKE( csv(`
10,The first line 
20,2nd line
30,Third line
40,4th line
50,The last is 5th
`))
MARKDOWN( briefCount(2), html(true) )

HTML()

Syntax: HTML(templates...) Since v8.0.52

Generates an HTML document using the provided templates.

For detailed usage and examples, refer to the HTML section.

TEXT()

Syntax: TEXT(templates...) Since v8.0.52

Generates a text document using the provided templates.

It functions similarly to HTML(), but does not perform HTML escaping on the data.

DISCARD()

Syntax: DISCARD() Since v8.0.7

DISCARD() silently ignore all records as its name implies, so that no output generates.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

FAKE( json({
    [ 1, "hello" ],
    [ 2, "world" ]
}))
WHEN( value(0) == 2, do( value(0), strToUpper(value(1)), {
    ARGS()
    WHEN( true, doLog("OUTPUT:", value(0), value(1)) )
    DISCARD()
}))
CSV()

CHART()

Syntax: CHART() Since v8.0.8

Generates chart using Apache echarts.

Refer to CHART() examples for the various usages.