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.
Procedure
- 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.
- 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);
- 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');
- 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:
- 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.
- 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 .
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:
After configuring the parameters, save and submit the latest version of the job and test the job.
Click Execute to run the job.
- 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.
- Create a PatchData task. After creating a periodic job, you need to configure PatchData for the job.
- In the left navigation pane of DataArts Factory, choose .
- Click the Batch Job Monitoring tab. In the Operation column of the job, choose . 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
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
- Click OK. The system starts to run the PatchData task based on the configured scheduling period.
- 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
- Run the following SQL statement to check whether destination table B contains historical data of source table A:
SELECT * FROM B
What is your overall rating for this page?
Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot