Doris–基础–4.2–数据导入–Insert Into
1、介绍
- 类似Mysql中的insert语句
- 方式：
- 通过 insert into table select …的方式从Doris的表中读取数据并导入到另一张表中
- 通过 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 命令，其返回结果会根据执行结果的不同，分为以下几种
- 结果集为空
- 结果集不为空
4.1、结果集为空
mysql> insert into table12 select * from table11;
Query OK, 0 rows affected (0.02 sec)
- Query OK 表示执行成功。
- 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’}
- Query OK 表示执行成功。
- 0 rows affected 受影响的行数是2。
- 2 warnings 表示被过滤的行数是2。
- label：
- 为用户指定的 label 或自动生成的 label。
- Label 是该 Insert Into 导入作业的标识，每个导入作业，都有一个在单 database 内部唯一的 Label。
- status：表示导入数据是否可见。
- 如果可见：显示 visible
- 如果不可见：显示 committed。
- txnId：为这个 insert 对应的导入事务的 id。
- 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 操作返回结果的正确处理逻辑
- 如果返回结果为 ERROR 1064 (HY000)，则表示导入失败。
- 如果返回结果为 Query OK，则表示执行成功。
- 如果 rows affected 为 0，表示结果集为空，没有数据被导入。
- 如果 rows affected 大于 0：
- 如果 status 为 committed，表示数据还不可见。需要通过 show transaction 语句查看状态直到 visible
- 如果 status 为 visible，表示数据导入成功。
- 如果 warnings 大于 0，表示有数据被过滤，可以通过 show load 语句获取 url 查看被过滤的行
778
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
