Running SQL Statements

TDengine provides comprehensive support for SQL, allowing users to perform data queries, inserts, and deletions 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 specific to time-series data processing, such as aggregation queries, downsampling, and interpolation queries, to accommodate the characteristics of time-series data. These extensions enable users to handle time-series data more efficiently and conduct complex data analysis and processing. For specific supported SQL syntax, please refer to TDengine SQL.

Below is an introduction to how to use various language connectors to execute SQL commands for creating databases, creating tables, inserting data, and querying data.

note

REST connection: Each programming language's connector encapsulates the connection using HTTP requests, supporting data writing and querying operations. Developers still access TDengine through the interfaces provided by the connector.
REST API: Directly calls the REST API interface provided by taosadapter to perform data writing and querying operations. Code examples demonstrate using the curl command.

Create Database and Table

Using a smart meter as an example, below demonstrates how to execute SQL commands using various language connectors to create a database named power and then set power as the default database. Next, it creates a supertable named meters, with columns including timestamp, current, voltage, phase, and tags 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, specifying 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 using the <dbName>.<tableName> format; using the USE DBName approach in the application is not recommended.

Insert Data

Using a smart meter as an example, below demonstrates how to execute SQL to insert data into the meters supertable in the power database. The example uses TDengine's automatic table creation SQL syntax to write 3 data entries into the d1001 subtable and 1 data entry into the d1002 subtable, and then prints the actual number of inserted data entries.

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 function that defaults to the current time of the client's computer. NOW + 1s means the client's current time plus 1 second; the number after represents the time unit: a (milliseconds), s (seconds), m (minutes), h (hours), d (days), w (weeks), n (months), y (years).

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 function that defaults to the current time of the client's computer. NOW + 1s means the client's current time plus 1 second; the number after represents the 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

Using a smart meter as an example, below demonstrates how to execute SQL using various language connectors to query data, retrieving up to 100 rows from the meters supertable in the power database 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

Query operations are consistent with relational databases. When accessing return field content using indexes, start from 1; it is recommended to use field names for retrieval.

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

The Rust connector also supports using serde for deserialization to obtain results as structured data:

// 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 tracing. It acts similarly to traceId in distributed systems. A request may need to go through multiple services or modules to complete. reqId is used to identify and associate all related operations for this request, making it easier to trace and analyze the complete path of the request.

Benefits of using reqId include:

• Request tracing: By associating the same reqId with all related operations of a request, you can trace the complete path of the request within the system.
• Performance analysis: Analyzing a request's reqId allows you to understand the processing time across various services and modules, helping to identify performance bottlenecks.
• Fault diagnosis: When a request fails, you can find out where the issue occurred by examining the reqId associated with that request.

If users do not set a reqId, the connector will randomly generate one internally, but it is recommended to set it explicitly for better association with user requests.

Below are code samples for setting reqId while executing SQL with 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, specifying 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'