云原生数据仓库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:通过定义查询,将一行或多行数据插入表中。
注意:
如果插入数据时不指定列名,则要插入的数据必须和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 '订单表表'
向order_info 表中插入一条数据。
INSERT INTO order_info (order_no,stt_ym,mount ,order_num)
values('034656','202201',1542.5,36);
向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);
向order_info 表中插入多条数据时,可以省略列名(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致)。
INSERT INTO order_info
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40);
向order_info 表中插入多条数据,可以省略列名(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致)。
INSERT INTO order_info
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40);
2. INSERT SELECT FROM
对于可以通过其他表得到本表数据的,可以通过INSERT SELECT FROM将数据复制到本表。
语法:
INSERT INTO table_name
[( column_name [, …] )]
query;
column_name:列名
query:可以是SELECT FROM TABLE或者SELECT FROM VIEW。
示例:
以指定列名的方式,从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';
不指定指定列名的方式(插入的数据必须和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';
3. REPLACE INTO
用于实时覆盖写入数据,写入数据时,会先根据主键判断写入的数据是否已经存在于表中,
如果待写入数据已经存在,则先删除该行数据,然后插入新的数据。
如果待写入数据不存在,则直接插入新数据。
语法:
REPLACE INTO table_name [(column_name,...)]
VALUES ({常量|NULL|DEFAULT},...),(...),...
示例:
向order_info 表中插入一条数据。
REPLACE INTO order_info (order_no,stt_ym,mount ,order_num)
values('034656','202201',1542.5,36);
向order_info 表中插入多条数据,可以省略列名(插入的数据必须和CREATE TABLE语句中声明的列的顺序一致)。
REPLACE INTO order_info
values
('034656','202201',1542.5,36),
('025479','202201',2623.5,30),
('028752','202201',1263.5,40);
4. REPLACE SELECT FROM
将其他表中的数据实时覆盖写入目标表中。写入数据时,根据主键判断待写入的数据是否已经存在于表中,如果已经存在,则先删除该行数据,然后插入新的数据;如果不存在,则直接插入新数据。
语法:
REPLACE INTO table_name
[(column_name,...)]
query;
query:可以是SELECT FROM TABLE或者SELECT FROM VIEW。
column_name:列名
示例:
以指定列名的方式,从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';
5. INSERT OVERWRITE INTO SELECT
表数据的高性能写入方式INSERT OVERWRITE INTO SELECT,覆盖写入
应用场景:
INSERT OVERWRITE INTO SELECT常规的使用场景包括:
- 进行分区级数据写入。
- 进行数据初始化(全量写入)。
- 进行大批量数据写入操作,不建议用于少量数据的写入。
功能原理:
写入任务是通过外表方式将外部数据批量写入到AnalyticDB MySQL内部的。需要在AnalyticDB MySQL中定义对应数据源的外表,然后通过INSERT OVERWRITE INTO SELECT语句将外表数据写入AnalyticDB MySQL表。
每个表的写入任务串行执行,即单表写入并发为1,无法调整。为保证单任务写入性能,防止集群负载过高,集群写入任务并发默认为2,不建议调整。
说明 如有需要调整写入并发数,请提交工单联系技术支持,由技术支持评估调整。
INSERT OVERWRITE INTO SELECT的基本特性如下。
- 资源消耗大:该写入模式在进行高性能写入时会消耗大量集群资源,建议在业务低峰期使用。
- 批量可见:写入任务完成前数据不可见,任务完成后该任务写入的数据批量可见。如果目标表中已存在数据,INSERT OVERWRITE INTO SELECT命令执行结束之前,目标表中的数据不会发生任何变化;INSERT OVERWRITE INTO SELECT命令执行结束后,系统自动将数据写入目标表中,目标表的原数据将被清空。
- 分区覆盖:通过INSERT OVERWRITE INTO SELECT写入的分区数据会覆盖目标表同一分区的数据。
- 自动构建索引:写入时同步构建索引,写入任务完成,目标表则具备索引,可提升查询性能。
注意事项:
不能同时通过INSERT OVERWRITE INTO SELECT和实时写入方式(INSERT INTO、REPLACE INTO、DELETE、UPDATE)向同一个表中写入数据,否则实时写入的数据会被丢弃。
语法:
INSERT OVERWRITE INTO table_name [(column_name,...)]
query;
table_name:表名。
column_name:列名。
query:SELECT查询语句。
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';
以指定列名的方式,从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';
异步写入:
- 提交任务
通常使用SUBMIT JOB提交异步任务,由后台调度执行。示例语句如下。
SUBMIT JOB
INSERT OVERWRITE new_adb_table
SELECT * FROM adb_table_name
- 写入调优
在写入任务前增加Hint(/* direct_batch_load=true*/)可以加速写入任务。该Hint可以在节约大量资源的情况下进一步提高写入性能。示例语句如下。
/* direct_batch_load=true*/
SUBMIT JOB
INSERT OVERWRITE new_adb_table
SELECT * FROM adb_table_name
说明 仅3.1.5及以上内核版本支持/* direct_batch_load=true*/。若使用后性能无明显优化,可提交工单进行升级与优化。查看内核版本,请参见如何查看实例版本信息。
3. 进度查询
通过SUBMIT JOB提交写入任务后会返回job_id。以该job_id为条件查询写入任务的状态,示例语句如下。文章来源:https://www.toymoban.com/news/detail-550563.html
show job status where job='<job_id>'
返回结果status列为FINISH,则写入任务完成。文章来源地址https://www.toymoban.com/news/detail-550563.html
到了这里,关于云原生数据仓库AnalyticDB Mysql(ADB分析型数据库)-DML语法之新增插入数据详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!