TQL – MACHBASE

TQL

It is required to properly read and transform data that has been sent by sensors. And also read and send data from database to other systems in demanded format.

Output format independent

1
2
SQL( `select time, value from example where name='signal' limit 100` )
CSV( timeformat("Default") )

1
2
SQL( `select time, value from example where name='signal' limit 100` )
JSON( timeformat("Default") )

1
2
3
4
5
6
7
8
9
SQL( `select time, value from example where name='signal' limit 100` )
CHART(
    size("600px", "340px"),
    chartOption({
        xAxis:{data:column(0)},
        yAxis:{},
        series:[ { type:"line", data:column(1)} ]
    })
)

Data source independent

1
2
3
4
5
6
7
8
9
FAKE( json({ 
    [ "A", 1.0 ],
    [ "B", 1.5 ],
    [ "C", 2.0 ],
    [ "D", 2.5 ] }))

MAPVALUE(1, value(1) * 10 )

CSV()

1
2
3
4
5
6
7
8
CSV(`A,1.0
B,1.5
C,2.0
D,2.5`, field(1, floatType(), "value"))

MAPVALUE(1, value(1) * 10 )

CSV()

1
2
3
4
5
SQL(`select time, value from example where name = 'signal' limit 4`)

MAPVALUE(1, value(1) * 10 )

CSV()

The purpose of TQL is transforming data format. This chapter shows how to do this without developing additional applications.

N:M transforming

Iris

The example tql code below gives a brief idea of what is TQL for.

avg. values of each classes.

CSV(file("https://docs.machbase.com/assets/example/iris.csv"))
GROUP( by(value(4), "species"),
    avg(value(0), "Avg. Sepal L."),
    avg(value(1), "Avg. Sepal W."),
    avg(value(2), "Avg. Petal L."),
    avg(value(3), "Avg. Petal W.")
)
CHART(
    chartOption({
        "xAxis":{"type": "category", "data": column(0)},
        "yAxis": {},
        "legend": {"show": true},
        "series": [
            { "type": "bar", "name": "Avg. Sepal L.", "data": column(1)},
            { "type": "bar", "name": "Avg. Sepal W.", "data": column(2)},
            { "type": "bar", "name": "Avg. Petal L.", "data": column(3)},
            { "type": "bar", "name": "Avg. Petal W.", "data": column(4)}
        ]
    })
)

min, median, avg, max, stddev of sepal length of the setosa class.

CSV(file("https://docs.machbase.com/assets/example/iris.csv"))
FILTER( strToUpper(value(4)) == "IRIS-SETOSA")
GROUP( by(value(4)), 
    min(value(0), "Min"),
    median(value(0), "Median"),
    avg(value(0), "Avg"),
    max(value(0), "Max"),
    stddev(value(0), "StdDev.")
)
CHART(
    chartOption({
        "xAxis": { "type": "category", "data": ["iris-setosa"]},
        "yAxis": {},
        "legend": {"show": "true"},
        "series": [
            {"type":"bar", "name": "Min", "data": column(1)},
            {"type":"bar", "name": "Median", "data": column(2)},
            {"type":"bar", "name": "Avg", "data": column(3)},
            {"type":"bar", "name": "Max", "data": column(4)},
            {"type":"bar", "name": "StdDev.", "data": column(5)}
        ]
    })
)

In this chapter

Last updated on Jun 7, 2024