Expression Use Example

Context

Use the job orchestration and job scheduling functions to generate daily transaction statistics reports according to transaction details tables.

The tables involved in this example are as follows:

• trade_log: This table records data generated in each transaction.
• trade_report: This table is generated based on trade_log and records the daily transaction summary.

Prerequisites

• A DLI data connection named dli_demo has been created.

  If this data connection is not created, create one. For details, see Creating Data Connections.

• A database named dli_db has been created in DLI.

  If this database is not created, create one. For details, see Creating a Database.

• Tables trade_log and trade_report have been created in the dli_db database.

  If the tables are not created, create them. For details, see Creating a Table.

Procedure

1. Create and develop a SQL script.
   1. In the navigation tree of the DataArts Factory console, choose Data Development > Develop Script.
   2. Access the area on the right and choose Create SQL Script > DLI.
   3. Go to the SQL script development page and set the data connection, database, and resource queue on the script property bar.
   4. Enter the following SQL statements in the script editor:

      INSERT OVERWRITE TABLE trade_report
      SELECT
        sum(trade_count),
        '${yesterday}'
      FROM
        trade_log
      where
        date_format(trade_time, 'yyyy-MM-dd') = '${yesterday}'

   5. Click and set the script name to generate_trade_report.

2. Create and develop a job.
   1. In the navigation tree of the DataArts Factory console, choose Data Development > Develop Job.
   2. Access the area on the right and click Create Job to create an empty job named job.
   3. Go to the job development page, drag the DLI SQL node to the canvas, click the icon, and configure node properties.

      Description of key properties:

      • SQL Script: SQL script generate_trade_report that is developed in 1.
      • Database Name: Database configured in SQL script generate_trade_report.
      • Queue Name: Resource queue configured in SQL script generate_trade_report.
      • Script Parameter: Parameter yesterday configured in SQL script generate_trade_report. Enter the following EL expression as the parameter values:

        #{Job.getYesterday("yyyy-MM-dd")}

        Expression Description: The job object uses the getYesterday method to obtain the time of the day before the job plan execution time. The time format is yyyy-MM-dd.

        If the job plan time is 2018/9/26 01:00:00, the calculation result of this expression is 2018-09-25. The calculation result will replace the value of parameter ${yesterday} in the SQL script. The SQL statements after the replacement are as follows:

        INSERT OVERWRITE TABLE trade_report
        SELECT
          sum(trade_count),
          '2018-09-25'
        FROM
          trade_log
        where
          date_format(trade_time, 'yyyy-MM-dd') = '2018-09-25'

   4. Click to test the running job.
   5. After the job test is complete, click to save the job configuration.