DataWorks:Data processing

Dwd_log_info_di_odps table

Double-click the dwd_log_info_di_odps table. On the editing page that opens, click DDL and enter the following 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 in the yyyymmddhh:mi:ss format',
 status STRING COMMENT 'Status code returned by the server',
 bytes STRING COMMENT 'Number of bytes returned to the client',
 region STRING COMMENT 'Region, obtained from the IP address',
 method STRING COMMENT 'HTTP request type',
 url STRING COMMENT 'URL',
 protocol STRING COMMENT 'HTTP protocol version',
 referer STRING COMMENT 'Source URL',
 device STRING COMMENT 'Device type',
 identity STRING COMMENT 'Access type: crawler, feed, user, or unknown'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

Dws_user_info_all_di_odps table

Double-click the dws_user_info_all_di_odps table. On the editing page that opens, click DDL and enter the following 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, obtained from the IP address',
 device STRING COMMENT 'Device type',
 identity STRING COMMENT 'Access type: crawler, feed, user, or unknown',
 method STRING COMMENT 'HTTP request type',
 url STRING COMMENT 'URL',
 referer STRING COMMENT 'Source URL',
 time STRING COMMENT 'Time in the yyyymmddhh:mi:ss format'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

Ads_user_info_1d_odps table

Double-click the ads_user_info_1d_odps table. On the editing page that opens, click DDL and enter the following statement.

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

Dwd_log_info_di_odps node

On the workflow canvas, double-click the dwd_log_info_di_odps node. Use the function you created to write SQL code that processes fields from the ancestor table ods_raw_log_d_odps and writes the output to the dwd_log_info_di_odps table. For a data processing example, see Appendix: Data processing example.

1. Edit the code.

   -- Scenario: This SQL statement uses the getregion function to parse the IP addresses in the raw log data. It also uses regular expressions and other methods to split the raw data into analyzable fields and write them to the dwd_log_info_di_odps table.
   --      This tutorial provides the getregion function, which converts an IP address to a region.
   -- Notes:
   --     1. Before you can use a function in a DataWorks node, you must upload the required resources to DataWorks and then register the function by using those resources in the UI.
   --        The resource used to register the getregion function in this tutorial is ip2region.jar.
   --     2. DataWorks provides scheduling parameters that allow you to write daily incremental data to the corresponding business partition of a target table in a scheduling scenario.
   --        In a real-world development scenario, you can define code variables in the ${variable_name} format. On the scheduling configuration page, you can assign scheduling parameters to these variables to enable dynamic parameter passing during scheduled runs.
   INSERT OVERWRITE TABLE dwd_log_info_di_odps PARTITION (dt='${bizdate}')
   SELECT ip 
     , uid
     , time
     , status
     , bytes 
     , getregion(ip) AS region -- Use the custom UDF to get the region from the IP address.
     , regexp_substr(request, '(^[^ ]+ )') AS method -- Use a regular expression to split the request into three fields.
     , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
     , regexp_substr(request, '([^ ]+$)') AS protocol 
     , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer -- Use a regular expression to parse the referer and get a more accurate URL.
     , CASE
       WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' -- Get the device information and access type from the 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 Scheduling Properties on the right to open the Scheduling Settings panel, where you can configure scheduling and node information. For more information, see Node Scheduling Properties. The following parameters can be configured:

   Parameter	Settings	Illustration
   Scheduling Parameter	In the Scheduling Parameter section, configure the following parameter: Parameter name: bizdate Parameter Value: $[yyyymmdd-1]	The scheduling parameter pane shows that the bizdate parameter has a value of $[yyyymmdd-1] and its source is Parse from Code. A Preview scheduling parameter button is at the bottom.
   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.	In the Time Properties panel, the instance generation method is T+1 (Next-day generation), the scheduling type is Normal Scheduling, the scheduling cycle is Daily, the scheduled time is set to 00:30, the rerun property is Rerunnable on success or failure, and the effective date is Permanently Effective.
   Resource Group	Select the serverless resource group you created in the Prepare the environment step.	In the Resource properties pane, the scheduling resource group is set to User_portraits (use the actual name of your serverless resource group).
   Dependencies	The Code Auto-parsing feature automatically sets the ods_raw_log_d_odps node that generates the ods_raw_log_d_odps table as an upstream dependency of the current node dwd_log_info_di_odps. Set the dwd_log_info_di_odps table as the node output to allow downstream jobs that query this table to automatically depend on the current node.	In the Dependencies pane, Auto-parse dependencies before commit is enabled. The ancestor node is automatically configured as ods_raw_log_d_odps by using Parse from code. The output of the current node can come from system defaults, manual additions, or code parsing.

   Note

   Ancestor node dependency: Click Parse from code. The system parses the table names from the code and uses the output name of the corresponding ancestor node, formatted as Production_MaxCompute_Project_Name.TableName, as the Output Name of Ancestor Node.

   Current node output: Click Parse from code. The system parses the table name from the code to generate an output name for the current node, formatted as Production_MaxCompute_Project_Name.TableName. This output is then available for downstream nodes.

3. Configure any other required settings, then click the Save icon in the toolbar of the node editor.

Configure the dws_user_info_all_di_odps node

On the workflow canvas, double-click the dws_user_info_all_di_odps node. Write SQL code to merge the ancestor tables dwd_log_info_di_odps and ods_user_info_d_odps and write the results to the dws_user_info_all_di_odps table.

1. Edit the code.

   -- Scenario: This SQL statement aggregates the processed log data from dwd_log_info_di_odps and the basic user information from ods_user_info_d_odps, and writes the result to the dws_user_info_all_di_odps table.
   -- Note: DataWorks provides scheduling parameters that allow you to write daily incremental data to the corresponding business partition of a target table in a scheduling scenario.
   --      In a real-world development scenario, you can define code variables in the ${variable_name} format. On the scheduling configuration page, you can assign scheduling parameters to these variables to enable dynamic parameter passing during scheduled runs.
   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 Scheduling Properties on the right to open the Scheduling Settings panel and configure scheduling and node information. For more information, see Node scheduling properties. The configuration items are as follows:

   Parameter	Settings	Illustration
   Scheduling Parameter	In the Scheduling Parameter section, configure the following parameter: Parameter name: bizdate Parameter Value: $[yyyymmdd-1]	The scheduling parameter pane shows that the bizdate parameter has a value of $[yyyymmdd-1] and its source is Parse from Code. A Preview scheduling parameter button is at the bottom.
   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.	In the Time properties pane, Instance Generation is set to Generate next day (T+1), Scheduling Type is Normal, Scheduling Cycle is Day, Scheduled Time is set to 00:30, Rerun Policy is Rerun on success or failure, and Effective Date is Permanent.
   Resource Group	Select the serverless resource group you created in the Prepare the environment step.	In the Resource properties pane, the scheduling resource group is set to User_portraits (use the actual name of your serverless resource group).
   Dependencies	The system automatically parses the code and sets the nodes dwd_log_info_di_odps and ods_user_info_d_odps that output the dwd_log_info_di_odps and ods_user_info_d_odps tables as upstream dependencies for the current node dws_user_info_all_di_odps. The system also registers the node's output table dws_user_info_all_di_odps as the node output, which allows downstream queries on this table to automatically establish a dependency on the current node.	The configuration is the same as for the dwd_log_info_di_odps node. The ancestor nodes are automatically configured as ods_user_info_d_odps and dwd_log_info_di_odps by parsing the code. The output of the current node includes dws_user_info_all_di_odps.

   Note

   Ancestor node dependency: Click Parse from code. The system parses the table names from the code and uses the output name of the corresponding ancestor node, formatted as Production_MaxCompute_Project_Name.TableName, as the Output Name of Ancestor Node.

   Current node output: Click Parse from code. The system parses the table name from the code to generate an output name for the current node, formatted as Production_MaxCompute_Project_Name.TableName. This output is then available for downstream nodes.

3. Configure any other required settings, then click the Save icon in the toolbar of the node editor.

Configure the ads_user_info_1d_odps node

On the workflow canvas, double-click the ads_user_info_1d_odps node. Write SQL code to process the ancestor table dws_user_info_all_di_odps and write the results to the ads_user_info_1d_odps table.

1. Edit the code.

   -- Scenario: This SQL statement further processes the dws_user_info_all_di_odps wide table of user access information to generate basic user profile data and writes the result to the ads_user_info_1d_odps table.
   -- Note: DataWorks provides scheduling parameters that allow you to write daily incremental data to the corresponding business partition of a target table in a scheduling scenario.
   --      In a real-world development scenario, you can define code variables in the ${variable_name} format. On the scheduling configuration page, you can assign scheduling parameters to these variables to enable dynamic parameter passing during scheduled runs.
   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 scheduling properties on the right to open the Scheduling Settings panel, where you can configure scheduling and node information. For more information, see Node scheduling properties. The configurations are as follows:

   Parameter	Settings	Illustration
   Scheduling Parameter	In the Scheduling Parameter section, configure the following parameter: Parameter name: bizdate Parameter Value: $[yyyymmdd-1]	The scheduling parameter pane shows that the bizdate parameter has a value of $[yyyymmdd-1] and its source is Parse from Code. A Preview scheduling parameter button is at the bottom.
   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.	In the Time properties pane, Instance Generation is set to Generate next day (T+1), Scheduling Type is Normal, Scheduling Cycle is Day, Scheduled Time is set to 00:30, Rerun Policy is Rerun on success or failure, and Effective Date is Permanent.
   Resource Group	Select the serverless resource group you created in the Prepare the environment step.	In the Resource properties pane, the scheduling resource group is set to User_portraits (use the actual name of your serverless resource group).
   Dependencies	The Auto-Parse Code feature automatically configures node dependencies based on data lineage. It sets the dws_user_info_all_1d_odps node, which produces the dws_user_info_all_1d_odps table, as the upstream dependency for the current ads_user_info_1d_odps node. The node's output is set to the ads_user_info_1d table so that downstream queries to this table automatically create a dependency on the current node.	The configuration is the same as for the previous nodes. The ancestor node is automatically configured as dws_user_info_all_di_odps by parsing the code. The output of the current node includes ads_user_info_1d_odps.

   Note

   Ancestor node dependency: Click Parse from code. The system parses the table names from the code and uses the output name of the corresponding ancestor node, formatted as Production_MaxCompute_Project_Name.TableName, as the Output Name of Ancestor Node.

   Current node output: Click Parse from code. The system parses the table name from the code to generate an output name for the current node, formatted as Production_MaxCompute_Project_Name.TableName. This output is then available for downstream nodes.

3. Configure any other required settings, then click the Save icon in the toolbar of the node editor.