DataWorks:Analyze IP geolocation based on a MaxCompute UDF

Download the IP address database

Download the sample IP address database, ipdata.csv, to your local device. To analyze your own IP addresses, download an IP address database yourself.

The sample data has the following structure:

• The data format is UTF-8.

• The first four values are the start address and the end address of an IP address range. The first two are decimal integers, and the last two are in dotted-decimal notation. The IP address range is provided in integer format so that you can calculate whether an IP address belongs to the network segment.

Upload the IP address database to a table

1. Create a MaxCompute table.

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

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

   3. In the Create Node dialog box, set Engine Instance to the compute resource that you bound in the Prerequisites section, enter a node Name, and then click OK.

   4. In the ODPS SQL node editor, enter the following statements:

      -- If a table named ipresource already exists, drop it first.
      DROP TABLE IF EXISTS ipresource;
      
      -- Create a table to store the IP address database information.
      CREATE TABLE IF NOT EXISTS ipresource 
      (
          start_ip     BIGINT,   -- Start address of the IP range (decimal integer)
          end_ip       BIGINT,   -- End address of the IP range (decimal integer)
          start_ip_arg STRING,   -- Start address of the IP range (dotted-decimal string, such as "203.0.113.0")
          end_ip_arg   STRING,   -- End address of the IP range (dotted-decimal string, such as "203.0.113.255")
          country      STRING,   -- Country name
          area         STRING,   -- Area name (usually empty or the same as the country)
          city         STRING,   -- City name (may be a larger administrative division in some records)
          county       STRING,   -- District or county name
          isp          STRING    -- Internet service provider
      );

   5. Click the icon to run the code. In the Parameter dialog box, select the serverless resource group that you bound and click Running. After the Estimate MaxCompute Computing Cost step is complete, click Running to run the node task.

2. Upload the data to the table.

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

   2. In the left-side navigation pane, click the icon to go to the Upload Data page.

   3. Click Upload Data to go to the data upload configuration page. Use the following configuration as a reference.

      Parameter	Description
      Specify Data to Be Uploaded > Select file	Upload the ipdata.csv file that you downloaded to your local device.
      Configure Destination Table > Compute Engine	Select MaxCompute.
      Configure Destination Table > MaxCompute Project Name	Select the MaxCompute project from the Prerequisites section.
      Configure Destination Table > Select Destination Table	Select the ipresource table as the destination table. If the newly created table is not available for selection, choose Data Map > My Data > Refresh Table metadata and manually refresh the metadata of odps.<project_name>.ipresource.
      Configure Destination Table > Resource Group	Select the serverless resource group from the Prerequisites section.
      Configure Destination Table > Preview Data of Uploaded File	Click Mapping by Order to map the file data to the fields of the ipresource table.

   4. Click Upload Data and wait for the data upload to complete.

3. Verify that the data is uploaded.

   Return to the ODPS SQL node editor on the Data Development page. Write the following SQL commands, select them, and run them.

   --Query the number of rows in the table.
   SELECT COUNT(*) FROM ipresource;
   
   --Preview the first 10 rows of data.
   SELECT * FROM ipresource limit 10;

   start_ip and end_ip contain decimal integers, and start_ip_arg and end_ip_arg contain dotted-decimal addresses. If the values are misaligned or the table is empty, upload the file again and map the columns to the table fields again.

Develop the UDF

To compare IP addresses in SQL, you need a UDF that converts a dotted-decimal IP address string, such as 203.0.113.10, into a decimal integer that can be compared by magnitude.

1. Create a MaxCompute Python resource.

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

   2. In the Create Resource dialog box, enter a Name for the resource (for example, mc.py), select Upload as ODPS Resource, and then click Create.

   3. In the Python resource, enter the following code:

      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

      icon to submit the resource.

2. Create a MaxCompute UDF.

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

   2. In the Create Function dialog box, enter a Name for the function (for example, ip2int). If multiple MaxCompute engines are bound in Data Development, select the MaxCompute Engine Instance in which you want to create this function. Then click Create. This is the function name that you use to call the function, and it cannot be modified after you specify it.

   3. On the function editor page, configure the parameters. The following table describes the key parameters. For more information about the parameters, see Register a MaxCompute function.

      Parameter	Description
      Function Type	Keep the default value Other Functions.
      Function Name	The name of the UDF, which is the name used to reference the function in SQL. The name must be globally unique.
      Class Name	The name of the main class that implements the UDF. This parameter is required. Example value: mc.IPtoInt. Note For a Python resource, join the Python resource name and the class name with a period, and omit the .py extension of the resource name.
      Resources	Select the mc.py resource file from the previous step.

3. Click the icon in the toolbar to save the function.

4. Click the icon in the toolbar. In the dialog box for submitting a new version, enter a Change Description, and then click OK.

Analyze IP geolocation by using SQL

The UDF converts the IP address that you query into an integer, and the SQL statement returns the IP address range in the ipresource table that contains that integer.

1. Return to the ODPS SQL node editor. Write the following SQL command, select it, and run it.

   -- Replace 203.0.113.10 with the IP address that you want to query.
   -- The address must be in dotted-decimal notation. The UDF returns 0 for a value that it cannot parse, and the query then returns no rows.
   SELECT * FROM ipresource
   WHERE ip2int('203.0.113.10') >= start_ip
   AND ip2int('203.0.113.10') <= end_ip;

2. Click the icon. In the Parameter dialog box, select the serverless resource group that you bound, click Running, and click Running again after the Estimate MaxCompute Computing Cost step is complete.

3. View the result. You obtain the geolocation of the queried IP address.