基于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工作空间列表页，在顶部切换至目标地域，找到已创建的工作空间，单击操作列的快速进入 > Data Studio，进入Data Studio。
在Data Studio（数据开发）页面左侧导航栏单击按钮，进入数据开发页面的项目目录。
单击项目目录右侧的，选择新建节点 > MaxCompute > MaxCompute SQL。
在新建节点对话框中输入节点名称，并单击确认。

在MaxCompute 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    -- 网络运营商（如 电信、联通、移动）
);

在节点编辑上方选择数据源处，选择您准备工作中所绑定的MaxCompute计算资源。
单击运行后，查看运行结果。

上传数据至表。

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

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

参数		描述
指定待上传数据	选择文件	上传已下载至本地的`ipdata.csv`文件。
设置目标表	目标引擎	MaxCompute
	MaxCompute项目名称	选择准备工作中的MaxCompute项目。
	选择目标表	选择ipresource表作为目标表。重要若选不到新创建的表，请在数据地图 > 我的数据 > 刷新表元数据中，手动刷新元数据odps.<project_name>.ipresource。
资源组		选择准备工作中的Serverless资源组。
上传文件数据预览		单击按顺序映射，完成文件数据与ipresource表字段映射。

单击数据上传，等待数据上传完成。

验证数据是否上传成功。
回到Data Studio（数据开发）页面的MaxCompute SQL节点编辑页，编写并选中运行以下SQL命令。如果能看到数据条数和样本数据，则说明上传成功。
```
--查询表中数据条数。
SELECT COUNT(*) FROM ipresource;

--预览前10条数据
SELECT * FROM ipresource limit 10;
```

开发UDF函数

为了能用SQL比较IP地址，我们需要一个UDF函数，将点分十进制的IP字符串（如 '1.0.2xx.4x'）转换成一个可比较大小的整数。

在Data Studio页面的左侧导航栏单击资源管理按钮，进入资源管理页面。
新建MaxCompute Python资源。
1. 在资源管理页面，单击按钮，选择新建资源 > MaxCompute Python。
2. 在新建资源和函数对话框中，输入资源名称（示例名：mc.py），单击确认。
3. 上传本地文件mc.py至文件内容，并选择数据源为准备工作中已绑定的计算资源。
4. 在上方工具栏对资源保存并发布。

新建MaxCompute UDF函数。

在资源管理页面，单击按钮，选择新建函数 > MaxCompute Function。
在新建资源和函数对话框中，输入函数名称（示例名：ip2int），单击确认。此为函数调用时的函数名，填写之后不可修改。

在函数的编辑页面，配置各项参数。关键参数如下：

更多参数说明，请参见MaxCompute资源与函数。

参数	描述
函数类型	保持默认选项OTHER。
数据源	选择准备工作中已绑定的计算资源。
类名	实现UDF的主类名，必填。示例值为`mc.IPtoInt`。
资源列表	选择上一步骤的资源文件`mc.py`。

在上方工具栏对函数保存并发布。

SQL分析IP来源

回到Data Studio（数据开发）页面的MaxCompute SQL节点编辑页，编写并选中运行以下SQL命令。

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

在编辑上面选择数据源处，选择您准备工作中所绑定的MaxCompute计算资源。
单击运行后，查看运行结果。最终，您可得到该IP所属于的地址。

基于MaxCompute UDF分析IP来源