開發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代碼大小不能超過128KB，SQL命令條數不能超過200條。
查詢結果	僅支援select和with起始的SQL語句輸出格式化的結果集，且最多能顯示10000行結果資料。

注意事項

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任務樣本如下：