machbase-neo
TQL
As Reading API

As Reading API

ℹ️
For the examples, create a table and insert data with the following SQL statements.
CREATE TAG TABLE IF NOT EXISTS EXAMPLE (
    NAME VARCHAR(20) PRIMARY KEY,
    TIME DATETIME BASETIME,
VALUE DOUBLE SUMMARIZED);

INSERT INTO EXAMPLE VALUES('TAG0', TO_DATE('2021-08-12'), 10);
INSERT INTO EXAMPLE VALUES('TAG0', TO_DATE('2021-08-13'), 11);

When you save a TQL script, the editor will display a link icon in the top right corner. Click on it to copy the address of the script file.

CSV

Save the code below as output-csv.tql.

1
2

SQL( `select * from example limit 2` )
CSV()

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-csv.tql
TAG0,1628694000000000000,10
TAG0,1628780400000000000,11

Save the code below as output-csv.tql.

1
2

SQL( `select * from example limit 2` )
CSV( delimiter("|") )

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-csv.tql
TAG0|1628694000000000000|10
TAG0|1628780400000000000|11

JSON

Save the code below as output-json.tql.

1
2

SQL( `select * from example limit 2` )
JSON()

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-json.tql
{
    "data": {
        "columns": [ "NAME", "TIME", "VALUE" ],
        "types": [ "string", "datetime", "double" ],
        "rows": [
            [ "TAG0", 1628694000000000000, 10 ],
            [ "TAG0", 1628780400000000000, 11 ]
        ]
    },
    "success": true,
    "reason": "success",
    "elapse": "770.078µs"
}

Save the code below as output-json.tql.

1
2

SQL( `select * from example limit 2` )
JSON( transpose(true) )

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-json.tql
{
    "data": {
        "columns": [ "NAME", "TIME", "VALUE" ],
        "types": [ "string", "datetime", "double" ],
        "cols": [
            [ "TAG0", "TAG0" ],
            [ 1628694000000000000, 1628780400000000000 ],
            [ 10, 11 ]
        ]
    },
    "success": true,
    "reason": "success",
    "elapse": "718.625µs"
}

Save the code below as output-json.tql.

1
2

SQL( `select * from example limit 2` )
JSON( rowsFlatten(true) )

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-json.tql
{
    "data": {
        "columns": [ "NAME", "TIME", "VALUE" ],
        "types": [ "string", "datetime", "double" ],
        "rows": [
            "TAG0", 1628694000000000000, 10,
            "TAG0", 1628780400000000000, 11
        ]
    },
    "success": true,
    "reason": "success",
    "elapse": "718.625µs"
}

Save the code below as output-json.tql.

1
2

SQL( `select * from example limit 2` )
JSON( rowsArray(true) )

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-json.tql
{
    "data": {
        "columns": [ "NAME", "TIME", "VALUE" ],
        "types": [ "string", "datetime", "double" ],
        "rows": [
            { "NAME": "TAG0", "TIME": 1628694000000000000, "VALUE": 10 },
            { "NAME": "TAG0", "TIME": 1628780400000000000, "VALUE": 11 }
        ]
    },
    "success": true,
    "reason": "success",
    "elapse": "718.625µs"
}

MARKDOWN

Save the code below as output-markdown.tql.

1
2

SQL( `select * from example limit 2` )
MARKDOWN()

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-markdown.tql
|NAME|TIME|VALUE|
|:-----|:-----|:-----|
|TAG0|1628694000000000000|10.000000|
|TAG0|1628780400000000000|11.000000|

Save the code below as output-markdown.tql.

1
2

SQL( `select * from example limit 2` )
MARKDOWN( html(true) )

Invoke the tql file with curl command.

$ curl http://127.0.0.1:5654/db/tql/output-markdown.tql
<div>
<table>
<thead>
    <tr><th align="left">NAME</th><th align="left">TIME</th><th align="left">VALUE</th></tr>
</thead>
<tbody>
    <tr><td align="left">TAG0</td><td align="left">1628694000000000000</td><td align="left">10.000000</td></tr>
    <tr><td align="left">TAG0</td><td align="left">1628780400000000000</td><td align="left">11.000000</td>
    </tr>
</tbody>
</table>
</div>

CHART

Save tql file

Save the code below as output-chart.tql.

1
2
3
4
5
6
7
8

SQL(`select time, value from example where name = ? limit 2`, "TAG0")
CHART(
    chartOption({
        xAxis: { data: column(0) },
        yAxis: {},
        series: { type:"bar", data: column(1) }
    })
)

HTTP GET

Open web browser with http://127.0.0.1:5654/db/tql/output-chart.tql

The legacy CHART_LINE(), CHART_BAR(), CHART_SCATTER() and its family functions are deprecated with the new CHART() function. Please refer to the CHART() for the examples.

CHART with chartJson()

Save tql file

Save the code below as output-chart.tql.

1
2
3
4
5
6
7
8
9

SQL(`select time, value from example where name = ? limit 2`, "TAG0")
CHART(
    chartJson(true),
    chartOption({
        xAxis: { data: column(0) },
        yAxis: {},
        series: { type:"bar", data: column(1) }
    })
)

HTTP GET

Open web browser with http://127.0.0.1:5654/db/tql/output-chart.tql

{
  "chartID":"MzM3NjYzNjg5MTYxNjQ2MDg_", 
  "jsAssets": ["/web/echarts/echarts.min.js"],
  "jsCodeAssets": ["/web/api/tql-assets/MzM3NjYzNjg5MTYxNjQ2MDg_.js"],
  "style": {
      "width": "600px",
      "height": "600px"	
  },
  "theme": "white"
}

CHART with chartID()

Save tql file

Save the code below as output-chart.tql.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SQL(`select time, value from example where name = ? limit 2`, "TAG0")
CHART(
    chartID("myChart"),
    chartJson(true),
    chartOption({
        xAxis: { data: column(0) },
        yAxis: {},
        series: { type:"bar", data: column(1) }
    })
)

HTTP GET

Open web browser with http://127.0.0.1:5654/db/tql/output-chart.tql

{
  "chartID":"myChart", 
  "jsAssets": ["/web/echarts/echarts.min.js"],
  "jsCodeAssets": ["/web/api/tql-assets/myChart.js"],
  "style": {
      "width": "600px",
      "height": "600px"	
  },
  "theme": "white"
}

This scenario is useful when your DOM document has <div id='myChart'/>.

... in HTML ...
<div id='myChart' />
<script>
    fetch('http://127.0.0.1:5654/db/tql/output-chart.tql').then( function(rsp) {
        return rsp.json();
    }).then( function(c) {
        c.jsAssets.concat(c.jsCodeAssets).forEach((src) => {
            const sScript = document.createElement('script');
            sScript.src = src;
            sScript.type = 'text/javascript';
            document.getElementsByTagName('head')[0].appendChild(sScript);
        })
    })
</script>
... omit ...
Last updated on
As Writing APISRC