Doris--基础--4.2--数据导入--Insert Into

Doris–基础–4.2–数据导入–Insert Into

---

1、介绍

1. 类似Mysql中的insert语句
2. 方式：
   1. 通过 insert into table select …的方式从Doris的表中读取数据并导入到另一张表中
   2. 通过 insert into table values(…)的方式插入单条数据

2、格式

INSERT INTO tbl SELECT ...
INSERT INTO tbl (col1, col2, ...) VALUES (1, 2, ...), (1,3, ...);

3、创建导入

Insert Into 命令需要通过 MySQL 协议提交，创建导入请求会同步返回导入结果。

3.1、语法

INSERT INTO table_name [partition_info] [WITH LABEL label] [col_list] [query_stmt] [VALUES]

3.2、示例

3.2.1、准备

建表

CREATE TABLE IF NOT EXISTS test_db.table11
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);



CREATE TABLE IF NOT EXISTS test_db.table12
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);

3.2.2、示例1

insert into test_db.table11 values(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10);
insert into test_db.table11 values(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22); 
insert into test_db.table11 values(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5); 
insert into test_db.table11 values(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11); 
insert into test_db.table11 values(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3); 

3.2.3、示例2

INSERT INTO table12 WITH LABEL label1 SELECT * FROM table11;

注意：当需要使用CTE(Common Table Expressions)作为 insert 操作中的查询部分时，必须指定 WITH LABEL 和 column list 部分

4、导入结果

Insert Into 本身就是一个 SQL 命令，其返回结果会根据执行结果的不同，分为以下几种

1. 结果集为空
2. 结果集不为空

4.1、结果集为空

mysql> insert into table12 select * from table11;
Query OK, 0 rows affected (0.02 sec)

1. Query OK 表示执行成功。
2. 0 rows affected 受影响的行数是0。

4.2、结果集不为空

 
mysql> insert into table12 select * from table11;
Query OK, 4 rows affected (0.38 sec)
{'label':'insert_8510c568-9eda-4173-9e36-6adc7d35291c', 'status':'visible', 'txnId':'10'}

mysql> insert into table12 with label my_label1 select * from table11;
Query OK, 4 rows affected (0.38 sec)
{'label':'my_label1', 'status':'visible', 'txnId':'10'}

mysql> insert into table12 select * from table11;
Query OK, 2 rows affected, 2 warnings (0.31 sec)
{'label':'insert_f0747f0e-7a35-46e2-affa-13a235f4020d', 'status':'visible', 'txnId':'10'}

mysql> insert into table12 select * from table11;
Query OK, 2 rows affected, 2 warnings (0.31 sec)
{'label':'insert_f0747f0e-7a35-46e2-affa-13a235f4020d', 'status':'committed', 'txnId':'10'}

{‘label’:‘my_label1’, ‘status’:‘visible’, ‘txnId’:‘4005’}
{‘label’:‘insert_f0747f0e-7a35-46e2-affa-13a235f4020d’, ‘status’:‘committed’, ‘txnId’:‘4005’}
{‘label’:‘my_label1’, ‘status’:‘visible’, ‘txnId’:‘4005’, ‘err’:‘some other error’}

1. Query OK 表示执行成功。
2. 0 rows affected 受影响的行数是2。
3. 2 warnings 表示被过滤的行数是2。
4. label：
   1. 为用户指定的 label 或自动生成的 label。
   2. Label 是该 Insert Into 导入作业的标识，每个导入作业，都有一个在单 database 内部唯一的 Label。
5. status：表示导入数据是否可见。
   1. 如果可见：显示 visible
   2. 如果不可见：显示 committed。
6. txnId：为这个 insert 对应的导入事务的 id。
7. err：会显示一些其他非预期错误。

4.3、查看数据状态

4.3.1、查看被过滤的行时，用户可以通过如下语句

show load where label="xxx";


MySQL [test_db]> show load where label="my_label1"\G;
*************************** 1. row ***************************
         JobId: 13312
         Label: my_label1
         State: FINISHED
      Progress: ETL:100%; LOAD:100%
          Type: INSERT
       EtlInfo: NULL
      TaskInfo: cluster:N/A; timeout(s):3600; max_filter_ratio:0.0
      ErrorMsg: NULL
    CreateTime: 2023-02-12 20:37:14
  EtlStartTime: 2023-02-12 20:37:14
 EtlFinishTime: 2023-02-12 20:37:14
 LoadStartTime: 2023-02-12 20:37:14
LoadFinishTime: 2023-02-12 20:37:14
           URL: 
    JobDetails: {"Unfinished backends":{},"ScannedRows":0,"TaskNumber":0,"LoadBytes":0,"All backends":{},"FileNumber":0,"FileSize":0}
 TransactionId: 8
  ErrorTablets: {}
1 row in set (0.00 sec)

4.3.1、查看这批数据的可见状态

show transaction where id=txnId;

MySQL [test_db]> show transaction where id=10\G;
*************************** 1. row ***************************
     TransactionId: 10
             Label: my_label3
       Coordinator: FE: 192.168.187.101
 TransactionStatus: VISIBLE
 LoadJobSourceType: INSERT_STREAMING
       PrepareTime: 2023-02-12 20:48:26
     PreCommitTime: NULL
        CommitTime: 2023-02-12 20:48:26
       PublishTime: 2023-02-12 20:48:26
        FinishTime: 2023-02-12 20:48:26
            Reason: 
ErrorReplicasCount: 0
        ListenerId: -1
         TimeoutMs: 300000
            ErrMsg: 
1 row in set (0.00 sec)

TransactionStatus=visible，表示数据可见。

4.4、Insert 执行失败

执行失败表示没有任何数据被成功导入，并返回如下：

mysql> insert into table12 select * from table11 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：可以用于查询错误的数据

4.5、总结：对于 insert 操作返回结果的正确处理逻辑

1. 如果返回结果为 ERROR 1064 (HY000)，则表示导入失败。
2. 如果返回结果为 Query OK，则表示执行成功。
3. 如果 rows affected 为 0，表示结果集为空，没有数据被导入。
4. 如果 rows affected 大于 0：
   1. 如果 status 为 committed，表示数据还不可见。需要通过 show transaction 语句查看状态直到 visible
   2. 如果 status 为 visible，表示数据导入成功。
5. 如果 warnings 大于 0，表示有数据被过滤，可以通过 show load 语句获取 url 查看被过滤的行