DataWorks为您提供ODPS SQL节点,可用于周期性调度MaxCompute的SQL任务,并完成与其他类型节点的集成和联合调度。MaxCompute SQL任务采用类似SQL的语法,适用于海量数据(TB级)但实时性要求不高的分布式处理场景。本文为您介绍在DataWorks上开发SQL任务的注意事项及开发指导。
前提条件
已创建ODPS SQL节点,详情请参见创建并管理MaxCompute节点。
背景信息
ODPS SQL用于处理和查询MaxCompute中的数据,支持常见的SQL操作,例如SELECT、INSERT、UPDATE、DELETE等,以及特定的MaxCompute语法和函数。使用ODPS SQL,您可以编写类似于SQL的语句来查询和处理数据,而无需编写复杂的数据处理逻辑,SQL语法详情请参见SQL概述。
使用限制
DataWorks中开发ODPS SQL节点的限制如下:
分类 | 说明 |
注释使用 | 不支持在关键字(SET、USE)语句后单独加注释。 |
不支持在注释中使用英文分号(;)。 | |
不支持在已完结的语句结尾加注释。SQL语句后添加英文分号(;),表示语句已完结。 | |
SQL下发 | ODPS SQL不支持单独使用SET、USE语句,必须和具体的SQL语句一起执行。 |
SQL开发 | SQL代码大小不能超过128 KB,SQL命令条数不能超过200条。 |
查询结果 | 仅支持SELECT和WITH起始的SQL语句输出格式化的结果集,且最多能显示10,000行结果数据,返回结果大小不超过10 MB。 |
注意事项
DataWorks上不同环境运行关键字(SET、USE)相关语句时,执行顺序不同。
在数据开发DataStudio中运行:将合并当前任务代码内所有的关键字(SET、USE)语句,作为所有SQL的前置语句。
在调度环境运行:将按照实际编写顺序执行。
假设在节点中定义的代码如下。
SET a=b;
CREATE TABLE name1(id string);
SET c=d;
CREATE TABLE name2(id string);
不同环境执行顺序如下:
执行SQL | 数据开发DataStudio | 调度运维 |
第一条SQL语句 |
|
|
第二条SQL语句 |
|
|
编辑代码:简单示例
SQL命令
MaxCompute的SQL命令使用类似标准SQL语法,支持DDL、DML、DQL语句,及MaxCompute的特定语法等命令,各SQL命令的详细语法要求及使用示例请参见SQL概述。下面以一个简单示例为您介绍SQL命令的开发及运行。
创建表
您可使用
CREATE TABLE
语句创建非分区表、分区表、外部表及聚簇表,详情请参见CREATE TABLE。SQL示例如下:--创建一张分区表test1 CREATE TABLE if NOT EXISTS students ( id BIGINT, name STRING, age BIGINT, birth DATE) partitioned BY (gender STRING);
插入数据
您可使用
INSERT INTO
或INSERT OVERWRITE
语句向目标表中插入更新数据,详情请参见插入或覆写数据(INSERT INTO | INSERT OVERWRITE)。SQL示例如下:--插入数据 INSERT INTO students PARTITION(gender='boy') VALUES (1,'张三',15,DATE '2008-05-15') ; INSERT INTO students PARTITION(gender='boy') VALUES (2,'李四',17,DATE '2006-07-20') ; INSERT INTO students PARTITION(gender='girl') VALUES (3,'李霞',20,DATE '2003-04-20') ; INSERT INTO students PARTITION(gender='girl') VALUES (4,'王兰',21,DATE '2002-01-08') ; INSERT INTO students PARTITION(gender='boy') VALUES (5,'王五',17,DATE '2006-09-12') ;
重要请尽量避免使用
INSERT INTO
语句插入数据,可能会造成不可预料的数据重复。推荐您使用INSERT OVERWRITE
的方式,详情请参见插入或覆写数据。查询数据
您可使用
SELECT
语句实现嵌套查询、分组查询、排序等操作,详情请参考SELECT语法。SQL示例如下:--开启全表扫描,仅此Session有效 SET odps.sql.allow.fullscan=true; --查询所有男生的信息,并按照ID进行升序排序 SELECT * FROM students WHERE gender='boy' ORDER BY id;
说明RAM用户默认没有生产表查询权限,生产表查询权限请前往安全中心申请,DataWorks上MaxCompute数据权限预设以及访问控制,详情请参见MaxCompute数据权限控制详情。MaxCompute命令授权详情请参见通过命令管理用户权限。
SQL函数
MaxCompute支持使用内建函数和自定义函数进行数据开发及分析,内建函数详情请参见内建函数概述,自定义函数详情请参见MaxCompute UDF概述。下面以一个简单示例为您介绍SQL函数的使用。
内建函数
内建函数为MaxCompute自身预置的函数,您可以直接调用。基于上述示例,使用
dateadd
函数对birth
列按照指定单位和幅度变化,命令示例如下:--开启全表扫描,仅此Session有效 SET odps.sql.allow.fullscan=true; SELECT id, name, age, birth, dateadd(birth,1,'mm') AS birth_dateadd FROM students;
自定义函数(UDF)
使用自定义函数时,您需要自行编写函数代码、上传为资源并注册函数,具体操作请参见创建并使用自定义函数。
运行及返回结果
运行结果会直接以电子表格的形式展示。您可以在DataWorks中执行操作,或者在电子表格中打开,也可以自由复制内容粘贴至本地Excel中,详情请参见任务调试流程。
说明由于国际标准化组织发布的中国时区信息调整,通过DataWorks执行相关SQL时,日期显示某些时间段会存在时间差异:1900~1928年的日期时间差异5分52秒,1900年之前的日期时间差异9秒。
运行日志:您可以单击运行日志页签,查看Logview,详情请参见使用Logview 2.0查看作业运行信息。
返回结果:
查询所有男生的信息,并按照ID进行升序排序。
+------------+------------+------------+------------+------------+ | id | name | age | birth | gender | +------------+------------+------------+------------+------------+ | 1 | 张三 | 15 | 2008-05-15 | boy | | 2 | 李四 | 17 | 2006-07-20 | boy | | 5 | 王五 | 17 | 2006-09-12 | boy | +------------+------------+------------+------------+------------+
对birth列按照指定单位和幅度变化。
+------------+------------+------------+------------+---------------+ | id | name | age | birth | birth_dateadd | +------------+------------+------------+------------+---------------+ | 4 | 王兰 | 21 | 2002-01-08 | 2002-02-08 | | 3 | 李霞 | 20 | 2003-04-20 | 2003-05-20 | | 2 | 李四 | 17 | 2006-07-20 | 2006-08-20 | | 1 | 张三 | 15 | 2008-05-15 | 2008-06-15 | | 5 | 王五 | 17 | 2006-09-12 | 2006-10-12 | +------------+------------+------------+------------+---------------+
您可在查询结果页针对指定字段执行排序操作,并对目标查询结果执行下载等相关操作,详情请参见处理查询结果。
编辑代码:进阶示例
更复杂的ODPS SQL任务示例如下: