Skip to main content

Insert Using SQL

Introduction​

Application programs can execute INSERT statement through client libraries to insert rows. The TDengine CLI can also be used to manually insert data.

Insert Single Row​

The below SQL statement is used to insert one row into table "d1001".

INSERT INTO d1001 VALUES (ts1, 10.3, 219, 0.31);

ts1 is Unix timestamp, the timestamps which is larger than the difference between current time and KEEP in config is only allowed. For further detail, refer to TDengine SQL insert timestamp section.

Insert Multiple Rows​

Multiple rows can be inserted in a single SQL statement. The example below inserts 2 rows into table "d1001".

INSERT INTO d1001 VALUES (ts2, 10.2, 220, 0.23) (ts2, 10.3, 218, 0.25);

ts1 and ts2 is Unix timestamp, the timestamps which is larger than the difference between current time and KEEP in config is only allowed. For further detail, refer to TDengine SQL insert timestamp section.

Insert into Multiple Tables​

Data can be inserted into multiple tables in the same SQL statement. The example below inserts 2 rows into table "d1001" and 1 row into table "d1002".

INSERT INTO d1001 VALUES (ts1, 10.3, 219, 0.31) (ts2, 12.6, 218, 0.33) d1002 VALUES (ts3, 12.3, 221, 0.31);

ts1, ts2 and ts3 is Unix timestamp, the timestamps which is larger than the difference between current time and KEEP in config is only allowed. For further detail, refer to TDengine SQL insert timestamp section.

For more details about INSERT please refer to INSERT.

info
  • Inserting in batches can improve performance. The higher the batch size, the better the performance. Please note that a single row can't exceed 48K bytes and each SQL statement can't exceed 1MB.
  • Inserting with multiple threads can also improve performance. However, at a certain point, increasing the number of threads no longer offers any benefit and can even decrease performance due to the overhead involved in frequent thread switching. The optimal number of threads for a system depends on the processing capabilities and configuration of the server, the configuration of the database, the data schema, and the batch size for writing data. In general, more powerful clients and servers can support higher numbers of concurrently writing threads. Given a sufficiently powerful server, a higher number of vgroups for a database also increases the number of concurrent writes. Finally, a simpler data schema enables more concurrent writes as well.
warning
  • If the timestamp of a new record already exists in a table, columns with new data for that timestamp replace old data with new data, while columns without new data are not affected.
  • The timestamp to be inserted must be newer than the timestamp of subtracting current time by the parameter KEEP. If KEEP is set to 3650 days, then the data older than 3650 days ago can't be inserted. The timestamp to be inserted cannot be newer than the timestamp of current time plus parameter DURATION. If DURATION is set to 2, the data newer than 2 days later can't be inserted.

Sample program​

Insert Using SQL​

package com.taos.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;


public class RestInsertExample {
    private static Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:TAOS-RS://localhost:6041?user=root&password=taosdata";
        return DriverManager.getConnection(jdbcUrl);
    }

    private static List<String> getRawData() {
        return Arrays.asList(
                "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,'California.SanFrancisco',2",
                "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,'California.SanFrancisco',2",
                "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,'California.SanFrancisco',2",
                "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,'California.SanFrancisco',3",
                "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,'California.LosAngeles',2",
                "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,'California.LosAngeles',2",
                "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,'California.LosAngeles',3",
                "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,'California.LosAngeles',3"
        );
    }


    /**
     * The generated SQL is:
     * INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:05.000',10.30000,219,0.31000)
     * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:15.000',12.60000,218,0.33000)
     * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:16.800',12.30000,221,0.31000)
     * power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES('2018-10-03 14:38:16.650',10.30000,218,0.25000)
     * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:05.500',11.80000,221,0.28000)
     * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:16.600',13.40000,223,0.29000)
     * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:05.000',10.80000,223,0.29000)
     * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:06.500',11.50000,221,0.35000)
     */
    private static String getSQL() {
        StringBuilder sb = new StringBuilder("INSERT INTO ");
        for (String line : getRawData()) {
            String[] ps = line.split(",");
            sb.append("power." + ps[0]).append(" USING power.meters TAGS(")
                    .append(ps[5]).append(", ") // tag: location
                    .append(ps[6]) // tag: groupId
                    .append(") VALUES(")
                    .append('\'').append(ps[1]).append('\'').append(",") // ts
                    .append(ps[2]).append(",") // current
                    .append(ps[3]).append(",") // voltage
                    .append(ps[4]).append(") "); // phase
        }
        return sb.toString();
    }

    public static void insertData() throws SQLException {
        try (Connection conn = getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE DATABASE power KEEP 3650");
                stmt.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
                        "TAGS (location BINARY(64), groupId INT)");
                String sql = getSQL();
                int rowCount = stmt.executeUpdate(sql);
                System.out.println("rowCount=" + rowCount); // rowCount=8
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        insertData();
    }
}

view source code

note
  1. With either native connection or REST connection, the above samples can work well.
  2. Please note that use db can't be used with a REST connection because REST connections are stateless, so in the samples dbName.tbName is used to specify the table name.

Insert with Parameter Binding​

TDengine also provides API support for parameter binding. Similar to MySQL, only ? can be used in these APIs to represent the parameters to bind. This avoids the resource consumption of SQL syntax parsing when writing data through the parameter binding interface, thus significantly improving write performance in most cases.

Parameter binding is available only with native connection.

package com.taos.example;

import com.taosdata.jdbc.TSDBPreparedStatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Random;
import java.util.stream.Collectors;

public class StmtInsertExample {
    private static String datePattern = "yyyy-MM-dd HH:mm:ss.SSS";
    private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern(datePattern);

    private static List<String> getRawData(int size) {
        SimpleDateFormat format = new SimpleDateFormat(datePattern);
        List<String> result = new ArrayList<>();
        long current = System.currentTimeMillis();
        Random random = new Random();
        for (int i = 0; i < size; i++) {
            String time = format.format(current + i);
            int id = random.nextInt(10);
            result.add("d" + id + "," + time + ",10.30000,219,0.31000,California.SanFrancisco,2");
        }
        return result.stream()
                .sorted(Comparator.comparing(s -> s.split(",")[0])).collect(Collectors.toList());
    }

    private static Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:TAOS://localhost:6030?user=root&password=taosdata";
        return DriverManager.getConnection(jdbcUrl);
    }

    private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE DATABASE if not exists power KEEP 3650");
            stmt.executeUpdate("use power");
            stmt.execute("CREATE STABLE if not exists meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
                    "TAGS (location BINARY(64), groupId INT)");
        }
    }

    private static void insertData() throws SQLException {
        try (Connection conn = getConnection()) {
            createTable(conn);
            String psql = "INSERT INTO ? USING power.meters TAGS(?, ?) VALUES(?, ?, ?, ?)";
            try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
                String tableName = null;
                ArrayList<Long> ts = new ArrayList<>();
                ArrayList<Float> current = new ArrayList<>();
                ArrayList<Integer> voltage = new ArrayList<>();
                ArrayList<Float> phase = new ArrayList<>();
                for (String line : getRawData(100000)) {
                    String[] ps = line.split(",");
                    if (tableName == null) {
                        // bind table name and tags
                        tableName = "power." + ps[0];
                        pst.setTableName(ps[0]);
                        pst.setTagString(0, ps[5]);
                        pst.setTagInt(1, Integer.valueOf(ps[6]));
                    } else {
                        if (!tableName.equals(ps[0])) {
                            pst.setTimestamp(0, ts);
                            pst.setFloat(1, current);
                            pst.setInt(2, voltage);
                            pst.setFloat(3, phase);
                            pst.columnDataAddBatch();
                            pst.columnDataExecuteBatch();

                            // bind table name and tags
                            tableName = ps[0];
                            pst.setTableName(ps[0]);
                            pst.setTagString(0, ps[5]);
                            pst.setTagInt(1, Integer.valueOf(ps[6]));
                            ts.clear();
                            current.clear();
                            voltage.clear();
                            phase.clear();
                        }
                    }
                    // bind values
                    // ps[1] looks like: 2018-10-03 14:38:05.000
                    LocalDateTime localDateTime = LocalDateTime.parse(ps[1], formatter);
                    ts.add(localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli());
                    current.add(Float.valueOf(ps[2]));
                    voltage.add(Integer.valueOf(ps[3]));
                    phase.add(Float.valueOf(ps[4]));
                }
                pst.setTimestamp(0, ts);
                pst.setFloat(1, current);
                pst.setInt(2, voltage);
                pst.setFloat(3, phase);
                pst.columnDataAddBatch();
                pst.columnDataExecuteBatch();
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        insertData();
    }
}

view source code

TDengine

TDengine™ is a next generation data historian purpose-built for Industry 4.0 and Industrial IoT. It enables real-time data ingestion, storage, analysis, and distribution of petabytes per day, generated by billions of sensors and data collectors. With TDengine making big data affordable and accessible, digital transformation has never been easier.

© 2022–2024 TDengine

Products
TDengine Enterprise
TDengine Cloud
Learn
Docs
Blog
Downloads
Company
About
Newsroom
Legal
Partners
Sitemap
Newsletter
Contact Us
Social