开发ODPS SQL任务 - 大数据开发治理平台 DataWorks

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语句	`SET a=b; SET c=d; CREATE TABLE name1(id string);`	`SET a=b; CREATE TABLE name1(id string);`
第二条SQL语句	`SET a=b; SET c=d; CREATE TABLE name2(id string);`	`SET c=d; CREATE TABLE name2(id string);`

编辑代码：简单示例

SQL命令

MaxCompute的SQL命令使用类似标准SQL语法，支持DDL、DML、DQL语句，及MaxCompute的特定语法等命令，各SQL命令的详细语法要求及使用示例请参见SQL概述。下面以一个简单示例为您介绍SQL命令的开发及运行。

说明

MaxCompute 2.0扩展函数使用到新数据类型时，您需要在该函数的SQL语句前加SET odps.sql.type.system.odps2=true;，并与SQL一起提交运行，以便正常使用新数据类型。2.0数据类型详情请参见2.0数据类型版本。
DataWorks提供调度参数，可实现调度场景下代码动态入参，您可在ODPS 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任务示例如下：