ApsaraDB for SelectDB:Use INSERT INTO to import data

Run ETL on an internal table

To transform data from one SelectDB table and write the results to another:

INSERT INTO bj_store_sales
SELECT id, total, user_id, sale_timestamp FROM store_sales WHERE region = "bj";

This reads rows from store_sales where region = "bj" and writes them to bj_store_sales.

Sync data from a data lake

SelectDB catalogs let you map external data sources — including Hive, Iceberg, Hudi, Elasticsearch, and Java Database Connectivity (JDBC) sources — and query them with federated queries. Use a catalog to sync data from a data lake into a SelectDB table.

The following example syncs data from a Hive source into SelectDB.

1. Connect to your SelectDB instance. For details, see Connect to an ApsaraDB for SelectDB instance by using a MySQL client.

2. Create a catalog to integrate the Hive data source. For details, see Hive data source.

3. (Optional) Create a destination database. Skip this step if the database already exists.

   CREATE DATABASE hive_db;

4. Switch to the destination database.

   USE hive_db;

5. Create a destination table. If the table already exists, verify that its column types match the source table. For the type mapping reference, see Column data type mappings.

   CREATE TABLE test_Hive2SelectDB
   (
       id int,
       name varchar(50),
       age int
   )
   DISTRIBUTED BY HASH(id) BUCKETS 4
   PROPERTIES("replication_num" = "1");

6. (Optional) Preview the table before importing.

   SELECT * FROM test_Hive2SelectDB;

   

7. Run the INSERT INTO...SELECT statement to sync data. Assign a unique label to the import job with WITH LABEL.

   INSERT INTO test_Hive2SelectDB WITH LABEL test_label SELECT * FROM hive_catalog.testdb.hive_t;

8. Query the destination table to verify the data. The destination table data is shown on the left and the source data on the right.

   

Using a SQL client

Wrap multiple INSERT INTO statements in a transaction to batch them into a single import:

BEGIN;
INSERT INTO test_table VALUES (1, 'Zhang San', 32),(2, 'Li Si', 45),(3, 'Zhao Liu', 23);
INSERT INTO test_table VALUES (4, 'Wang Yi', 32),(5, 'Zhao Er', 45),(6, 'Li Er', 23);
INSERT INTO test_table VALUES (7, 'Li Yi', 32),(8, 'Wang San', 45),(9, 'Zhao Si', 23);
COMMIT;

Using a JDBC application

The following example batches multiple INSERT INTO statements in a single transaction using JDBC. Replace the placeholder values with your own.

public static void main(String[] args) throws Exception {
    // Number of INSERT statements per batch
    int insertNum = 10;
    // Number of rows per INSERT statement
    int batchSize = 10000;

    // Replace <host> and <port> with your VPC (virtual private cloud) endpoint values.
    // Find these on the Instance Details page under Network Information.
    String URL = "jdbc:mysql://<host>:<port>/test_db?useLocalSessionState=true";
    Connection connection = DriverManager.getConnection(URL, "admin", "<password>");
    Statement statement = connection.createStatement();
    statement.execute("BEGIN;");

    for (int num = 0; num < insertNum; num++) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO test_table VALUES ");
        for (int i = 0; i < batchSize; i++) {
            if (i > 0) {
                sql.append(",");
            }
            // Replace with your actual field values
            sql.append("(1, 'Zhang San', 32)");
        }
        statement.addBatch(sql.toString());
    }
    statement.addBatch("COMMIT;");
    statement.executeBatch();

    statement.close();
    connection.close();
}

Successful import with no rows

If the SELECT clause returns no rows, SelectDB returns:

INSERT INTO tbl1 SELECT * FROM empty_tbl;
Query OK, 0 rows affected (0.02 sec)

Query OK means the statement ran without error. 0 rows affected means no data was imported.

Successful import with rows

INSERT INTO tbl1 SELECT * FROM tbl2;
Query OK, 4 rows affected (0.38 sec)
{'label':'insert_8510c568-9eda-****-9e36-6adc7d35291c', 'status':'visible', 'txnId':'4005'}

The JSON response includes:

If status is committed, the data will eventually become visible. To check:

SHOW TRANSACTION WHERE id=4005;

If TransactionStatus shows visible, the data is queryable.

Successful import with filtered rows

If some rows were filtered out, the result shows a warning count:

Query OK, 2 rows affected, 2 warnings (0.31 sec)
{'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'visible', 'txnId':'4005'}

To inspect filtered rows, find the label in the SHOW LOAD output:

SHOW LOAD WHERE label="insert_f0747f0e-7a35-****-affa-13a235f4020d";

Then query the error details with the URL from the output:

SHOW LOAD WARNINGS ON "<error-url>";

Failed import

If the import fails, no data is written and SelectDB returns an error:

INSERT INTO tbl1 SELECT * FROM tbl2 WHERE k1 = "a";
ERROR 1064 (HY000): all partitions have no load data. url: http://10.74.167.16:8042/api/_load_error_log?file=__shard_2/error_log_insert_stmt_ba8bb9e158e4879-ae8de8507c0bf8a2_ba8bb9e158e4879_ae8de8507c0bf8a2

Retrieve detailed error information using the URL in the error message:

SHOW LOAD WARNINGS ON "<error-url>";

Session variables

Partial column updates

By default, INSERT INTO writes full rows. To update only specific columns on a Unique Key model table that uses Merge on Write (MOW):

SET enable_unique_key_partial_update = true;

For a full list of variables, see Variable management.