阅读99 返回首页    go 谷歌

DML语句__SQL_大数据计算服务-阿里云

更新表中的数据(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。

在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_detail向sale_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里最多可以写128路输出，超过128路报语法错误。
  • 在一个multi insert中，对于分区表，同一个目标分区不允许出现多次; 对于未分区表，该表不能出现多次。
  • 对于同一张分区表的不同分区，不能同时有insert overwrite和insert into操作，否则报错返回。

如，

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

输出到动态分区(DYNAMIC PARTITION)

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

语法格式：

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

说明：

  • 目前，在使用动态分区功能的SQL中，在分布式环境下，单个进程最多只能输出512个动态分区，否则引发运行时异常；
  • 在现阶段，任意动态分区SQL不允许生成超过2000个动态分区，否则引发运行时异常；
  • 动态生成的分区值不允许以为NULL，否则会引发异常；
  • 如果目标表有多级分区，在运行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;
  2.     insert overwrite table sale_detail_dypart partition (sale_date, region)
  3.         select * from sale_detail;
  4.     -- 成功返回
  6.     insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  7.         select shop_name,customer_id,total_price,region from sale_detail;
  8.     -- 成功返回，多级分区，指定一级分区
  10.     insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  11.         select shop_name,customer_id,total_price from sale_detail;
  12.     -- 失败返回，动态分区插入时，动态分区列必须在select列表中
  14.     insert overwrite table sales partition (region='china', sale_date)
  15.         select shop_name,customer_id,total_price,region from sale_detail;
  16.     -- 失败返回，不能仅指定低级子分区，而动态插入高级分区

SELECT操作

语法格式：

  1.     SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  2.         FROM table_reference
  3.         [WHERE where_condition]
  4.         [GROUP BY col_list]
  5.         [ORDER BY order_condition]
  6.         [DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
  7.         [LIMIT number]

在使用select语句时需要注意如下几点：

  • select操作从表中读取数据，要读的列可以用列名指定，或者用*代表所有的列，一个简单的select如下:
  1.     select * from sale_detail;

或者只读取sale_detail的一列shop_name

  1.     select shop_name from sale_detail;

在where中可以指定过滤的条件，如

  1.     select * from sale_detail where shop_name like 'hang%';

备注:

  • 请注意，当使用Select语句屏显时，目前最多只能显示1000行结果。当select作为子句时，无此限制，select子句会将全部结果返回给上层查询。
  • where子句：支持的过滤条件包括：
过滤条件 描述
> , < , =, >=, <=, <>
like, rlike
in, not in 如果在in/not in条件后加子查询，子查询只能返回一列值，且返回值的数量不能超过1000。

在select语句的where子句中可以指定分区范围，这样可以仅仅扫描表的指定部分，避免全表扫描。如下所示：

  1.     select sale_detail.*
  2.         from sale_detail
  3.         where sale_detail.sale_date >= '2008' and sale_detail.sale_date <= '2014';

MaxCompute SQL的where子句不支持between条件查询。

  • 在table_reference中支持使用嵌套子查询，如：
  1.     select * from (select region from sale_detail) t where region = 'shanghai';
  • distinct：如果有重复数据行时，在字段前使用distinct，会将重复字段去重，只返回一个值，而使用all将返回字段中所有重复的值，不指定此选项时默认效果和all相同。使用distinct只返回一行记录，如
  1.     select distinct region from sale_detail;
  2.     select distinct region, sale_date from sale_detail;
  3.     -- distinct多列，distinct的作用域是select的列集合，不是单个列。
  • group by：分组查询, 一般group by是和聚合函数配合使用。在select中包含聚合函数时：
  1. 用group by的key可以是输入表的列名;
  2. 也可以是由输入表的列构成的表达式，不允许是select语句的输出列的别名;
  3. 规则1的优先级高于规则2。当规则1和规则2发生冲突时，即group by的key即是输入表的列或表达式，又是select的输出列，以规则1为准。

如果如

  1.     select region from sale_detail group by region;
  2.     -- 直接使用输入表列名作为group by的列，可以运行
  4.     select sum(total_price) from sale_detail group by region;
  5.     -- 以region值分组，返回每一组的销售额总量，可以运行
  7.     select region, sum(total_price) from sale_detail group by region;
  8.     -- 以region值分组，返回每一组的region值(组内唯一)及销售额总量，可以运行
  12.     select region as r from sale_detail group by r;
  13.     -- 使用select列的别名运行，报错返回
  15.     select 'China-' + region as r from sale_detail group by 'China-' + region;
  16.     -- 必须使用列的完整表达式
  20.     select region, total_price from sale_detail group by region;
  21.     -- 报错返回，select的所有列中，没有使用聚合函数的列，必须出现在group by中
  23.     select region, total_price from sale_detail group by region, total_price;
  24.     -- 可以运行

有这样的限制是因为，在SQL解析中，group by操作通常是先于select操作的，因此group by只能接受输入表的列或表达式为key。

备注:

  • order by：对所有数据按照某几列进行全局排序。如果您希望按照降序对记录进行排序，可以使用DESC关键字。由于是全局排序，order by必须与limit共同使用。对在使用order by排序时，NULL会被认为比任何值都小，这个行为与Mysql一致，但是与Oracle不一致。与group by不同，order by后面必须加select列的别名，当select某列时，如果没有指定列的别名，将列名作为列的别名。
  1.     select * from sale_detail order by region;
  2.     -- 报错返回，order by没有与limit共同使用
  4.     select * from sale_detail order by region limit 100;
  6.     select region as r from sale_detail order by region;
  7.     -- 报错返回，order by后面必须加列的别名。
  9.     select region as r from sale_detail order by r;

  • [limit number]的number是常数，限制输出行数。当使用无limit的select语句直接从屏幕输出查看结果时，最多只输出5000行。每个项目空间的这个屏显最大限制限制可能不同，可以通过控制台面板控制。

  • distribute by：对数据按照某几列的值做hash分片，必须使用select的输出列别名。

  1.     select region from sale_detail distribute by region;
  2.     -- 列名即是别名，可以运行
  4.     select region as r from sale_detail distribute by region;
  5.     -- 报错返回，后面必须加列的别名。
  7.     select region as r from sale_detail distribute by r;
  • sort by：局部排序，语句前必须加distribute by。实际上sort by是对distribute by的结果进行局部排序。必须使用select的输出列别名。
  1.     select region from sale_detail distribute by region sort by region;
  2.     select region as r from sale_detail sort by region;
  3.     -- 没有distribute by，报错退出。
  • order by不和distribute by/sort by共用，同时group by也不和distribute by/sort by共用，必须使用select的输出列别名。

备注:

  • order by/sort by/distribute by的key必须是select语句的输出列，即列的别名。在MaxCompute SQL解析中， order by/sort by/distribute by是后于select操作的，因此它们只能接受select语句的输出列为key。

子查询

普通的select是从几张表中读数据，如select column_1, column_2 … from table_name，但查询的对象也可以是另外一个select操作，如：

  1.     select * from (select shop_name from sale_detail) a;

备注：

  • 子查询必须要有别名。

在from子句中，子查询可以当作一张表来使用，与其它的表或子查询进行join操作，如

  1.     create table shop as select * from sale_detail;
  3.     select a.shop_name, a.customer_id, a.total_price from
  4.         (select * from shop) a join sale_detail on a.shop_name = sale_detail.shop_name;

UNION ALL

语法格式：

  1.     select_statement UNION ALL select_statement

将两个或多个select操作返回的数据集联合成一个数据集，如果结果有重复行时，会返回所有符合条件的行，不进行重复行的去重处理。需要注意的是：MaxCompute SQL不支持顶级的两个查询结果合并，要改写为一个子查询的形式，如

  1.     select * from sale_detail where region = 'hangzhou'
  2.         union all
  3.     select * from sale_detail where region = 'shanghai';

需要改成：

  1.     select * from (
  2.         select * from sale_detail where region = 'hangzhou'
  3.         union all
  4.         select * from sale_detail where region = 'shanghai')
  5.         t;

备注:

  • union all操作对应的各个子查询的列个数、名称和类型必须一致。如果列名不一致时，可以使用列的别名加以解决。
  • 一般情况下，MaxCompute 最多允许128路union all，超过此限制报语法错误。

JOIN操作

MaxCompute 的JOIN支持多路间接，但不支持笛卡尔积，即无on条件的链接。语法定义：

  1.     join_table:
  2.         table_reference join table_factor [join_condition]
  3.         | table_reference {left outer|right outer|full outer|inner} join table_reference join_condition
  5.     table_reference:
  6.         table_factor
  7.         | join_table
  9.     table_factor:
  10.         tbl_name [alias]
  11.         | table_subquery alias
  12.         | ( table_references )
  14.     join_condition:
  15.         on equality_expression ( and equality_expression )*

备注:

  • equality_expression是一个等式表达式

left join 会从左表(shop)那里返回所有的记录，即使在右表(sale_detail)中没有匹配的行。

  1.     select a.shop_name as ashop, b.shop_name as bshop from shop a
  2.         left outer join sale_detail b on a.shop_name=b.shop_name;
  3.     -- 由于表shop及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。

right outer join 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配，例如：

  1.     select a.shop_name as ashop, b.shop_name as bshop from shop a
  2.         right outer join sale_detail b on a.shop_name=b.shop_name;

full outer join 全连接，返回左右表中的所有记录，例如：

  1.     select a.shop_name as ashop, b.shop_name as bshop from shop a
  2.         full outer join sale_detail b on a.shop_name=b.shop_name;

在表中存在至少一个匹配时，inner join 返回行。 关键字inner可省略。

  1.     select a.shop_name from shop a inner join sale_detail b on a.shop_name=b.shop_name;
  3.     select a.shop_name from shop a join sale_detail b on a.shop_name=b.shop_name;

连接条件，只允许and连接的等值条件，并且最多支持16路join操作。只有在MAPJOIN中，可以使用不等值连接或者使用or连接多个条件。

  1.     select a.* from shop a full outer join sale_detail b on a.shop_name=b.shop_name
  2.         full outer join sale_detail c on a.shop_name=c.shop_name;
  3.     -- 支持多路join链接示例，最多支持16路join
  5.     select a.* from shop a join sale_detail b on a.shop_name != b.shop_name;
  6.     -- 不支持不等值Join链接条件，报错返回。

MAPJOIN HINT

当一个大表和一个或多个小表做join时，可以使用mapjoin，性能比普通的join要快很多。mapjoin的基本原理是：在小数据量情况下，SQL会将用户指定的小表全部加载到执行join操作的程序的内存中，从而加快join的执行速度。需要注意，使用mapjoin时：

  • left outer join的左表必须是大表；
  • right outer join的右表必须是大表；
  • inner join左表或右表均可以作为大表；
  • full outer join不能使用mapjoin；
  • mapjoin支持小表为子查询；
  • 使用mapjoin时需要引用小表或是子查询时，需要引用别名；
  • 在mapjoin中，可以使用不等值连接或者使用or连接多个条件；
  • 目前MaxCompute 在mapjoin中最多支持指定6张小表，否则报语法错误；
  • 如果使用mapjoin，则所有小表占用的内存总和不得超过512MB。请注意由于MaxCompute 是压缩存储，因此小表在被加载到内存后，数据大小会急剧膨胀。此处的512MB限制是加载到内存后的空间大小；
  • 多个表join时，最左边的两个表不能同时是mapjoin的表。

下面是一个简单的示例：

  1.     select /* + mapjoin(a) */
  2.         a.shop_name,
  3.         b.customer_id,
  4.         b.total_price
  5.     from shop a join sale_detail b
  6.     on a.shop_name = b.shop_name;

MaxCompute SQL不支持支持在普通join的on条件中使用不等值表达式、or 逻辑等复杂的join条件，但是在mapjoin中可以进行如上操作，例如：

  1.     select /*+ mapjoin(a) */
  2.         a.total_price,
  3.         b.total_price
  4.     from shop a join sale_detail b
  5.     on a.total_price < b.total_price or a.total_price + b.total_price < 500;

HAVING子句

由于MaxCompute SQL的WHERE关键字无法与合计函数一起使用，可以采用having字句。

语法格式： 

  1. SELECT column_name, aggregate_function(column_name)
  2. FROM table_name
  3. WHERE column_name operator value
  4. GROUP BY column_name
  5. HAVING aggregate_function(column_name) operator value

使用场景举例：比如有一张订单表Orders，包括客户名称（Customer,），订单金额（OrderPrice），订单日期（Order_date），订单号（Order_id）四个字段。现在希望查找订单总额少于2000的客户。现在我们可以写如下语句： 

  1. SELECT Customer,SUM(OrderPrice) FROM Orders
  2. GROUP BY Customer
  3. HAVING SUM(OrderPrice)<2000

最后更新：2016-12-06 12:51:48

  上一篇：go DDL语句__SQL_大数据计算服务-阿里云
  下一篇：go 内建函数-上__SQL_大数据计算服务-阿里云