DataWorks:Process data

Create the dwd_log_info_di_odps table

Double-click the dwd_log_info_di_odps table. On the table configuration tab that appears, click DDL and enter the following CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS dwd_log_info_di_odps (
 ip STRING COMMENT 'The IP address of the client that is used to send the request',
 uid STRING COMMENT 'The ID of the user',
 time STRING COMMENT 'The time in the format of yyyymmddhh:mi:ss',
 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 type of the HTTP request',
 url STRING COMMENT '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;

Create the dws_user_info_all_di_odps table

Double-click the dws_user_info_all_di_odps table. On the table configuration tab that appears, click DDL and enter the following CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS dws_user_info_all_di_odps (
 uid STRING COMMENT 'The ID of the user',
 gender STRING COMMENT 'The gender',
 age_range STRING COMMENT 'The age range of the user',
 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 type of the HTTP request',
 url STRING COMMENT 'url',
 referer STRING COMMENT 'The source URL',
 time STRING COMMENT 'The time in the format of yyyymmddhh:mi:ss'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

Create the ads_user_info_1d_odps table

Double-click the ads_user_info_1d_odps table. On the table configuration tab that appears, click DDL and enter the following CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS ads_user_info_1d_odps (
 uid STRING COMMENT 'The ID of the user',
 region STRING COMMENT 'The region, which is obtained based on the IP address',
 device STRING COMMENT 'The terminal type',
 pv BIGINT COMMENT 'pv',
 gender STRING COMMENT 'The gender',
 age_range STRING COMMENT 'The age range of the user',
 zodiac STRING COMMENT 'The zodiac sign'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;    

Configure the dwd_log_info_di_odps node

On the configuration tab of the workflow, double-click the dwd_log_info_di_odps node. On the configuration tab of the node, enter the SQL code that processes fields in the ods_raw_log_d_odps ancestor table by using the created function and writes the processed data to the dwd_log_info_di_odps table. For more information about how to process data, see Appendix: Data processing example.

1. Edit node code.

   -- Scenario: The following SQL statement uses the getregion function to parse IP addresses in raw log data, use methods such as regular expressions to split the parsed data into analyzable fields, and then write the fields into the dwd_log_info_di_odps table. 
   --      In this example, the getregion function that is used to convert IP addresses into regions is prepared. 
   -- Note:
   --     1. Before you can use a function in a DataWorks node, you must upload the resources required for registering the function to DataWorks and then register the function by using the resources in a visualized manner. 
   --        In this example, the resource used to register the getregion function is ip2region.jar. 
   --     2. You can configure scheduling parameters for nodes in DataWorks to write incremental data to the related partition in the desired table every day in the scheduling scenario. 
   --        In actual development scenarios, you can define variables in the code of a node in the ${Variable name} format and assign scheduling parameters to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters can be dynamically replaced in the node code based on the configurations of the scheduling parameters. 
   INSERT OVERWRITE TABLE dwd_log_info_di_odps PARTITION (dt='${bizdate}')
   SELECT ip 
     , uid
     , time
     , status
     , bytes 
     , getregion(ip) AS region -- Obtain the region based on the IP address by using the UDF. 
     , regexp_substr(request, '(^[^ ]+ )') AS method -- Use the regular expression to extract three fields from the request. 
     , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
     , regexp_substr(request, '([^ ]+$)') AS protocol 
     , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer -- Use the regular expression to clarify the referrer to obtain a more accurate URL. 
     , CASE
       WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' -- Obtain the terminal information and access type based on the agent parameter. 
       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 tab of the node, click Properties in the right-side navigation pane. On the Properties tab, configure the scheduling properties and basic information for the node. For more information, see Scheduling properties of a node. The following table describes the parameters.

   Section	Description	Illustration
   Scheduling Parameter	Configure the following parameters in the Scheduling Parameter section: Parameter Name: Set the value to bizdate. Parameter Value: Set the value to $[yyyymmdd-1].
   Schedule	Scheduling Cycle: Set the value to Day. Scheduled time: Set the value to 00:30. Rerun: Set the value to Allow Regardless of Running Status. Use the default values for other parameters. Note The time when the current node is scheduled to run every day is determined by the scheduling time of the zero load node workshop_start of the workflow. The current node is scheduled to run after 00:30 every day.
   Resource Group	Select the serverless resource group that you purchased in the environment preparation phase.
   Dependencies	Set Automatic Parsing From Code Before Node Committing to Yes to allow the system to configure the ods_raw_log_d_odps node that generates the ods_raw_log_d_odps table as the ancestor node of the dwd_log_info_di_odps node. The dwd_log_info_di_odps table is used as the output of the dwd_log_info_di_odps node. This way, the dwd_log_info_di_odps node can be automatically configured as an ancestor node of other nodes when these nodes query the table data generated by the dwd_log_info_di_odps node.

   Note

   Determine the ancestor nodes of the current node: Click Parse Input and Output from Code, the table name in the code is parsed to the output node named in the format of MaxCompute project name in the production environment.Table name. The output node name is used as the name of the output of an ancestor node.

   Determine the output of the current node: Click Parse Input and Output from Code, the table name in the code is parsed to the output node named in the format of MaxCompute project name in the production environment.Table name, which is output to the downstream node.

3. In this example, other required configuration items can be configured based on your business requirements. After the configuration is complete, click the icon in the top toolbar on the configuration tab of the node to save the node configurations.

Configure the dws_user_info_all_di_odps node

On the configuration tab of the workflow, double-click the dws_user_info_all_di_odps node. On the configuration tab of the node, enter the SQL code that merges the dwd_log_info_di_odps and ods_user_info_d_odps ancestor tables and writes the merged data to the dws_user_info_all_di_odps table.

1. Edit node code.

   -- Scenario: Aggregate the processed log data in the dwd_log_info_di_odps table and the basic user information in the ods_user_info_d_odps table and write the aggregated data to the dws_user_info_all_di_odps table. 
   -- Note: You can configure scheduling parameters for nodes in DataWorks to write incremental data to the related partition in the desired table every day in the scheduling scenario. 
   --      In actual development scenarios, you can define variables in the code of a node in the format of ${Variable name} and assign scheduling parameters to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters can be dynamically replaced in the node code based on the configurations of the scheduling parameters. 
   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 tab of the node, click Properties in the right-side navigation pane. On the Properties tab, configure the scheduling properties and basic information for the node. For more information, see Scheduling properties of a node. The following table describes the parameters.

   Section	Description	Illustration
   Scheduling Parameter	Configure the following parameters in the Scheduling Parameter section: Parameter Name: Set the value to bizdate. Parameter Value: Set the value to $[yyyymmdd-1].
   Schedule	Scheduling Cycle: Set the value to Day. Scheduled time: Set the value to 00:30. Rerun: Set the value to Allow Regardless of Running Status. Use the default values for other parameters. Note The time when the current node is scheduled to run every day is determined by the scheduling time of the zero load node workshop_start of the workflow. The current node is scheduled to run after 00:30 every day.
   Resource Group	Select the serverless resource group that you purchased in the environment preparation phase.
   Dependencies	Set Automatic Parsing From Code Before Node Committing to Yes to allow the system to configure the dwd_log_info_di_odps and ods_user_info_d_odps nodes that generate the dwd_log_info_di_odps and ods_user_info_d_odps tables as the ancestor node of the dws_user_info_all_di_odps node. The dws_user_info_all_di_odps table is used as the output of the dws_user_info_all_di_odps node. This way, the dws_user_info_all_di_odps node can be automatically configured as an ancestor node of other nodes when these nodes query the table data generated by the dws_user_info_all_di_odps node.

   Note

   Determine the ancestor nodes of the current node: Click Parse Input and Output from Code, the table name in the code is parsed to the output node named in the format of MaxCompute project name in the production environment.Table name. The output node name is used as the name of the output of an ancestor node.

   Determine the output of the current node: Click Parse Input and Output from Code, the table name in the code is parsed to the output node named in the format of MaxCompute project name in the production environment.Table name, which is output to the downstream node.

3. In this example, other required configuration items can be configured based on your business requirements. After the configuration is complete, click the icon in the top toolbar on the configuration tab of the node to save the node configurations.

Configure the ads_user_info_1d_odps node

On the configuration tab of the workflow, double-click the ads_user_info_1d_odps node. On the configuration tab of the node, enter the SQL code that processes the dws_user_info_all_di_odps ancestor table and writes the processed data to the ads_user_info_1d_odps table.

1. Edit node code.

   -- Scenario: The following SQL statement is used to further process the dws_user_info_all_di_odps wide table that is used to store user access information into the ads_user_info_1d_odps table that is used to store basic user profile data. 
   -- Note: You can configure scheduling parameters for nodes in DataWorks to write incremental data to the related partition in the desired table every day in the scheduling scenario. 
   --      In actual development scenarios, you can define variables in the code of a node in the format of ${Variable name} and assign scheduling parameters to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters can be dynamically replaced in the node code based on the configurations of the scheduling parameters. 
   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 tab of the node, click Properties in the right-side navigation pane. On the Properties tab, configure the scheduling properties and basic information for the node. For more information, see Scheduling properties of a node. The following table describes the parameters.

   Section	Description	Illustration
   Scheduling Parameter	Configure the following parameters in the Scheduling Parameter section: Parameter Name: Set the value to bizdate. Parameter Value: Set the value to $[yyyymmdd-1].
   Schedule	Scheduling Cycle: Set the value to Day. Scheduled time: Set the value to 00:30. Rerun: Set the value to Allow Regardless of Running Status. Use the default values for other parameters. Note The time when the current node is scheduled to run every day is determined by the scheduling time of the zero load node workshop_start of the workflow. The current node is scheduled to run after 00:30 every day.
   Resource Group	Select the serverless resource group that you purchased in the environment preparation phase.
   Dependencies	Set Automatic Parsing From Code Before Node Committing to Yes to allow the system to configure the dws_user_info_all_1d_odps node that generates the dws_user_info_all_1d_odps table as the ancestor node of the ads_user_info_1d_odps node. The ads_user_info_1d table is used as the output of the ads_user_info_1d node. This way, the ads_user_info_1d node can be automatically configured as an ancestor node of other nodes when these nodes query the table data generated by the ads_user_info_1d node.

   Note

   Determine the ancestor nodes of the current node: Click Parse Input and Output from Code, the table name in the code is parsed to the output node named in the format of MaxCompute project name in the production environment.Table name. The output node name is used as the name of the output of an ancestor node.

   Determine the output of the current node: Click Parse Input and Output from Code, the table name in the code is parsed to the output node named in the format of MaxCompute project name in the production environment.Table name, which is output to the downstream node.

3. In this example, other required configuration items can be configured based on your business requirements. After the configuration is complete, click the icon in the top toolbar on the configuration tab of the node to save the node configurations.