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

• templates: One or more template strings or file(path) references. Each argument can be a direct template string or a file path using file(path) to load the template from a file. The template content uses the Go HTML template language. For more information, see the template documentation.
• cache() cache result data. see Cache Result Data for details.

Within the template, you have access to a value object that exposes the current record’s field values and row number.

The following fields and properties are available within the HTML template context:

.V is a map object containing field names as keys and their corresponding values.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL(`SELECT NAME, TIME, VALUE FROM EXAMPLE LIMIT 5`) HTML({ {{ if .IsFirst }} <html> <body> <h2>HTML Template Example</h2> <hr> <table> <tr> {{range .Columns}} <th>{{ . }}</th> {{end}} </tr> {{ end }} <tr> <td>{{ .V.NAME }}</td> <td>{{ .V.TIME | timeformat "RFC3339" "Asia/Seoul"}}</td> <td>{{ .V.VALUE }}</td> </tr> {{ if .IsLast }} </table> <hr> Total: {{ .Num }} </body> </html> {{ end }} })

.Value is a function that accesses the fields of the current record by their index.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

FAKE( csv(` 10,The first line 20,2nd line 30,Third line 40,4th line 50,The last is 5th `)) HTML({ {{ if .IsFirst }} <html> <body> <h2>HTML Template Example</h2> <hr> {{ end }} <li>{{ .Value 0 }} : {{ .Value 1 }} {{ if .IsLast }} <hr> Total: {{ .Num }} </body> </html> {{ end }} })

.Values is an array containing all field values of the current record.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

FAKE( csv(` 10,The first line 20,2nd line 30,Third line 40,4th line 50,The last is 5th `)) HTML({ {{ if .IsFirst }} <html> <body> <h2>HTML Template Example</h2> <hr> {{ end }} <li>{{ (index .Values 0) }} : {{ (index .Values 1 ) }} {{ if .IsLast }} <hr> Total: {{ .Num }} </body> </html> {{ end }} })

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.