DataWorks:Find IP geolocation by using MaxCompute UDFs

Create a MaxCompute table

1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a region. Find your workspace and choose Shortcuts > Data Studio in the Actions column.

2. On the Data Studio page, click the icon in the navigation pane to open Workspace Directories.

3. Click the icon and select Create Node > MaxCompute > MaxCompute SQL.

4. Enter a name and click OK.

5. In the node editor, enter the following SQL:

   -- Drop the table if it already exists.
   DROP TABLE IF EXISTS ipresource;
   -- Create a table to store the IP address database.
   CREATE TABLE IF NOT EXISTS ipresource
   (
       start_ip     BIGINT,   -- IP range start (decimal integer)
       end_ip       BIGINT,   -- IP range end (decimal integer)
       start_ip_arg STRING,   -- IP range start (dotted-decimal notation, e.g., "1.0.1.x")
       end_ip_arg   STRING,   -- IP range end (dotted-decimal notation, 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 rows)
       county       STRING,   -- District or county name
       isp          STRING    -- Internet Service Provider (ISP), e.g., China Telecom, China Unicom, China Mobile
   );

6. In the Select DataSource dropdown, select the MaxCompute computing resource you bound.

7. Click Running Duration and verify that the table is created.

Upload data to the table

1. Click the icon in the upper-left corner. In 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:

   If the ipresource table does not appear in the Destination Table 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
   MaxCompute Project Name	Select the target MaxCompute project
   Destination Table	Select the ipresource table
   Resource Group	Select the serverless resource group you prepared
   Preview Data of Uploaded File	Click Mapping by Order to map the CSV columns to the ipresource table fields

4. Click Upload Data.

Verify the upload

Return to the MaxCompute SQL node editor and run the following queries. If row counts and sample rows appear, the upload was successful.

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

Create a Python resource

1. On the Data Studio page, click the Resource Management icon .

2. Click the icon and select Create Resource > MaxCompute Python.

3. In the dialog box, enter a name such as mc.py and click OK.

4. Upload the mc.py file to File Content and set Data Source to the computing resource you bound.

5. In the toolbar, click Save and then Publish.

Create a MaxCompute function

1. Click the icon and select Create Function > MaxCompute Function.

2. In the dialog box, enter a name such as ip2int and click OK. This name is the UDF name used in SQL and cannot be changed after creation.

3. Configure the following parameters. For a full parameter reference, see MaxCompute resources and functions.

   Parameter	Value
   Function Type	Keep the default: OTHER
   Data Source	Select the computing resource bound to the workspace
   Class Name	mc.IPtoInt
   Resources	Select the mc.py resource from the previous step

4. In the toolbar, click Save and then Publish.