DataWorks:Process data

Create dwd_log_info_di_odps

Double-click the dwd_log_info_di_odps table and click DDL in its editor, then enter this CREATE TABLE statement.

CREATE TABLE IF NOT EXISTS dwd_log_info_di_odps (
 ip STRING COMMENT 'IP address',
 uid STRING COMMENT 'User ID',
 time STRING COMMENT 'Time yyyymmddhh:mi:ss',
 status STRING COMMENT 'Server response status code',
 bytes STRING COMMENT 'Bytes returned to client',
 region STRING COMMENT 'Region derived from IP',
 method STRING COMMENT 'HTTP request method',
 url STRING COMMENT 'url',
 protocol STRING COMMENT 'HTTP protocol version',
 referer STRING COMMENT 'Referrer URL',
 device STRING COMMENT 'Device type ',
 identity STRING COMMENT 'Visit type: crawler, feed, user, or unknown'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

Create dws_user_info_all_di_odps

Double-click dws_user_info_all_di_odps and click DDL in its editor, then enter this CREATE TABLE statement.

CREATE TABLE IF NOT EXISTS dws_user_info_all_di_odps (
 uid STRING COMMENT 'User ID',
 gender STRING COMMENT 'Gender',
 age_range STRING COMMENT 'Age range',
 zodiac STRING COMMENT 'Zodiac sign',
 region STRING COMMENT 'Region derived from IP',
 device STRING COMMENT 'Device type ',
 identity STRING COMMENT 'Visit type: crawler, feed, user, or unknown',
 method STRING COMMENT 'HTTP request method',
 url STRING COMMENT 'url',
 referer STRING COMMENT 'Referrer URL',
 time STRING COMMENT 'Time yyyymmddhh:mi:ss'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

Create ads_user_info_1d_odps

Double-click ads_user_info_1d_odps and click DDL in its editor, then enter this CREATE TABLE statement.

CREATE TABLE IF NOT EXISTS ads_user_info_1d_odps (
 uid STRING COMMENT 'User ID',
 region STRING COMMENT 'Region derived from IP',
 device STRING COMMENT 'Device type ',
 pv BIGINT COMMENT 'pv',
 gender STRING COMMENT 'Gender',
 age_range STRING COMMENT 'Age range',
 zodiac STRING COMMENT 'Zodiac sign'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;    

Configure the dwd_log_info_di_odps node

In the business process canvas, double-click dwd_log_info_di_odps. Write SQL that uses the new function to process the upstream table ods_raw_log_d_odps field and writes results to the dwd_log_info_di_odps table. Example processing:Appendix: Processing examples.

1. Edit code.

   -- Scenario: Use getregion and regular expressions to parse raw log IP addresses and fields, then write analyzable fields to dwd_log_info_di_odps.
   --      This example provides getregion to map IP addresses to regions.
   -- Additional information:
   --     1. Before using a function in a DataWorks node, upload its resource and register the function in the visual interface.
   --        The getregion resource in this example is ip2region.jar.
   --     2. DataWorks scheduling parameters let you write daily incremental data to the matching business partition.
   --        In development, define code variables as ${variable_name} and assign scheduling parameters to them on the scheduling page to pass dynamic values to code.
   INSERT OVERWRITE TABLE dwd_log_info_di_odps PARTITION (dt='${bizdate}')
   SELECT ip 
     , uid
     , time
     , status
     , bytes 
     , getregion(ip) AS region --Use a custom UDF to derive the region from IP.
     , regexp_substr(request, '(^[^ ]+ )') AS method --Split request into three fields with a regular expression.
     , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
     , regexp_substr(request, '([^ ]+$)') AS protocol 
     , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer --Parse referer with a regular expression to obtain a more precise URL.
     , CASE
       WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' --Derive device and visit type from agent.
       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;

2. Configure scheduling properties.

   On the configuration page, click Schedule on the right to open the Scheduling Settings panel for scheduling and node settings. For details, see Node scheduling configuration. Configure:

   Setting	Value	Illustration
   Scheduling Parameter	In the Scheduling ParameterParameter settings: Parameter name:bizdate. Parameter Value:$[yyyymmdd-1].	The scheduling parameter panel shows the parameter bizdate with value $[yyyymmdd-1], sourced from code parsing. At the bottom is the Scheduling Parameter Preview button.
   Schedule	Scheduling period: Day. Scheduling Time: 00:30. RUN Attribute: Allows the task to be rerun regardless of its previous success or failure. Use the default values for other parameters. Note The node runs only after 00:30 every day.	The time properties panel shows instance generation as T+1, generated the following day, scheduling type as Normal Scheduling, cycle as Day, scheduled time as 00:30, rerun property as Rerun after success or failure, and effective date as Always Effective.
   Resource Group	Select the Prepare the environment Serverless resource group created earlier.	The resource properties panel shows the scheduling resource group as User_portraits (use the name of the Serverless resource group you created).
   Dependencies	Through automatic code parsing, DataWorks sets the node that produces data for table ods_raw_log_d_odps (ods_raw_log_d_odps) as an upstream dependency of the current node dwd_log_info_di_odps. It sets table dwd_log_info_di_odps as this node’s output, so downstream nodes that query the table automatically depend on this node.	In the scheduling dependencies panel, automatic parsing before commit is set to Yes. Upstream nodes are Parse Inputs and Outputs from Code and are configured as ods_raw_log_d_odps. Node outputs include system default, manually added, and parsed entries.

   Note

   Upstream dependencies: Click Parse Code Inputs and Outputs to find the upstream node whose output is MaxCompute production project name.table name.Output Name of Ancestor Node.

   Node output name: Click Parse Code Inputs and Outputs: Click to parse table names in the code and generate this node’s output name in the MaxCompute production project name.table name format for downstream nodes.

3. Configure any other required settings. Then click in the node editor toolbar to save.

Configure the dws_user_info_all_di_odps node

In the business process canvas, double-click dws_user_info_all_di_odps. Write SQL to join the upstream tables dwd_log_info_di_odps and ods_user_info_d_odps and write to the dws_user_info_all_di_odps table.

1. Edit code.

   -- Scenario: Combine processed logs in dwd_log_info_di_odps with user data in ods_user_info_d_odps and write to dws_user_info_all_di_odps.
   -- Additional information:DataWorks scheduling parameters let you write daily incremental data to the matching business partition.
   --      In development, define code variables as ${variable_name} and assign scheduling parameters to them on the scheduling page to pass dynamic values to code.
   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;

2. Configure scheduling properties.

   On the configuration page, click Schedule on the right to open the Scheduling Settings panel for scheduling and node settings. For details, see Node scheduling configuration. Configure:

   Setting	Value	Illustration
   Scheduling Parameter	In the Scheduling ParameterParameter settings: Parameter name:bizdate Parameter Value:$[yyyymmdd-1]	The scheduling parameter panel shows the parameter bizdate with value $[yyyymmdd-1], sourced from code parsing. At the bottom is the Scheduling Parameter Preview button.
   Schedule	Scheduling period: Day. Scheduling Time: 00:30. RUN Attribute: Allows the task to be rerun regardless of its previous success or failure. Use the default values for other parameters. Note The node runs only after 00:30 every day.	The time properties panel shows instance generation as T+1, generated the following day, scheduling type as Normal Scheduling, cycle as Day, scheduled time as 00:30, rerun property as Rerun after success or failure, and effective date as Always Effective.
   Resource Group	Select the Prepare the environment Serverless resource group created earlier.	The resource properties panel shows the scheduling resource group as User_portraits (use the name of the Serverless resource group you created).
   Dependencies	Automatic code parsing identifies the nodes that produce data for tables dwd_log_info_di_odps and ods_user_info_d_odps: dwd_log_info_di_odps and ods_user_info_d_odps. It sets them as upstream dependencies of the current node dws_user_info_all_di_odps. It also sets the output table dws_user_info_all_di_odps as this node’s output, so downstream nodes that query the table automatically depend on this node.	Configure dependencies as for dwd_log_info_di_odps. Code parsing sets upstream nodes to ods_user_info_d_odps and dwd_log_info_di_odps. The node output includes dws_user_info_all_di_odps.

   Note

   Upstream dependencies: Click Parse Code Inputs and Outputs to find the upstream node whose output is MaxCompute production project name.table name.Output Name of Ancestor Node.

   Node output name: Click Parse Code Inputs and Outputs: Click to parse table names in the code and generate this node’s output name in the MaxCompute production project name.table name format for downstream nodes.

3. Configure any other required settings. Then click in the node editor toolbar to save.

Configure the ads_user_info_1d_odps node

In the business process canvas, double-click ads_user_info_1d_odpsDouble-click the node and write SQL to process the upstream dws_user_info_all_di_odps table and write the result to ads_user_info_1d_odps table.

1. Edit code.

   -- Scenario: Process the user access wide table dws_user_info_all_di_odps into a basic user profile in ads_user_info_1d_odps.
   -- Additional information:DataWorks scheduling parameters let you write daily incremental data to the matching business partition.
   --      In development, define code variables as ${variable_name} and assign scheduling parameters to them on the scheduling page to pass dynamic values to code.
   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; 

2. Configure scheduling properties.

   On the configuration page, click Schedule on the right to open the Scheduling Settings panel for scheduling and node settings. For details, see Node scheduling configuration. Configure:

   Setting	Value	Illustration
   Scheduling Parameter	In the Scheduling ParameterParameter settings: Parameter name:bizdate. Parameter Value:$[yyyymmdd-1].	The scheduling parameter panel shows the parameter bizdate with value $[yyyymmdd-1], sourced from code parsing. At the bottom is the Scheduling Parameter Preview button.
   Schedule	Scheduling period: Day. Scheduling Time: 00:30. RUN Attribute: Allows the task to be rerun regardless of its previous success or failure. Use the default values for other parameters. Note The node runs only after 00:30 every day.	The time properties panel shows instance generation as T+1, generated the following day, scheduling type as Normal Scheduling, cycle as Day, scheduled time as 00:30, rerun property as Rerun after success or failure, and effective date as Always Effective.
   Resource Group	Select the Prepare the environment Serverless resource group created earlier.	The resource properties panel shows the scheduling resource group as User_portraits (use the name of the Serverless resource group you created).
   Dependencies	Through automatic code parsingAutomatically configure dependencies from node lineage. Set the node producing dws_user_info_all_1d_odps table data as dws_user_info_all_1d_odps an upstream dependency of the current nodeads_user_info_1d_odps as an upstream dependency. Set the node output table ads_user_info_1d as this node’s output for downstream queries.	Configure dependencies as above. Code parsing sets the upstream node to dws_user_info_all_di_odps. The node output includes ads_user_info_1d_odps.

   Note

   Upstream dependencies: Click Parse Code Inputs and Outputs to find the upstream node whose output is MaxCompute production project name.table name.Output Name of Ancestor Node.

   Node output name: Click Parse Code Inputs and Outputs: Click to parse table names in the code and generate this node’s output name in the MaxCompute production project name.table name format for downstream nodes.

3. Configure any other required settings. Then click in the node editor toolbar to save.