基於MaxCompute UDF進行SQL分析 - DataWorks

面對MaxCompute無法直接調用外部API的限制，本文將通過一個完整的實踐案例，教您如何利用“離線IP庫+UDF”的方案，在MaxCompute中高效地實現IP地址歸屬地分析。

案例說明

MaxCompute是一個封閉的計算環境，無法直接調用外部的HTTP API（如淘寶IP庫API）來即時查詢IP歸屬地。當需要對MaxCompute的IP地址進行IP歸屬地分析時，以下三種方案可實現需求。

方案選項	實現方式	優點	缺點	結論
方案一：資料匯出查詢	將需要分析的IP從MaxCompute匯出到本地，再用指令碼迴圈調用API查詢。	思路簡單。	效率極低，受API頻率限制（如10 QPS），不適用于海量資料。	不推薦
方案二：本地庫查詢	將IP庫和需要分析的IP都下載到本地，在本地程式中進行匹配。	無API頻率限制。	資料脫離了資料倉儲，無法與MaxCompute中的其他業務資料（如訂單表）進行關聯分析。	不推薦
方案三：IP庫入倉查詢	將整個IP地址庫上傳到MaxCompute的一張表中，然後利用SQL的UDF函數進行查詢對比。	效能高：充分利用MaxCompute的並行計算能力。整合度好：可直接與任何MaxCompute表進行關聯。可擴充：適用於任意資料規模。	需要自我維護IP庫的定期更新。	最佳方案

結論：方案三是唯一能滿足巨量資料情境下高效能、高整合度分析需求的方案。本教程將詳細介紹如何?這一最佳方案。

方案設計

我們將採用 “離線IP庫 + 自訂函數(UDF)” 的方案。該方案分為三大模組：

資料準備：將公開的IP地址庫檔案上傳到MaxCompute表中，作為我們的地址標準。
UDF開發：建立一個UDF函數，用於將字串IP轉換為整數，這是高效進行範圍匹配的關鍵。
SQL分析：編寫SQL，調用UDF函數將IP與IP庫表進行對比，擷取IP地址的最終歸屬地。

準備工作

建立未使用新版資料開發（Data Studio）的工作空間。本例使用DataWorks簡單模式的工作空間。
為工作空間綁定Serverless資源群組。
綁定MaxCompute計算資源並綁定至工作空間，並完成測試連通性。
在目標工作空間建立商務程序。

操作步驟

下載IP地址庫資料

地址庫資料樣本資料：ipdata.csv。

如果您需要使用自己的IP地址，請自行下載IP地址庫。

樣本資料結構說明：

資料格式為UTF-8。
前四個資料是IP地址的起始地址與結束位址。前兩個是十進位整數形式，後兩個是點分形式。IP位址區段為整數形式，以便計算IP是否屬於這個網段。

上傳IP地址庫資料至表

建立MaxCompute表。

進入資料開發頁面。
登入DataWorks控制台，切換至目標地區後，單擊左側導覽列的資料開發與營運 > 資料開發，在下拉框中選擇對應工作空間後單擊進入資料開發。
在DataStudio（資料開發）頁面的資料開發頁簽下，按右鍵目標商務程序，選擇建立節點 > MaxCompute > ODPS SQL。
在建立節點對話方塊中，引擎執行個體選擇準備工作中綁定的計算資源，輸入節點名稱，單擊確認。

在ODPS SQL節點編輯頁面，輸入如下語句。

-- 如果已存在名為 ipresource 的表，則先刪除
DROP TABLE IF EXISTS ipresource;

-- 建立用於儲存IP地址庫資訊的表
CREATE TABLE IF NOT EXISTS ipresource 
(
    start_ip     BIGINT,   -- IP段的起始地址（十進位整數形式）
    end_ip       BIGINT,   -- IP段的結束位址（十進位整數形式）
    start_ip_arg STRING,   -- IP段的起始地址（點分十進位字串形式，如 "1.0.1.x"）
    end_ip_arg   STRING,   -- IP段的結束位址（點分十進位字串形式，如 "1.0.3.x"）
    country      STRING,   -- 國家名稱
    area         STRING,   -- 地區名稱（通常為空白或與國家重複）
    city         STRING,   -- 城市名稱（部分資料可能為省份）
    county       STRING,   -- 區/縣名稱
    isp          STRING    -- 網路電訊廠商（如電信、聯通、移動）
);

單擊表徵圖運行代碼。在參數彈窗中選擇您所綁定的Serverless資源群組，單擊運行。等待費用預估完成，單擊運行節點任務。

上傳資料至表。

單擊左上方表徵圖，在彈出頁面中單擊全部產品 > Data Integration > 上傳與下載，進入上傳與下載頁面。
單擊左側導覽列的表徵圖，進入資料上傳頁面。

單擊資料上傳按鈕，進入資料上傳配置頁面，可參考以下配置。

參數		描述
指定待上傳資料	選擇檔案	上傳已下載至本地的`ipdata.csv`檔案。
設定目標表	目標引擎	MaxCompute
	MaxCompute專案名稱	選擇準備工作中的MaxCompute專案。
	選擇目標表	選擇ipresource表作為目標表。重要若選不到新建立的表，請在資料地圖 > 我的資料 > 重新整理表中繼資料中，手動重新整理中繼資料odps.<project_name>.ipresource。
資源群組		選擇準備工作中的Serverless資源群組。
上傳檔案資料預覽		單擊按順序映射，完成檔案資料與ipresource表欄位對應。

單擊資料上傳，等待資料上傳完成。

驗證資料是否上傳成功。
回到DataStudio（資料開發）頁面的資料開發頁簽的ODPS SQL節點編輯頁面，編寫並選中運行以下SQL命令。如果能看到資料條數和樣本資料，則說明上傳成功。
```
--查詢表中資料條數。
SELECT COUNT(*) FROM ipresource;

--預覽前10條資料
SELECT * FROM ipresource limit 10;
```

開發UDF函數

為了能用SQL比較IP地址，我們需要一個UDF函數，將點分十進位的IP字串地址（如 '1.0.2xx.4x'）轉換成一個可比較大小的十進位整數。

建立MaxCompute Python資源。
1. 在資料開發頁面，按右鍵目標商務程序，選擇建立資源 > MaxCompute > Python。
2. 在建立資源對話方塊中，填寫資源名稱（樣本名：mc.py），並勾選上傳為ODPS資源，單擊建立。
3. 在Python資源中輸入如下代碼。
```
from functools import reduce
from odps.udf import annotate

@annotate("string->bigint")
class IPtoInt(object):
    def evaluate(self, ip):
        try:
            return reduce(lambda x, y: (x << 8) + y, map(int, ip.split('.')))
        except:
            return 0
```
4. 單擊表徵圖，提交資源。

建立MaxCompute UDF函數。

在資料開發頁面，按右鍵目標商務程序，選擇建立函數 > MaxCompute > 函數。
在建立函數對話方塊中，輸入函數名稱（樣本名：ip2int），單擊建立。此為函數調用時的函數名，填寫之後不可修改。
如果資料開發中綁定了多個MaxCompute引擎，則選擇本次需要建立函數的MaxCompute引擎執行個體。

在函數的編輯頁面，配置各項參數。關鍵參數如下：

更多參數說明，請參見註冊MaxCompute函數。

參數	描述
函數類型	保持預設設定其他函數。
函數名	UDF函數名，即SQL中引用該函數所使用的名稱。需要全域唯一，且註冊函數後不支援修改。
類名	實現UDF的主類名，必填。樣本值為`mc.IPtoInt`。說明當資源類型為Python時，類名格式為Python資源名稱.類名（資源名稱中的.py無需填寫）。
資源清單	選擇上一步驟的資源檔`mc.py`。

單擊工具列中的表徵圖，儲存函數。
單擊工具列中的表徵圖，在提交新版本對話方塊中，輸入變更描述，單擊確認。

SQL分析IP來源

回到ODPS SQL節點編輯頁面，編寫並選中運行以下SQL命令。

-- 需要替換成您所需查詢的真實IP地址
SELECT * FROM ipresource
WHERE ip2int('1.0.2xx.4x') >= start_ip
AND ip2int('1.0.2xx.4x') <= end_ip;

單擊表徵圖運行代碼。在參數彈窗中選擇您所綁定的Serverless資源群組，單擊運行。等待費用預估完成，單擊運行節點任務。
查看運行結果。最終，您可得到該IP所屬於的地址。

DataWorks：基於MaxCompute UDF分析IP來源