云原生数据仓库AnalyticDB Mysql(ADB分析型数据库)-DML语法之新增插入数据详解

云原生数据仓库AnalyticDB Mysql(ADB分析型数据库)-DML语法之新增插入数据

ADB分析型数据库，是阿里云平台上资源，原名叫云原生数据仓库AnalyticDB Mysql，俗称ADB分析型数据库，那么ADB的语法与mysql关系型数据库语法存在一定的差异。
本文为笔记，介绍ADB的DML语法的应用。

1. INSERT INTO

INSERT INTO用于向表中插入数据，主键重复时会自动忽略当前写入数据不做更新，作用等同于INSERT IGNORE INTO。

语法：

INSERT [IGNORE] 
    INTO table_name 
    [( column_name [, …] )]
    [VALUES]
    [(value_list[, …])]
    [query]; 
    
IGNORE：可选参数，判断是否有与之相同的主键数据，新记录不会被写入。
column_name：可选参数，列名。
query：通过定义查询，将一行或多行数据插入表中。
1
2
3
4
5
6
7
8
9
10

注意：
如果插入数据时不指定列名，则要插入的数据必须和CREATE TABLE语句中声明的列的顺序一致。

示例：

CREATE TABLE adb_order.order_info (
  order_no varchar NOT NULL COMMENT '订单号',
  stt_ym varchar NOT NULL COMMENT '统计年月',
  mount decimal(16, 2) NOT NULL COMMENT '订单金额',
  order_num int NOT NULL COMMENT '订单量',
  PRIMARY KEY (order_no)
)
PARTITION BY HASH KEY (stt_ym) PARTITION NUM 128
TABLEGROUP adb_order_test
OPTIONS (UPDATETYPE='realtime')
COMMENT '订单表表'
1
2
3
4
5
6
7
8
9
10
11

向order_info 表中插入一条数据。

INSERT INTO order_info (order_no,stt_ym,mount ,order_num) 
values('034656','202201',1542.5,36); 
1
2

向order_info 表中插入多条数据。

INSERT INTO order_info (order_no,stt_ym,mount ,order_num) 
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40); 
1
2
3
4
5

向order_info 表中插入多条数据时，可以省略列名(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致）。

INSERT INTO order_info 
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40); 
1
2
3
4
5

向order_info 表中插入多条数据，可以省略列名(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致）。

INSERT INTO order_info 
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40); 
1
2
3
4
5

2. INSERT SELECT FROM

对于可以通过其他表得到本表数据的，可以通过INSERT SELECT FROM将数据复制到本表。

语法：

INSERT INTO table_name 
[( column_name [, …] )]
query;       

column_name：列名
query：可以是SELECT FROM TABLE或者SELECT FROM VIEW。
1
2
3
4
5
6

示例：
以指定列名的方式，从order_info1,order_info2 表中复制某几列数据到new_order_info 表中。

INSERT INTO new_order_info (order_no, order_name, mount,stt_ym)
SELECT a.order_no, b.order_name, a.mount, b.stt_ym 
FROM order_info1 a,order_info2 b
WHERE a.order_no = b.order_no
and b.stt_ym = '202201';   
1
2
3
4
5

不指定指定列名的方式(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致)，从order_info1,order_info2 表中复制某几列数据到new_order_info 表中。

INSERT INTO new_order_info 
SELECT a.order_no, b.order_name, a.mount, b.stt_ym 
FROM order_info1 a,order_info2 b
WHERE a.order_no = b.order_no
and b.stt_ym = '202201';   
1
2
3
4
5

3. REPLACE INTO

用于实时覆盖写入数据，写入数据时，会先根据主键判断写入的数据是否已经存在于表中，
如果待写入数据已经存在，则先删除该行数据，然后插入新的数据。
如果待写入数据不存在，则直接插入新数据。

语法：

REPLACE INTO table_name [(column_name,...)] 
VALUES ({常量|NULL|DEFAULT},...),(...),...      
1
2

示例：
向order_info 表中插入一条数据。

REPLACE INTO order_info (order_no,stt_ym,mount ,order_num) 
values('034656','202201',1542.5,36); 
1
2

向order_info 表中插入多条数据，可以省略列名(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致）。

REPLACE INTO order_info 
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40); 
1
2
3
4
5

4. REPLACE SELECT FROM

将其他表中的数据实时覆盖写入目标表中。写入数据时，根据主键判断待写入的数据是否已经存在于表中，如果已经存在，则先删除该行数据，然后插入新的数据；如果不存在，则直接插入新数据。

语法：

REPLACE INTO table_name 
[(column_name,...)]
query;    

query：可以是SELECT FROM TABLE或者SELECT FROM VIEW。
column_name：列名
1
2
3
4
5
6

示例：
以指定列名的方式，从order_info1,order_info2 表中复制某几列数据到new_order_info 表中。

REPLACE INTO new_order_info (order_no, order_name, mount,stt_ym)
SELECT a.order_no, b.order_name, a.mount, b.stt_ym 
FROM order_info1 a,order_info2 b
WHERE a.order_no = b.order_no
and b.stt_ym = '202201';   
1
2
3
4
5

5. INSERT OVERWRITE INTO SELECT

表数据的高性能写入方式INSERT OVERWRITE INTO SELECT，覆盖写入

应用场景：
INSERT OVERWRITE INTO SELECT常规的使用场景包括：

1. 进行分区级数据写入。
2. 进行数据初始化（全量写入）。
3. 进行大批量数据写入操作，不建议用于少量数据的写入。

功能原理：
写入任务是通过外表方式将外部数据批量写入到AnalyticDB MySQL内部的。需要在AnalyticDB MySQL中定义对应数据源的外表，然后通过INSERT OVERWRITE INTO SELECT语句将外表数据写入AnalyticDB MySQL表。

每个表的写入任务串行执行，即单表写入并发为1，无法调整。为保证单任务写入性能，防止集群负载过高，集群写入任务并发默认为2，不建议调整。
说明 如有需要调整写入并发数，请提交工单联系技术支持，由技术支持评估调整。

INSERT OVERWRITE INTO SELECT的基本特性如下。

1. 资源消耗大：该写入模式在进行高性能写入时会消耗大量集群资源，建议在业务低峰期使用。
2. 批量可见：写入任务完成前数据不可见，任务完成后该任务写入的数据批量可见。如果目标表中已存在数据，INSERT OVERWRITE INTO SELECT命令执行结束之前，目标表中的数据不会发生任何变化；INSERT OVERWRITE INTO SELECT命令执行结束后，系统自动将数据写入目标表中，目标表的原数据将被清空。
3. 分区覆盖：通过INSERT OVERWRITE INTO SELECT写入的分区数据会覆盖目标表同一分区的数据。
4. 自动构建索引：写入时同步构建索引，写入任务完成，目标表则具备索引，可提升查询性能。

注意事项：
不能同时通过INSERT OVERWRITE INTO SELECT和实时写入方式（INSERT INTO、REPLACE INTO、DELETE、UPDATE）向同一个表中写入数据，否则实时写入的数据会被丢弃。

语法：

INSERT OVERWRITE INTO table_name [(column_name,...)]
query;

table_name：表名。
column_name：列名。
query：SELECT查询语句。
1
2
3
4
5
6

query中的查询数据必须和目标表CREATE TABLE语句中声明的列的顺序与类型一致，或少于目标表列。SELECT语句中的列数比目标表中的列数多，会写入失败；如果SELECT语句中的列数比目标表中的列数少，写入数据时，目标表中多出的列会自动填充默认值，无默认值时值为NULL。

示例：
以指定列名的方式，从order_info1,order_info2 表中复制某几列数据到new_order_info 表中。

INSERT OVERWRITE INTO new_order_info (order_no, order_name, mount,stt_ym)
SELECT a.order_no, b.order_name, a.mount, b.stt_ym 
FROM order_info1 a,order_info2 b
WHERE a.order_no = b.order_no
and b.stt_ym = '202201';   
1
2
3
4
5

以指定列名的方式，从order_info1 表中选择order_no列到new_order_info 表中order_no列。

INSERT OVERWRITE INTO new_order_info (order_no)
SELECT order_no 
FROM order_info1
WHERE tt_ym = '202201';   
1
2
3
4

异步写入：

1. 提交任务
   通常使用SUBMIT JOB提交异步任务，由后台调度执行。示例语句如下。

SUBMIT JOB
INSERT OVERWRITE new_adb_table
SELECT * FROM adb_table_name
1
2
3

2. 写入调优
   在写入任务前增加Hint（/* direct_batch_load=true*/）可以加速写入任务。该Hint可以在节约大量资源的情况下进一步提高写入性能。示例语句如下。

/* direct_batch_load=true*/
SUBMIT JOB
INSERT OVERWRITE new_adb_table
SELECT * FROM adb_table_name
1
2
3
4

说明 仅3.1.5及以上内核版本支持/* direct_batch_load=true*/。若使用后性能无明显优化，可提交工单进行升级与优化。查看内核版本，请参见如何查看实例版本信息。
3. 进度查询
通过SUBMIT JOB提交写入任务后会返回job_id。以该job_id为条件查询写入任务的状态，示例语句如下。

show job status where job=''
1

返回结果status列为FINISH，则写入任务完成。