表操作
1. 查看表的详细信息:
odps@ YITIAN_BJ_MC>desc sale_detail;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2019-05-31 10:43:36 |
| LastDDLTime: 2019-05-31 10:43:36 |
| LastModifiedTime: 2019-05-31 14:59:55 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 1752 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| shop_name | string | | |
| customer_id | string | | |
| total_price | double | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| sale_date | string | |
| region | string | |
+------------------------------------------------------------------------------------+
说明 通过 Describe Table查看到的Size包含了在回收站的数据Size。如果您需要清空回收站,可以先执行 purge table table_name;,然后再 Describe Table查看除回收站以外的数据大小。您也可以执行show recyclebin;查看本项目中回收站内的数据明细。
使用 DESC EXTENDED table_name;
命令查看Hash Clustering Table的Clustering属性,如下所示,Clustering属性将显示在Extended Info中:
odps@ YITIAN_BJ_MC>desc extended t2;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2019-06-01 15:06:52 |
| LastDDLTime: 2019-06-01 15:06:52 |
| LastModifiedTime: 2019-06-01 15:06:52 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Comment |
+------------------------------------------------------------------------------------+
| a | string | | | |
| b | string | | | |
| c | bigint | | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| dt | string | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 4dac5206ec7443808f11f34f9a9e3a26 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| StoredAs: CFile |
| CompressionStrategy: normal |
| ClusterType: hash |
| BucketNum: 1024 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
2. 通过 create table...as select...
语句创建表,并在建表的同时将数据复制到新表中:
odps@ YITIAN_BJ_MC>create table sale_detail_ctasl as select * from sale_detail where sale_date='201312' and region='hangzhou';
此时,如果sale_detail中存在数据,上面的示例会将sale_detail的数据全部复制到sale_detail_ctas1表中。
说明 此处sale_detail是一张分区表,而通过
create table...as select...
语句创建的表不会复制分区属性,只会把源表的分区列作为目标表的一般列处理,即sale_detail_ctas1是一个含有5列的非分区表。
odps@ YITIAN_BJ_MC>desc sale_detail_ctasl;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2019-06-01 14:45:12 |
| LastDDLTime: 2019-06-01 14:45:12 |
| LastModifiedTime: 2019-06-01 14:45:12 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 1400 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| shop_name | string | | |
| customer_id | string | | |
| total_price | double | | |
| sale_date | string | | |
| region | string | | |
+------------------------------------------------------------------------------------+
在 create table...as select...
语句中,如果在select子句中使用常量作为列的值,建议指定列的名字,如下所示:
odps@ YITIAN_BJ_MC>create table sale_detail_ctas2 as
>select shop_name, customer_id, total_price, '2013' as sale_date, 'china' as region
>from sale_detail where sale_date='201312' and region='hangzhou';
odps@ YITIAN_BJ_MC>select * from sale_detail_ctas2;
ID = 20190601064947117gdd3eqsa
Log view:...
Job Queueing...
Summary:
+------------+-------------+-------------+------------+------------+
| shop_name | customer_id | total_price | sale_date | region |
+------------+-------------+-------------+------------+------------+
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| shopx | x_id | 100.0 | 2013 | china |
| shopy | y_id | 200.0 | 2013 | china |
+------------+-------------+-------------+------------+------------+
如果不加列的别名,如下所示:
odps@ YITIAN_BJ_MC>create table sale_detail_ctas3 as
>select shop_name, customer_id, total_price, '2013', 'china'
>from sale_detail where sale_date='201312' and region='hangzhou';
则创建的表sale_detail_ctas3的第四、五列类似于_c5
、_c6:
odps@ YITIAN_BJ_MC>select * from sale_detail_ctas3;
ID = 20190601065159966g2ww66pr2
Log view:...
Job Queueing...
Summary:
+------------+-------------+-------------+------------+------------+
| shop_name | customer_id | total_price | _c3 | _c4 |
+------------+-------------+-------------+------------+------------+
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| sample | NULL | NULL | 2013 | china |
| shopx | x_id | 100.0 | 2013 | china |
| shopy | y_id | 200.0 | 2013 | china |
+------------+-------------+-------------+------------+------------+
3. 如果希望源表和目标表具有相同的表结构,可以尝试使用 create table...like
操作,如下所示:
odps@ YITIAN_BJ_MC>create table sale_detail_like like sale_detail;
odps@ YITIAN_BJ_MC>desc sale_detail_like;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2019-06-01 14:52:51 |
| LastDDLTime: 2019-06-01 14:52:51 |
| LastModifiedTime: 2019-06-01 14:52:51 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| shop_name | string | | |
| customer_id | string | | |
| total_price | double | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| sale_date | string | |
| region | string | |
+------------------------------------------------------------------------------------+
此时,sale_detail_like
的表结构与sale_detail
完全相同。除生命周期属性外,列名、列注释以及表注释等均相同。但sale_detail
中的数据不会被复制到sale_detail_like
表中。
4. 创建Hash Clustering表示例:
odps@ YITIAN_BJ_MC>create table t1(a string, b string, c bigint)
>clustered by (c) sorted by (c) into 1024 buckets; -- 创建非分区表
odps@ YITIAN_BJ_MC>desc t1;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2019-06-01 15:05:27 |
| LastDDLTime: 2019-06-01 15:05:27 |
| LastModifiedTime: 2019-06-01 15:05:27 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| a | string | | |
| b | string | | |
| c | bigint | | |
+------------------------------------------------------------------------------------+
创建分区表:
odps@ YITIAN_BJ_MC>create table t2(a string, b string, c bigint)
>partitioned by (dt string)
>clustered by (c) sorted by (c) into 1024 buckets;
odps@ YITIAN_BJ_MC>desc t2;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2019-06-01 15:06:52 |
| LastDDLTime: 2019-06-01 15:06:52 |
| LastModifiedTime: 2019-06-01 15:06:52 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| a | string | | |
| b | string | | |
| c | bigint | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| dt | string | |
+------------------------------------------------------------------------------------+
5. 删除表:
DROP TABLE [IF EXISTS] table_name;
- 如果不指定if exists选项而表不存在,则返回异常。若指定此选项,无论表是否存在,皆返回成功。
- 删除外部表时,OSS上的数据不会被删除。
6. 重命名表:
ALTER TABLE table_name RENAME TO new_table_name;
- rename操作仅修改表的名字,不改动表中的数据。
- 如果已存在与new_table_name同名表,则报错。
- 如果table_name不存在,则报错。
7. 修改表owner:
alter table table_name changeowner to 'ALIYUN$xxx@aliyun.com';
8. 修改表的注释
ALTER TABLE table_name SET COMMENT 'tbl comment';
- table_name必须是已存在的表。
- comment最长1024字节。
9. 修改表的Hash Clustering属性
ALTER TABLE table_name
[CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS]
去除表的hash clustering属性:
ALTER TABLE table_name NOT CLUSTERED;
- alter table改变聚集属性,只对于分区表有效,非分区表一旦聚集属性建立就无法改变。
- 由于alter table只影响新分区,所以该语句不可以再指定PARTITIONALTER TABLE语句适用于存量表,在增加了新的聚集属性之后,新的分区将做hash cluster存储。
10. 清空非分区表里的数据
将指定的非分区表中的数据清空,该命令不支持分区表。对于分区表,可以用ALTER TABLE table_name DROP PARTITION
的方式将分区里的数据清除。
清空非分区表里的数据的语法格式,如下所示:
TRUNCATE TABLE table_name;
生命周期操作
1. 修改表的生命周期属性的语法格式,如下所示:
ALTER TABLE table_name SET lifecycle days;
说明
- days参数为生命周期时间,只接受正整数,单位为天。
- 如果表table_name是非分区表,自最后一次数据被修改开始计算,经过days天后数据仍未被改动,则此表无需您干预,将会被MaxCompute自动回收(类似drop table操作)。
- 在MaxCompute中,每当表的数据被修改后,表的LastDataModifiedTime将会被更新,因此,MaxCompute会根据每张表的LastDataModifiedTime以及lifecycle的设置来判断是否要回收此表。
- 如果table_name是分区表,则根据各分区的LastDataModifiedTime判断该分区是否该被回收。
- 不同于非分区表,分区表的最后一个分区被回收后,该表不会被删除。
- 生命周期只能设定到表级别,不能再分区级设置生命周期。
- 创建表时即可指定生命周期。
- 非分区表不支持取消lifecycle,只能修改lifecycle。分区表可以取消某个具体分区的lifecycle
create table test_lifecycle(key string) lifecycle 100;
-- 新建test_lifecycle表,生命周期为100天。
alter table test_lifecycle set lifecycle 50;
-- 修改test_lifecycle表,将生命周期设为50天。
2. 禁止生命周期:
ALTER TABLE table_name partition_spec ENABLE|DISABLE LIFECYCLE;
使用示例:
ALTER TABLE trans PARTITION(dt='20141111') DISABLE LIFECYCLE;
分区和列操作
1. 添加分区操作
语法格式:
ALTER TABLE TABLE_NAME ADD [IF NOT EXISTS] PARTITION partition_spec
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...);
说明
- 分区名必须小写。
- 仅支持新增分区,不支持新增分区字段。
- 如果未指定if not exists而同名的分区已存在,则返回报错。
- 目前MaxCompute单表支持的分区数量上限为6万。
- 对于多级分区的表,如果想添加新的分区,必须指明全部的分区值。
使用示例如下:
alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
-- 成功添加分区,用来存储2013年12月杭州地区的销售记录。
alter table sale_detail add if not exists partition (sale_date='201312', region='shanghai');
-- 成功添加分区,用来存储2013年12月上海地区的销售记录。
alter table sale_detail add if not exists partition(sale_date='20111011');
-- 仅指定一个分区sale_date,出错返回
alter table sale_detail add if not exists partition(region='shanghai');
-- 仅指定一个分区region,出错返回
2. 删除分区操作
ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
示例如下,假设从表sale_detail中删除一个分区,如下所示:
alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou');
-- 成功删除2013年12月杭州分区的销售记录。
3. 添加列操作
添加列的语法格式,如下所示:
ALTER TABLE table_name ADD COLUMNS (col_name1 type1,col_name2 type2...);
同时添加列和注释,如下所示:
ALTER TABLE table_name ADD COLUMNS (col_name1 type1 comment 'XXX',col_name2 type2 comment 'XXX');
说明 添加的新列不支持指定顺序,默认在最后一列。
4. 修改列名称
ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;
5. 修改列、分区注释
修改列、分区注释的语法格式,如下所示:
ALTER TABLE table_name CHANGE COLUMN col_name COMMENT comment_string;
6. 修改分区值
MaxCompute SQL支持通过rename操作更改对应表的分区值。
修改分区值的语法格式,如下所示:
ALTER TABLE table_name PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
RENAME TO PARTITION (partition_col1 = partition_col_newvalue1, partition_col2 = partiton_col_newvalue2, ...);
说明
- 不支持修改分区列列名,只能修改分区列对应的值。
- 修改多级分区的一个或者多个分区值,多级分区的每一级的分区值都必须写上。
视图操作
1. 创建视图:https://help.aliyun.com/document_detail/73770.html?spm=a2c4g.11186623.6.653.2f775a230EiwVQ
2. 删除视图
3. 重命名视图
INSERT操作
1. 基本的命令格式:
INSERT OVERWRITE|INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]
[(col1,col2 ...)]
select_statement FROM from_statement;
说明
- MaxCompute的insert语法与通常使用的MySQL或Oracle的insert语法有差别,在insert overwrite/into后需要加入table关键字,而非直接使用tablename。
- 当insert的目标表是分区表时,指定分区值
[PARTITION (partcol1=val1, partcol2=val2 …)]
语法中不允许使用函数等表达式。- 目前insert overwrite还不支持指定插入列的功能,暂时只能用insert into。
- 不支持insert into到hash clustering表。
- 当遇到并发写入时,MaxCompute会根据ACID进行并发写的保障。关于ACID的具体语义,请参见.MaxCompute的ACID。
在MaxCompute SQL处理数据的过程中,insert overwrite/into用于将计算的结果保存目标表中。insert into与insert overwrite的区别是:insert into会向表或表的分区中追加数据,而insert overwrite会在向表或分区中插入数据前清空表中的原有数据。
比如计算sale_detail表中不同地区的销售额,操作如下:
> create table sale_detail_insert like sale_detail;
> alter table sale_detail_insert add partition(sale_date='2013', region='china');
> insert overwrite table sale_detail_insert partition (sale_date='2013', region='china') select shop_name, customer_id,total_price from sale_detail;
向某个分区插入数据时,分区列不允许出现在select列表中:
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;
-- 报错返回,sale_date,region为分区列,不允许出现在静态分区的insert语句中。
同时,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 into partition作业如果不能控制并发,只能通过预创建分区来避免问题。
2. 多路输出(MULTI INSERT)
https://help.aliyun.com/document_detail/73776.html?spm=a2c4g.11186623.6.656.3ea32b5aP58bKB
3. 输出到动态分区
https://help.aliyun.com/document_detail/73779.html?spm=a2c4g.11186623.6.657.7689289dhGNpVJ
SELECT语句
1. 命令格式:
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]
Select分区表时禁止全表扫描。
2018-01-10 20点后创建的新项目,默认情况下执行SQL时,针对该project里的分区表不允许全表扫描,必须有分区条件指定需要扫描的分区,由此减少SQL的不必要I/O,从而减少计算资源的浪费,同时也减少了不必要的后付费模式的计算费用(后付费模式中,数据输入量是计量计费参数之一)。
若实在需要对分区表进行全表扫描,可以在对分区表全表扫描的SQL语句前加一个set语句set odps.sql.allow.fullscan=true;
,并和SQL语句一起提交执行。假设sale_detail表为分区表,则要全表扫描需同时提交如下简单查询命令:
set odps.sql.allow.fullscan=true;
select * from sale_detail;
如果需要整个项目都允许全表扫描,可以通过开关自行打开或关闭(true/false),命令:
setproject odps.sql.allow.fullscan=true;
2. Distinct去重
Distinct:如果有重复数据行时,在字段前使用distinct,会将重复字段去重,只返回一个值,而使用all将返回字段中所有重复的值,不指定此选项时默认效果和all相同。
使用distinct只返回一行记录,如下所示:
select distinct region from sale_detail;
select distinct region, sale_date from sale_detail;
-- distinct多列,distinct的作用域是 Select 的列集合,不是单个列。
3. select_expr正则表达式
MaxCompute sql支持使用select_expr正则表达式选列。
使用select_expr正则表达式时,需要使用 ` (反单引号)将正则表达式括起来,举例如下。
SELECT `abc.*` FROM t;
选出 t 表中所有列名以abc开头的列。SELECT `(ds)?+.+` FROM t;
选出t表中列名不为ds的所有列。SELECT `(ds|pt)?+.+` FROM t;
选出t表中排除ds和pt两列的其他列。SELECT `(d.*)?+.+` FROM t;
选出t表中排除列名以d开头的其他列。
4. Group BY分组查询,Order by/Sort by/Distribute by
https://help.aliyun.com/document_detail/73777.html?spm=a2c4g.11186623.6.659.5c1612b0Wc7EXD
5. Select语序
按照Select语法格式书写的Select语句,实际上的逻辑执行顺序与标准的书写语序实际并不相同。
以下用示例进行说明:
SELECT key
,MAX(value)
FROM src t
WHERE value > 0
GROUP BY key
HAVING SUM(value) > 100
ORDER BY key
LIMIT 100
;
实际上的逻辑执行顺序是FROM->WHERE->GROUY BY->HAVING->SELECT->ORDER BY->LIMIT
:
- order by中只能引用Select列表中生成的列,而不是访问From的源表中的列。
- Having可以访问的是group by key和聚合函数。
- Select的时候,如果有group by,便只能访问group key和聚合函数,而不是From中源表中的列。
为了避免混淆,MaxCompute支持以执行顺序书写查询语句,例如上面的语句可以写为:
FROM src t
WHERE value > 0
GROUP BY key
HAVING SUM(value) > 100
SELECT key
,MAX(value)
ORDER BY key
LIMIT 100
;
6. select子查询
https://help.aliyun.com/document_detail/73781.html?spm=a2c4g.11186623.6.661.8a70184alRQBEp
7. 交集、并集和补集
语法格式如下:
select_statement UNION ALL select_statement;
select_statement UNION [DISTINCT] select_statement;
select_statement INTERSECT ALL select_statement;
select_statement INTERSECT [DISTINCT] select_statement;
select_statement EXCEPT ALL select_statement;
select_statement EXCEPT [DISTINCT] select_statement;
select_statement MINUS ALL select_statement;
select_statement MINUS [DISTINCT] select_statement;
- UNION: 求两个数据集的并集。即将两个数据集合并成一个数据集。
- INTERSECT:求两个数据集的交集。即输出两个数据集均包含的记录。
- EXCEPT: 求第二个数据集在第一个数据集中的补集。即输出第一个数据集包含而第二个数据集不包含的记录。
- MINUS: 等同于EXCEPT。
8. Join操作
https://help.aliyun.com/document_detail/73783.html?spm=a2c4g.11186623.6.663.61377eb54svzlN
9. SEMI JOIN和ANTI JOIN
LEFT SEMI JOIN
当Join条件成立时,返回左表中的数据。也就是mytable1中某行的Id在mytable2的所有Id中出现过,此行就保留在结果集中。
示例如下:
SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;
只会返回mytable1中的数据,只要mytable1的Id在mytable2的Id中出现。
LEFT ANTI JOIN
当Join条件不成立时,返回左表中的数据。也就是mytable1中某行的Id在mytable2的所有Id中没有出现过,此行便保留在结果集中。
示例如下:
SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;
只会返回mytable1中的数据,只要mytable1的Id在mytable2的Id没有出现。
10. MAP JOIN HINT
当一个大表和一个或多个小表JOIN时,您可以使用MAPJOIN提升性能。
MAPJOIN的基本原理:在小数据量情况下,SQL会将您指定的小表全部加载到执行JOIN操作的程序的内存中,从而加快JOIN的执行速度。
https://help.aliyun.com/document_detail/73785.html?spm=a2c4g.11186623.6.665.1524526f6nhiHD
11. Lateral View
Lateral View和split,explode等UDTF一起使用,它能够将一行数据拆成多行数据,并在此基础上对拆分后的数据进行聚合。
https://help.aliyun.com/document_detail/87722.html?spm=a2c4g.11186623.6.666.274741d9xO01i5
12. HAVING子句
由于MaxCompute SQL的Where关键字无法与合计函数一起使用,可以采用HAVING子句。
命令格式如下。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
示例如下。
比如有一张订单表Orders,包括客户名称(Customer),订单金额(OrderPrice),订单日期(Order_date),订单号(Order_id)四个字段。现在希望查找订单总额少于2000的客户。SQL语句如下所示:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
Explain
MaxCompute SQL提供Explain操作,用来显示对应于DML语句的最终执行计划结构的描述。所谓执行计划就是最终用来执行SQL语义的程序。
命令格式如下:
EXPLAIN <DML query>;
Explain的执行结果包含如下内容:
- 对应于该DML语句的所有Task的依赖结构。
- Task中所有Task的依赖结构。
- Task中所有Operator的依赖结构。
VALUES
本文向您介绍INSERT … VALUES
命令操作。
通常在业务测试阶段,需要给一个小数据表准备些基本数据,您可以通过 INSERT … VALUES
的方法在测试表中快速写入一些测试数据。
说明 目前
INSERT OVERWRITE
不支持这种指定插入列的功能,只能使用INSERT INTO
实现。
命令格式如下:
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;
create table if not exists srcp (key string,value bigint) partitioned by (p string);
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);
INSERT … VALUES
语句执行成功后,查询表srcp分区 p=abc
,结果如下。
+------------+------------+------------+
| key | value | p |
+------------+------------+------------+
| a | 1 | abc |
| b | 2 | abc |
| c | 3 | abc |
+------------+------------+------------+
2. 非特定分区内插入数据
当表有很多列,而准备数据的时候希望只插入部分列的数据,此时可以使用插入列表功能。
示例
drop table if exists srcp;
create table if not exists srcp (key string,value bigint) partitioned by (p string);
insert into table srcp partition (p)(key,p) values ('d','2019'),('e','2019'),('f','2019');
查询结果如下:
odps@ YITIAN_BJ_MC>select * from srcp where p='2019';
ID = 20190602023446900g7i62ssa
Log view:...
Job Queueing...
Summary:
+------------+------------+------------+
| key | value | p |
+------------+------------+------------+
| d | NULL | 2019 |
| e | NULL | 2019 |
| f | NULL | 2019 |
+------------+------------+------------+
Values table功能
Values table并不限于在insert语句中使用,任何DML语句都可以使用。
INSERT … VALUES
有一个限制:values必须是常量。但是当需要在插入的数据中进行一些简单的运算时,可使用MaxCompute的values table功能。示例:
drop table if exists srcp;
create table if not exists srcp (key string,value bigint) partitioned by (p string);
insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'2019' from values ('d',4),('e',5),('f',6) t(a,b);
查询数据如下:
odps@ YITIAN_BJ_MC>select * from srcp where p='2019';
ID = 20190602024518126gjll2tsa
Job Queueing...
Summary:
+------------+------------+------------+
| key | value | p |
+------------+------------+------------+
| d4 | 2 | 2019 |
| e5 | 2 | 2019 |
| f6 | 2 | 2019 |
+------------+------------+------------+
说明 其中的
values (…), (…) t(a, b)
相当于定义了一个名为t,列为a、b的表,类型分别为STRING、BIGINT,其中的类型从values列表中推导。这样在不准备任何物理表时,可以模拟一个有任意数据的、多行的表,并进行任意运算。(这种方式不会创建物理表,可以看做是一种临时表)
VALUES TABLE
这个用法还可以取代 select * from dual
与 union all
组合的方式,来拼出常量表,如下所示。
select 1 c from dual
union all
select 2 c from dual;
--等同于
select * from values (1), (2) as t(c);
--返回结果:
+------------+
| c |
+------------+
| 1 |
| 2 |
+------------+
还有一种values表的特殊形式,如下所示。
select abs(-1), length('abc'), getdate();
--返回结果
+------------+------------+------------+
| _c0 | _c1 | _c2 |
+------------+------------+------------+
| 1 | 3 | 2019-06-02 10:51:03 |
+------------+------------+------------+
如上述语句所示,可以不写from语句,直接执行select(只要select表达式列表中不出现上游表的数据)。其底层实现为从一个1行,0列的匿名values表选取。这样,在您试图测试UDF或其他函数时,可免去手工创建DUAL表的过程。
说明:通过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. 窗口函数
MaxCompute SQL中可以使用窗口函数进行灵活的分析处理工作,窗口函数只能出现在select子句中。窗口函数中请不要嵌套使用窗口函数和聚合函数,窗口函数不可以和同级别的聚合函数一起使用。目前在一个MaxCompute SQL语句中,最多可以使用5个窗口函数。
窗口函数的语法声明:
window_func() over (partition by [col1,col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] windowing_clause)
- partition by部分用来指定开窗的列。分区列的值相同的行被视为在同一个窗口内。现阶段,同一窗口内最多包含1亿行数据(建议不超过500万行),否则运行时报错。
- order by用来指定数据在一个窗口内如何排序。
- windowing_clause部分可以用rows指定开窗方式,有以下两种方式:
- rows between x preceding|following and y preceding|following表示窗口范围是从前或后x行到前或后y行。
- rows x preceding|following窗口范围是从前或后第x行到当前行。
2. 其他窗口函数:https://help.aliyun.com/document_detail/34994.html?spm=a2c4g.11186623.6.678.37c83804R51871
COUNT/AVG/MAX/MIN/MEDIAN/STDDEV/STDDEV_SAMP/SUM/DENSE_RANK/RANK/LAG/LEAD/PERCENT_RANK/ROW_NUMBER/CLUSTER_SAMPLE/CUME_DIST/NTILE/
参考资料
- https://help.aliyun.com/document_detail/73769.html?spm=a2c4g.11186623.6.651.48d6924elLJavU
- https://help.aliyun.com/document_detail/73771.html?spm=a2c4g.11186623.6.652.10b63da5Nrjdro
- https://help.aliyun.com/document_detail/73775.html?spm=a2c4g.11186623.6.655.2d653ce0XYzuOo
- https://help.aliyun.com/document_detail/73777.html?spm=a2c4g.11186623.6.659.5c1612b0Wc7EXD
- https://help.aliyun.com/document_detail/73780.html?spm=a2c4g.11186623.6.660.60ad6d92xwAGdL
- https://help.aliyun.com/document_detail/73778.html?spm=a2c4g.11186623.6.673.5e3331828sNVj1
- https://help.aliyun.com/document_detail/34994.html?spm=a2c4g.11186623.6.678.37c83804R51871