DataWorks:Find IP geolocation by using MaxCompute UDFs

Create the table

1. Log on to the DataWorks console. In the top navigation bar, select the region. In the left-side navigation pane, choose Data Development and O&M > Data Development. Select the target workspace from the drop-down list and click Go to Data Development.

2. On the DataStudio page, right-click the target business flow and choose Create Node > MaxCompute > ODPS SQL.

3. In the dialog box, select the computing resource for Engine Instance, enter a name, and click Confirm.

4. In the node editor, run the following SQL to create the table:

   -- Drop the table if it already exists.
   DROP TABLE IF EXISTS ipresource;
   -- Create a table to store the IP database.
   CREATE TABLE IF NOT EXISTS ipresource
   (
       start_ip     BIGINT,   -- Start of the IP range (decimal integer).
       end_ip       BIGINT,   -- End of the IP range (decimal integer).
       start_ip_arg STRING,   -- Start of the IP range (dotted-decimal, e.g. "1.0.1.x").
       end_ip_arg   STRING,   -- End of the IP range (dotted-decimal, e.g. "1.0.3.x").
       country      STRING,   -- Country name.
       area         STRING,   -- Area name (usually empty or same as the country).
       city         STRING,   -- City name (may be a province for some data).
       county       STRING,   -- District or county name.
       isp          STRING    -- Internet service provider (e.g., China Telecom, China Unicom, China Mobile).
   );

5. Click the icon to run the code. In the Parameters dialog box, select the serverless resource group and click Run. Wait for Estimate MaxCompute Computing Cost to complete, then click Run.

Upload data to the table

The sample IP database file is ipdata.csv (UTF-8 encoded). The first four fields are the start and end addresses of each IP range: two in decimal integer format and two in dotted-decimal format.

1. Click the icon in the upper-left corner. On the page that appears, click All Products > Data Integration > Upload and Download.

2. Click the icon in the left navigation pane.

3. Click Upload Data and configure the following parameters:

   Important

   If the ipresource table does not appear in the list, go to Data Map > My Data > Refresh Table Metadata and manually refresh the metadata for odps.<project_name>.ipresource.

   Parameter	Value
   Specify Data to Be Uploaded > Select File	Upload the ipdata.csv file
   Configure Destination Table > Compute Engine	MaxCompute
   MaxComputeProject Name	Select the target MaxCompute project
   Destination Table	Select the ipresource table
   Resource Group	Select the serverless resource group
   Preview Data of Uploaded File	Click Mapping by Order to map the CSV columns to the table fields

4. Click Upload Data.

Verify the upload

Return to the ODPS SQL node editor on the DataStudio page and run the following queries:

-- Check the total number of rows loaded.
SELECT COUNT(*) FROM ipresource;
-- Preview the first 10 rows.
SELECT * FROM ipresource LIMIT 10;

Create the Python resource

1. On the DataStudio page, right-click the target business flow and choose Create Resource > MaxCompute > Python.

2. In the dialog box, enter a name (for example, mc.py). Select Upload to MaxCompute and click Create.

3. In the node editor, enter the following code:

   from functools import reduce
   from odps.udf import annotate
   
   # Declares the UDF signature: takes a STRING, returns a BIGINT.
   @annotate("string->bigint")
   class IPtoInt(object):
       def evaluate(self, ip):
           try:
               # Converts each octet to an integer and combines them using bit-shifting.
               # Example: '203.0.113.5' -> (203 << 24) | (0 << 16) | (113 << 8) | 5
               return reduce(lambda x, y: (x << 8) + y, map(int, ip.split('.')))
           except:
               return 0

4. Click the icon to save and upload the resource.

Register the UDF

1. On the DataStudio page, right-click the target business flow and choose Create Function > MaxCompute > Function.

2. In the dialog box, enter a name (for example, ip2int) and click Create. This name is the UDF name used in SQL and cannot be changed after creation.

3. On the editor page, configure the following parameters. For a full parameter reference, see Register a MaxCompute function.

   Parameter	Value
   Function Type	Other Function (default)
   Class Name	mc.IPtoInt — format is <resource_name_without_.py>.<ClassName>
   Resources	Select mc.py from the previous step

4. Click the icon to save the function.

5. Click the icon. In the dialog box, enter a change description and click Confirm.