全部产品
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 钉钉智能硬件
MaxCompute

INSERT操作

更新时间:2018-02-11 13:04:16

更新表中的数据(INSERT OVERWRITE/INTO)

命令格式如下

  1. INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)]
  2. select_statement
  3. FROM from_statement;

注意

  • MaxCompute的Insert语法与通常使用的MySQL或Oracle的Insert语法有差别,在insert overwrite|into后需要加入table关键字,不是直接使用tablename。

  • 当Insert的目标表是分区表时,指定分区值[PARTITION (partcol1=val1, partcol2=val2 …)]语法中不允许使用函数等表达式。

  • 目前INSERT OVERWRITE还不支持指定插入列的功能,暂时只能用INSERT INTO。

在MaxCompute SQL处理数据的过程中,Insert overwrite/into用于将计算的结果保存目标表中。

Insert into与Insert overwrite的区别是:Insert into会向表或表的分区中追加数据,而Insert overwrite则会在向表或分区中插入数据前清空表中的原有数据。

在使用MaxCompute处理数据的过程中,Insert overwrite/into是最常用到的语句,它们会将计算的结果保存到一个表中,以供下一步计算使用。比如计算sale_detail表中不同地区的销售额,操作如下:

  1. create table sale_detail_insert like sale_detail;
  2. alter table sale_detail_insert add partition(sale_date='2013', region='china');
  3. insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  4. select shop_name, customer_id, total_price from sale_detail;

注意

在进行Insert更新数据操作时,源表与目标表的对应关系依赖于在select子句中列的顺序,而不是表与表之间列名的对应关系,下面的SQL语句仍然是合法的:

  1. insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  2. select customer_id, shop_name, total_price from sale_detail;
  3. -- 在创建sale_detail_insert表时,列的顺序为:
  4. -- shop_name string, customer_id string, total_price bigint
  5. -- 而从sale_detailsale_detail_insert插入数据是,sale_detail的插入顺序为:
  6. -- customer_id, shop_name, total_price
  7. -- 此时,会将sale_detail.customer_id的数据插入sale_detail_insert.shop_name
  8. -- sale_detail.shop_name的数据插入sale_detail_insert.customer_id

向某个分区插入数据时,分区列不允许出现在select列表中:

  1. insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  2. select shop_name, customer_id, total_price, sale_date, region from sale_detail;
  3. -- 报错返回,sale_dateregion 为分区列,不允许出现在静态分区的 insert 语句中。

同时,partition的值只能是常量,不可以出现表达式。以下用法是非法的:

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

多路输出(MULTI INSERT)

MaxCompute SQL支持在一个语句中插入不同的结果表或者分区。

命令格式如下

  1. FROM from_statement
  2. INSERT OVERWRITE | INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
  3. select_statement1 [FROM from_statement]
  4. [INSERT OVERWRITE | INTO TABLE tablename2 [PARTITION (partcol1=val3, partcol2=val4 ...)]
  5. select_statement2 [FROM from_statement]]

注意

  • 一般情况下,单个SQL中最多可以写256路输出,超过256路,则报语法错误。

  • 在一个multi insert中:

    • 对于分区表,同一个目标分区不允许出现多次。

    • 对于未分区表,该表不能出现多次。

  • 对于同一张分区表的不同分区,不能同时有Insert overwrite和Insert into操作,否则报错返回。

示例如下

  1. create table sale_detail_multi like sale_detail;
  2. from sale_detail
  3. insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  4. select shop_name, customer_id, total_price where .....
  5. insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
  6. select shop_name, customer_id, total_price where .....;
  7. -- 成功返回,将 sale_detail 的数据插入到 sales 里的 2010 年及 2011 年中国大区的销售记录中。
  8. from sale_detail
  9. insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  10. select shop_name, customer_id, total_price
  11. insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  12. select shop_name, customer_id, total_price;
  13. -- 出错返回,同一分区出现多次。
  14. from sale_detail
  15. insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  16. select shop_name, customer_id, total_price
  17. insert into table sale_detail_multi partition (sale_date='2011', region='china' )
  18. select shop_name, customer_id, total_price;
  19. -- 出错返回,同一张表的不同分区,不能同时有 insert overwrite insert into 操作。

输出到动态分区(DYNAMIC PARTITION)

在Insert overwrite到一张分区表时,可以在语句中指定分区的值。也可以用另外一种更加灵活的方式,在分区中指定一个分区列名,但不给出值。相应地,在select子句中的对应列来提供分区的值。

命令格式如下

  1. insert overwrite table tablename partition (partcol1, partcol2 ...) select_statement from from_statement;

注意:

  • select_statement字段中,后面的字段将提供目标表动态分区值。如目标表就一级动态分区,则select_statement最后一个字段值即为目标表的动态分区值。

  • 目前,在使用动态分区功能的SQL中,在分布式环境下,单个进程最多只能输出512个动态分区,否则引发运行时异常。

  • 在现阶段,任意动态分区SQL不允许生成超过2000个动态分区,否则引发运行时异常。

  • 动态生成的分区值不允许为NULL,也不支持含有特殊字符和中文,否则会引发异常,如:FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=xxx

  • 如果目标表有多级分区,在运行Insert语句时允许指定部分分区为静态,但是静态分区必须是高级分区。

动态分区的示例如下

  1. create table total_revenues (revenue bigint) partitioned by (region string);
  2. insert overwrite table total_revenues partition(region)
  3. select total_price as revenue, region
  4. from sale_detail;

按照上述写法,在SQL运行之前,是不知道会产生哪些分区的,只有在select运行结束后,才能由region字段产生的值确定会产生哪些分区,这也是叫做动态分区的原因。

其他示例如下

  1. create table sale_detail_dypart like sale_detail;--创建示例目标表
  1. --示例一:
  2. insert overwrite table sale_detail_dypart partition (sale_date, region)
  3. select shop_name,customer_id,total_price,sale_date,region from sale_detail;
  4. -- 成功返回;
  • 此时sale_detail表中,sale_date的值决定目标表的sale_date分区值,region的值决定目标表的region分区值。

  • 动态分区中,select_statement字段和目标表动态分区的对应是按字段顺序决定的。如该示例中,select语句若写成select shop_name,customer_id,total_price,region,sale_date from sale_detail;则sale_detail表中,region值决定决定目标表的 sale_date分区值,sale_date的值决定目标表的region分区值。

  1. --示例二:
  2. insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  3. select shop_name,customer_id,total_price,region from sale_detail;
  4. -- 成功返回,多级分区,指定一级分区
  1. --示例三:
  2. insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  3. select shop_name,customer_id,total_price from sale_detail;
  4. -- 失败返回,动态分区插入时,动态分区列必须在select列表中
  1. --示例四:
  2. insert overwrite table sales partition (region='china', sale_date)
  3. select shop_name,customer_id,total_price,region from sale_detail;
  4. -- 失败返回,不能仅指定低级子分区,而动态插入高级分区

另外,旧版MaxCompute在进行动态分区时,如果分区列的类型与对应select列表中列的类型不严格一致,会报错。MaxCompute2.0则支持隐式类型转换,示例如下:

  1. create table parttable(a int, b double) partitioned by (p string);
  2. insert into parttable partition(p) select key, value, current_timestmap() from src;
  3. select * from parttable;

执行上述语句后返回结果如下:

a b c
0 NULL 2017-01-23 22:30:47.130406621
0 NULL 2017-01-23 22:30:47.130406621

VALUES

通常在业务测试阶段,需要给一个小数据表准备些基本数据,您可以通过INSERT … VALUES的方法快速对测试表写入一些测试数据。

注意

目前INSERT OVERWRITE还不支持这种指定插入列的功能,暂时只能用INSERT INTO。

命令格式如下

  1. INSERT INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)][co1name1,colname2...] VALUES (col1_value,col2_value,...)[,(col1_value,col2_value,...),...]

示例一

  1. drop table if exists srcp;
  2. create table if not exists srcp (key string ,value bigint) partitioned by (p string);
  3. insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);

Insert…values语句执行成功后,查询表srcp分区p=’abc’,结果如下:

  1. +-----+------------+---+
  2. | key | value | p |
  3. +-----+------------+---+
  4. | a | 1 | abc |
  5. | b | 2 | abc |
  6. | c | 3 | abc |
  7. +-----+------------+---+

当表有很多列,而准备数据的时候希望只插入部分列的数据,此时可以用插入列表功能如下。

示例二

  1. drop table if exists srcp;
  2. create table if not exists srcp (key string ,value bigint) partitioned by (p string);
  3. insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');

Insert…values语句执行成功后,查询表srcp分区p=’20170101’,结果如下:

  1. +-----+------------+---+
  2. | key | value | p |
  3. +-----+------------+---+
  4. | d | NULL | 20170101 |
  5. | e | NULL | 20170101 |
  6. | f | NULL | 20170101 |
  7. +-----+------------+---+

对于在values中没有制定的列,可以看到取缺省值为NULL。插入列表功能不一定和values一起用,对于Insert into…select…,同样可以使用。

Insert…values有一个限制:values必须是常量,但是有时候希望在插入的数据中进行一些简单的运算,此时可以使用MaxCompute的values table功能,详情见示例三。

示例三:

  1. drop table if exists srcp;
  2. create table if not exists srcp (key string ,value bigint) partitioned by (p string);
  3. 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);

其中的values (…), (…) t (a, b),相当于定义了一个名为t,列为a,b的表,类型为(a string,b bigint),其中的类型从values列表中推导。这样在不准备任何物理表的时候,可以模拟一个有任意数据的,多行的表,并进行任意运算。

Insert…values语句执行成功后,查询表srcp分区p=’20170102’,结果如下:

  1. +-----+------------+---+
  2. | key | value | p |
  3. +-----+------------+---+
  4. | d4 | 2 | 20170102 |
  5. | e5 | 2 | 20170102 |
  6. | f6 | 2 | 20170102 |
  7. +-----+------------+---+

注意:

  • values只支持常量不支持函数,类似ARRAY复杂类型目前无法构造对应的常量,可以将语句改造成:insert into table srcp (p ='abc') select 'a',array('1', '2', '3');达到一样的效果。

  • 通过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');

实际上,values表并不限于在Insert语句中使用,任何DML语句都可以使用。

还有一种values表的特殊形式,如下所示:

  1. select abs(-1), length('abc'), getdate();

如上述语句所示,可以不写from语句,直接执行select,只要select的表达式列表不用任何上游表数据就可以。其底层实现为从一个1行,0列的匿名values表选取。这样,在希望测试一些函数,比如自己的UDF等时,便不用再手工创建DUAL表。

本文导读目录