Sync data

Configure user log sync to OSS

Use an offline data integration task to fetch user log data from the platform’s HttpFile data source and sync it to a private OSS data source.

Synchronize log information from the HttpFile data source to the self-managed OSS.

1. On the DataStudio page, double-click the ods_raw_log_d_2oss_spark node to open its configuration page.

2. Configure the synchronization network connection.

   After you complete the following network and resource configuration, click The next Step and complete the connectivity test as prompted.

   Parameter	Description
   Data source	Source: HttpFile. Data source name: user_behavior_analysis_httpfile.
   My Resource Group	Select the purchased serverless resource group.
   Data going	Destination: OSS. Data source name: Select the private OSS data source created earlier. In this example, select test_g.

3. Configure the synchronization task.

   Parameter	Description
   Data source	File Path: /user_log.txt. File Type: Select text. Field Delimiter: Enter | as the column delimiter. Compression Format: Options include None, Gzip, Bzip2, and Zip. Select None. Skip Header: Select No.
   Data going	File Type: Select text. Object Name (Path Included): Enter the path based on your self-managed OSS directory. In this example, enter ods_raw_log_d/log_${bizdate}/log_${bizdate}.txt. In this path, ods_raw_log_d is the directory name you created, and $bizdate represents the date of the previous day. Field Delimiter: Enter | as the column delimiter.

4. Scheduling settings.

   On the configuration page, click Scheduling Configuration on the right to open the Scheduling Configuration panel. Configure scheduling and node information as follows.

   Note

   DataWorks provides scheduling parameters. These let you write daily data to different OSS paths and files, naming directories and files by business date.

   In practice, you can use the ${variable_name} format in the File name (with path) field to define path variables. Then, assign scheduling parameter values to these variables in the scheduling configuration panel. This dynamically generates destination paths and filenames during scheduling.

   Configuration item	Configuration content
   Scheduling Parameters	In the Scheduling Parameters section, click Add Parameter and add: Parameter name: bizdate Parameter value: $[yyyymmdd-1] For details, see Configure scheduling parameters.
   Scheduling Dependency	In Scheduling Dependency, confirm that the output table is listed as this node’s output. Format: workspacename.nodename. For details, see Configure scheduling dependency.

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

Configure user data sync to OSS

Use an offline data integration task to fetch user data from the platform’s MySQL data source and sync it to a private OSS data source.

Configure the ods_raw_log_d_spark node

Create the external table ods_raw_log_d_spark using EMR Spark SQL. Use the LOCATION clause to access log data written by the offline data integration task to your private OSS object storage Bucket.

1. Code configuration.

   -- Scenario: The following SQL uses Spark SQL to create the external table ods_raw_log_d_spark via EMR Spark SQL.
   -- The LOCATION clause accesses log data written by the offline data integration task to the private OSS object storage Bucket.
   -- A dt partition is added accordingly.
   -- Note:
   --      DataWorks provides scheduling parameters to write daily incremental data into the target table’s business-date partition during scheduling.
   --      In real-world development, define code variables using the ${variable_name} format.
   --      Assign scheduling parameter values to these variables in the scheduling configuration panel to enable dynamic code input during scheduling.
   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

   The location in the preceding code is an example path that corresponds to the data destination configured in the batch synchronization task. Enter your own file path. In the path, dw-emr-demo is the OSS bucket domain name that you created during environment preparation.

2. Configure scheduling settings.

   Configure task scheduling for the ods_raw_log_d_spark node. Use the configured scheduling parameters to fetch the private OSS log file for the corresponding business date and write it into the Spark table’s matching business-date partition.

   Configuration item	Configuration content
   Scheduling Parameters	In the Scheduling Parameters section, click Add Parameter and add: Parameter name: bizdate Parameter value: $[yyyymmdd-1]. For details, see Configure scheduling parameters.
   Scheduling Dependency	In Scheduling Dependency, confirm that the output table is listed as this node’s output. Format: workspacename.nodename. For details, see Configure scheduling dependency.

   Note

   This section configures the scheduling parameter ${bizdate} and sets its value to T-1. In offline computing scenarios, bizdate refers to the date when business transactions occurred—also known as the business date. For example, if you calculate yesterday’s revenue today, “yesterday” refers to the transaction date, which is the business date.

3. After the configuration is complete, click to save the node.

Configure the ods_user_info_d_spark node

Create the external table ods_user_info_d_spark using an EMR Spark SQL node. Use the LOCATION clause to access user information written by the offline data integration task to your private OSS object storage Bucket.

1. Code configuration.

   -- Scenario: The following SQL uses Spark SQL in an EMR Spark SQL node to create the external table ods_user_info_d_spark.
   -- The LOCATION clause accesses user information written by the offline data integration task to the private OSS object storage Bucket.
   -- Data is written into the corresponding dt partition.
   -- Note:
   --      DataWorks provides scheduling parameters to write daily incremental data into the target table’s business-date partition during scheduling.
   --      In real-world development, define code variables using the ${variable_name} format.
   --      Assign scheduling parameter values to these variables in the scheduling configuration panel to enable dynamic code input during scheduling.
   CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_info_d_spark
   (
       `uid`        STRING COMMENT 'User ID'
       ,`gender`    STRING COMMENT 'Gender'
       ,`age_range` STRING COMMENT 'Age range'
       ,`zodiac`    STRING COMMENT '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

   The location in the preceding code is an example path that corresponds to the data destination configured in the batch synchronization task. Enter your own file path. In the path, dw-emr-demo is the OSS bucket domain name that you created during environment preparation.

2. Configure scheduling settings.

   Configure task scheduling for the ods_user_info_d_spark node. Use the configured scheduling parameters to fetch the private OSS user information file for the corresponding business date and write it into the Spark table’s matching business-date partition.

   Configuration item	Configuration content	Example
   Scheduling Parameters	In the Scheduling Parameters section, click Add Parameter and add: Parameter name: bizdate Parameter value: $[yyyymmdd-1]. For details, see Configure scheduling parameters.	In the variable configuration interface, enter bizdate as the parameter name and $[yyyymmdd-1] as the parameter value.
   Scheduling Dependency	In Scheduling Dependency, confirm that the output table is listed as this node’s output. Format: workspacename.nodename For details, see Configure scheduling dependency.	In the Output Name section, review the output list to confirm the output table exists. If needed, manually add it by entering the output name and clicking Add.

3. After the configuration is complete, click to save the node.