全部产品
MaxCompute

INSERT 操作

更新时间:2017-07-24 20:48:09   分享:   

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

语法格式:

  1. INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
  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_date, region 为分区列,不允许出现在静态分区的 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) (p, a) 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

首先,请 安装 MaxCompute Studio添加 MaxCompute 项目并创建工程,然后新建 MaxCompute 脚本文件,如下图所示:

1

执行后,在 MaxCompute Project Explorer 中可以找到新创建的表,并看到 values 中的数据已经插入到表中,如下图所示:

1

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

1

执行后,在 MaxCompute Project Explorer 中找到目标表,并看到 values 中的数据已经插入,如下图所示:

1

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

INSERT… VALUES… 有一个限制:values 必须是常量,但有时候希望在插入的数据中进行一些简单的运算,此时可以使用 MaxCompute 的 VALUES TABLE 功能,如下图所示:

1

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

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

还有一种 VALUES 表的特殊形式:

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

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

本文导读目录
本文导读目录
以上内容是否对您有帮助?