Glance at TQL
machbase-neo supports Transforming Query Language and execution API.
As application developers, we generally take similar approaching to build applications that utilize databases. The process is starting typically with querying database and retrieving data in a tabular form(rows and columns), converting it into the desired data structure, then manipulating and displaying the final result into demanded shapes such as JSON, CSV or chart.
TQL is simplifying this process within few lines of script. And other inter-working applications call the TQL via HTTP endpoint as if it is an executable API.
What is TQL
TQL (transforming Query Language) is a DSL for the data manipulation. It defines a flow of data stream, and the individual data unit is a record. A records has key and value, a key is generally auto-generated sequential integer like ROWNUM of query result. a value is a tuple that contains actual data fields.
TQL script starts with SRC (source) that defines how to retrieve data and generates records by transforming the raw data. The SINK should be the end of TQL script which defines how to output records.
In some cases TQL script needs to transform records, involving mathematic calculation, simply string concatenation or interacts with external databases. Those tasks can be defined in MAP functions.
So, TQL script should start with SRC and end with SINK and it can has zero or one more MAP functions.
SRC
There are several SRC functions. For example, SQL()
produces records by querying machbase-neo database or even external (bridged) databases with the given sql statement. FAKE()
generates artificial data. CSV()
can read csv data, BYTES()
reads arbitrary binary data from file system or client’s HTTP request and MQTT payload.
SINK
The basic SINK function might be INSERT()
which writes the incoming records onto machbase-neo database. CHART()
function renders a chart with incoming records. JSON()
and CSV()
encode incoming data into proper formats.
MAP
MAP functions are the core of the transforming data from a shape to an other.
Run TQL
Open Web UI
Open machbase-neo web UI with your web browser,
the default address is http://127.0.0.1:5654/
, username sys
and password manager
.
New TQL page
Select “TQL” on the ‘New…’ page.
Copy the code and run
Copy and paste the sample TQL code into the TQL editor.
Then click ▶︎ icon on the top left of the editor, it will display a line chart as the image below. Which is a wave that has 1.5 Hz frequency and 1.0 amplitude.
Let’s try some CSV,JSON formats.
TQL as API
Save this code as hello.tql
(click save icon on the top right of the editor) and open it with web browser at http://127.0.0.1:5654/db/tql/hello.tql, or use curl command on the terminal.
Icon | Description |
---|---|
When tql script is saved, the editor shows the link icon on the top right corner, click it to copy the address of the script file. |
curl -o - http://127.0.0.1:5654/db/tql/hello.tql
$ curl -o - -v http://127.0.0.1:5654/db/tql/hello.tql
...omit...
>
< HTTP/1.1 200 OK
< Content-Type: text/csv
< Transfer-Encoding: chunked
<
1686787739025518000,-0.238191
1686787739035518000,-0.328532
1686787739045518000,-0.415960
1686787739055518000,-0.499692
1686787739065518000,-0.578992
...omit...
JSON()
Let’s try to change CSV()
to JSON()
, save and execute again.
And invoke the hello.tql with curl
from terminal.
$ curl -o - -v http://127.0.0.1:5654/db/tql/hello.tql
...omit...
< HTTP/1.1 200 OK
< Content-Type: application/json
< Transfer-Encoding: chunked
<
{
"data": {
"columns": [ "time", "value" ],
"types": [ "datetime", "double" ],
"rows": [
[ 1686788907538618000, 0.9344920354538058 ],
[ 1686788907548618000, 0.8968436523101743 ],
...omit...
},
"success": true,
"reason": "success",
"elapse": "956.291µs"
}
JSON() with transpose()
If you are developing a data visualization application, it shall be helpful to know that tql JSON output supports transpose the result in columns instead of rows. Apply JSON( transpose(true) )
and invoke it again. The result JSON contains cols array.
$ curl -o - -v http://127.0.0.1:5654/db/tql/hello.tql
...omit...
< HTTP/1.1 200 OK
< Content-Type: application/json
< Transfer-Encoding: chunked
<
{
"data": {
"columns": [ "time", "value" ],
"types": [ "datetime", "double" ],
"cols": [
[ 1686789517241103000, ...omit..., 1686789520231103000],
[ -0.7638449771082523, ...omit..., 0.8211935584502427]
]
},
"success": true,
"reason": "success",
"elapse": "1.208166ms"
}
This feature is the simplest way for developers to create RESTful APIs providing other applications accessing data.
INSERT
Change CSV()
to INSERT("time", "value", table("example"), tag("temperature"))
and execute again.
Select Table
SQL('select * from tag limit 10')
CSV()