DataWorks:Process data

Upload the ip2region.jar resource

1. Download the ip2region.jar package to your local machine.

2. On the Data Studio page, click the icon in the left-side navigation pane. In the RESOURCE MANAGEMENT pane, click the icon and choose Create Resource > MaxCompute Jar. In the Create Resource or Function dialog box, set the Name parameter and click OK.

   The resource name does not need to match the JAR file name.

3. Set File Source to On-premises, click Upload next to File Content, and select the ip2region.jar file you downloaded.

4. From the Data Source drop-down list, select the MaxCompute computing resource associated with your workspace. This is the resource you configured when preparing environments.

5. Click Save and then Deploy in the top toolbar to deploy the resource to the MaxCompute project in both development and production environments.

Register the getregion UDF

1. In the RESOURCE MANAGEMENT pane, click the icon and choose Create Function > MaxCompute Function. In the Create Resource or Function dialog box, set the Name parameter to getregion.

2. On the configuration tab, set the following parameters. Keep the defaults for all other parameters.

   Parameter	Value
   Function Type	OTHER
   Data Source	The MaxCompute computing resource associated with your workspace
   Class Name	org.alidata.odps.udf.Ip2Region
   Resource List	ip2region.jar
   Description	Conversion of an IP address into a region
   Command Syntax	getregion('ip')
   Parameter Description	IP address

3. Click Save and then Deploy to deploy the UDF to the MaxCompute project in both environments.

Configure the dwd_log_info_di_odps node

This node uses getregion to map each log entry's IP address to a region, and uses regular expressions to parse the HTTP request field into separate method, URL, and protocol fields. The output goes to dwd_log_info_di_odps.

1. In the Workspace Directories section, find your workflow and click the workflow name to open the canvas.

2. In the canvas, move the pointer over the dwd_log_info_di_odps node and click Open Node.

3. Copy the following SQL and paste it into the code editor.

   -- Create the dwd_log_info_di_odps table.
   CREATE TABLE IF NOT EXISTS dwd_log_info_di_odps (
    ip STRING COMMENT 'The IP address',
    uid STRING COMMENT 'The user ID',
    time STRING COMMENT 'The time in the yyyymmddhh:mi:ss format',
    status STRING COMMENT 'The status code that is returned by the server',
    bytes STRING COMMENT 'The number of bytes that are returned to the client',
    region STRING COMMENT 'The region, which is obtained based on the IP address',
    method STRING COMMENT 'The HTTP request type',
    url STRING COMMENT 'The URL',
    protocol STRING COMMENT 'The version number of HTTP',
    referer STRING COMMENT 'The source URL',
    device STRING COMMENT 'The terminal type',
    identity STRING COMMENT 'The access type, which can be crawler, feed, user, or unknown'
   )
   PARTITIONED BY (
    dt STRING
   )
   LIFECYCLE 14;
   
   -- Split raw log entries from ods_raw_log_d_odps into structured fields.
   -- Each raw log entry is a single delimited string (##@@ separator).
   -- getregion(ip): calls the registered UDF to map IP address to geographic region.
   -- regexp_substr / regexp_extract: parses the HTTP request field into method, URL, and protocol.
   -- CASE WHEN on agent: classifies device type (android, iphone, etc.) and access identity
   --   (crawler, feed, user, or unknown) based on the user-agent string.
   -- ${bizdate}: scheduling variable replaced by the data timestamp at runtime (e.g., 20250223).
   INSERT OVERWRITE TABLE dwd_log_info_di_odps PARTITION (dt='${bizdate}')
   SELECT ip
     , uid
     , time
     , status
     , bytes
     , getregion(ip) AS region -- Map IP address to region using the registered UDF.
     , regexp_substr(request, '(^[^ ]+ )') AS method -- Extract HTTP method from request field.
     , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url -- Extract URL from request field.
     , regexp_substr(request, '([^ ]+$)') AS protocol -- Extract HTTP version from request field.
     , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer -- Normalize referer to domain only.
     , CASE
       WHEN TOLOWER(agent) RLIKE 'android' THEN 'android'
       WHEN TOLOWER(agent) RLIKE 'iphone' THEN 'iphone'
       WHEN TOLOWER(agent) RLIKE 'ipad' THEN 'ipad'
       WHEN TOLOWER(agent) RLIKE 'macintosh' THEN 'macintosh'
       WHEN TOLOWER(agent) RLIKE 'windows phone' THEN 'windows_phone'
       WHEN TOLOWER(agent) RLIKE 'windows' THEN 'windows_pc'
       ELSE 'unknown'
     END AS device
     , CASE
       WHEN TOLOWER(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler'
       WHEN TOLOWER(agent) RLIKE 'feed'
       OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') RLIKE 'feed' THEN 'feed'
       WHEN TOLOWER(agent) NOT RLIKE '(bot|spider|crawler|feed|slurp)'
       AND agent RLIKE '^[Mozilla|Opera]'
       AND regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') NOT RLIKE 'feed' THEN 'user'
       ELSE 'unknown'
     END AS identity
     FROM (
       SELECT SPLIT(col, '##@@')[0] AS ip
       , SPLIT(col, '##@@')[1] AS uid
       , SPLIT(col, '##@@')[2] AS time
       , SPLIT(col, '##@@')[3] AS request
       , SPLIT(col, '##@@')[4] AS status
       , SPLIT(col, '##@@')[5] AS bytes
       , SPLIT(col, '##@@')[6] AS referer
       , SPLIT(col, '##@@')[7] AS agent
     FROM ods_raw_log_d_odps
     WHERE dt ='${bizdate}'
   ) a;

4. In the right-side navigation pane, click Debugging Configurations and set the following parameters. These are used when you run the workflow in Step 4.

   Parameter	Value
   Computing Resource	The MaxCompute computing resource associated with your workspace
   Resource Group	The serverless resource group you purchased when preparing environments
   Script Parameters	Leave blank. The ${bizdate} variable is replaced with a constant you specify when running the workflow.

5. (Optional) Click Properties to review scheduling settings. In this tutorial, scheduling parameters are configured at the workflow level, so you do not need to configure them on individual nodes. For details, see Scheduling properties.

6. Click Save in the top toolbar.

Configure the dws_user_info_all_di_odps node

This node left-joins the processed log data (dwd_log_info_di_odps) with the user records table (ods_user_info_d_odps) on uid, merging behavioral data with demographic data in dws_user_info_all_di_odps.

1. In the canvas, move the pointer over the dws_user_info_all_di_odps node and click Open Node.

2. Copy the following SQL and paste it into the code editor.

   -- Create the dws_user_info_all_di_odps table.
   CREATE TABLE IF NOT EXISTS dws_user_info_all_di_odps (
    uid STRING COMMENT 'The user ID',
    gender STRING COMMENT 'The gender',
    age_range STRING COMMENT 'The age range',
    zodiac STRING COMMENT 'The zodiac sign',
    region STRING COMMENT 'The region, which is obtained based on the IP address',
    device STRING COMMENT 'The terminal type',
    identity STRING COMMENT 'The access type, which can be crawler, feed, user, or unknown',
    method STRING COMMENT 'The HTTP request type',
    url STRING COMMENT 'The URL',
    referer STRING COMMENT 'The source URL',
    time STRING COMMENT 'The time in the yyyymmddhh:mi:ss format'
   )
   PARTITIONED BY (
    dt STRING
   )
   LIFECYCLE 14;
   
   -- Join processed log data (dwd_log_info_di_odps) with user records (ods_user_info_d_odps) on uid.
   -- LEFT OUTER JOIN: retains all log rows even if no matching user record exists.
   -- COALESCE(a.uid, b.uid): handles cases where uid appears in one table but not the other.
   -- ${bizdate}: scheduling variable replaced by the data timestamp at runtime.
   INSERT OVERWRITE TABLE dws_user_info_all_di_odps  PARTITION (dt='${bizdate}')
   SELECT COALESCE(a.uid, b.uid) AS uid
     , b.gender
     , b.age_range
     , b.zodiac
     , a.region
     , a.device
     , a.identity
     , a.method
     , a.url
     , a.referer
     , a.time
   FROM (
     SELECT *
     FROM dwd_log_info_di_odps
     WHERE dt = '${bizdate}'
   ) a
   LEFT OUTER JOIN (
     SELECT *
     FROM ods_user_info_d_odps
     WHERE dt = '${bizdate}'
   ) b
   ON a.uid = b.uid;

3. Configure the same Debugging Configurations parameters as in the previous node.

4. Click Save.

Configure the ads_user_info_1d_odps node

This node groups the joined data by uid and applies aggregation functions to produce one profile row per user, stored in ads_user_info_1d_odps.

1. In the canvas, move the pointer over the ads_user_info_1d_odps node and click Open Node.

2. Copy the following SQL and paste it into the code editor.

   -- Create the ads_user_info_1d_odps table.
   CREATE TABLE IF NOT EXISTS ads_user_info_1d_odps (
    uid STRING COMMENT 'The user ID',
    region STRING COMMENT 'The region, which is obtained based on the IP address',
    device STRING COMMENT 'The terminal type',
    pv BIGINT COMMENT 'The pv',
    gender STRING COMMENT 'The gender',
    age_range STRING COMMENT 'The age range',
    zodiac STRING COMMENT 'The zodiac sign'
   )
   PARTITIONED BY (
    dt STRING
   )
   LIFECYCLE 14;
   
   -- Aggregate the wide table (dws_user_info_all_di_odps) by uid to produce one profile row per user.
   -- COUNT(0) AS pv: counts the total number of page views per user.
   -- MAX(): selects one value per user for region, device, gender, age_range, and zodiac.
   -- ${bizdate}: scheduling variable replaced by the data timestamp at runtime.
   INSERT OVERWRITE TABLE ads_user_info_1d_odps  PARTITION (dt='${bizdate}')
   SELECT uid
     , MAX(region)
     , MAX(device)
     , COUNT(0) AS pv
     , MAX(gender)
     , MAX(age_range)
     , MAX(zodiac)
   FROM dws_user_info_all_di_odps
   WHERE dt = '${bizdate}'
   GROUP BY uid;

3. Configure the same Debugging Configurations parameters as in the previous nodes.

4. Click Save.