Updated on 2026-11-12 GMT+08:00

Using PatchData

Scenario

In the migration of a project, if you want to supplement historical business data in a previous period and view details of the historical data, PatchData can meet your requirements.

A job executes a scheduling task to generate a series of instances in a certain period of time. This series of instances are called PatchData. PatchData can be used to fix the job instances that have data errors in the historical records or to build job records for debugging programs.

  • In addition to SQL scripts, PatchData supports other nodes.
  • If the content of a SQL script changes, the PatchData job runs the latest script.
  • When you use PatchData, if the variable in the SQL statement is DATE, enter ${DATE} in the script. The script parameter DATE is then automatically added to the job parameters, and its value can be an EL expression. If the variable is a time variable, enter the expression of the DateUtil embedded object. The platform automatically converts the expression into a historical date. For details about how to use EL expressions, see EL Expressions.
  • PatchData jobs support script parameters and global environment variables as well as job parameters.

Constraints

PatchData is available only when periodic scheduling is configured for the data development job.

Example

Scenario

Among the product data tables of a company, there is a source data table A that records the product sales amount. To import the historical product sales amount to the destination table B, you can create a PatchData job.

Table 1 lists the source and destination tables.
Table 1 Source and destination tables

Source Table

Destination Table

A

B

Procedure

  1. Prepare the source and destination tables. To facilitate subsequent job execution and verification, you need to create a source DWS table and a destination DWS table and insert data into the tables.
    1. Create a DWS table. You can create a DWS SQL script on the DataArts Factory console of DataArts Studio and run the following SQL statements:
      /* Create tables. */
      CREATE TABLE A (PRODUCT_ID INT, SALES INT, DATE DATE);
      CREATE TABLE B (PRODUCT_ID INT, SALES INT, DATE DATE);
    2. Insert sample data into the source data table. You can create a DWS SQL script on the DataArts Factory console of DataArts Studio and run the following SQL statements:
      /* Insert sample historical data into the source table. */
      INSERT INTO A VALUES ('1','60', '2022-03-01');
      INSERT INTO A VALUES ('2','80', '2022-03-01');
      INSERT INTO A VALUES ('1','50', '2022-02-28');
      INSERT INTO A VALUES ('2','55', '2022-02-28');
      INSERT INTO A VALUES ('1','60', '2022-02-27');
      INSERT INTO A VALUES ('2','45', '2022-02-27');
  2. Develop a PatchData script. Ensure that the script expression contains a time variable. (For example, if the variable in the SQL statement is DATE, enter ${DATE} in the script.) You can set the expression for script parameter DATE in job parameter settings in 3.
    On the Develop Script page, enter following statement in the editor:
    INSERT INTO B SELECT * FROM A WHERE DATE = ${DATE};
    Figure 1 Developing a script

    After compiling the script, save it and submit the latest version.

  3. Develop a PatchData batch processing job. When developing the job, you need to configure the node attributes and scheduling period.

    In the left navigation pane of the DataArts Factory console, choose Data Development > Develop Job.

    Figure 2 Node parameters

    • If the job-associated SQL script uses a parameter, the parameter name (such as DATE) is displayed. Set the parameter value in the text box next to the parameter name. The parameter value can be an EL expression. For details about EL expressions, see Expression Overview.

      If the parameter is time, view the example expression of the DateUtil embedded object. The platform automatically replaces the parameter with the historical date of the patch data (determined by the service date of the patch data).

      You can also directly enter a SQL expression.

    • If the parameters of the associated SQL script change, you can click to synchronize the change or click to edit the parameters.
    • The following is an example of script parameters:
      Example: #{DateUtil.format(DateUtil.addDays(Job.planTime,-1),'yyyy-MM-dd')}
      • Job.planTime indicates the planned job time, and yyyy-MM-dd indicates the time format.
      • If the planned job time is March 2, the previous day is March 1. The planned job time will be replaced by the configured patch data service date.
      • The Job.planTime is converted into a time in the yyyy-MM-dd format using an expression.

    Configure the scheduling period of the PatchData job. Click Scheduling Setup and set Scheduling Frequency to Every day.

    Figure 3 Configuring the scheduling period

    • If Scheduling Frequency is set to Every day, the job is scheduled every day, and a PatchData instance is generated. You can view the statuses of PatchData instances on the Monitor Instance page. On the Monitor Instance page, view the instance information about the job and perform more operations on instances as required.
    • The job scheduling time takes effect from March 9, 2023, and the job is scheduled at 02:00 every day.
    • Run the following SQL statement to check whether destination table B contains data of source table A:

      SELECT * FROM B

    After configuring the parameters, save and submit the latest version of the job and test the job.

    Click Execute to run the job.

  4. Create a PatchData task.
    After creating a periodic job, you need to configure PatchData for the job.
    1. In the left navigation pane of DataArts Factory, choose Monitoring > Job Monitoring.
    2. Click the Batch Job Monitoring tab. In the Operation column of the job, choose More > Configure PatchData. The Configure PatchData page is displayed.
      If you want to supplement historical data from February 27, 2023 to March 1, 2023, set Date to Feb 28, 2023 00:00:00 – Mar 02, 2023 23:59:59. The system automatically transfers the configured date to the planned job time. In the expression of the script time variable DATE, the defined time is the planned job time minus one day. That is, the time of the day before the planned job time is the time range (Feb 27, 2023 to Mar 1, 2023) for PatchData.
      Figure 4 Configuring PatchData
      Table 2 Description

      Parameter

      Description

      PatchData Name

      Name of the automatically generated PatchData task. The value can be modified.

      Job Name

      Name of the job that requires PatchData, which is automatically displayed

      Scheduling Time Type

      • Consecutive date range

        The PatchData time is a continuous date range.

      • Discrete date ranges

        The PatchData time consists of discrete date ranges.

      Date

      If Scheduling Time Type is set to Consecutive date range:

      This parameter indicates the period of time when PatchData is required. If the date is later than the current time, the current time is displayed by default.

      NOTE:

      PatchData can be configured for a job multiple times. However, avoid configuring PatchData multiple times on the same date to prevent data duplication or disorder.

      If you select Patch data in reverse order of date, the patch data of each day is in positive sequence.

      NOTE:
      • This function is applicable when the data of each day is not coupled with each other.
      • The PatchData job will ignore the dependencies between the job instances created before this date.

      If Scheduling Time Type is set to Discrete date ranges:

      You also need to set the following PatchData parameters:

      You can click Add Date Range to add multiple discrete date ranges for PatchData. You must set at least one date range.

      You can click Delete to delete discrete date ranges.

      NOTE:

      DataArts Studio does not support concurrent running of PatchData instances and periodic job instances of underlying services (such as CDM and DLI). To prevent PatchData instances from affecting periodic job instances and avoid exceptions, ensure that they do not run at the same time.

      Run PatchData Tasks Periodically

      • Yes: PatchData jobs will be executed based on the configured period.

        The first value indicates a specific value.

        The second value indicates that data is patched based on a specified period, for example, hours, days, weeks, or months.

        NOTE:

        If you set a period, PatchData tasks will be scheduled based on that period. If the job is scheduled every few minutes, hours, or days, PatchData tasks will be scheduled based on the period you set. For example, if you want to patch data from 00:00 on Jan 1, 2023 to 00:00 on Feb 1, 2023 for an hourly job that starts at 01:00 every day, and set the PatchData period to two days, PatchData tasks will be scheduled at 00:00 on Jan 1, 2023, 00:00 on Jan 3, 2023, 00:00 on Jan 5, 2023, and so on. If the PatchData task scheduling period is in months and the first scheduling date falls on the last day of a month, PatchData tasks will be scheduled on the last day of each month.

      • No: PatchData jobs will not be executed periodically. Instead, the system executes PatchData jobs based on the existing rule.

      Cycle

      This parameter is required when Scheduling Time Type is set to Discrete date ranges.

      It specifies the PatchData cycle.

      You can click Viewing Scheduling Details to view the execution time of the task instances in the current time segment.

      NOTE:

      This parameter is required only when a job is scheduled by hour or minute and Scheduling Time Type is set to Discrete date ranges.

      Parallel Periods

      Number of instances to be executed at the same time. A maximum of five instances can be executed at the same time.

      NOTE:

      Set this parameter based on the site requirements. For example, if a CDM job instance is used, data cannot be supplemented at the same time. The value of this parameter can only be set to 1.

      Upstream or Downstream Job

      This parameter is optional. Select the upstream and downstream jobs (of the current job) that require PatchData. You can select multiple jobs.

      Patch Data by Day

      If you select Yes, PatchData instances on the same day can be executed concurrently for a job, but those on different days cannot be executed concurrently. For example, a job instance scheduled at 5:00 and one scheduled at 6:00 can be executed concurrently, but a job instance scheduled on 1st of a month and one scheduled on 2nd of the month cannot be executed concurrently.

      Yes: Data is patched by day.

      No: Data is not patched by day.

      Stop Upon Failure

      This parameter is mandatory if Patch Data by Day is set to Yes.

      Yes: If a daily PatchData task fails, subsequent PatchData tasks stop immediately.

      No: If a daily PatchData task fails, subsequent PatchData tasks continue.

      NOTE:

      If data is patched by day and a PatchData task fails on a day, no PatchData task will be executed on the next day. This function is supported only by daily PatchData tasks, and not by hourly PatchData tasks.

      Priority

      Select a PatchData priority. The default value is None. You can also set the priority of the workspace-level PatchData job by referring to Setting PatchData Priority.

      After you set priorities for PatchData jobs, when system resources are insufficient, compute resources are preferentially allocated to jobs with higher priorities. A larger number indicates a higher priority.

      NOTE:

      The priority of PatchData is higher than that of PatchData in the workspace.

      Priorities can be set only for DLI SQL operators.

      The mapping between the PatchData priority and spark.sql.dli.job.priority in DLI is as follows:

      • If the PatchData priority is set to 1, spark.sql.dli.job.priority in DLI is 1.
      • If the PatchData priority is set to 2, spark.sql.dli.job.priority in DLI is 3.
      • If the PatchData priority is set to 3, spark.sql.dli.job.priority in DLI is 5.
      • If the PatchData priority is set to 4, spark.sql.dli.job.priority in DLI is 8.
      • If the PatchData priority is set to 5, spark.sql.dli.job.priority in DLI is 10.

      Ignore OBS Listening

      • Yes: OBS listening is ignored in PatchData scenarios.
      • No: The system listens to the OBS path in PatchData scenarios.

      Job Execution Time Range

      Whether an execution period can be set for the PatchData job

      • Yes

        You can set the time period on each day for running the PatchData job.

      • No
    3. Click OK. The system starts to run the PatchData task based on the configured scheduling period.
    4. On the Monitor PatchData page, you can view the PatchData task status, date, number of parallel periods, PatchData job name, and stopped tasks. You can also view logs of the PatchData task.
      Figure 5 Querying PatchData details

    5. Run the following SQL statement to check whether destination table B contains historical data of source table A:
      SELECT * FROM B