Use the one-click data warehouse feature of DLA to develop data in DMS

Procedure

1. Log on to the DMS console.
2. In the top navigation bar, move the pointer over the More icon and choose Data Factory > Task Orchestration.
3. In the Free orchestration tasks section of the Task Orchestration tab, click New task flow.
   
4. , set the Task Flow Name and Description parameters and click OK.
   
5. Click the blank area on the canvas. In the right-side pane, click the Variables tab and configure the following variables.
   After you configure the variables, you can use ${Variable name} in SQL statements to specify a point in time or time period. For more information about the rules and usage of variables, see Task.

   In this example, create a variable named weekday, set the Time Format parameter to e, and then set the offset to +1 Day.

   The value of this variable indicates the specific day of a week. In this example, the variable value indicates that Sunday is the first day of the week. If you set the offset to 0, the variable value indicates that Monday is the first day of the week.

   
6. , drag the DLA one-click DW task node under Archiving the warehouse.
7. Configure the DLA one-click DW task node.
   This task node can be used to store a database in the form of a file to an OSS data warehouse. This helps you create a data warehouse with ease.
   1. Click the DLA one-click DW task node on the canvas. In the right-side pane, click the Content tab.
   2. On the Content tab, set the parameters that are described in the following table and click Save.
      

      Parameter	Description
      Source of data	The database instance that you want to connect to DLA as the data source. In this example, select an ApsaraDB RDS for MySQL instance.
      Target database (Schema) name	The name of the schema to be created in DLA.
      OSS path	The OSS path to which you want to store the data in the data source.
      Advanced options	The custom configurations of the schema to be created in DLA, such as filtering by field or filtering by table. For more information, see Advanced options. Example: include-tables=adjust_source.

      Note For more information about how to create a data warehouse in DLA, see Create a data warehouse by synchronizing full data with one click in T+1 days.
8. , drag the DLA-SQL task node under Archiving the warehouse.
9. Configure the DLA-SQL task node.
   This task node can be used to query, process, and analyze the data in the data warehouse.
   1. Right-click the DLA-SQL task node on the canvas and select Rename. Modify the name of the task node and press the Enter key. In this example, set the name to DLA-SQL-day.
   2. Click the DLA-SQL task node on the canvas. In the right-side pane, click the Content tab. Then, enter SQL statements for generating an analysis report.
      You can use the following sample SQL statements:

      CREATE EXTERNAL TABLE IF NOT EXISTS `rds_mysql_0723`.`adjust_day` (
        `id` DOUBLE COMMENT '',
        `auditon` TIMESTAMP COMMENT '',
        `returnmemo` STRING  COMMENT '',
        `issystem` DOUBLE COMMENT ''
      )
      STORED AS `PARQUET`
      LOCATION 'oss://xxx/DLA/DLA-Formation-rds_mysql_0723/${weekday}/adjust_day/'
      TBLPROPERTIES (
        'auto.create.location' = 'true',);
      
      INSERT OVERWRITE adjust_day
      SELECT
      id,
      auditon,
      returnmemo,
      issystem
      FROM adjust_source;

      

      Note After you enter SQL statements and configure variables, you can click SQLPreview to check whether the configurations meet your expectation. After you confirm that the current configurations meet your expectation, click Save.
10. On the canvas, draw a line from the DLA one-click DW task node to the DLA-SQL-day task node.
    
11. After you connect the task nodes, turn on Turn on/off on the Scheduling tab in the right-side pane. Set the parameters and click Save.
    Note You can schedule a task flow to be run by month, week, or day based on your needs. In this example, the DLA analysis task flow is scheduled to be run at 05:00 every business day.