DataWorks:Process data

1. Edit node code

On the configuration tab of the workflow, double-click the name of the dwd_log_info_di node. On the configuration tab of the node, enter the following code. In DataWorks, variables in the node code are defined in the format of ${Variable name}. ${bizdate} is a variable in the node code. The value of the variable is assigned in the next step.

-- 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 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. For more information, visit https://www.alibabacloud.com/help/zh/dataworks/user-guide/create-and-use-maxcompute-resources.
-- 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 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 dwd_log_info_di 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 
  WHERE dt ='${bizdate}'
) a;

2. Configure scheduling properties

If you configure scheduling properties for the ods_raw_log_d node as those shown in the following content, after the node synchronizes data from the OSS object user_log.txt to the MaxCompute table ods_raw_log_d at 00:15 every day in the scheduling scenario, the dwd_log_info_di node is triggered to process the data in the ods_raw_log_d table and write the processed data to the data timestamp-based partition in the dwd_log_info_di table.

• In the Parameters section, enter bizdate for Parameter Name and $[yyyymmdd-1] for Parameter Value, which is used to query the date of the previous day.

• In the Schedule section, set Scheduling Cycle to Day. You do not need to separately configure the Scheduled time parameter for the current node. The time when the current node is scheduled to run every day is determined by the scheduling time of the root node of the WorkShop workflow. The scheduling time of the root node is later than 00:15 every day.

• In the Dependencies section, set Automatic Parsing From Code Before Node Committing to Yes to allow the system to configure the ods_raw_log_d node that generates the ods_raw_log_d table as the ancestor node of the dwd_log_info_di node. The dwd_log_info_di table is used as the output of the dwd_log_info_di node. This way, the dwd_log_info_di 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 node.

  Note

  The output of a DataWorks node is used to establish scheduling dependencies between other nodes and the current node. In DataWorks, the output of a node must be configured as the input of its descendant nodes, which forms scheduling dependencies between nodes.

  • The system automatically generates two output names for each node in the formats of projectName.randomNumber_out and projectName.nodeName_out, respectively.

  • If the automatic parsing feature is enabled, the system generates an output name for a node based on the code parsing result in the format of projectName.tableName.

3. Save the configurations

In this example, you can configure other required configuration items 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.

1. Edit node code

On the configuration tab of the workflow, double-click the name of the dws_user_info_all_di node. On the configuration tab of the node, enter the following code. In DataWorks, variables in the node code are defined in the format of ${Variable name}. ${bizdate} is a variable in the node code. The value of the variable is assigned in the next step.

-- Scenario: Aggregate the processed log data in the dwd_log_info_di table and the basic user information in the ods_user_info_d table and write the aggregated data to the dws_user_info_all_di 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 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 
  WHERE dt = '${bizdate}'
) a
LEFT OUTER JOIN (
  SELECT *
  FROM ods_user_info_d
  WHERE dt = '${bizdate}'
) b
ON a.uid = b.uid;

2. Configure scheduling properties

If you configure scheduling properties for the ods_user_info_d node as those shown in the following content, after the node synchronizes data from the ApsaraDB RDS for MySQL table ods_user_info_d to the MaxCompute table ods_user_info_d and the dwd_log_info_di node processes data in the ods_raw_log_d table at 00:15 every day in the scheduling scenario, the dws_user_info_all_di node is triggered to aggregate and write the data to the related partition in the dws_user_info_all_di table.

• In the Parameters section, enter bizdate for Parameter Name and $[yyyymmdd-1] for Parameter Value, which is used to query the date of the previous day.

  

• In the Schedule section, set Scheduling Cycle to Day. You do not need to separately configure the Scheduled time parameter for the current node. The time when the current node is scheduled to run every day is determined by the scheduling time of the root node of the WorkShop workflow. The scheduling time of the root node is later than 00:15 every day.

• In the Dependencies section, set Automatic Parsing From Code Before Node Committing to Yes to allow the system to configure the dwd_log_info_di and ods_user_info_d nodes that generate the dwd_log_info_di and ods_user_info_d tables as the ancestor nodes of the dws_user_info_all_di node. The dws_user_info_all_di table is used as the output of the dws_user_info_all_di node. This way, the dws_user_info_all_di 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 node.

  

3. Save the configurations

In this example, you can configure other required configuration items 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.

1. Edit node code

On the configuration tab of the workflow, double-click the name of the ads_user_info_1d node. On the configuration tab of the node, enter the following code. In DataWorks, variables in the node code are defined in the format of ${Variable name}. ${bizdate} is a variable in the node code. The value of the variable is assigned in the next step.

-- Scenario: The following SQL statement is used to further process the dws_user_info_all_di wide table that is used to store user access information into the ads_user_info_1d 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 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
WHERE dt = '${bizdate}'
GROUP BY uid; 

2. Configure scheduling properties

To implement periodic scheduling, you need to define the properties that are relevant to periodic scheduling of nodes.

• In the Parameters section, enter bizdate for Parameter Name and $[yyyymmdd-1] for Parameter Value, which is used to query the date of the previous day.

  

• In the Schedule section, you do not need to separately configure the Scheduled time parameter for the current node. The time when the current node is scheduled to run every day is determined by the scheduling time of the root node of the workflow named WorkShop. The scheduling time of the root node is later than 00:15 every day.

• In the Dependencies section, set Automatic Parsing From Code Before Node Committing to Yes to allow the system to configure the dws_user_info_all_1d node that generates the dws_user_info_all_1d table as the ancestor node of the ads_user_info_1d 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.

3. Save the configurations

In this example, you can configure other required configuration items 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.