开发ODPS SQL任务

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 INTOINSERT 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任务示例如下: