E-MapReduce:Synchronize data

Configure the node to synchronize the website access logs of users to the private OSS bucket

You can use a batch synchronization task to synchronize the website access logs of users stored in the HttpFile data source to the private OSS bucket.

Synchronize the website access logs of the HttpFile data source to the created OSS bucket.

1. On the DataStudio page, double-click the ods_raw_log_d_2oss_spark node to go to the configuration tab of the node.

2. Establish network connections between the resource group that you want to use and the data sources.

   After you complete the configuration of network connections and resources, click Next and complete the connectivity test as prompted.

   Parameter	Description
   Source	Source: Set the value to HttpFile. Data Source Name: Set the value to user_behavior_analysis_httpfile.
   Resource Group	Select the serverless resource group that you purchased.
   Destination	Destination: Set the value to OSS. Data Source Name: Set the value to test_g, which specifies the name of the private OSS data source that you added.

3. Configure the data synchronization node.

   Parameter	Description
   Source	File Path: Set the value to /user_log.txt. Text type: Set the value to text. Column Delimiter: Enter |. Compression format: The compression format of the OSS object. Valid values: None, Gzip, Bzip2, and Zip. Select None. Skip Header: Set the value to No.
   Destination	Text type: Set the value to text. Object Name (Path Included): The path of the OSS object. Configure this parameter based on the folder that you created in the OSS bucket. In this example, ods_raw_log_d/log_${bizdate}/log_${bizdate}.txt is entered. ods_raw_log_d is the name of the folder that you created in the OSS bucket. $bizdate indicates the date of the previous day. Column Delimiter: Enter |.

4. Configure the scheduling properties.

   In the right-side navigation pane of the configuration tab of the node, click Properties. On the Properties tab, configure the scheduling properties and basic information for the node. The following table describes the configurations.

   Note

   DataWorks provides scheduling parameters that you can use to write daily data to different directories and objects of an OSS bucket in the scheduling scenario. The directories and objects are named based on data timestamps.

   Based on your business scenario, you can configure a variable in the ${Variable name} format in the directory when you configure the Object Name (Path Included) parameter for the private OSS bucket. Then, you can assign a scheduling parameter as a value to the variable on the Properties tab of the configuration tab of the node. This way, the directory and object name in the private OSS bucket can be dynamically generated in the scheduling scenario.

   Section	Description	Screenshot
   Scheduling Parameter	In this section, click Add Parameter to add a scheduling parameter. Set Parameter Name to bizdate. Set Parameter Value to $[yyyymmdd-1]. For more information, see Configure scheduling parameters.
   Dependencies	In this section, make sure that the output table is used as the output name of the current node. The output table is named in the worksspacename.nodename format. For more information, see Configure scheduling dependencies.

5. After the configuration is complete, click the icon in the toolbar.

Configure the node to synchronize the basic user information to the private OSS bucket

You can use a batch synchronization task to synchronize the basic user information stored in the MySQL data source to the private OSS bucket.

Configure the ods_raw_log_d_spark node

Based on the ods_raw_log_d_spark external table created by using the EMR Spark SQL node, you can use LOCATION to access the website access logs of users that are synchronized to the private OSS bucket by the related batch synchronization task.

1. Configure the code.

   -- Scenario: SQL statements of Spark SQL are used in the following sample code. In the code, the ods_raw_log_d_spark external table is created by using the EMR Spark SQL node and LOCATION is used to access the website access logs of users that are synchronized to the private OSS bucket by the related batch synchronization task. 
   -- Note:
   --      DataWorks provides scheduling parameters that you can use to write daily incremental data to the corresponding partition of the destination table in the scheduling scenario. 
   --      In the actual development scenario, you can define variables in the node code in the ${Variable name} format. Then, you can assign scheduling parameters as values to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters are dynamically replaced in the node code based on the configurations of the scheduling parameters in the scheduling scenario.  
   CREATE EXTERNAL TABLE IF NOT EXISTS ods_raw_log_d_spark
   (
     `col` STRING
   ) 
   PARTITIONED BY (
     dt STRING
   )
   LOCATION 'oss://dw-emr-demo/ods_raw_log_d/log_${bizdate}/';
   
   ALTER TABLE ods_raw_log_d_spark ADD IF NOT EXISTS PARTITION (dt = '${bizdate}') 
   LOCATION 'oss://dw-emr-demo/ods_raw_log_d/log_${bizdate}/'
   ;

   Note

   In the preceding code, a sample path is used for LOCATION. The path is based on the value of the Object Name (Path Included) parameter when you configure the related batch synchronization node. You must set the LOCATION parameter to the path of the created folder. dw-emr-demo is the domain name of the OSS bucket that you created when you prepared the environment.

2. Configure the scheduling properties.

   Configure the scheduling parameters for the ods_raw_log_d_spark node to obtain the private OSS log object with a specific data timestamp, and write data to the corresponding partition with the same data timestamp in a Spark table.

   Section	Description	Screenshot
   Scheduling Parameter	In this section, click Add Parameter to add a scheduling parameter. Set Parameter Name to bizdate. Set Parameter Value to $[yyyymmdd-1]. For more information, see Configure scheduling parameters.
   Dependencies	In this section, make sure that the output table is used as the output name of the current node. The output table is named in the worksspacename.nodename format. For more information, see Configure scheduling dependencies.

   Note

   In the SQL statements in this topic, the ${bizdate} scheduling parameter is configured and the value T-1 is assigned to the scheduling parameter. In batch computing scenarios, bizdate indicates the date on which a business transaction is conducted, which is often referred to as the data timestamp. For example, if you collect statistical data on the turnover of the previous day on the current day, the previous day is the date on which the business transaction is conducted and represents the data timestamp.

3. After the configuration is complete, click the icon.

Configure the ods_user_info_d_spark node

Based on the ods_user_info_d_spark external table created by using the EMR Spark SQL node, you can use LOCATION to access the basic user information that is synchronized to the private OSS bucket by the related batch synchronization task.

1. Configure the code.

   -- Scenario: SQL statements of Spark SQL are used in the following sample code. In the code, the ods_user_info_d_spark external table is created by using the EMR Spark SQL node and LOCATION is used to access the basic user information that is synchronized to the private OSS bucket by the related batch synchronization task. Then, the data is written to the dt partition. 
   -- Note:
   --      DataWorks provides scheduling parameters that you can use to write daily incremental data to the corresponding partition of the destination table in the scheduling scenario. 
   --      In the actual development scenario, you can define variables in the node code in the ${Variable name} format. Then, you can assign scheduling parameters as values to the variables on the Properties tab of the configuration tab of the node. This way, the values of scheduling parameters are dynamically replaced in the node code based on the configurations of the scheduling parameters in the scheduling scenario. 
   CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_info_d_spark
   (
       `uid`        STRING COMMENT 'The user ID'
       ,`gender`    STRING COMMENT 'The gender'
       ,`age_range` STRING COMMENT 'The age range'
       ,`zodiac`    STRING COMMENT 'The zodiac sign'
   )
   PARTITIONED BY 
   (
       dt           STRING
   )
   ROW FORMAT DELIMITED 
   FIELDS
   TERMINATED
   BY'|'
   STORED AS TEXTFILE
   LOCATION 'oss://dw-emr-demo/ods_user_info_d/user_${bizdate}/'
   ;
   
   ALTER TABLE ods_user_info_d_spark ADD IF NOT EXISTS PARTITION (dt = '${bizdate}') 
   LOCATION'oss://dw-emr-demo/ods_user_info_d/user_${bizdate}/'
   ;

   Note

   In the preceding code, a sample path is used for LOCATION. The path is based on the value of the Object Name (Path Included) parameter when you configure the related batch synchronization node. You must set the LOCATION parameter to the path of the created folder. dw-emr-demo is the domain name of the OSS bucket that you created when you prepared the environment.

2. Configure the scheduling properties.

   Configure the scheduling parameters for the ods_user_info_d_spark node to obtain the private OSS user information object with a specific data timestamp, and write data to the corresponding partition with the same data timestamp in a Spark table.

   Section	Description	Screenshot
   Scheduling Parameter	In this section, click Add Parameter to add a scheduling parameter. Set Parameter Name to bizdate. Set Parameter Value to $[yyyymmdd-1]. For more information, see Configure scheduling parameters.
   Dependencies	In this section, make sure that the output table is used as the output name of the current node. The output table is named in the worksspacename.nodename format. For more information, see Configure scheduling dependencies.

3. After the configuration is complete, click the icon.