REST API

To support the development of various types of applications and platforms, TDengine provides an API that conforms to REST principles; namely REST API. To minimize the learning cost, unlike REST APIs for other database engines, TDengine allows insertion of SQL commands in the BODY of an HTTP POST request, to operate the database.

REST API - 图1note

One difference from the native connector is that the REST interface is stateless and so the USE db_name command has no effect. All references to table names and super table names need to specify the database name in the prefix. (Since version 2.2.0.0, TDengine supports specification of the db_name in RESTful URL. If the database name prefix is not specified in the SQL command, the db_name specified in the URL will be used. Since version 2.4.0.0, REST service is provided by taosAdapter by default and it requires that the db_name must be specified in the URL.)

Installation

The REST interface does not rely on any TDengine native library, so the client application does not need to install any TDengine libraries. The client application’s development language only needs to support the HTTP protocol.

Verification

If the TDengine server is already installed, it can be verified as follows:

The following example is in an Ubuntu environment and uses the curl tool to verify that the REST interface is working. Note that the curl tool may need to be installed in your environment.

The following example lists all databases on the host h1.taosdata.com. To use it in your environment, replace h1.taosdata.com and 6041 (the default port) with the actual running TDengine service FQDN and port number.

  1. curl -L -H "Authorization: Basic cm9vdDp0YW9zZGF0YQ==" -d "show databases;" h1.taosdata.com:6041/rest/sql

The following return value results indicate that the verification passed.

  1. {
  2.   "status": "succ",
  3.   "head": [
  4.     "name",
  5.     "created_time",
  6.     "ntables",
  7.     "vgroups",
  8.     "replica",
  9.     "quorum",
  10.     "days",
  11.     "keep1,keep2,keep(D)",
  12.     "cache(MB)",
  13.     "blocks",
  14.     "minrows",
  15.     "maxrows",
  16.     "wallevel",
  17.     "fsync",
  18.     "comp",
  19.     "precision",
  20.     "status"
  21.   ],
  22.   "data": [
  23.     [
  24.       "log",
  25.       "2020-09-02 17:23:00.039",
  26.       4,
  27.       1,
  28.       1,
  29.       1,
  30.       10,
  31.       "30,30,30",
  32.       1,
  33.       3,
  34.       100,
  35.       4096,
  36.       1,
  37.       3000,
  38.       2,
  39.       "us",
  40.       "ready"
  41.     ]
  42.   ],
  43.   "rows": 1
  44. }

HTTP request URL format

  1. http://<fqdn>:<port>/rest/sql/[db_name]

Parameter Description:

  • fqnd: FQDN or IP address of any host in the cluster
  • port: httpPort configuration item in the configuration file, default is 6041
  • db_name: Optional parameter that specifies the default database name for the executed SQL command. (supported since version 2.2.0.0)

For example, http://h1.taos.com:6041/rest/sql/test is a URL to h1.taos.com:6041 and sets the default database name to test.

TDengine supports both Basic authentication and custom authentication mechanisms, and subsequent versions will provide a standard secure digital signature mechanism for authentication.

  • The custom authentication information is as follows. More details about “token” later.

    1. Authorization: Taosd <TOKEN>

  • Basic authentication information is shown below

    1. Authorization: Basic <TOKEN>

The HTTP request’s BODY is a complete SQL command, and the data table in the SQL statement should be provided with a database prefix, e.g., db_name.tb_name. If the table name does not have a database prefix and the database name is not specified in the URL, the system will response an error because the HTTP module is a simple forwarder and has no awareness of the current DB.

Use curl to initiate an HTTP request with a custom authentication method, with the following syntax.

  1. curl -L -H "Authorization: Basic <TOKEN>" -d "<SQL>" <ip>:<PORT>/rest/sql/[db_name]

Or

  1. curl -L -u username:password -d "<SQL>" <ip>:<PORT>/rest/sql/[db_name]

where TOKEN is the string after Base64 encoding of {username}:{password}, e.g. root:taosdata is encoded as cm9vdDp0YW9zZGF0YQ==.

HTTP Return Format

The return result is in JSON format, as follows:

  1. {
  2.     "status": "succ",
  3.     "head": ["ts", "current", ...],
  4.     "column_meta": [["ts",9,8],["current",6,4], ...],
  5.     "data": [
  6.         ["2018-10-03 14:38:05.000", 10.3, ...],
  7.         ["2018-10-03 14:38:15.000", 12.6, ...]
  8.     ],
  9.     "rows": 2
  10. }

Description:

  • status: tells you whethre the operation result is success or failure.
  • head: the definition of the table, or just one column “affected_rows” if no result set is returned. (As of version 2.0.17.0, it is recommended not to rely on the head return value to determine the data column type but rather use column_meta. In later versions, the head item may be removed from the return value.)
  • column_meta: this item is added to the return value to indicate the data type of each column in the data with version 2.0.17.0 and later versions. Each column is described by three values: column name, column type, and type length. For example, ["current",6,4] means that the column name is “current”, the column type is 6, which is the float type, and the type length is 4, which is the float type with 4 bytes. If the column type is binary or nchar, the type length indicates the maximum length of content stored in the column, not the length of the specific data in this return value. When the column type is nchar, the type length indicates the number of Unicode characters that can be saved, not bytes.
  • data: The exact data returned, presented row by row, or just [[affected_rows]] if no result set is returned. The order of the data columns in each row of data is the same as that of the data columns described in column_meta.
  • rows: Indicates how many rows of data there are.

The column types in column_meta are described as follows:

  • 1:BOOL
  • 2:TINYINT
  • 3:SMALLINT
  • 4:INT
  • 5:BIGINT
  • 6:FLOAT
  • 7:DOUBLE
  • 8:BINARY
  • 9:TIMESTAMP
  • 10:NCHAR

Custom Authorization Code

HTTP requests require an authorization code <TOKEN> for identification purposes. The administrator usually provides the authorization code, and it can be obtained simply by sending an HTTP GET request as follows:

  1. curl http://<fqnd>:<port>/rest/login/<username>/<password>

Where fqdn is the FQDN or IP address of the TDengine database. port is the port number of the TDengine service. username is the database username. password is the database password. The return value is in JSON format, and the meaning of each field is as follows.

  • status: flag bit of the request result

  • code: return value code

  • desc: authorization code

Example of getting authorization code.

  1. curl http://192.168.0.1:6041/rest/login/root/taosdata

Response body:

  1. {
  2.   "status": "succ",
  3.   "code": 0,
  4.   "desc": "/KfeAzX/f9na8qdtNZmtONryp201ma04bEl8LcvLUd7a8qdtNZmtONryp201ma04"
  5. }

For example

  • query all records from table d1001 of database demo

    1. curl -L -H "Authorization: Basic cm9vdDp0YW9zZGF0YQ==" -d "select * from demo.d1001" 192.168.0.1:6041/rest/sql

    Response body:

    1. {
    2.   "status": "succ",
    3.   "head": ["ts", "current", "voltage", "phase"],
    4.   "column_meta": [
    5.     ["ts", 9, 8],
    6.     ["current", 6, 4],
    7.     ["voltage", 4, 4],
    8.     ["phase", 6, 4]
    9.   ],
    10.   "data": [
    11.     ["2018-10-03 14:38:05.000", 10.3, 219, 0.31],
    12.     ["2018-10-03 14:38:15.000", 12.6, 218, 0.33]
    13.   ],
    14.   "rows": 2
    15. }

  • Create database demo:

    1. curl -L -H "Authorization: Basic cm9vdDp0YW9zZGF0YQ==" -d "create database demo" 192.168.0.1:6041/rest/sql

    Response body:

    1. {
    2.   "status": "succ",
    3.   "head": ["affected_rows"],
    4.   "column_meta": [["affected_rows", 4, 4]],
    5.   "data": [[1]],
    6.   "rows": 1
    7. }

Other Uses

Unix timestamps for result sets

When the HTTP request URL uses /rest/sqlt, the returned result set’s timestamp value will be in Unix timestamp format, for example:

  1. curl -L -H "Authorization: Basic cm9vdDp0YW9zZGF0YQ==" -d "select * from demo.d1001" 192.168.0.1:6041/rest/sqlt

Response body:

  1. {
  2.   "status": "succ",
  3.   "head": ["ts", "current", "voltage", "phase"],
  4.   "column_meta": [
  5.     ["ts", 9, 8],
  6.     ["current", 6, 4],
  7.     ["voltage", 4, 4],
  8.     ["phase", 6, 4]
  9.   ],
  10.   "data": [
  11.     [1538548685000, 10.3, 219, 0.31],
  12.     [1538548695000, 12.6, 218, 0.33]
  13.   ],
  14.   "rows": 2
  15. }

UTC format for the result set

When the HTTP request URL uses /rest/sqlutc, the timestamp of the returned result set will be expressed as a UTC format, for example:

  1.   curl -L -H "Authorization: Basic cm9vdDp0YW9zZGF0YQ==" -d "select * from demo.t1" 192.168.0.1:6041/rest/sqlutc

Response body:

  1. {
  2.   "status": "succ",
  3.   "head": ["ts", "current", "voltage", "phase"],
  4.   "column_meta": [
  5.     ["ts", 9, 8],
  6.     ["current", 6, 4],
  7.     ["voltage", 4, 4],
  8.     ["phase", 6, 4]
  9.   ],
  10.   "data": [
  11.     ["2018-10-03T14:38:05.000+0800", 10.3, 219, 0.31],
  12.     ["2018-10-03T14:38:15.000+0800", 12.6, 218, 0.33]
  13.   ],
  14.   "rows": 2
  15. }

Important configuration items

Only some configuration parameters related to the RESTful interface are listed below. Please see the description in the configuration file for other system parameters.

  • The port number of the external RESTful service is bound to 6041 by default (the actual value is serverPort + 11, so it can be changed by modifying the setting of the serverPort parameter).
  • httpMaxThreads: the number of threads to start, default is 2 (the default value is rounded down to half of the CPU cores with version 2.0.17.0 and later versions).
  • restfulRowLimit: the maximum number of result sets (in JSON format) to return. The default value is 10240.
  • httpEnableCompress: whether to support compression, the default is not supported. Currently, TDengine only supports the gzip compression format.
  • httpDebugFlag: logging switch, default is 131. 131: error and alarm messages only, 135: debug messages, 143: very detailed debug messages.
  • httpDbNameMandatory: users must specify the default database name in the RESTful URL. The default is 0, which turns off this check. If set to 1, users must put a default database name in every RESTful URL. Otherwise, it will return an execution error and reject this SQL statement, regardless of whether the SQL statement executed at this time requires a specified database.
REST API - 图2note

If you are using the REST API provided by taosd, you should write the above configuration in taosd’s configuration file taos.cfg. If you use the REST API of taosAdapter, you need to refer to taosAdapter corresponding configuration method.