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

INSERT操作

更新时间:2017-11-12 21:54:18

更新表中的数据(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 …)] 语法中不允许使用函数等表达式。

在 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 的方法快速对测试表写入一些测试数据。

命令格式如下

  1. INSERT OVERWRITE|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 表并不限于在 Insert 语句中使用,任何 DML 语句都可以使用。

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

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

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

本文导读目录