Skip to content
machbase-neo
TQL
Glance at TQL

Glance at TQL

Machbase-neo supports Transforming Query Language (TQL) and execution API.

As application developers, we typically follow a similar approach to build applications that utilize databases. The process usually starts with querying the database and retrieving data in a tabular form (rows and columns). This data is then converted into the desired data structure, manipulated, and displayed in the required formats such as JSON, CSV, or charts. TQL simplifies this process with just a few lines of script. Additionally, other applications can interact with TQL via HTTP endpoints, treating it as an executable API.

What is TQL

TQL (Transforming Query Language) is a domain-specific language (DSL) for data manipulation. It defines a flow of data streams, where each individual data unit is a record. A record has a key and a value.

  • key : generally an auto-generated sequential integer, similar to ROWNUM in query results
  • value : a tuple that contains the actual data fields

tql_records

TQL scripts start with SRC (source) functions that define how to retrieve data and generate records by transforming the raw data. The script ends with SINK functions, which define how to output the records. Between SRC and SINK, you can use MAP functions to transform the data as needed.

tql_flow_min

In some cases, a TQL script needs to transform records, involving mathematical calculations, simple string concatenations, or interactions with external databases. These tasks can be defined using MAP functions.

Thus, a TQL script should start with SRC functions and end with SINK functions. It can include zero or more MAP functions in between to perform the necessary transformations.

tql_flow

SRC

There are several SRC functions available in TQL.

  • SQL() : produces records by querying the Machbase-neo database or even external (bridged) databases using the given SQL statement
  • FAKE() : generates artificial data for testing purposes
  • CSV() : reads data from CSV files
  • BYTES() : reads arbitrary binary data from the file system, client’s HTTP request, or MQTT payload

tql_src

SINK

  • INSERT() : writes the incoming records to the Machbase-neo database
  • CHART() : renders a chart with the incoming records
  • JSON(), CSV() : encode the incoming data into their respective formats, making it easier to integrate with other applications or display the data in a user-friendly manner

tql_sink

MAP

MAP functions are essential for transforming data from one shape to another. They allow you to perform various operations such as mathematical calculations, string manipulations, data format conversions, and interactions with external systems. By using MAP functions, you can efficiently process and reshape your data to meet the specific requirements of your application.

tql_map

Run TQL

Open Web UI

Open the Machbase-neo web UI in your web browser. The default address is http://127.0.0.1:5654/. Use the username sys and the 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 the ▶︎ icon on the top left of the editor. It displays a chart as shown in the images below, representing a wave with a frequency of 1.5 Hz and an amplitude of 1.0.

FAKE( oscillator(freq(1.5, 1.0), range('now', '3s', '10ms')) )
CHART_SCATTER()

Output formats

Let’s explore some data formats like CSV and JSON.

  • CSV Format: This format is useful for exporting data to spreadsheets or other applications that support CSV files.
  • JSON Format: This format is ideal for web applications and APIs, as it is easy to parse and integrate with JavaScript.

By using TQL, you can easily convert your data into these formats with just a few lines of code.

FAKE( oscillator(freq(1.5, 1.0), range('now', '3s', '10ms')) )
JSON()

TQL as API

Save this code as hello.tql by clicking the save icon at the top right corner of the editor. You can then access it through your web browser at http://127.0.0.1:5654/db/tql/hello.tql, or use the curl command in the terminal to retrieve the data.

IconDescription
When a TQL script is saved, the editor displays a link icon at the top right corner. Click this icon 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; charset=utf-8
< 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.

Invoke hello.tql with curl from the terminal to receive the result in JSON format.

curl -o - http://127.0.0.1:5654/db/tql/hello.tql

The JSON response with HTTP headers looks like this.

$ 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 is helpful to know that TQL’s JSON output supports transposing the result into columns instead of rows. By applying JSON(transpose(true)) and invoking it again, the resulting JSON will contain a 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, allowing other applications to access data seamlessly.

INSERT

Change CSV() to INSERT("time", "value", table("example"), tag("temperature")) and execute again.

Select Table 

SQL('select * from example limit 10')
CSV()
Last updated on
Basics