使用Insert Into语句导入数据及最佳实践-云数据库 SelectDB 版-阿里云

云数据库 SelectDB 版兼容标准SQL语法，可通过标准的Insert Into方式导入数据。

背景信息

Insert Into命令是MySQL等数据库中常用的数据导入方式。云数据库 SelectDB 版兼容标准SQL语法，支持通过Insert Into命令导入数据。包含以下两种：

INSERT INTO tbl SELECT ...
INSERT INTO tbl (col1, col2, ...) VALUES (1, 2, ...), (1,3, ...);
重要
INSERT INTO VALUES写入性能低，不建议在生产环境中使用。推荐使用Stream Load接口进行攒批写入，性能提升可达百倍。

注意事项

在业务场景中，频繁地写入少量数据可能导致实例性能大幅下降，甚至表死锁。因此，强烈建议您将数据进行攒批处理，单表的写入频率建议每10秒以上一次。

Insert Into Select

通过SelectDB提供的大量SQL函数和联邦查询能力，Insert Into Select能够高效地对SelectDB内部数据及外部数据湖数据进行计算处理，并将其导入SelectDB的新表中，以便于后续的数据分析服务。

内表数据ETL转换

如果数据已经在SelectDB表中，可通过Insert Into Select进行数据ETL（Extract，Transform，Load）转换，并将其保存到一个新表中。例如，将SelectDB中store_sales表中region为bj的数据保存至新表bj_store_sales，具体语句如下。

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

外部数据源同步

如果数据在数据湖等外部系统中，可以在SelectDB中创建Catalog，映射到数据湖等外部系统中的数据，然后通过Insert Into Select将其中的数据导入到SelectDB表中。具体步骤如下：使用Catalog集成Hive数据源

SelectDB支持使用Catalog集成Hive、Iceberg、Hudi、Elasticsearch、JDBC等数据源，本示例以集成并导入Hive数据源为例，其他数据源请参见湖仓一体。

连接SelectDB实例。具体操作，请参见通过MySQL客户端连接云数据库SelectDB版实例。
创建Catalog，集成外部数据源。如何操作，请参见Hive数据源。
（可选）创建数据库hive_db。
如果您已经创建了目标数据库，可跳过此步骤。
```
create database hive_db;
```
切换至目标数据库。
```
use hive_db;
```
创建表。
如果您已经有了目标表，根据列类型映射检查目标列类型需与Hive源数据列类型是否一一对应。
如果您还没有目标表，创建表时，目标列类型需与Hive源数据列类型一一对应。列映射详情，请参见列类型映射。
```
CREATE TABLE test_Hive2SelectDB
(
    id int,
    name varchar(50),
    age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");
```
（可选）查看表数据。
```
select * from test_Hive2SelectDB;
```
迁移数据。
通过Insert Into Select，同步Hive数据到SelectDB中，并指定导入作业唯一标识Label。
```
INSERT INTO test_Hive2SelectDB WITH LABEL test_label SELECT *  FROM hive_catalog.testdb.hive_t;
```
查询数据。
左侧为目标表的数据，右侧为源数据表的数据。

Insert Into Values

Insert Into Values是MySQL等数据库中常用的数据写入方式，建议仅用于测试环境的使用。典型的使用方式是直接通过SQL客户端、JDBC程序发送数据写入请求。

创建待导入的SelectDB数据表如下。

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

SQL示例

BEGIN;
INSERT INTO test_table  VALUES (1, '张三', 32),(2, '李四', 45),(3, '赵六', 23);
INSERT INTO test_table  VALUES (4, '王一', 32),(5, '赵二', 45),(6, '李二', 23);
INSERT INTO test_table  VALUES (7, '李一', 32),(8, '王三', 45),(9, '赵四', 23);
COMMIT;

JDBC程序示例

public static void main(String[] args) throws Exception {
    // 单次导入插入语句的数量。
    int insertNum = 10;
    // 单条插入攒批的数量。
    int batchSize = 10000;
    String URL="jdbc:mysql://<HOST地址>:<MySQL协议端口>/test_db?useLocalSessionState=true";  // 您从云数据库 SelectDB 版控制台的实例详情 > 网络信息中获取VPC地址（或公网地址）。
    Connection connection = DriverManager.getConnection(URL, "admin", "password");  // 云数据库SelectDB版实例的账号和密码。
    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(",");
            }
            // 拼接一行数据，如:ID,姓名,年龄。可根据具体业务修改。
            sql.append("(1, '张三', 32)");
        }
        //add sql to batch: INSERT INTO tbl values(),(),()
        statement.addBatch(sql.toString());
    }
    statement.addBatch("commit;");
    statement.executeBatch();
    // 关闭资源。
    statement.close();
    connection.close();
}

最佳实践

查看返回结果。
Insert Into操作是一个同步操作，返回结果即表示操作结束。您需要根据返回结果的不同，进行对应的处理。
1. 执行成功，结果集为空。
  如果 insert 对应 select 语句的结果集为空，则返回如下：
```
INSERT INTO tbl1 SELECT * FROM empty_tbl;
Query OK, 0 rows affected (0.02 sec)
```
  Query OK表示执行成功。0 rows affected表示没有数据被导入。
2. 执行成功，结果集不为空。
  在结果集不为空的情况下。返回结果分为如下几种情况。
```
INSERT INTO tbl1 SELECT * FROM tbl2;
Query OK, 4 rows affected (0.38 sec)
{'label':'insert_8510c568-9eda-****-9e36-6adc7d35291c', 'status':'visible', 'txnId':'4005'}
 
INSERT INTO tbl1 with label my_label1 SELECT * FROM tbl2;
Query OK, 4 rows affected (0.38 sec)
{'label':'my_label1', 'status':'visible', 'txnId':'4005'}
 
INSERT INTO tbl1 SELECT * FROM tbl2;
Query OK, 2 rows affected, 2 warnings (0.31 sec)
{'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'visible', 'txnId':'4005'}
 
INSERT INTO tbl1 SELECT * FROM tbl2;
Query OK, 2 rows affected, 2 warnings (0.31 sec)
{'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'committed', 'txnId':'4005'}
```
  其中，Query OK表示执行成功。4 rows affected表示总共有4行数据被导入。2 warnings表示被过滤的行数。同时会返回一个 JSON 串。
```
{'label':'my_label1', 'status':'visible', 'txnId':'4005'}
{'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'committed', 'txnId':'4005'}
{'label':'my_label1', 'status':'visible', 'txnId':'4005', 'err':'some other error'}
```
  其中，label为您指定的 label 或自动生成的label，label是该Insert Into导入作业的标识，每个导入作业，都有一个在单database内部唯一的label。status表示导入数据是否可见，如果可见显示visible，如果不可见显示committed。txnId为这个insert对应的导入事务的id。err字段会显示一些其他非预期错误。
  当需要查看被过滤的行时，您可以通过如下语句：
```
SHOW LOAD WHERE label="xxx";
```
  返回结果中的 URL 可以用于查询错误的数据，具体见后面查看错误行小结。数据不可见是一个临时状态，这批数据最终是一定可见的。可以通过如下语句查看这批数据的可见状态：
```
SHOW TRANSACTION WHERE id=4005;
```
  返回结果中的TransactionStatus列如果为visible，则表述数据可见。
3. 执行失败。
  执行失败表示没有任何数据被成功导入，并返回如下：
```
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
```
  其中ERROR 1064 (HY000): all partitions have no load data显示失败原因。通过其中的 URL 可以用于查询错误的数据：
```
SHOW LOAD WARNINGS ON "url";
```
超时时间。
Insert Into操作的超时时间由会话变量query_timeout控制，默认为5分钟。超时则作业会被取消。
Label和原子性。
Insert Into操作同样能够保证导入的原子性。当需要使用CTE(Common Table Expressions)作为 Insert Into操作中的查询部分时，必须指定WITH LABEL和column部分。
过滤阈值。
与其他导入方式不同，Insert Into操作不能指定过滤阈值（max_filter_ratio）。默认的过滤阈值为 1，即有错误行都可以被忽略。
对于有要求数据不能够被过滤的业务场景，可以通过设置会话变量enable_insert_strict为true来确保当有数据被过滤掉的时候，Insert Into不会被执行成功。
性能问题。
不建议使用Insert Into Values方式进行数据导入，尤其是大数据的线上生产环境。如果必须采用该方式，建议将多行数据合并到一个Insert Into语句中进行批量提交，单个批次建议为1000至1000000条数据。
部分列更新。
Insert Into的默认行为是整行写入。在Unique数据模型MOW实现方式中，您可按需开启部分列更新功能，需要设置如下会话变量：
```
set enable_unique_key_partial_update=true
```
更多变量设置详情，请参见变量管理。
重要
- 此参数仅在表为Unique模型，且实现方式为写时合并（MOW，merge-on-write）时有效。
- 开启此参数后，如果控制INSERT语句是严格模式，即enable_insert_strict的值为true（默认值），则INSERT INTO语句仅具备更新功能，不具备插入新数据的功能。如果INSERT INTO语句中包含表内不存在的key，则会报错。
- 开启此参数后，如果您期望在使用INSERT INTO语句进行部分列更新的同时，也能插入新数据，需要在enable_unique_key_partial_update设置为true的同时，将enable_insert_strict设置为false。如何设置参数，请参见设置。

常见问题

Q：导入过程中，报get table cloud commit lock timeout怎么办？

A：由于您写入数据频率太快，导致表死锁。强烈建议您将数据进行攒批处理，单表的写入频率建议每5秒以上一次。