SQL指南

MaxCompute SQL语法


SQL概述

DMP大数据平台数据开发模块是基于阿里云MaxCompute作为核心计算和存储引擎,为您提供海量数据(GB、TB、EB级别)的整合、离线加工分析、数据挖掘等功能。

MaxCompute作业提交后会有几十秒到数分钟不等的排队调度,所以适合处理跑批作业,一次作业批量处理海量数据,不适合直接对接需要每秒处理几千至数万笔事务的前台业务系统。

MaxCompute SQL采用的是类似于SQL的语法。它的语法是标准语法ANSI SQL92的一个子集,并有自己的扩展,但不能因此简单地把MaxCompute等价成一个数据库,它在很多方面并不具备数据库的特征,如事务、主键约束、索引等,更多差异请参见与其他SQL语法的差异。目前在MaxCompute中允许的最大SQL长度是3MB。

关键字

MaxCompute将SQL语句的关键字作为保留字。在对表、列或是分区命名时如若使用关键字,需给关键字加``符号进行转义,否则会报错。保留字不区分大小写。下面只给出常用的保留字列表,完整的保留字列表请参见 MaxCompute SQL保留字

% & && ( ) * + - . /
; < <= <> = > >= ? ADD ALL
ALTER AND AS ASC BETWEEN BETWEEN BIGINT BOOLEAN BY CASECAST
COLUMN COMMENT CREATE DESC DISTINCT DISTRIBUTE DOUBLE DROP ELSE FALSE
FROM FULL GROUP IF IN INSERT INTO IS JOIN LEFT
LIFECYCLE LIKE LIMIT MAPJOIN NOT NULL ON OR ORDER OUTER
OVERWRITE PARTITION RENAME REPLACE RIGHT RLIKE SELECT SORT STRING TABLE
THEN TOUCH TRUE UNION VIEW WHEN WHERE

注意:

  • 在对表、列或分区进行命名时,不要使用保留字与关键字,否则可能会报错。
  • 保留字不区分大小写。

类型转换说明

MaxCompute SQL允许数据类型之间的转换,类型转换方式包括显式类型转换和隐式类型转换。更多详情请参见 类型转换

  • 显式类型转换:是指用cast将一种数据类型的值转换为另一种类型的值的行为。
  • 隐式类型转换:是指在运行时,由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。隐式转换作用域包括各种运算符、内建函数等作用域。

分区表

MaxCompute SQL支持分区表。指定分区表会对您带来诸多便利,例如提高SQL运行效率、减少计费等,目前支持Tinyint、Smallint、Int、Bigint、Varchar和String分区类型

分区有以下使用限制:

  • 单表分区层级最多6级。

  • 单表分区数最多允许60000个分区。

  • 一次查询最多查询分区数为10000个分区。

  • String分区类型的分区值不支持使用中文。

示例如下:

--创建一个二级分区表,以日期为一级分区,地域为二级分区
create table src (key string, value bigint) partitioned by (pt string,region string);

查询时,Where条件过滤中使用分区列作为过滤条件:
select * from src where pt='20170601'and region='hangzhou'; --正确使用方式。
MaxCompute在生成查询计划时只会将'20170601'分区下region为'hangzhou'二级分区的数据纳入输入中。

select* fromsrc wherept = 20170601; -- 错误的使用方式。
在这样的使用方式下,MaxCompute并不能保障分区过滤机制的有效性。pt是String类型,当String类型与Bigint(20170601)比较时,
MaxCompute会将二者转换为Double类型,此时有可能会有精度损失。

对于部分MaxCompute的操作命令,处理分区表和非分区表时的语法有差别,详情请参见 表操作INSERT操作。

UNION ALL

参与UNION ALL运算的所有列的数据类型、列个数、列名称必须完全一致,否则会报异常。

Select Transform

Select Transform功能明显简化了对脚本代码的引用,支持Java、Python、Shell、Perl等语言,且编写过程简单,适合adhoc功能的实现。详情请参见Select Transform语法。

目前MaxCompute的select transform完全兼容了Hive的语法、功能和行为,包括input/output row format以及reader/writer。Hive上的脚本,大部分可以直接运行,部分脚本只需要经过稍微的改动即可运行。

使用限制

SQL限制项请参见 SQL限制项,不支持的DDL及DML语法请参见 与其他SQL语法的差异

说明:与其他SQL的差异和不兼容带来的限制,可参见 与标准SQL的主要区别及解决方法修改不兼容SQL实战解决

其他限制:

  • SCALAR SUBQUERY限制
  • Insert values限制:values必须是常量
  • MaxCompute最多允许256个表的union all/union
  • Mapjoin的小表不能超过512MB
  • 由于国际标准化组织发布的中国时区信息调整,执行相关SQL时,日期显示某些时间段会存在时间差异:1900-1928年的日期时间差异5分52秒,1900年之前的日期时间差异9秒。

与其他SQL语法的差异

MaxCompute不支持的DDL语法
语法 MaxCompute Hive MySQL Oracle SQL Server
CREATE TABLE—PRIMARY KEY N N Y Y Y
CREATE TABLE—NOT NULL N N Y Y Y
CREATE TABLE—CLUSTER BY Y Y N Y Y
CREATE TABLE—EXTERNAL TABLE Y (OSS, OTS, TDDL) Y N N N
CREATE TABLE—TEMPORARY TABLE N Y Y Y Y (with #prefix)
INDEX—CREATE INDEX N Y Y Y Y
VIRTUAL COLUMN N N (only 2 predefined) N Y Y
MaxCompute不支持的DML语法
语法 MaxCompute Hive MySQL Oracle SQL Server
CTE Y Y Y Y Y
SELECT—recursive CTE N N N Y Y
SELECT—GROUP BY ROLL UP Y Y Y Y Y
SELECT—GROUP BY CUBE Y Y N Y Y
SELECT—GROUPING SET Y Y N Y Y
SELECT—IMPLICT JOIN Y Y N Y Y
SELECT—PIVOT N N N Y Y
SEMI JOIN Y Y (corelated expression must be in WHERE, EXISTS must be corelated) Y N (has IN and EXISTS, but no SEMI JOIN grammer) N (has IN and EXISTS, but no SEMI JOIN grammer)
SELEC TRANSFROM Y Y N N N
SELECT—corelated subquery Y Y (corelated expression must be in WHERE, EXISTS must be corelated) Y Y Y
ORDER BY NULLS FIRST/LAST Y Y Y Y Y
LATERAL VIEW Y Y N Y (LATERAL keyword) Y (CROSS APPLY keyword)
SET OPERATOR—UNION (disintct) Y Y Y Y Y
SET OPERATOR—INTERSECT Y N N Y Y
SET OPERATOR—MINUS/EXCEPT Y N N Y Y(keyword EXCEPT)
INSERT INTO ... VALUES Y Y Y Y Y
INSERT INTO (ColumnList) Y Y Y Y Y
UPDATE … WHERE N Y Y Y Y
UPDATE … ORDER BY LIMIT N N Y N Y
DELETE … WHERE N Y Y Y Y
DELETE … ORDER BY LIMIT N N Y N N
ANALYTIC—reusable WINDOWING CLUSUE Y Y N N N (can implement with join)
ANALYTIC—CURRENT ROW Y Y N Y Y
ANALYTIC—UNBOUNDED Y N Y Y Y
ANALYTIC—RANGE … N Y N Y Y
WHILE DO N N Y Y Y
MaxCompute不支持的SCRIPTING语法
语法 MaxCompute Hive MySQL Oracle SQL Server
TABLE VARIABLE Y Y (TEMPORARY TABLE) Y (TEMPORARY TABLE) Y (TEMPORARY TABLE) Y
SCALER VARIABLE Y Y Y (DECLARE x INT) Y Y
ERROR HANDLING—RAISE ERROR N N Y Y Y
ERROR HANDLING—TRY CATCH N N N Y Y
FLOW CONTROL—LOOP N N Y Y Y
CURSOR N N Y Y Y

高速分析数据引擎ADS


高速分析数据引擎ADS是基于阿里云的分析型数据库MySQL(简称ADB),实现云端托管的大规模并行处理(MPP)的PB级高并发实时数据仓库。在数据存储模型上,采用关系模型进行数据存储,可使用SQL进行自由灵活的计算分析,无需预先建模。利用云端的无缝伸缩能力,可以处理百亿条甚至更多量级的数据时真正实现毫秒级计算。

ADS支持的数据类型

  1. boolean布尔类型,值只能是0或1。取值0的逻辑意义为假,取值1的逻辑意义为真,存储字节数1比特位。
  2. tinyint微整数类型,取值范围-128到127,存储字节数1字节。
  3. smallint整数类型,取值范围-32768到32767,存储字节数2字节。
  4. int整数类型,取值范围-2147483648到2147483647,存储字节数4字节。
  5. bigint大整数类型,取值范围-9223372036854775808到9223372036854775807,存储字节数8字节。
  6. float单精度浮点数,取值范围-3.402823466E+38到-1.175494351E-38, 0, 1.175494351E-38到 3.402823466E+38,IEEE标准,存储字节数4字节。
  7. double双精度浮点数,取值范围-1.7976931348623157E+308到-2.2250738585072014E-308, 0, 2.2250738585072014E-308 到 1.7976931348623157E+308,IEEE标准,存储字节数8字节。
  8. varchar变长字符串类型。
  9. date日期类型,取值范围'1000-01-01' 到 '9999-12-31',支持的数据格式为'YYYY-MM-DD',存储字节数为4字节。
  10. time 时间类型,取值范围'00:00:00' 到 '23:59:59',支持的数据格式为'HH:MM:SS',存储字节数为4字节。
  11. timestamp时间戳类型, 取值范围'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC,支持的的数据格式为'YYYY-MM-DD HH:MM:SS',存储字节数为4字节。

与MySQL数据类型对比

ADS数据类型 MySQL数据类型 差异
boolean bool、boolean 一致
tinyint tinyint 一致
smallint smallint 一致
int int、integer 一致
bigint bigint 一致
float float[(m,d)] ADS不支持自定义m和d,MySQL支持
double double[(m,d)] ADS不支持自定义m和d,MySQL支持
varchar varchar ADS有长度限制
date date 一致
time time 取值范围不同
timestamp timestamp ADS精确到秒,MySQL支持自定义精度

DMP数据分析平台多场景计算函数差异说明


序号 表达式 使用场景 MySQL ADS PostgreSQL
1 SUM([表达式]) 数据集编辑SQL模式、高级字段、图表维度度量
2 AVG([表达式]) 数据集编辑SQL模式、高级字段、图表维度度量
3 MAX([表达式]) 数据集编辑SQL模式、高级字段、图表维度度量
4 MIN([表达式]) 数据集编辑SQL模式、高级字段、图表维度度量
5 COUNT([表达式]) 数据集编辑SQL模式、高级字段、图表维度度量
6 表达式1 + 表达式2 数据集编辑SQL模式、高级字段
7 表达式1 - 表达式2 数据集编辑SQL模式、高级字段
8 表达式1 * 表达式2 数据集编辑SQL模式、高级字段
9 表达式1 / 表达式2 数据集编辑SQL模式、高级字段
10 SUBSTR(字符串, 开始位置, [长度]) 数据集编辑SQL模式、高级字段
11 REPLACE(字符串, 子串, 替换串) 数据集编辑SQL模式、高级字段
12 CASE 表达式 WHEN 值1 THEN 返回值1 ... [ELSE 默认返回值] 数据集编辑SQL模式、高级字段、分组字段、自定义排序
13 SUM([表达式]) / COUNTD( [表达式] ) 数据集编辑SQL模式、高级字段
14 DAY_DIFF(日期字段1,日期字段2) 数据集编辑SQL模式、高级字段 √:转换TIMESTAMPDIFF() √:转换TIMESTAMPDIFF() X:需要转换extract()、date_part()
15 HOUR_DIFF(日期字段1,日期字段2) 数据集编辑SQL模式、高级字段 √:转换TIMESTAMPDIFF() √:转换TIMESTAMPDIFF() X:需要转换extract()、date_part()
16 MINUTE_DIFF(日期字段1,日期字段2) 数据集编辑SQL模式、高级字段 √:转换TIMESTAMPDIFF() √:转换TIMESTAMPDIFF() X:需要转换extract()、date_part()
17 TODAY() 数据集编辑SQL模式、高级字段 √:转换curdate() √:转换curdate() X:需要转换now()
18 distinct 字段 数据集编辑SQL模式、图表维度度量、高级字段
19 IFNULL 数据集编辑SQL模式、图表维度度量、高级字段 X
20 DATE_FORMAT 数据集编辑SQL模式、图表维度度量 X:需要转换to_char(current_date,'yyyy-mm-dd')
21 Group By 数据集编辑SQL模式、图表维度度量
22 ORDER BY 数据集编辑SQL模式、图表维度度量
23 LIMIT 数据集编辑SQL模式、图表
24 BETWEEN AND 数据集编辑SQL模式、视图模式筛选条件、图表筛选条件、数据巡检
25 LIKE/NOT LIKE 数据集编辑SQL模式、视图模式筛选条件、图表筛选条件、数据巡检
26 IN/NOT IN 数据集编辑SQL模式、视图模式筛选条件、图表筛选条件、数据巡检
27 IS NULL / IS NOT NULL 数据集编辑SQL模式、视图模式筛选条件、图表筛选条件、数据巡检
28 != 数据集编辑SQL模式、视图模式筛选条件、图表筛选条件、数据巡检 √:支持!=、<>
29 Left Join 数据集编辑SQL模式、视图模式筛选条件
30 Right Join 数据集编辑SQL模式、视图模式筛选条件 X
31 Inner Join 数据集编辑SQL模式、视图模式筛选条件
32 Full Join 数据集编辑SQL模式、视图模式筛选条件 X
33 Union 数据集编辑SQL模式

results matching ""

    No results matching ""