阿里云odpsSql手册.pdf

大数据计算服务MaxComputeSQLSQL SQLSQL 概要介绍概要介绍 MaxCompute SQL适用于海量数据(TB级别)，实时性要求不高的场合，它的每个作业的准备，提交等阶段要花费较长时间，因此要求每秒处理几千至数万笔事务的业务是不能用 MaxCompute 完成的。MaxCompute SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不能因此简单的把MaxCompute 等价成一个数据库，它在很多方面并不具备数据库的特征，如事务、主键约束、索引等。目前在 MaxCompute 中允许的最大SQL长度是2MB。关键字关键字 MaxCompute 将SQL语句的关键字作为保留字。在对表、列或是分区命名时请不要使用，否则会报错。保留字不区分大小写。下面只给出常用的保留字列表，完整的保留字列表请参阅 MaxCompute SQL保留字。MaxCompute SQL允许数据类型之间的转换，类型转换方式包括：显式类型转换及隐式类型转换。类型转换说明类型转换说明 显式类型转换显式类型转换 显式类型转换是用cast将一种数据类型的值转换为另一种类型的值的行为，在MaxCompute SQL中支持的显式类型转换如下：%&()*+-./;=?ADD ALL ALTERAND AS ASC BETWEEN BIGINT BOOLEAN BYCASE CAST COLUMN COMMENT CREATE DESC DISTINCTDISTRIBUTE DOUBLE DROP ELSE FALSE FROM FULLGROUP IF IN INSERT INTO IS JOINLEFT LIFECYCLE LIKE LIMIT MAPJOIN NOT NULLON OR ORDER OUTER OVERWRITE PARTITION RENAMEREPLACE RIGHT RLIKE SELECT SORT STRING TABLETHEN TOUCH TRUE UNION VIEW WHEN WHEREFrom/ToFrom/ToBigintBigintDoubleDoubleStringStringDatetimeDatetimeBooleanBooleanDecimalDecimal大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL1 1-其中，Y表示可以转换，N表示不可以转换，-表示不需要转换。比如：备注:将double类型转为bigint类型时，小数部分会被截断，例如：cast(1.6 as bigint)=1；满足double格式的string类型转换为bigint时，会先将string转换为double，再将double转换为bigint，因此，小数部分会被截断，例如cast(1.6 as bigint)=1；满足bigint格式的string类型可以被转换为double类型，小数点后保留一位，例如：cast(1 asdouble)=1.0；不支持的显式类型转换会导致异常；如果在执行时转换失败，报错退出；日期类型转换时采用默认格式yyyy-mm-dd hh:mi:ss，详细说明信息请参考String类型与Datetime类型之间的转换；部分类型之间不可以通过显式的类型转换，但可以通过SQL内建函数进行转换，例如：从boolean类型转换到string类型，可使用函数to_char，详细介绍请参考 TO_CHAR，而to_date函数同样支持从string类型到datetime类型的转换，详细介绍请参考 TO_DATE；关于cast的介绍请参阅 CAST；DECIMAL超出值域，CAST STRING TO DECIMAL可能会出现最高位溢出报错，最低位溢出截断等情况。隐式类型转换及其作用域隐式类型转换及其作用域 隐式类型转换是指在运行时，由 MaxCompute 依据上下文使用环境及类型转换规则自动进行的类型转换。MaxCompute 支持的隐式类型转换规则与显式转换相同：Bigint-YYNNYDoubleY-YNNYStringYY-YNYDatetimeNNY-NNBooleanNNNN-NDecimalYYYNN-select cast(user_id as double)as new_id from user;select cast(2015-10-01 00:00:00 as datetime)as new_date from user;From/ToFrom/ToBigintBigintDoubleDoubleStringStringDatetimeDatetimeBooleanBooleanDecimalDecimalBigint-YYNNYDoubleY-YNNY大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL2 2-其中，Y表示可以转换，N表示不可以转换，-表示不需要转换。常见用法如下：备注:不支持的隐式类型转换会导致异常；如果在执行时转换失败，也会导致异常；由于隐式类型转换是 MaxCompute 依据上下文使用环境自动进行的类型转换，因此，我们推荐在类型不匹配时显式的用cast进行转换;隐式类型转换规则是有发生作用域的。在某些作用域中，只有一部分规则可以生效。详细信息请参考隐式类型转换的作用域；关系运算符关系运算符 关系运算符包括：=,=,IS NULL,IS NOT NULL,LIKE,RLIKE和IN。由于LIKE,RLIKE和IN的隐式类型转换规则不同于其他关系运算符，将单独拿出章节对这三种关系运算符做出说明。本小节的说明不包含这三种特殊的关系运算符。当不同类型的数据共同参与关系运算时，按照下述原则进行隐式类型转换。备注:如果待比较的两个类型间不能进行隐式类型转换，则该关系运算不能完成，报错退出；关系运算符介绍，请参阅 关系操作符；StringYY-YNYDatetimeNNY-NNBooleanNNNN-NDecimalYYYNN-select user_id+age+12345,concat(user_name,user_id,age)from user;From/ToFrom/ToBigintBigintDoubleDoubleStringStringDatetimeDatetimeBooleanBooleanDecimalDecimalBigint-DoubleDoubleNNDecimalDoubleDouble-DoubleNNDecimalStringDoubleDouble-DatetimeNDecimalDatetimeNNDatetime-NNBooleanNNNN-NDecimalDecimalDecimalDecimalNN-大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3 3-特殊的关系运算符(LIKE,RLIKE,IN)特殊的关系运算符(LIKE,RLIKE,IN)LIKE及RLIKE的使用方式形如：此二者在隐式类型转换中的注意事项：LIKE和RLIKE的source和pattern参数均仅接受string类型；其他类型不允许参与运算，也不能进行到string类型的隐式类型转换；IN的使用方式形如：In的隐式转换规则：In右侧的value值列表中的数据类型必须一致；当key与values之间比较时，若bigint,double,string之间比较，统一转double，若datetime和string之间比较，统一转datetime。除此之外不允许其它类型之间的转换。算术运算符算术运算符 算术运算符包括：+,-,*,/,%,+,-，其隐式转换规则：只有string、bigint、double和Decimal才能参与算术运算。String在参与运算前会进行隐式类型转换到double。Bigint和double共同参与计算时，会将bigint隐式转换为double。日期型和布尔型不允许参与算数运算。备注：算术运算符的相关章节 算术操作符。逻辑运算符逻辑运算符 逻辑运算符包括：and,or和not，其隐式转换规则:只有boolean才能参与逻辑运算。其他类型不允许参与逻辑运算，也不允许其他类型的隐式类型转换。备注：逻辑运算符的相关章节 逻辑操作符。source like pattern;source rlike pattern;key in(value1,value2,)大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL4 4-MaxCompute SQL内建函数MaxCompute SQL内建函数 MaxCompute SQL提供了大量的系统函数，方便用户对任意行的一列或多列进行计算，输出任意种的数据类型。其隐式转换规则:在调用函数时，如果输入参数的数据类型与函数定义的参数数据类型不一致，把输入参数的数据类型转换为函数定义的数据类型。每个ODPS SQL内建函数的参数对于允许的隐式类型转换的要求不同，详见 内建函数 部分的说明。CASE WHENCASE WHEN Case when的隐式转换规则:如果返回类型只有bigint,double，统一转double；如果返回类型中有string类型，统一转string，如果不能转则报错(如boolean类型)；除此之外不允许其它类型之间的转换；备注：Case when的详细介绍请参阅 CASE WHEN表达式 分区表分区表 MaxCompute SQL支持分区表。指定分区表会对用户带来诸多便利，例如：提高SQL运行效率，减少计费等。在如下场景下使用分区表将会带来较大的收益：在Select语句的Where条件过滤中使用分区列作为过滤条件；与此同时，部分对分区操作的SQL的运行效率则较低，给您带来较高的计费，例如：使用动态分区 对于部分 MaxCompute 操作命令，处理分区表和非分区表时的语法有差别，详细情况请参考DDL语句 及 DML语句 部分的说明。目前，ODPS分区仅支持string类型，不承诺其他分区类型的正确性，不支持其他任意类型的隐式类型转换。create table src(key string,value bigint)partitioned by(pt string);-目前，MaxCompute 仅承诺String类型分区select*from src where pt=20151201;-正确使用方式。MaxCompute 在生成查询计划时只会将20151201分区的数据纳入输入中select*from src where pt=20151201;-错误的使用方式。在这样的使用方式下，MaxCompute并不能保障分区过滤机制的有效性。pt是String类型，当String类型与Bigint(20151201)比较时，MaxCompute会将二者转换为Double类型，此时有可能会有精度损失。大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL5 5-UNION ALLUNION ALL 参与 UNION ALL 运算的所有列的数据类型、列个数、列名称必须完全一致，否则抛异常。String类型与Datetime类型之间的转换String类型与Datetime类型之间的转换 MaxCompute 支持string类型和datetime类型之间的相互转换。转换时使用的格式为yyyy-mm-ddhh:mi:ss，其中：备注：各个单位的值域中，如果首位为0，不可省略，例如：2014-1-9 12:12:12就是非法的datetime格式，无法从这个string类型数据转换为datetime类型，必须写为2014-01-0912:12:12。只有符合上述格式描述的string类型才能够转换为datetime类型，例如：cast(2013-12-3102:34:34 as datetime)，将会把string类型2013-12-31 02:34:34转换为datetime类型。同理，datetime转换为string时，默认转换为yyyy-mm-dd hh:mi:ss的格式。类似于下面的转换尝试，将会失败导致异常，例如：值得注意的是，dd部分的阈值上限取决于月份实际拥有的天数，如果超出对应月份实际拥有的天数，将会导致异常退出，例如：MaxCompute 提供了to_date函数，用以将不满足日期格式的string类型数据转换为datetime类型。详细信息请参阅 TO_DATE。单位单位字符串(忽略大小写)字符串(忽略大小写)有效值域有效值域年yyyy0001 9999月mm01 12日dd01 28,29,30,31时hh00 23分mi00 59秒ss00 59cast(2013/12/31 02/34/34 as datetime)cast(20131231023434 as datetime)cast(2013-12-31 2:34:34 as datetime)cast(2013-02-29 12:12:12 as datetime)-异常返回，2013年2月没有29日cast(2013-11-31 12:12:12 as datetime)-异常返回，2013年11月没有31日大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL6 6 关系操作符关系操作符 常见用法如下：操作符操作符说明说明A=B如果A或B为NULL，返回NULL；如果A等于B，返回TRUE，否则返回FALSEAB如果A或B为NULL，返回NULL；如果A不等于B，返回TRUE，否则返回FALSEAB如果A或B为NULL，返回NULL；如果A小于B，返回TRUE，否则返回FALSEAB如果A或B为NULL，返回NULL；如果A大于B，返回TRUE，否则返回FALSEA=B如果A或B为NULL，返回NULL；如果A大于等于B，返回TRUE，否则返回FALSEA IS NULL如果A为NULL，返回TRUE，否则返回FALSEA IS NOT NULL如果A不为NULL，返回TRUE，否则返回FALSEA LIKE B如果A或B为NULL，返回NULL，A为字符串，B为要匹配的模式，如果匹配，返回TRUE，否则返回FALSE。%匹配任意多个字符，_匹配单个字符。要匹配%或_需要用转义符表示%，_。aaa like a_=TRUE aaa like a%=TRUEaaa like aab=FALSE a%blike a%b=TRUE axb like a%b=FALSE-A RLIKE BA是字符串，B是字符串常量正则表达式；如果匹配成功，返回TRUE，否则返回FALSE；如果B为空串会报错退出；如果A或B为NULL，返回NULL；A IN BB是一个集合，如果A为NULL，返回NULL，如A在B中则返回TRUE，否则返回FALSE 若B仅有一个元素NULL，即A IN(NULL)，则返回NULL。若B含有NULL元素，将NULL视为B集合中其他元素的类型。B必须是常数并且至少有一项，所有类型要一致select*from user where user_id=0001;select*from user where user_name maggie;大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL7 7-由于double值存在一定的精度差，因此，我们不建议直接使用等号=对两个double类型数据进行比较。用户可以使用两个double类型相减，而后取绝对值的方式判断。当绝对值足够小时，认为两个double数值相等，例如:备注：Abs是ODPS提供的内建函数，意为取绝对值，详细可参考 ABS。通常情况下，ODPS的double类型能够保障14位有效数字。算术操作符算术操作符 常见用法如下：select*from user where age 50;select*from user where birth_day=1980-01-01 00:00:00;select*from user where is_female is null;select*from user where is_female is not null;select*from user where user_id in(0001,0010);select*from user where user_name like M%;abs(0.9999999999-1.0000000000),=,=,like,rlikein,not in如果在in/not in条件后加子查询，子查询只能返回一列值，且返回值的数量不能超过1000。select sale_detail.*from sale_detailwhere sale_detail.sale_date=2008 and sale_detail.sale_date=2014;select*from(select region from sale_detail)t where region=shanghai;select distinct region from sale_detail;select distinct region,sale_date from sale_detail;-distinct多列，distinct的作用域是select的列集合，不是单个列。大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3030-1.2.3.-group by：分组查询,一般group by是和聚合函数配合使用。在select中包含聚合函数时：用group by的key可以是输入表的列名;也可以是由输入表的列构成的表达式，不可以是select语句的输出列的别名;规则1的优先级高于规则2。当规则1和规则2发生冲突时，即group by的key即是输入表的列或表达式，又是select的输出列，以规则1为准。如果如 有这样的限制是因为，在SQL解析中，group by操作通常是先于select操作的，因此group by只能接受输入表的列或表达式为key。备注:关于聚合函数的介绍请参考 聚合函数 order by：对所有数据按照某几列进行全局排序。如果您希望按照降序对记录进行排序，可以使用DESC关键字。由于是全局排序，order by必须与limit共同使用。对在使用order by排序时，NULL会被认为比任何值都小，这个行为与Mysql一致，但是与Oracle不一致。与group by不同，order by后面必须加select列的别名，当select某列时，如果没有指定列的别名，将列名作为列的别名。limit number的number是常数，限制输出行数。当使用无limit的select语句直接从屏幕输出查看结果时，最多只输出5000行。每个项目空间的这个屏显最大限制限制可能不同，可以通过控制台面板控 select region from sale_detail group by region;-直接使用输入表列名作为group by的列，可以运行select sum(total_price)from sale_detail group by region;-以region值分组，返回每一组的销售额总量，可以运行select region,sum(total_price)from sale_detail group by region;-以region值分组，返回每一组的region值(组内唯一)及销售额总量，可以运行select region as r from sale_detail group by r;-使用select列的别名运行，报错返回select China-+region as r from sale_detail group by China-+region;-必须使用列的完整表达式select region,total_price from sale_detail group by region;-报错返回，select的所有列中，没有使用聚合函数的列，必须出现在group by中select region,total_price from sale_detail group by region,total_price;-可以运行 select*from sale_detail order by region;-报错返回，order by没有与limit共同使用select*from sale_detail order by region limit 100;select region as r from sale_detail order by region;-报错返回，order by后面必须加列的别名。select region as r from sale_detail order by r;大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3131-制。distribute by：对数据按照某几列的值做hash分片，必须使用select的输出列别名。sort by：局部排序，语句前必须加distribute by。实际上sort by是对distribute by的结果进行局部排序。必须使用select的输出列别名。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操作，如：备注：子查询必须要有别名。在from子句中，子查询可以当作一张表来使用，与其它的表或子查询进行join操作，如 select region from sale_detail distribute by region;-列名即是别名，可以运行select region as r from sale_detail distribute by region;-报错返回，后面必须加列的别名。select region as r from sale_detail distribute by r;select region from sale_detail distribute by region sort by region;select region as r from sale_detail sort by region;-没有distribute by，报错退出。select*from(select shop_name from sale_detail)a;create table shop as select*from sale_detail;大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3232-UNION ALLUNION ALL 语法格式：将两个或多个select操作返回的数据集联合成一个数据集，如果结果有重复行时，会返回所有符合条件的行，不进行重复行的去重处理。需要注意的是：MaxCompute SQL不支持顶级的两个查询结果合并，要改写为一个子查询的形式，如 需要改成：备注:union all操作对应的各个子查询的列个数、名称和类型必须一致。如果列名不一致时，可以使用列的别名加以解决。一般情况下，MaxCompute 最多允许128路union all，超过此限制报语法错误。JOIN操作JOIN操作 MaxCompute 的JOIN支持多路间接，但不支持笛卡尔积，即无on条件的链接。语法定义：select a.shop_name,a.customer_id,a.total_price from(select*from shop)a join sale_detail on a.shop_name=sale_detail.shop_name;select_statement UNION ALL select_statement select*from sale_detail where region=hangzhouunion allselect*from sale_detail where region=shanghai;select*from(select*from sale_detail where region=hangzhouunion allselect*from sale_detail where region=shanghai)t;join_table:table_reference join table_factor join_condition|table_reference left outer|right outer|full outer|inner join table_reference join_conditiontable_reference:table_factor|join_tabletable_factor:大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3333-备注:equality_expression是一个等式表达式 left join 会从左表(shop)那里返回所有的记录，即使在右表(sale_detail)中没有匹配的行。right outer join 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配，例如：full outer join 全连接，返回左右表中的所有记录，例如：在表中存在至少一个匹配时，inner join 返回行。关键字inner可省略。连接条件，只允许and连接的等值条件，并且最多支持16路join操作。只有在MAPJOIN中，可以使用不等值连接或者使用or连接多个条件。MAPJOIN HINTMAPJOIN HINT当一个大表和一个或多个小表做join时，可以使用mapjoin，性能比普通的join要快很多。mapjoin的基本原理tbl_name alias|table_subquery alias|(table_references)join_condition:on equality_expression(and equality_expression)*select a.shop_name as ashop,b.shop_name as bshop from shop aleft outer join sale_detail b on a.shop_name=b.shop_name;-由于表shop及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。select a.shop_name as ashop,b.shop_name as bshop from shop aright outer join sale_detail b on a.shop_name=b.shop_name;select a.shop_name as ashop,b.shop_name as bshop from shop afull outer join sale_detail b on a.shop_name=b.shop_name;select a.shop_name from shop a inner join sale_detail b on a.shop_name=b.shop_name;select a.shop_name from shop a join sale_detail b on a.shop_name=b.shop_name;select a.*from shop a full outer join sale_detail b on a.shop_name=b.shop_namefull outer join sale_detail c on a.shop_name=c.shop_name;-支持多路join链接示例，最多支持16路joinselect a.*from shop a join sale_detail b on a.shop_name!=b.shop_name;-不支持不等值Join链接条件，报错返回。大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3434-是：在小数据量情况下，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的表。下面是一个简单的示例：MaxCompute SQL不支持支持在普通join的on条件中使用不等值表达式、or 逻辑等复杂的join条件，但是在mapjoin中可以进行如上操作，例如：HAVING子句HAVING子句 由于MaxCompute SQL的WHERE关键字无法与合计函数一起使用，可以采用having字句。语法格式：select/*+mapjoin(a)*/a.shop_name,b.customer_id,b.total_pricefrom shop a join sale_detail bon a.shop_name=b.shop_name;select/*+mapjoin(a)*/a.total_price,b.total_pricefrom shop a join sale_detail bon a.total_price b.total_price or a.total_price+b.total_price 500;SELECT column_name,aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name)operator value大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL3535-使用场景举例：比如有一张订单表Orders，包括客户名称（Customer,），订单金额（OrderPrice），订单日期（Order_date），订单号（Order_id）四个字段。现在希望查找订单总额少于2000的客户。现在我们可以写如下语句：数学函数数学函数 ABSABS 函数定义：用途：返回绝对值。参数说明：number：Double或bigint类型，输入为bigint时返回bigint，输入为double时返回double类型。若输入为string类型会隐式转换到double类型后参与运算，其它类型抛异常。返回值：Double或者bigint类型，取决于输入参数的类型。若输入为null，返回null。备注：当输入bigint类型的值超过bigint的最大表示范围时，会返回double类型，这种情况下可能会损失精度。示例：下面是一个完整的abs函数在SQL中使用的例子，其他内建函数(除窗口函数、聚合函数外)的使用方式与其类似，不再一一举例：SELECT Customer,SUM(OrderPrice)FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)SELECT get_json_object(src_json.json,$.owner)FROM src_json;amyodps SELECT get_json_object(src_json.json,$.store.fruit0)FROM src_json;weight:8,type:appleodps SELECT get_json_object(src_json.json,$.non_exist_key)FROM src_json;NULLget_json_object(array:aaaa,1111,bbbb,2222,cccc,3333,$.array1.1)=2222get_json_object(aaa:bbb,ccc:ddd:eee,fff:ggg,hhh:h0,h1,h2,iii:jjj,$.ccc.hhh*)=h0,h1,h2get_json_object(aaa:bbb,ccc:ddd:eee,fff:ggg,hhh:h0,h1,h2,iii:jjj,$.ccc.hhh1)=h1 bigint instr(string str1,string str2,bigint start_position,bigint nth_appearance)大数据计算服务MaxCompute/SQL大数据计算服务MaxCompute/SQL4848-如果str2为空串时总是能匹配成功，因此instr(abc,)会返回1。示例：IS_ENCODINGIS_ENCODING 函数声明：用途：判断输入字符串str是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。可用于判断输入是否为乱码，通常的用法是将from_encoding设为utf-8，to_encoding设为gbk。参数说明：str：String类型，输入为NULL返回NULL。空字符串则可以被认为属于任何字符集。from_encoding，to_encoding：String类型，源及目标字符集。输入为NULL返回NULL。返回值：Boolean类型，如果str能够成功转换，则返回true，否则返回false 示例：KEYVALUEKEYVALUE 函数声明:用途:将srcStr（源字符串）按split1分成key-value对，按split2将key-value对分开，返回key所对应的value。instr(Tech on the net,e)=2instr(Tech on the net,e,1,1)=2instr(Tech on the net,e,1,2)=11instr(Tech on the net,e,1,3)=14instr(Tech on the net,e,-1,1)=14instr(Tech on the net,e,-3,2)=2instr(Tech on the net,o,-1,2)=0 boolean is_encoding(string str,string from_encoding,string to_encoding)is_encoding(测试,utf-8,gbk)=tr