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