SINK – MACHBASE

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.

tql_sink

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.

Write records to machbase that contains tag name.

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"))

Write records to machbase with same tag name by adding “name” field by PUSHVALUE().

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"))

Write records to machbase with same tag name by using tag() option if the destination is a tag table.

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.

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())

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

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"
}

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

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 |

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

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) )

column0	column1
10	The first line
20	2nd line
…	…

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.

Deprecated

CHART_LINE()

Syntax: CHART_LINE()

Generates a line chart in HTML format.

1
2
3
4
5
6
7
8
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '25ms')))
// |    0      1
// +--> time   value
// |
CHART_LINE(
    size("600px", "400px"),
    xAxis(0, "T", "time"), yAxis(0, "V", "value")
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '25ms')))
// |    0      1
// +--> time   value
// |
CHART(
    size("600px", "400px"),
    chartOption({
        xAxis: { name: "T", type:"time" },
        yAxis: { name: "V"},
        legend: { show: true },
        tooltip: { show: true, trigger: "axis" },
        series: [{ 
            type: "line",
            name: "column[1]",
            data: column(0).map(function(t, idx){
                return [t, column(1)[idx]];
            })
        }]
    })
)

CHART_BAR()

Syntax: CHART_BAR()

Generates a bar chart in HTML format.

1
2
3
4
5
6
7
8
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '25ms')))
// |    0      1
// +--> time   value
// |
CHART_BAR(
    size("600px", "300px"),
    xAxis(0, "T", "time"), yAxis(0, "V", "value")
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '25ms')))
// |    0      1
// +--> time   value
// |
CHART(
    size("600px", "400px"),
    chartOption({
        xAxis: { name: "T", type:"time" },
        yAxis: { name: "V"},
        legend: { show: true },
        tooltip: { show: true, trigger: "axis" },
        series: [{ 
            type: "bar",
            name: "column[1]",
            data: column(0).map(function(t, idx){
                return [t, column(1)[idx]];
            })
        }]
    })
)

CHART_SCATTER()

Syntax: CHART_SCATTER()

Generates a scatter chart in HTML format.

1
2
3
4
5
6
7
8
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '25ms')))
// |    0      1
// +--> time   value
// |
CHART_SCATTER(
    size("600px", "300px"),
    xAxis(0, "T", "time"), yAxis(0, "V", "value")
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '25ms')))
// |    0      1
// +--> time   value
// |
CHART(
    size("600px", "400px"),
    chartOption({
        xAxis: { name: "T", type:"time" },
        yAxis: { name: "V"},
        legend: { show: true },
        tooltip: { show: true, trigger: "axis" },
        series: [{ 
            type: "scatter",
            name: "column[1]",
            data: column(0).map(function(t, idx){
                return [t, column(1)[idx]];
            })
        }]
    })
)

CHART_LINE3D()

Syntax: CHART_LINE3D()

Generates a 3D line chart in HTML format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
FAKE(meshgrid(linspace(-1.0,1.0,100), linspace(-1.0, 1.0, 100)))
// |    0   1
// +--> x   y
// |
MAPVALUE(2, sin(10*(pow(value(0), 2) + pow(value(1), 2))) / 10 )
// |    0   1   2
// +--> x   y   z
// |
CHART_LINE3D(
  size('600px', '600px'),
  lineWidth(2), 
  gridSize(100, 30, 100), 
  visualMap(-0.12, 0.12)
)

 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
28
29
30
31
32
FAKE(meshgrid(linspace(-1.0,1.0,100), linspace(-1.0, 1.0, 100)))
// |    0   1
// +--> x   y
// |
MAPVALUE(2, sin(10*(pow(value(0), 2) + pow(value(1), 2))) / 10 )
// |    0   1   2
// +--> x   y   z
// |
CHART(
  plugins("gl"),
  size('600px', '600px'),
  chartOption({
    grid3D:{ boxWidth: 100, boxHeight: 30, boxDepth: 100},
    xAxis3D:{name:"x"},
    yAxis3D:{name:"y"},
    zAxis3D:{name:"z"},
    series:[{
        type: "line3D",
        lineStyle: { "width": 2 },
        data: column(0).map(function(x, idx){
            return [x, column(1)[idx], column(2)[idx]]
        })
    }],
    visualMap: {
        min: -0.12, max:0.12,
        inRange: {
            color:["#313695", "#4575b4", "#74add1", "#abd9e9", "#e0f3f8", "#ffffbf",
		    "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
        }
    }
  })
)

CHART_BAR3D()

Syntax: CHART_BAR3D()

Generates a 3D bar chart in HTML format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
FAKE(meshgrid(linspace(-1.0,1.0,100), linspace(-1.0, 1.0, 100)))
// |    0   1
// +--> x   y
// |
MAPVALUE(2, sin(10*(pow(value(0), 2) + pow(value(1), 2))) / 10 )
// |    0   1   2
// +--> x   y   z
// |
CHART_BAR3D(
  size('600px', '600px'),
  lineWidth(2), 
  gridSize(100, 30, 100), 
  visualMap(-0.12, 0.12)
)

 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
28
29
30
31
FAKE(meshgrid(linspace(-1.0,1.0,100), linspace(-1.0, 1.0, 100)))
// |    0   1
// +--> x   y
// |
MAPVALUE(2, sin(10*(pow(value(0), 2) + pow(value(1), 2))) / 10 )
// |    0   1   2
// +--> x   y   z
// |
CHART(
  plugins("gl"),
  size('600px', '600px'),
  chartOption({
    grid3D:{ boxWidth: 100, boxHeight: 30, boxDepth: 100},
    xAxis3D:{name:"x"},
    yAxis3D:{name:"y"},
    zAxis3D:{name:"z"},
    series:[{
        type: "bar3D",
        data: column(0).map(function(x, idx){
            return [x, column(1)[idx], column(2)[idx]]
        })
    }],
    visualMap: {
        min: -0.12, max:0.12,
        inRange: {
            color:["#313695", "#4575b4", "#74add1", "#abd9e9", "#e0f3f8", "#ffffbf",
		    "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
        }
    }
  })
)

CHART_SCATTER3D()

Syntax: CHART_SCATTER3D()

Generates a 3D scatter chart in HTML format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
FAKE(meshgrid(linspace(-1.0,1.0,100), linspace(-1.0, 1.0, 100)))
// |    0   1
// +--> x   y
// |
MAPVALUE(2, sin(10*(pow(value(0), 2) + pow(value(1), 2))) / 10 )
// |    0   1   2
// +--> x   y   z
// |
CHART_SCATTER3D(
  size('600px', '600px'),
  lineWidth(2), 
  gridSize(100, 30, 100), 
  visualMap(-0.12, 0.12)
)

 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
28
29
30
31
FAKE(meshgrid(linspace(-1.0,1.0,100), linspace(-1.0, 1.0, 100)))
// |    0   1
// +--> x   y
// |
MAPVALUE(2, sin(10*(pow(value(0), 2) + pow(value(1), 2))) / 10 )
// |    0   1   2
// +--> x   y   z
// |
CHART(
  plugins("gl"),
  size('600px', '600px'),
  chartOption({
    grid3D:{ boxWidth: 100, boxHeight: 30, boxDepth: 100},
    xAxis3D:{name:"x"},
    yAxis3D:{name:"y"},
    zAxis3D:{name:"z"},
    series:[{
        type: "scatter3D",
        data: column(0).map(function(x, idx){
            return [x, column(1)[idx], column(2)[idx]]
        })
    }],
    visualMap: {
        min: -0.12, max:0.12,
        inRange: {
            color:["#313695", "#4575b4", "#74add1", "#abd9e9", "#e0f3f8", "#ffffbf",
		    "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
        }
    }
  })
)

title()

Syntax: title(label)

label string

subtitle()

Syntax: subtitle(label)

label string

xAxis(), yAxis(), zAxis()

Syntax: xAxis(idx, label [, type])

idx number index of column for the axis
label string label of the axis
type string type fo the axis, available: 'time' and 'value', default is 'value' if not specified.

dataZoom()

Syntax: dataZoom(type, minPercentage, maxPercentage)

type string “slider”, “inside”
minPercentage number 0 ~ 100
maxPercentage number 0 ~ 100

opacity()

Syntax: opacity(alpha)

alpha number 0.0 ~ 1.0

autoRotate()

Syntax: autoRotate( [speed] )

speed number degree/sec, default is 10

gridSize()

Syntax: gridSize( width, height, depth )

width number percentage (default: 100)
height number percentage (default: 100)
depth number percentage (default: 100)

seriesLabels()

Syntax: seriesLabels( label... )

label string

Specify the label text of each series.

toolbox

toolboxSaveAsImage()

Syntax: toolboxSaveAsImage(filename) Since v8.0.4

filename string filename with extension supporting (.png, .jpeg, .svg).

Show the toolbox button to save chart as an image file.

toolboxDataZoom()

Syntax: toolboxDataZoom() Since v8.0.4

Show the toolbox button for data zoom.

toolboxDataView()

Syntax: toolboxDataView() Since v8.0.4

Show the toolbox button for raw data viewer.

1
2
3
4
5
6
7
8
9
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '20ms')) )
CHART_LINE( 
    xAxis(0, "T", "time"),
    yAxis(1, "V", "value"),
    size('400px', '300px'),
    toolboxSaveAsImage('image.png'),
    toolboxDataZoom(),
    toolboxDataView()
)

visualMap()

Syntax: visualMap(min, max)

min number
max number

It calls visualMapColor() internally with pre-defined default colors.

visualMapColor()

Syntax: visualMapColor(min, max, colors...) Since v8.0.4

min number
max number
colors colors in array of string

Example)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
FAKE( oscillator(freq(1.5, 1.0), freq(1.0, 0.7), range('now', '3s', '20ms')) )
CHART_LINE( 
    size('400px', '300px'),
    xAxis(0, "T", "time"),
    yAxis(1, "V", "value"),
    visualMapColor(-2.0, 2.0, 
        "#a50026", "#d73027", "#f46d43", "#fdae61", "#e0f3f8", 
        "#abd9e9", "#74add1", "#4575b4", "#313695", "#313695", 
        "#4575b4", "#74add1", "#abd9e9", "#e0f3f8", "#fdae61",
        "#f46d43", "#d73027", "#a50026"
    )
)

markArea()

Syntax: markArea(coord0, coord1 [, label [, color [, opacity]]])

coord0 any : area beginning x-value
coord1 any : area ending x-value
label string : title
color string : color of area
opacity number : 0~1 of opacity

Example)

1
2
3
4
5
6
7
8
FAKE( oscillator(freq(1.5, 1.0), range('now', '3s', '10ms')) )
CHART_SCATTER(
    size('400px', '300px'),
    xAxis(0, "T", "time"),
    yAxis(1, "V", "value"),
    markArea(time('now+1s'), time('now+2s'), 'Error', '#ff000033'),
    markArea(time('now+1.5s'), time('now+2.5s'), 'Marked', '#22ff0022')
 )

markXAxis()

Syntax: markXAxis(coord, label)

coord any : marked x-value
label string : title

1
2
3
4
5
6
7
FAKE( oscillator(freq(1.5, 1.0), range('now', '3s', '10ms')) )
CHART_SCATTER(
    size('400px', '300px'),
    xAxis(0, "T", "time"),
    yAxis(1, "V", "value"),
    markXAxis(time('now+1.5s'), 'NOW')
)

markYAxis()

Syntax: markYAxis(coord, label)

coord any : marked y-value
label string : title

1
2
3
4
5
6
7
8
FAKE( oscillator(freq(1.5, 1.0), range('now', '3s', '10ms')) )
CHART_SCATTER(
    size('400px', '300px'),
    xAxis(0, "T", "time"),
    yAxis(1, "V", "value"),
    markYAxis(1.0, 'max'),
    markYAxis(-1.0, 'min')
)

Last updated on Mar 19, 2024

SRC MAP