Running SQL Statements

TDengine provides comprehensive support for the SQL language, allowing users to query, insert, and delete data using familiar SQL syntax. TDengine's SQL also supports database and table management operations, such as creating, modifying, and deleting databases and tables. TDengine extends standard SQL by introducing features unique to time-series data processing, such as aggregation queries, downsampling, and interpolation queries, to adapt to the characteristics of time-series data. These extensions enable users to process time-series data more efficiently and perform complex data analysis and processing. For specific supported SQL syntax, please refer to TDengine SQL

Below, we introduce how to use language connectors to execute SQL for creating databases, tables, writing data, and querying data.

note

REST connection: Connectors for various programming languages encapsulate the use of HTTP requests for connections, supporting data writing and querying operations, with developers still using the interfaces provided by the connectors to access TDengine.
REST API: Directly call the REST API interface provided by taosadapter for data writing and querying operations. Code examples use the curl command for demonstration.

Creating Databases and Tables

Below, using smart meters as an example, we show how to use language connectors to execute SQL commands to create a database named power, then use the power database as the default database. Next, create a supertable (STABLE) named meters, whose table structure includes columns for timestamp, current, voltage, phase, etc., and labels for group ID and location.

Java

try (Connection connection = DriverManager.getConnection(jdbcUrl, properties);
     Statement stmt = connection.createStatement()) {

    // create database
    int rowsAffected = stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS power");
    // you can check rowsAffected here
    System.out.println("Create database power successfully, rowsAffected: " + rowsAffected);
    // create table
    rowsAffected = stmt.executeUpdate("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))");
    // you can check rowsAffected here
    System.out.println("Create stable power.meters successfully, rowsAffected: " + rowsAffected);
} catch (Exception ex) {
    // please refer to the JDBC specifications for detailed exceptions info
    System.out.printf("Failed to create database power or stable meters, %sErrMessage: %s%n",
            ex instanceof SQLException ? "ErrCode: " + ((SQLException) ex).getErrorCode() + ", " : "",
            ex.getMessage());
    // Print stack trace for context in examples. Use logging in production.
    ex.printStackTrace();
    throw ex;
}

view source code

Python

WebSocket Connection

import taosws

conn = None
host = "localhost"
port = 6041
try:
    conn = taosws.connect(user="root",
                          password="taosdata",
                          host=host,
                          port=port)

    # create database
    rowsAffected = conn.execute(f"CREATE DATABASE IF NOT EXISTS power")
    print(f"Create database power successfully, rowsAffected: {rowsAffected}");
    
    # create super table
    rowsAffected = conn.execute(
        "CREATE TABLE IF NOT EXISTS power.meters (`ts` TIMESTAMP, `current` FLOAT, `voltage` INT, `phase` FLOAT) TAGS (`groupid` INT, `location` BINARY(16))"
    )
    print(f"Create stable power.meters successfully, rowsAffected: {rowsAffected}");

except Exception as err:
    print(f"Failed to create database power or stable meters, ErrMessage:{err}") 
    raise err
finally:
    if conn:
        conn.close()

view source code

Native Connection

import taos

conn = None
host = "localhost"
port = 6030
try:
    conn = taos.connect(host=host,
                        port=port,
                        user="root",
                        password="taosdata")

    # create database
    rowsAffected = conn.execute(f"CREATE DATABASE IF NOT EXISTS power")
    print(f"Create database power successfully, rowsAffected: {rowsAffected}");
    
    # create super table
    rowsAffected = conn.execute(
        "CREATE TABLE IF NOT EXISTS power.meters (`ts` TIMESTAMP, `current` FLOAT, `voltage` INT, `phase` FLOAT) TAGS (`groupid` INT, `location` BINARY(16))"
    )
    print(f"Create stable power.meters successfully, rowsAffected: {rowsAffected}");

except Exception as err:
    print(f"Failed to create database power or stable meters, ErrMessage:{err}") 
    raise err
finally:
    if conn:
        conn.close()       

view source code

Rest Connection

import taosrest

conn = None
url = "http://localhost:6041"
try:
    conn = taosrest.connect(url=url,
                   user="root",
                   password="taosdata",
                   timeout=30)

    # create database
    rowsAffected = conn.execute(f"CREATE DATABASE IF NOT EXISTS power")
    print(f"Create database power successfully, rowsAffected: {rowsAffected}");

    # create super table
    rowsAffected = conn.execute(
        f"CREATE TABLE IF NOT EXISTS power.meters (`ts` TIMESTAMP, `current` FLOAT, `voltage` INT, `phase` FLOAT) TAGS (`groupid` INT, `location` BINARY(16))"
    )
    print(f"Create stable power.meters successfully, rowsAffected: {rowsAffected}");

except Exception as err:
    print(f"Failed to create database power or stable meters, ErrMessage:{err}") 
    raise err
finally:
    if conn:
        conn.close()

view source code

Go

// create database
res, err := db.Exec("CREATE DATABASE IF NOT EXISTS power")
if err != nil {
	log.Fatalln("Failed to create database power, ErrMessage: " + err.Error())
}
rowsAffected, err := res.RowsAffected()
if err != nil {
	log.Fatalln("Failed to get create database rowsAffected, ErrMessage: " + err.Error())
}
// you can check rowsAffected here
fmt.Println("Create database power successfully, rowsAffected: ", rowsAffected)
// create table
res, err = db.Exec("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))")
if err != nil {
	log.Fatalln("Failed to create stable meters, ErrMessage: " + err.Error())
}
rowsAffected, err = res.RowsAffected()
if err != nil {
	log.Fatalln("Failed to get create stable rowsAffected, ErrMessage: " + err.Error())
}
// you can check rowsAffected here
fmt.Println("Create stable power.meters successfully, rowsAffected:", rowsAffected)

view source code

Rust

let taos = TaosBuilder::from_dsn(url)?.build().await?;

// create database and use it
match taos.exec_many([
    "CREATE DATABASE IF NOT EXISTS power",
]).await {
    Ok(afffected_rows) => println!("Create database power successfully, rowsAffected: {}", afffected_rows),
    Err(err) => {
        eprintln!("Failed to create database power, ErrMessage: {}", err);
        return Err(err.into());
    }
}

// create super table
match taos.exec_many([
    "CREATE STABLE IF NOT EXISTS power.meters (`ts` TIMESTAMP, `current` FLOAT, `voltage` INT, `phase` FLOAT) \
    TAGS (`groupid` INT, `location` BINARY(24))",
]).await {
    Ok(afffected_rows) => println!("Create stable power.meters successfully, rowsAffected: {}", afffected_rows),
    Err(err) => {
        eprintln!("Failed to create stable power.meters, ErrMessage: {}", err);
        return Err(err.into());
    }
}

Ok(())

view source code

Node.js

async function createDbAndTable() {
    let wsSql = null;
    try {
        let conf = new taos.WSConfig(dsn);
        conf.setUser('root');
        conf.setPwd('taosdata');
        conf.setDb('power');
        wsSql = await taos.sqlConnect(conf);
        console.log("Connected to " + dsn + " successfully.");
        // create database
        await wsSql.exec('CREATE DATABASE IF NOT EXISTS power');
        console.log("Create database power successfully.");
        // create table
        await wsSql.exec('CREATE STABLE IF NOT EXISTS power.meters ' +
            '(ts timestamp, current float, voltage int, phase float) ' +
            'TAGS (location binary(64), groupId int);');

        console.log("Create stable power.meters successfully");
    } catch (err) {
        console.error(`Failed to create database power or stable meters, ErrCode: ${err.code}, ErrMessage: ${err.message}`);
        throw err;
    } finally {
        if (wsSql) {
            await wsSql.close();
        }
    }

}

view source code

C#

try
{
    // create database
    var affected = client.Exec("CREATE DATABASE IF NOT EXISTS power");
    Console.WriteLine($"Create database power successfully, rowsAffected: {affected}");
    // create table
    affected = client.Exec(
        "CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))");
    Console.WriteLine($"Create stable power.meters successfully, rowsAffected: {affected}");
}
catch (TDengineError e)
{
    // handle TDengine error
    Console.WriteLine("Failed to create database power or stable meters, ErrCode: " + e.Code +
                      ", ErrMessage: " + e.Error);
    throw;
}
catch (Exception e)
{
    // handle other exceptions
    Console.WriteLine("Failed to create database power or stable meters, ErrMessage: " + e.Message);
    throw;
}

view source code

C

WebSocket Connection

int   code = 0;
char *dsn = "ws://localhost:6041";
// connect
WS_TAOS *taos = ws_connect(dsn);

if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s, ErrCode: 0x%x, ErrMessage: %s.\n", dsn, ws_errno(NULL), ws_errstr(NULL));
  return -1;
}

// create database
WS_RES *result = ws_query(taos, "CREATE DATABASE IF NOT EXISTS power");
code = ws_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to create database power, ErrCode: 0x%x, ErrMessage: %s.\n", code, ws_errstr(result));
  ws_close(taos);
  return -1;
}
ws_free_result(result);
fprintf(stdout, "Create database power successfully.\n");

// create table
const char *sql =
    "CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId "
    "INT, location BINARY(24))";
result = ws_query(taos, sql);
code = ws_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to create stable power.meters, ErrCode: 0x%x, ErrMessage: %s\n.", code, ws_errstr(result));
  ws_close(taos);
  return -1;
}
ws_free_result(result);
fprintf(stdout, "Create stable power.meters successfully.\n");

// close & clean
ws_close(taos);
return 0;

view source code

Native Connection

const char *host = "localhost";
const char *user = "root";
const char *password = "taosdata";
uint16_t    port = 6030;
int         code = 0;

// connect
TAOS *taos = taos_connect(host, user, password, NULL, port);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s:%hu, ErrCode: 0x%x, ErrMessage: %s.\n", host, port, taos_errno(NULL),
         taos_errstr(NULL));
  taos_cleanup();
  return -1;
}

// create database
TAOS_RES *result = taos_query(taos, "CREATE DATABASE IF NOT EXISTS power");
code = taos_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to create database power, ErrCode: 0x%x, ErrMessage: %s.\n", code, taos_errstr(result));
  taos_close(taos);
  taos_cleanup();
  return -1;
}
taos_free_result(result);
fprintf(stdout, "Create database power successfully.\n");

// create table
const char *sql =
    "CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId "
    "INT, location BINARY(24))";
result = taos_query(taos, sql);
code = taos_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to create stable power.meters, ErrCode: 0x%x, ErrMessage: %s\n.", code, taos_errstr(result));
  taos_close(taos);
  taos_cleanup();
  return -1;
}
taos_free_result(result);
fprintf(stdout, "Create stable power.meters successfully.\n");

// close & clean
taos_close(taos);
taos_cleanup();
return 0;

view source code

REST API

Create Database

curl --location -uroot:taosdata 'http://127.0.0.1:6041/rest/sql' \
--data 'CREATE DATABASE IF NOT EXISTS power'

Create Table, specify the database as power in the URL

curl --location -uroot:taosdata 'http://127.0.0.1:6041/rest/sql/power' \
--data 'CREATE STABLE IF NOT EXISTS meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (groupId INT, location BINARY(24))'

Note: It is recommended to construct SQL statements in the format of <dbName>.<tableName>. It is not recommended to use USE DBName in applications.

Insert Data

Below, using smart meters as an example, demonstrates how to use connectors to execute SQL to insert data into the power database's meters supertable. The example uses TDengine's auto table creation SQL syntax, writes 3 records into the d1001 subtable, writes 1 record into the d1002 subtable, and then prints the actual number of records inserted.

Java

// insert data, please make sure the database and table are created before
String insertQuery = "INSERT INTO " +
        "power.d1001 USING power.meters TAGS(2,'California.SanFrancisco') " +
        "VALUES " +
        "(NOW + 1a, 10.30000, 219, 0.31000) " +
        "(NOW + 2a, 12.60000, 218, 0.33000) " +
        "(NOW + 3a, 12.30000, 221, 0.31000) " +
        "power.d1002 USING power.meters TAGS(3, 'California.SanFrancisco') " +
        "VALUES " +
        "(NOW + 1a, 10.30000, 218, 0.25000) ";
try (Connection connection = DriverManager.getConnection(jdbcUrl, properties);
     Statement stmt = connection.createStatement()) {

    int affectedRows = stmt.executeUpdate(insertQuery);
    // you can check affectedRows here
    System.out.println("Successfully inserted " + affectedRows + " rows to power.meters.");
} catch (Exception ex) {
    // please refer to the JDBC specifications for detailed exceptions info
    System.out.printf("Failed to insert data to power.meters, sql: %s, %sErrMessage: %s%n",
            insertQuery,
            ex instanceof SQLException ? "ErrCode: " + ((SQLException) ex).getErrorCode() + ", " : "",
            ex.getMessage());
    // Print stack trace for context in examples. Use logging in production.
    ex.printStackTrace();
    throw ex;
}

view source code

Note NOW is an internal system function, defaulting to the current time of the client's computer. NOW + 1s represents the client's current time plus 1 second, with the number following representing the time unit: a (millisecond), s (second), m (minute), h (hour), d (day), w (week), n (month), y (year).

Python

WebSocket Connection

import taosws

conn = None
host="localhost"
port=6041
try:
    conn = taosws.connect(user="root",
                          password="taosdata",
                          host=host,
                          port=port)

    sql = """
        INSERT INTO 
        power.d1001 USING power.meters (groupid, location) TAGS(2, 'California.SanFrancisco')
            VALUES (NOW + 1a, 10.30000, 219, 0.31000) 
            (NOW + 2a, 12.60000, 218, 0.33000) (NOW + 3a, 12.30000, 221, 0.31000)
        power.d1002 USING power.meters (groupid, location) TAGS(3, 'California.SanFrancisco') 
            VALUES (NOW + 1a, 10.30000, 218, 0.25000)
        """
    affectedRows = conn.execute(sql)
    print(f"Successfully inserted {affectedRows} rows to power.meters.")

except Exception as err:
    print(f"Failed to insert data to power.meters, sql: {sql}, ErrMessage: {err}.")
    raise err
finally:
    if conn:
        conn.close()

view source code

Native Connection

import taos

conn = None
host = "localhost"
port = 6030
try:
    conn = taos.connect(host=host,
                        port=port,
                        user="root",
                        password="taosdata")

    sql = """
        INSERT INTO 
        power.d1001 USING power.meters (groupid, location) TAGS(2, 'California.SanFrancisco')
            VALUES (NOW + 1a, 10.30000, 219, 0.31000) 
            (NOW + 2a, 12.60000, 218, 0.33000) (NOW + 3a, 12.30000, 221, 0.31000)
        power.d1002 USING power.meters (groupid, location)  TAGS(3, 'California.SanFrancisco') 
            VALUES (NOW + 1a, 10.30000, 218, 0.25000)
        """
    affectedRows = conn.execute(sql)
    print(f"Successfully inserted {affectedRows} rows to power.meters.")

except Exception as err:
    print(f"Failed to insert data to power.meters, sql: {sql}, ErrMessage: {err}.")
    raise err
finally:
    if conn:
        conn.close()

view source code

Rest Connection

import taosrest

conn = None
url="http://localhost:6041"
try:
    conn = taosrest.connect(url=url,
                   user="root",
                   password="taosdata",
                   timeout=30)

    sql = """
        INSERT INTO 
        power.d1001 USING power.meters (groupid, location) TAGS(2, 'California.SanFrancisco')
            VALUES (NOW + 1a, 10.30000, 219, 0.31000) 
            (NOW + 2a, 12.60000, 218, 0.33000) (NOW + 3a, 12.30000, 221, 0.31000)
        power.d1002 USING power.meters (groupid, location) TAGS(3, 'California.SanFrancisco') 
            VALUES (NOW + 1a, 10.30000, 218, 0.25000)
        """
    affectedRows = conn.execute(sql)
    print(f"Successfully inserted {affectedRows} rows to power.meters.")

except Exception as err:
    print(f"Failed to insert data to power.meters, sql:{sql}, ErrMessage:{err}.")
    raise err
finally:
    if conn:
        conn.close()

view source code

Go

// insert data, please make sure the database and table are created before
insertQuery := "INSERT INTO " +
	"power.d1001 USING power.meters TAGS(2,'California.SanFrancisco') " +
	"VALUES " +
	"(NOW + 1a, 10.30000, 219, 0.31000) " +
	"(NOW + 2a, 12.60000, 218, 0.33000) " +
	"(NOW + 3a, 12.30000, 221, 0.31000) " +
	"power.d1002 USING power.meters TAGS(3, 'California.SanFrancisco') " +
	"VALUES " +
	"(NOW + 1a, 10.30000, 218, 0.25000) "
res, err = db.Exec(insertQuery)
if err != nil {
	log.Fatalf("Failed to insert data to power.meters, sql: %s, ErrMessage: %s\n", insertQuery, err.Error())
}
rowsAffected, err = res.RowsAffected()
if err != nil {
	log.Fatalf("Failed to get insert rowsAffected, sql: %s, ErrMessage: %s\n", insertQuery, err.Error())
}
// you can check affectedRows here
fmt.Printf("Successfully inserted %d rows to power.meters.\n", rowsAffected)

view source code

Rust

let insert_sql = r#"INSERT INTO 
                    power.d1001 USING power.meters TAGS(2,'California.SanFrancisco')  
                    VALUES 
                    (NOW + 1a, 10.30000, 219, 0.31000) 
                    (NOW + 2a, 12.60000, 218, 0.33000) 
                    (NOW + 3a, 12.30000, 221, 0.31000) 
                    power.d1002 USING power.meters TAGS(3, 'California.SanFrancisco') 
                    VALUES 
                    (NOW + 1a, 10.30000, 218, 0.25000) "#;
match taos.exec(insert_sql).await{
    Ok(affected_rows) => println!("Successfully inserted {} rows to power.meters.", affected_rows),
    Err(err) => {
        eprintln!("Failed to insert data to power.meters, sql: {}, ErrMessage: {}", insert_sql, err);
        return Err(err.into());
    }
}

view source code

Node.js

async function insertData() {
    let wsSql = null
    let insertQuery = "INSERT INTO " +
        "power.d1001 USING power.meters (location, groupId) TAGS('California.SanFrancisco', 2) " +
        "VALUES " +
        "(NOW + 1a, 10.30000, 219, 0.31000) " +
        "(NOW + 2a, 12.60000, 218, 0.33000) " +
        "(NOW + 3a, 12.30000, 221, 0.31000) " +
        "power.d1002 USING power.meters (location, groupId) TAGS('California.SanFrancisco', 3) " +
        "VALUES " +
        "(NOW + 1a, 10.30000, 218, 0.25000) "; 

    try {
        wsSql = await createConnect();
        taosResult = await wsSql.exec(insertQuery);
        console.log("Successfully inserted " + taosResult.getAffectRows() + " rows to power.meters.");
    } catch (err) {
        console.error(`Failed to insert data to power.meters, sql: ${insertQuery}, ErrCode: ${err.code}, ErrMessage: ${err.message}`);
        throw err;
    } finally {
        if (wsSql) {
            await wsSql.close();
        }
    }
}

view source code

C#

// insert data, please make sure the database and table are created before
var insertQuery = "INSERT INTO " +
                  "power.d1001 USING power.meters TAGS(2,'California.SanFrancisco') " +
                  "VALUES " +
                  "(NOW + 1a, 10.30000, 219, 0.31000) " +
                  "(NOW + 2a, 12.60000, 218, 0.33000) " +
                  "(NOW + 3a, 12.30000, 221, 0.31000) " +
                  "power.d1002 USING power.meters TAGS(3, 'California.SanFrancisco') " +
                  "VALUES " +
                  "(NOW + 1a, 10.30000, 218, 0.25000) ";
try
{
    var affectedRows = client.Exec(insertQuery);
    Console.WriteLine("Successfully inserted " + affectedRows + " rows to power.meters.");
}
catch (TDengineError e)
{
    // handle TDengine error
    Console.WriteLine("Failed to insert data to power.meters, sql: " + insertQuery + ", ErrCode: " +
                      e.Code + ", ErrMessage: " +
                      e.Error);
    throw;
}
catch (Exception e)
{
    // handle other exceptions
    Console.WriteLine("Failed to insert data to power.meters, sql: " + insertQuery + ", ErrMessage: " +
                      e.Message);
    throw;
}

view source code

C

WebSocket Connection

int   code = 0;
char *dsn = "ws://localhost:6041";
// connect
WS_TAOS *taos = ws_connect(dsn);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s, ErrCode: 0x%x, ErrMessage: %s.\n", dsn, ws_errno(NULL), ws_errstr(NULL));
  return -1;
}

// insert data, please make sure the database and table are already created
const char *sql =
    "INSERT INTO "
    "power.d1001 USING power.meters TAGS(2,'California.SanFrancisco') "
    "VALUES "
    "(NOW + 1a, 10.30000, 219, 0.31000) "
    "(NOW + 2a, 12.60000, 218, 0.33000) "
    "(NOW + 3a, 12.30000, 221, 0.31000) "
    "power.d1002 USING power.meters TAGS(3, 'California.SanFrancisco') "
    "VALUES "
    "(NOW + 1a, 10.30000, 218, 0.25000) ";
WS_RES *result = ws_query(taos, sql);
code = ws_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to insert data to power.meters, sql: %s, ErrCode: 0x%x, ErrMessage: %s\n.", sql, code,
          ws_errstr(result));
  ws_close(taos);
  return -1;
}
ws_free_result(result);

// you can check affectedRows here
int rows = ws_affected_rows(result);
fprintf(stdout, "Successfully inserted %d rows into power.meters.\n", rows);

// close & clean
ws_close(taos);
return 0;

view source code

Native Connection

const char *host = "localhost";
const char *user = "root";
const char *password = "taosdata";
uint16_t    port = 6030;
int         code = 0;

// connect
TAOS *taos = taos_connect(host, user, password, NULL, port);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s:%hu, ErrCode: 0x%x, ErrMessage: %s.\n", host, port, taos_errno(NULL),
         taos_errstr(NULL));
  taos_cleanup();
  return -1;
}

// insert data, please make sure the database and table are already created
const char *sql =
    "INSERT INTO "
    "power.d1001 USING power.meters TAGS(2,'California.SanFrancisco') "
    "VALUES "
    "(NOW + 1a, 10.30000, 219, 0.31000) "
    "(NOW + 2a, 12.60000, 218, 0.33000) "
    "(NOW + 3a, 12.30000, 221, 0.31000) "
    "power.d1002 USING power.meters TAGS(3, 'California.SanFrancisco') "
    "VALUES "
    "(NOW + 1a, 10.30000, 218, 0.25000) ";
TAOS_RES *result = taos_query(taos, sql);
code = taos_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to insert data to power.meters, sql: %s, ErrCode: 0x%x, ErrMessage: %s\n.", sql, code, taos_errstr(result));
  taos_close(taos);
  taos_cleanup();
  return -1;
}
taos_free_result(result);

// you can check affectedRows here
int rows = taos_affected_rows(result);
fprintf(stdout, "Successfully inserted %d rows into power.meters.\n", rows);

// close & clean
taos_close(taos);
taos_cleanup();
return 0;

view source code

Note NOW is an internal system function, defaulting to the current time of the client's computer. NOW + 1s represents the client's current time plus 1 second, where the number is followed by a time unit: a (milliseconds), s (seconds), m (minutes), h (hours), d (days), w (weeks), n (months), y (years).

REST API

Write data

curl --location -uroot:taosdata 'http://127.0.0.1:6041/rest/sql' \
--data 'INSERT INTO power.d1001 USING power.meters TAGS(2,'\''California.SanFrancisco'\'') VALUES (NOW + 1a, 10.30000, 219, 0.31000) (NOW + 2a, 12.60000, 218, 0.33000) (NOW + 3a, 12.30000, 221, 0.31000) power.d1002 USING power.meters TAGS(3, '\''California.SanFrancisco'\'') VALUES (NOW + 1a, 10.30000, 218, 0.25000)'

Query data

Below, using smart meters as an example, demonstrates how to use connectors in various languages to execute SQL to query data from the power database meters supertable, querying up to 100 rows of data and printing the results line by line.

Java

String sql = "SELECT ts, current, location FROM power.meters limit 100";
try (Connection connection = DriverManager.getConnection(jdbcUrl, properties);
     Statement stmt = connection.createStatement();
     // query data, make sure the database and table are created before
     ResultSet resultSet = stmt.executeQuery(sql)) {

    Timestamp ts;
    float current;
    String location;
    while (resultSet.next()) {
        ts = resultSet.getTimestamp(1);
        current = resultSet.getFloat(2);
        // we recommend using the column name to get the value
        location = resultSet.getString("location");

        // you can check data here
        System.out.printf("ts: %s, current: %f, location: %s %n", ts, current, location);
    }
} catch (Exception ex) {
    // please refer to the JDBC specifications for detailed exceptions info
    System.out.printf("Failed to query data from power.meters, sql: %s, %sErrMessage: %s%n",
            sql,
            ex instanceof SQLException ? "ErrCode: " + ((SQLException) ex).getErrorCode() + ", " : "",
            ex.getMessage());
    // Print stack trace for context in examples. Use logging in production.
    ex.printStackTrace();
    throw ex;
}

view source code

Note Querying and operating relational databases are consistent, use indices starting from 1 to get returned field content, and it is recommended to use field names to retrieve.

Python

WebSocket Connection

import taosws

conn = None
host="localhost"
port=6041
try:
    conn = taosws.connect(user="root",
                          password="taosdata",
                          host=host,
                          port=port)
    sql = "SELECT ts, current, location FROM power.meters limit 100"
    result = conn.query(sql)
    for row in result:
        print(f"ts: {row[0]}, current: {row[1]}, location:  {row[2]}")

except Exception as err:
    print(f"Failed to query data from power.meters, sql: {sql}, ErrMessage:{err}")
    raise err
finally:
    if conn:
        conn.close()

view source code

Native Connection

import taos
host="localhost"
port=6030
conn = None
try:
    conn = taos.connect(host=host,
                        port=port,
                        user="root",
                        password="taosdata")
    sql = "SELECT ts, current, location FROM power.meters limit 100"
    result = conn.query(sql)
    # Get data from result as list of tuple
    data = result.fetch_all()
    for row in data:
        print(f"ts: {row[0]}, current: {row[1]}, location:  {row[2]}")

except Exception as err:
    print(f"Failed to query data from power.meters, sql: {sql}, ErrMessage:{err}")
    raise err
finally:
    if conn:
        conn.close()

view source code

Rest Connection

import taosrest

client = None
url="http://localhost:6041"
try:
    client = taosrest.RestClient(url=url,
                   user="root",
                   password="taosdata",
                   timeout=30)
    sql = f"SELECT ts, current, location FROM power.meters limit 100"
    result = client.sql(sql)
    if result["data"]:
        for row in result["data"]:
            print(f"ts: {row[0]}, current: {row[1]}, location:  {row[2]}")

except Exception as err:
    print(f"Failed to query data from power.meters, sql: {sql}, ErrMessage:{err}")
    raise err

view source code

Go

// query data, make sure the database and table are created before
sql := "SELECT ts, current, location FROM power.meters limit 100"
rows, err := db.Query(sql)
if err != nil {
	log.Fatalf("Failed to query data from power.meters, sql: %s, ErrMessage: %s\n", sql, err.Error())
}
for rows.Next() {
	// Add your data processing logic here
	var (
		ts       time.Time
		current  float32
		location string
	)
	err = rows.Scan(&ts, &current, &location)
	if err != nil {
		log.Fatalf("Failed to scan data, sql: %s, ErrMessage: %s\n", sql, err)
	}
	fmt.Printf("ts: %s, current: %f, location: %s\n", ts, current, location)
}

view source code

Rust

// query data, make sure the database and table are created before
let sql = "SELECT ts, current, location FROM power.meters limit 100";
match taos.query(sql).await{
    Ok(mut result) => {
        for field in result.fields() {
            println!("got field: {}", field.name());
        }
        
        let mut rows = result.rows();
        let mut nrows = 0;
        while let Some(row) = rows.try_next().await? {
            for (col, (name, value)) in row.enumerate() {
                println!(
                    "[{}] got value in col {} (named `{:>8}`): {}",
                    nrows, col, name, value
                );
            }
            nrows += 1;
        }
    }
    Err(err) => {
        eprintln!("Failed to query data from power.meters, sql: {}, ErrMessage: {}", sql, err);
        return Err(err.into());
    }
}

view source code

Rust connector also supports using serde for deserializing to get structured results:

// query data, make sure the database and table are created before
#[derive(Debug, serde::Deserialize)]
#[allow(dead_code)]
struct Record {
    // deserialize timestamp to chrono::DateTime<Local>
    ts: DateTime<Local>,
    // float to f32
    current: Option<f32>,
    // binary/varchar to String
    location: String,
}

let sql = "SELECT ts, current, location FROM power.meters limit 100";
match taos.query("SELECT ts, current, location FROM power.meters limit 100").await {
    Ok(mut query) => {
        match query.deserialize::<Record>().try_collect::<Vec<_>>().await {
            Ok(records) => {
                dbg!(records);
            }
            Err(err) => {
                eprintln!("Failed to deserialize query results; ErrMessage: {}", err);
                return Err(err.into());
            }
        }
    }
    Err(err) => {
        eprintln!("Failed to query data from power.meters, sql: {}, ErrMessage: {}", sql, err);
        return Err(err.into());
    }
}

view source code

Node.js

async function queryData() {
    let wsRows = null;
    let wsSql = null;
    let sql = 'SELECT ts, current, location FROM power.meters limit 100';
    try {
        wsSql = await createConnect();
        wsRows = await wsSql.query(sql);
        while (await wsRows.next()) {
            let row = wsRows.getData();
            console.log('ts: ' + row[0] + ', current: ' + row[1] + ', location:  ' + row[2]);
        }
    }
    catch (err) {
        console.error(`Failed to query data from power.meters, sql: ${sql}, ErrCode: ${err.code}, ErrMessage: ${err.message}`);
        throw err;
    }
    finally {
        if (wsRows) {
            await wsRows.close();
        }
        if (wsSql) {
            await wsSql.close();
        }
    }
}

view source code

C#

// query data, make sure the database and table are created before
var query = "SELECT ts, current, location FROM power.meters limit 100";
try
{
    using (var rows = client.Query(query))
    {
        while (rows.Read())
        {
            // Add your data processing logic here
            var ts = (DateTime)rows.GetValue(0);
            var current = (float)rows.GetValue(1);
            var location = Encoding.UTF8.GetString((byte[])rows.GetValue(2));
            Console.WriteLine(
                $"ts: {ts:yyyy-MM-dd HH:mm:ss.fff}, current: {current}, location: {location}");
        }
    }
}
catch (TDengineError e)
{
    // handle TDengine error
    Console.WriteLine("Failed to query data from power.meters, sql: " + query + ", ErrCode: " + e.Code +
                      ", ErrMessage: " + e.Error);
    throw;
}
catch (Exception e)
{
    // handle other exceptions
    Console.WriteLine(
        "Failed to query data from power.meters, sql: " + query + ", ErrMessage: " + e.Message);
    throw;
}

view source code

C

WebSocket Connection

int   code = 0;
char *dsn = "ws://localhost:6041";

// connect
WS_TAOS *taos = ws_connect(dsn);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s, ErrCode: 0x%x, ErrMessage: %s.\n", dsn, ws_errno(NULL), ws_errstr(NULL));
  return -1;
}

// query data, please make sure the database and table are already created
const char *sql = "SELECT ts, current, location FROM power.meters limit 100";
WS_RES     *result = ws_query(taos, sql);
code = ws_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to query data from power.meters, sql: %s, ErrCode: 0x%x, ErrMessage: %s\n.", sql, code,
          ws_errstr(result));
  ws_close(taos);
  return -1;
}

WS_ROW          row = NULL;
int             rows = 0;
int             num_fields = ws_field_count(result);
const WS_FIELD *fields = ws_fetch_fields(result);

fprintf(stdout, "query successfully, got %d fields, the sql is: %s.\n", num_fields, sql);

// fetch the records row by row
while ((row = ws_fetch_row(result))) {
  // Add your data processing logic here

  rows++;
}
fprintf(stdout, "total rows: %d\n", rows);
ws_free_result(result);

// close & clean
ws_close(taos);
return 0;

view source code

Native Connection

const char *host = "localhost";
const char *user = "root";
const char *password = "taosdata";
uint16_t    port = 6030;
int         code = 0;

// connect
TAOS *taos = taos_connect(host, user, password, NULL, port);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s:%hu, ErrCode: 0x%x, ErrMessage: %s.\n", host, port, taos_errno(NULL),
         taos_errstr(NULL));
  taos_cleanup();
  return -1;
}

// query data, please make sure the database and table are already created
const char *sql = "SELECT ts, current, location FROM power.meters limit 100";
TAOS_RES   *result = taos_query(taos, sql);
code = taos_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to query data from power.meters, sql: %s, ErrCode: 0x%x, ErrMessage: %s\n.", sql, code,
         taos_errstr(result));
  taos_close(taos);
  taos_cleanup();
  return -1;
}

TAOS_ROW    row = NULL;
int         rows = 0;
int         num_fields = taos_field_count(result);
TAOS_FIELD *fields = taos_fetch_fields(result);

fprintf(stdout, "query successfully, got %d fields, the sql is: %s.\n", num_fields, sql);

// fetch the records row by row
while ((row = taos_fetch_row(result))) {
  // Add your data processing logic here

  rows++;
}
fprintf(stdout, "total rows: %d\n", rows);
taos_free_result(result);

// close & clean
taos_close(taos);
taos_cleanup();
return 0;

view source code

REST API

Query Data

curl --location -uroot:taosdata 'http://127.0.0.1:6041/rest/sql' \
--data 'SELECT ts, current, location FROM power.meters limit 100'

Execute SQL with reqId

reqId can be used for request link tracing, similar to the role of traceId in distributed systems. A request might need to pass through multiple services or modules to be completed. reqId is used to identify and associate all related operations of this request, allowing us to track and analyze the complete path of the request.

Using reqId has the following benefits:

• Request tracing: By associating the same reqId with all related operations of a request, you can trace the complete path of the request in the system.
• Performance analysis: By analyzing a request's reqId, you can understand the processing time of the request across various services and modules, thereby identifying performance bottlenecks.
• Fault diagnosis: When a request fails, you can identify where the problem occurred by examining the reqId associated with the request.

If the user does not set a reqId, the connector will internally generate one randomly, but it is recommended that users explicitly set it to better associate it with their requests.

Below are code examples of setting reqId to execute SQL in various language connectors.

Java

long reqId = 3L;
try (Connection connection = DriverManager.getConnection(jdbcUrl, properties);
     // Create a statement that allows specifying a request ID
     AbstractStatement aStmt = (AbstractStatement) connection.createStatement()) {

    try (ResultSet resultSet = aStmt.executeQuery("SELECT ts, current, location FROM power.meters limit 1", reqId)) {
        Timestamp ts;
        float current;
        String location;
        while (resultSet.next()) {
            ts = resultSet.getTimestamp(1);
            current = resultSet.getFloat(2);
            // we recommend using the column name to get the value
            location = resultSet.getString("location");

            // you can check data here
            System.out.printf("ts: %s, current: %f, location: %s %n", ts, current, location);

        }
    }
} catch (Exception ex) {
    // please refer to the JDBC specifications for detailed exceptions info
    System.out.printf("Failed to execute sql with reqId: %s, %sErrMessage: %s%n", reqId,
            ex instanceof SQLException ? "ErrCode: " + ((SQLException) ex).getErrorCode() + ", " : "",
            ex.getMessage());
    // Print stack trace for context in examples. Use logging in production.
    ex.printStackTrace();
    throw ex;
}

view source code

Python

WebSocket Connection

import taosws

conn = None
reqId = 3
host="localhost"
port=6041
try:
    conn = taosws.connect(
        user="root",
        password="taosdata",
        host=host,
        port=port,
    )

    result = conn.query_with_req_id("SELECT ts, current, location FROM power.meters limit 100", req_id=3)
    # Get data from result as list of tuple
    for row in result:
        print(f"ts: {row[0]}, current: {row[1]}, location:  {row[2]}")

except Exception as err:
    print(f"Failed to execute sql with reqId:{reqId}, ErrMessage:{err}")
    raise err
finally:
    if conn:
        conn.close()

view source code

Native Connection

import taos

conn = None
reqId = 3
host="localhost"
port=6030
try:
    conn = taos.connect(host=host,
                        port=port,
                        user="root",
                        password="taosdata")

    result = conn.query("SELECT ts, current, location FROM power.meters limit 100", reqId)
    # Get data from result as list of tuple
    data = result.fetch_all()
    for row in data:
         print(f"ts: {row[0]}, current: {row[1]}, location:  {row[2]}")

except Exception as err:
    print(f"Failed to execute sql with reqId:{reqId}, ErrMessage:{err}")
    raise err
finally:
    if conn:
        conn.close()

view source code

Rest Connection

import taosrest

client = None
url="http://localhost:6041"
reqId = 3
try:
    client = taosrest.RestClient(url=url,
                   user="root",
                   password="taosdata",
                   timeout=30)

    result = client.sql(f"SELECT ts, current, location FROM power.meters limit 100", reqId)
    if result["data"]:
        for row in result["data"]:
            print(f"ts: {row[0]}, current: {row[1]}, location:  {row[2]}")

except Exception as err:
    print(f"Failed to execute sql with reqId:{reqId}, ErrMessage:{err}")
    raise err

view source code

Go

// use context to set request id
reqId := int64(3)
ctx := context.WithValue(context.Background(), "taos_req_id", reqId)
// execute query with context
querySql := "SELECT ts, current, location FROM power.meters limit 1"
rows, err := db.QueryContext(ctx, querySql)
if err != nil {
	log.Fatalf("Failed to execute sql with reqId: %d, url: %s, sql: %s, ErrMessage: %s\n", reqId, taosDSN, querySql, err.Error())
}
for rows.Next() {
	// Add your data processing logic here
	var (
		ts       time.Time
		current  float32
		location string
	)
	err = rows.Scan(&ts, &current, &location)
	if err != nil {
		log.Fatalf("Failed to scan data, reqId: %d, url:%s, sql: %s, ErrMessage: %s\n", reqId, taosDSN, querySql, err)
	}
	fmt.Printf("ts: %s, current: %f, location: %s\n", ts, current, location)
}

view source code

Rust

    let req_id :u64  = 3;
    match taos.query_with_req_id("SELECT ts, current, location FROM power.meters limit 1", req_id).await{
        Ok(mut result) => {
            for field in result.fields() {
                println!("got field: {}", field.name());
            }
        
            let mut rows = result.rows();
            let mut nrows = 0;
            while let Some(row) = rows.try_next().await? {
                for (col, (name, value)) in row.enumerate() {
                    println!(
                        "[{}] got value in col {} (named `{:>8}`): {}",
                        nrows, col, name, value
                    );
                }
                nrows += 1;
            }
        }
        Err(err) => {
            eprintln!("Failed to execute sql with reqId: {}, ErrMessage: {}", req_id, err);
            return Err(err.into());
        }
    }

view source code

Node.js

async function sqlWithReqid() {
    let wsRows = null;
    let wsSql = null;
    let reqId = 1;
    try {
        wsSql = await createConnect();
        wsRows = await wsSql.query('SELECT ts, current, location FROM power.meters limit 100', reqId);
        while (await wsRows.next()) {
            let row = wsRows.getData();
            console.log('ts: ' + row[0] + ', current: ' + row[1] + ', location:  ' + row[2]);
        }
    }
    catch (err) {
        console.error(`Failed to query data from power.meters, reqId: ${reqId}, ErrCode: ${err.code}, ErrMessage: ${err.message}`);
        throw err;
    }
    finally {
        if (wsRows) {
            await wsRows.close();
        }
        if (wsSql) {
            await wsSql.close();
        }
    }
}

view source code

C#

var reqId = (long)3;
// query data
var query = "SELECT ts, current, location FROM power.meters limit 1";
try
{
    // query with request id 3
    using (var rows = client.Query(query, reqId))
    {
        while (rows.Read())
        {
            var ts = (DateTime)rows.GetValue(0);
            var current = (float)rows.GetValue(1);
            var location = Encoding.UTF8.GetString((byte[])rows.GetValue(2));
            Console.WriteLine(
                $"ts: {ts:yyyy-MM-dd HH:mm:ss.fff}, current: {current}, location: {location}");
        }
    }
}
catch (TDengineError e)
{
    // handle TDengine error
    Console.WriteLine("Failed to execute sql with reqId: " + reqId + ", sql: " + query + ", ErrCode: " +
                      e.Code + ", ErrMessage: " + e.Error);
    throw;
}
catch (Exception e)
{
    // handle other exceptions
    Console.WriteLine("Failed to execute sql with reqId: " + reqId + ", sql: " + query + ", ErrMessage: " +
                      e.Message);
    throw;
}

view source code

C

int   code = 0;
char *dsn = "ws://localhost:6041";

// connect
WS_TAOS *taos = ws_connect(dsn);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s, ErrCode: 0x%x, ErrMessage: %s.\n", dsn, ws_errno(NULL), ws_errstr(NULL));
  return -1;
}

const char *sql = "SELECT ts, current, location FROM power.meters limit 1";
// query data with reqid
long    reqid = 3L;
WS_RES *result = ws_query_with_reqid(taos, sql, reqid);
code = ws_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to execute sql withQID: %ld, ErrCode: 0x%x, ErrMessage: %s\n.", reqid, code,
          ws_errstr(result));
  ws_close(taos);
  return -1;
}

WS_ROW          row = NULL;
int             rows = 0;
int             num_fields = ws_field_count(result);
const WS_FIELD *fields = ws_fetch_fields(result);

fprintf(stdout, "query successfully, got %d fields, the sql is: %s.\n", num_fields, sql);

// fetch the records row by row
while ((row = ws_fetch_row(result))) {
  // Add your data processing logic here

  rows++;
}
fprintf(stdout, "total rows: %d\n", rows);
ws_free_result(result);

// close & clean
ws_close(taos);
return 0;

view source code

const char *host = "localhost";
const char *user = "root";
const char *password = "taosdata";
uint16_t    port = 6030;
int         code = 0;

// connect
TAOS *taos = taos_connect(host, user, password, NULL, port);
if (taos == NULL) {
  fprintf(stderr, "Failed to connect to %s:%hu, ErrCode: 0x%x, ErrMessage: %s.\n", host, port, taos_errno(NULL),
          taos_errstr(NULL));
  taos_cleanup();
  return -1;
}

const char *sql = "SELECT ts, current, location FROM power.meters limit 1";
// query data with reqid
long      reqid = 3L;
TAOS_RES *result = taos_query_with_reqid(taos, sql, reqid);
code = taos_errno(result);
if (code != 0) {
  fprintf(stderr, "Failed to execute sql withQID: %ld, ErrCode: 0x%x, ErrMessage: %s\n.", reqid, code,
          taos_errstr(result));
  taos_close(taos);
  taos_cleanup();
  return -1;
}

TAOS_ROW    row = NULL;
int         rows = 0;
int         num_fields = taos_field_count(result);
TAOS_FIELD *fields = taos_fetch_fields(result);

fprintf(stdout, "query successfully, got %d fields, the sql is: %s.\n", num_fields, sql);

// fetch the records row by row
while ((row = taos_fetch_row(result))) {
  // Add your data processing logic here

  rows++;
}
fprintf(stdout, "total rows: %d\n", rows);
taos_free_result(result);

// close & clean
taos_close(taos);
taos_cleanup();
return 0;

view source code

REST API

Query data, specify reqId as 3

curl --location -uroot:taosdata 'http://127.0.0.1:6041/rest/sql?req_id=3' \
--data 'SELECT ts, current, location FROM power.meters limit 1'