MaxCompute SQL中insert语句使用详情

最新推荐文章于 2023-12-06 18:18:11 发布
最新推荐文章于 2023-12-06 18:18:11 发布
阅读量8k 收藏 15
点赞数 2
分类专栏： MaxCompute 文章标签： MaxCompute SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yitian_z/article/details/105588335
版权

MaxCompute insert语句使用

本文介绍使用INSERT OVERWRITE和INSERT INTO两种命令更新表数据，主要内容包括：

  1. insert into table ... values ...语句
  2. insert into/overwrite ... select ...语句
  3. Insert多路输出（MULTI INSERT）
  4. 输出到动态分区（DYNAMIC PARTITION）

INSERT VALUES语句

命令使用

INSERT INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2,...)][(co1name1,colname2,...)] 
[VALUES (col1_value,col2_value,...),(col1_value,col2_value,...),...]
  • tablename：要插入数据的目标表名称。此名称需为已经存在的表名称。
  • PARTITION (partcol1=val1, partcol2=val2,...)]：分区信息。如果需要更新的表为分区表，需要指定该参数。
  • [(co1name1,colname2,...)：目标表中的字段名称。
  • col_value：目标表中列对应的列值，多个列值之间用逗号（,）分隔。此列值必须为常量，列值未指定时，缺省为NULL。

 

注意：通过VALUES写入DATETIME、TIMESTAMP类型数据时，需要在VALUES中指定类型名称，如下所示。 

insert into table srcp (p='abc') values (datetime'2017-11-11 00:00:00',timestamp'2017-11-11 00:00:00.123456789');

1. 特定分区内插入数据：

insert into table sale_detail
partition (sale_date='202003', region='hangzhou')
values ('unique', 'user1', 1000),
  ('adidas', 'user2', 2000),
  ('zara', 'user3', 1500),
  ('veromoda', 'user1', 2000),
  ('nike', 'user4', 1000),
  ('ur', 'user2', 1400),
  ('ochirly', 'user1', 2000),
  ('lily', 'user3', 3000),
  ('onemore', "user2", 1200);

查询插入数据结果如下：

shop_name	customer_id	total_price	sale_date	region
+----------+------------+------------+----------+-------+
unique	user1	1000.0	202003	hangzhou
adidas	user2	2000.0	202003	hangzhou
zara	user3	1500.0	202003	hangzhou
veromoda	user1	2000.0	202003	hangzhou
nike	user4	1000.0	202003	hangzhou
ur	user2	1400.0	202003	hangzhou
ochirly	user1	2000.0	202003	hangzhou
lily	user3	3000.0	202003	hangzhou
onemore	user2	1200.0	202003	hangzhou

2. 非特定分区插入数据：

insert into table sale_detail
partition (sale_date, region)
  (shop_name, customer_id, total_price, sale_date, region)
values
  ('unique', 'user1', 1000, '202001', 'shanghai'),
  ('adidas', 'user2', 2000, '202001', 'shanghai'),
  ('zara', 'user3', 1500, '202001', 'shanghai');

查询插入结果：

shop_name	customer_id	total_price	sale_date	region
+----------+------------+------------+----------+-------+
unique	user1	1000.0	202001	shanghai
adidas	user2	2000.0	202001	shanghai
zara	user3	1500.0	202001	shanghai

VALUES TABLE功能

VALUES TABLE并不仅限于在INSERT语句中使用，任何DML语句都可以使用。VALUES TABLE功能的使用方式如下：

  • 在没有任何物理表时，您可以模拟一个有任意数据的、多行的表，并进行任意运算。

    下例中的values (…), (…) t(a, b)相当于定义了一个名为t，列为a、b，类型分别为STRING、BIGINT的表。列的类型需从VALUES列表中推导。

使用示例：

--删除已存在的表srcp。
drop table if exists srcp;
--创建分区表srcp。
create table if not exists srcp (key string,value bigint) partitioned by (p string);
--为表srcp中插入数据。
insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'20170102' from values ('d',4),('e',5),('f',6) t(a,b);
--查询表srcp。
select * from srcp where p='20170102';
+-----+------------+---+
| key | value      | p |
+-----+------------+---+
| d4  | 2          | 20170102 |
| e5  | 2          | 20170102 |
| f6  | 2          | 20170102 |
+-----+------------+---

INSERT into/overwrite语句

在MaxCompute SQL处理数据的过程中，INSERT OVERWRITE/INTO用于将计算的结果保存目标表中。

  • insert into：直接向表或表的分区中追加数据。不支持INSERT INTO到Hash Clustering表。如果您需要插入少量测试数据，可以配合VALUES语句使用。
  • insert overwrite：先清空表中的原有数据，再向表或分区中插入数据。目前INSERT OVERWRITE不支持指定插入列的功能，暂时只能用INSERT INTO。
INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)]
select_statement
FROM from_statement;
  • tablename：需要插入数据的目标表名称。
  • PARTITION (partcol1=val1, partcol2=val2 ...)：需要插入数据的分区名称，此参数不允许使用函数等表达式，只能是常量。
  • select_statement：SELECT子句，从源表中查询需要插入的数据。说明
    • 源表与目标表的对应关系依赖于在SELECT子句中列的顺序，而不是表与表之间列名的对应关系。
    • 向某个分区插入数据时，分区列不允许出现在SELECT子句。
  • from_statement：FROM子句，代表数据来源。例如，源表名称。

使用示例：

1. 计算sale_detail表中不同地区的销售额存入表sale_detail_insert中：

-- 创建目标表sale_detail_insert
create table sale_detail_insert like sale_detail;

-- 给目标表增加分区
alter table sale_detail_insert add partition (
  sale_date='201912',
  region='guangzhou'
);

-- 从源表sale_detail中取出数据插入目标表sale_detail_insert
insert overwrite table sale_detail_insert
partition (sale_date='201912', region='guangzhou')
select shop_name, customer_id, total_price
from sale_detail where region='beijing';

-- 查询插入的数据
select * from sale_detail_insert where region='guangzhou' and sale_date='201912';

查询插入的结果如下:

shop_name	customer_id	total_price	sale_date	region
+----------+------------+------------+----------+-------+
unique	user1	1000.0	201912	guangzhou
adidas	user2	2000.0	201912	guangzhou
zara	user3	1500.0	201912	guangzhou
veromoda	user1	2000.0	201912	guangzhou
nike	user4	1000.0	201912	guangzhou
ur	user2	1400.0	201912	guangzhou
ochirly	user1	2000.0	201912	guangzhou
lily	user3	3000.0	201912	guangzhou
onemore	user2	1200.0	201912	guangzhou

2. 源表与目标表的对应关系依赖于在select子句中列的顺序，而不是表与表之间列名的对应关系。例如如下语句：

insert overwrite table sale_detail_insert partition (sale_date='2018', region='china')
  select customer_id, shop_name, total_price from sale_detail;

此时，会将sale_detail.customer_id的数据插入sale_detail_insert.shop_name，将sale_detail.shop_name的数据插入sale_detail_insert.customer_id。

3. 向某个分区插入数据时，分区列不允许出现在select列表中。下面语句报错返回，sale_date,region为分区列，不允许出现在静态分区的insert语句中。

insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
   select shop_name, customer_id, total_price, sale_date, region  from sale_detail;

4. partition的值只能是常量，不可以出现表达式。以下为错误用法。

insert overwrite table sale_detail_insert partition (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
   select shop_name, customer_id, total_price from sale_detail;

使用动态分区注意事项

如果您需要更新表数据到动态分区，请注意以下事项：

  • insert into partition时，如果分区不存在，会自动创建分区。
  • 多个insert into partition作业并发时，如果分区不存在，会自动创建分区，但只会成功创建一个分区。

Insert多路输出（MULTI INSERT）

MaxCompute SQL支持在一个语句中将数据插入不同的目标表或者分区中实现多路输出。

命令格式

FROM from_statement
INSERT OVERWRITE | INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 [FROM from_statement]
[INSERT OVERWRITE | INTO TABLE tablename2 [PARTITION (partcol1=val3, partcol2=val4 ...)]
select_statement2 [FROM from_statement]]
  • from_statement：FROM子句，代表数据来源。例如，源表名称。
  • PARTITION (partcol1=val1, partcol2=val2 ...)：需要插入数据的分区名称，此参数不允许使用函数等表达式，只能是常量。
  • tablename1,tablename2：需要插入数据的目标表名称。
  • select_statement：SELECT子句，从源表中查询需要插入的数据。

使用示例：

1. 将表sale_detail的数据插入到sale_detail_multi里的202003年及202004年中hangzhou和beijing的销售记录中：

-- 创建表sale_detail_multi
create table sale_detail_multi like sale_detail;

-- 开启全表扫描，仅此session有效
set odps.sql.allow.fullscan=ture;

-- 将表sale_detail中的数据插入到表sale_detail_multi
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='202003', region='hangzhou')
select shop_name, customer_id, total_price where region='hangzhou'
insert overwrite table sale_detail_multi partition (sale_date='202004', region='beijing')
select shop_name, customer_id, total_price where region='beijing';

select * from sale_detail_multi;

查询插入的数据如下：

shop_name	customer_id	total_price	sale_date	region
+----------+------------+------------+----------+-------+
unique	user1	1000.0	202003	hangzhou
adidas	user2	2000.0	202003	hangzhou
zara	user3	1500.0	202003	hangzhou
veromoda	user1	2000.0	202003	hangzhou
nike	user4	1000.0	202003	hangzhou
ur	user2	1400.0	202003	hangzhou
ochirly	user1	2000.0	202003	hangzhou
lily	user3	3000.0	202003	hangzhou
onemore	user2	1200.0	202003	hangzhou
unique	user1	1000.0	202004	beijing
adidas	user2	2000.0	202004	beijing
zara	user3	1500.0	202004	beijing
veromoda	user1	2000.0	202004	beijing
nike	user4	1000.0	202004	beijing
ur	user2	1400.0	202004	beijing
ochirly	user1	2000.0	202004	beijing
lily	user3	3000.0	202004	beijing
onemore	user2	1200.0	202004	beijing

 2. 如果同一分区出现多次，如下语句，则报错返回：

from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price;

3. 如果同一张表的不同分区，同时有insert overwrite和insert into操作，如下语句则报错返回：

from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert into table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price;

输出到动态分区（DYNAMIC PARTITION）

背景信息

在使用INSERT OVERWRITE语句将数据插入到分区表时，MaxCompute提供了如下两种方式：

  • 输出到静态分区：在INSERT语句中直接指定分区值，将数据插入指定的分区。
  • 输出到动态分区：在INSERT语句中不直接指定分区值，只指定分区列名。分区列的值在SELECT子句中提供，系统自动根据分区字段的值将数据插入到相应分区。

动态分区语法

INSERT OVERWRITE|INTO TABLE tablename PARTITION (partcol1, partcol2 ...) 
select_statement FROM from_statement;
  • tablename：需要插入数据的目标表表名。
  • partcol1, partcol2 ...：目标表分区列列名。
  • select_statement：源表的查询语句。select_statement子句中的字段将提供目标表的动态分区值。如果目标表只有一级动态分区，则select_statement的最后一个字段值即为目标表的动态分区值。源表SELECT的值和输出分区的值的关系仅仅是通过位置来确定的，和字段名称没有关系。

使用示例：

示例1：将源表中的数据插入到目标表中。在SQL运行之前，无法得知会产生哪些分区。只有在语句运行结束后，才能通过region字段产生的值确定产生的分区。

-- 创建目标表total_revenues
create table total_revenues (revenue bigint) partitioned by (region string);

-- 将源表sale_detail中的数据插入到目标表total_revenues
-- MaxCompute 2.0 支持total_price的自动类型转换，这里MaxCompute 1.0不支持
insert overwrite table total_revenues partition (region)
select cast(total_price as bigint) as revenue, region from sale_detail where region='hangzhou';

select * from total_revenues;

插入数据查询结果如下：

revenue	region
+--------+-------+
1000	hangzhou
2000	hangzhou
1500	hangzhou
2000	hangzhou
1000	hangzhou
1400	hangzhou
2000	hangzhou
3000	hangzhou
1200	hangzhou

示例2：将源表中的数据插入到目标表中。多级分区，指定一级分区sale_date。

insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
select shop_name,customer_id,total_price,region from sale_detail;

示例3：动态分区插入时，动态分区列必须在SELECT列表中，否则会执行失败，例如下面语句。

insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
select shop_name,customer_id,total_price from sale_detail;

示例4：动态分区插入时，不能仅指定低级子分区，而动态插入高级分区，否则会执行失败，例如下面语句。

insert overwrite table sales partition (region='china', sale_date)
select shop_name,customer_id,total_price,sale_date from sale_detail;
Ethan_pika
关注
专栏目录
MaxCompute SQL日期函数
胜利的曙光的博客
03-29 3280
MaxCompute SQL日期常用函数。
Hive分区表的分区操作
热门推荐
IT影风的博客
05-09 5万+
为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。1、创建分区表通过PARTITIONED BY子句指定，分区的顺序决定了谁是父目录，谁是子目录。创建有一个分区的分区表：CREATE TABLE IF NOT EXISTS part_test(           c1     ...
MaxCompute - 更新表数据（INSERT OVERWRITE and INSERT INTO）
基咯咯
05-09 3423
INSERT命令说明 命令格式： INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)] select_statement FROM from_statement; 格式说明： 在MaxCompute SQL处理数据的过程中，I...
MaxCompute-关于insert overwrite语句清掉的数据也会占用空间存储
weixin_44375570的博客
07-21 993
之前有部门明明项目空间下没建什么表，也没上传数据，存储空间却不够了。咨询了odps的技术，原来是用户的调度任务使用的insertoverwrite语句的问题。在使用MaxComputeSQL处理数据时，insertoverwrite操作可以将select查询的结果保存至目标表中。此外执行调度任务，执行失败了，但是执行过程也会清数据，也增加存储，且用户调度任务频率为每6小时。覆盖插入，就是说会清掉目标表的数据，再把源表的数据插进去。同时，默认删掉的数据会保留3天，就会占用空间存储量。...
MaxCompute SQL：INSERT语句详解
"MaxCompute SQL中的INSERT语句用于向数据表中添加或更新数据，主要包含以下几种方式：INSERT INTO和INSERT OVERWRITE，以及它们的变体，如INSERT INTO TABLE VALUES和INSERT INTO/OVERWRITE TABLE SELECT。...
maxcompute操作_MAXCOMPUTE操作基础SQL语句（DDL部分）-阿里云开发者社区
weixin_36354965的博客
01-16 920
操作1：建表语句(不带分区)creat table page_view (user_id bigint,view_time bigint,page_url string,referrer_url string,ip string);注意事项：MAXCOMPUT创建表的时候必须加上table这个关键字，如以上语句如果为create后直接跟着表名则会报错，语句结束时以；结尾。操作2：建表语句(带分区)...
如何在MaxCompute中利用Insert Overwrite语句根据特定条件更新和删除数据？
最新发布
10-26
在MaxCompute平台上，执行数据更新和删除操作并不像传统关系型数据库中那样直接使用`UPDATE`和`DELETE`语句，而是通过特定的技巧来实现。其中，`INSERT INTO ... OVERWRITE`是一个非常实用的语句，它在追加数据的...
MaxCompute SQL 概要
hfzd24的博客
08-14 5012
MaxCompute SQL 适用于海量数据（GB、TB、EB 级别），离线批量计算的场合。MaxCompute 作业提交后会有几十秒到数分钟不等的排队调度，所以适合处理跑批作业，一次作业批量处理海量数据，不适合直接对接需要每秒处理几千至数万笔事务的前台业务系统。 MaxCompute SQL 采用的是类似于 SQL 的语法，可以看作是标准 SQL 的子集，但不能因此简单的把 MaxComput
自用hive with as insert into partition等综合例子
Yonggie的博客
09-14 2131
1 create table table1 as with hive1 as ( select a1, a2, a3, a4, a5, a6, a7, a8, a9
Hive insert Overrite partition踩坑
qq_37807821的博客
07-22 2155
Hive insert Overrite partition 动态分区踩坑实录 业务上要求有一张表要动态插入按照时间分区的数据，然后就用了如下语法(已脱敏)： 先说结论：动态分区按照查询的最后一个字段来，我这里没有按照这个规范写，就报了很多奇奇怪怪的错误。 在Application上找到spark的执行日志时，发现卡在最后一个stage的repartitionAndSortWithPartitions 到 mapPartitions 阶段，而且表现为所有executor上的task数都为0，但是都有shuf
SQL语法基础之insert
qq_38461443的博客
07-26 2960
Insert语句用于插入数据到表中，其基本语法有以下三种 • Insert语句三种写法的常规用法 • Insert into students5 values(1,’aaa’); • Insert into students5 set sid=2,sname=‘bbb’; • Insert into students5 select sid,sname from where sid=3; • 其中insert…values和insert…set两种语句都是将指定的数据插入到 现成的表中，而insert…se
【hive】- 使用insert into/insert overwrite插入数据到静态分区、动态分区、动静态分区
sodaloveer的博客
11-15 1万+
使用insert into/insert overwrite插入数据到静态分区、动态分区、动静态分区
hive分区表插入数据
sinat_24296421的博客
09-29 8711
插入语句 insert into table 库名.表名 partition(【分区列名】= 【分区值】) values (column_value1,column_value2,column_value3,......) 示例： insert into table app.app_crm_bd_home_data partition(dt = '2020-09-06') values(623709,10768,14,62,20200907,10,4); hive表新增字段，示例 alte.
创建分区表，以及将数据写入分区表
u011939633的博客
12-28 3657
创建分区表 CREATE TABLE 表名(`字段1` string,`字段2` int) partitioned by(分区字段 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 例子 CREATE TABLE my_table(`id` string,`cnt` int) partitioned by(date string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; .
MySql分区
shell_x的博客
01-04 174
mysql分区表 分区表的特点，在逻辑上为一个表，在物理上存储在多个文件中。 create table `login_log`( id int(10) unsigned not null comment'登陆用户id', time timestamp not null default current_timestamp, ip int(10) unsigned not nu...
hive之insert导入分区数据
kwame211的博客
02-27 1万+
数据库分区的主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间，主要包括两种分区形式：水平分区与垂直分区。水平分区是对表进行行分区。而垂直分区是对列进行分区，一般是通过对表的垂直划分来减少目标表的宽度，常用的是水平分区。 回到顶部 1、分区参数介绍 hive.exec.dynamic.partition:是否启动动态分区。默认false。 hive.exec.d...
hive：insert into/overwrite插入分区详解
m0_46241543的博客
12-06 2166
最近在做数据清洗的工作，从ods层到dwd层对数据进行标准化。有多张表需要汇入主题表，因为表中的字段比较多，况且也不统一，需要从指定字段拿数据，并且清洗，最后汇入主题表。PS：又结束了一天忙碌的工作，祝愿大家开心生活每一天。
不指定insert into 字段入全表
❤莫等闲❤的博客
05-13 5373
一次业务场景需要，得把一个数据库中的数据导入另一个数据库，2个数据库类型不同，当时采用的方式为： 把数据库A的表中的数据导出成dat文件（这个数据库导出的文件就是dat文件），一行一条记录，字段顺序按照建表字段顺序，各个字段中间用欧元符号分隔，在数据库B中建表，表结构与数据库中的表结构完全一致，最后再增加一个ids自增字段。dat文件存放在文件服务器指定目录，程序通过ssh连接服务器，并获取dat文件流来获取文件，一行一行读取文件内容然后批量入B库。文件名称中的编码字符可以确定这个文件入哪...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值