基于MaxCompute UDF进行SQL分析_大数据开发治理平台 DataWorks(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资源组，单击运行。等待费用预估完成，单击运行节点任务。

上传数据至表。

单击左上角图标，在弹出页面中单击全部产品 > 数据集成 > 上传与下载，进入上传与下载页面。
单击左侧导航栏的图标，进入数据上传页面。

单击数据上传按钮，进入数据上传配置页面，可参考以下配置。

参数		描述
指定待上传数据	选择文件	上传已下载至本地的`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所属于的地址。

基于MaxCompute UDF分析IP来源