The extract, transform, and load (ETL) feature in Data Transmission Service (DTS) includes three transformation components you can add to an ETL task canvas: JOIN, Field Calculator, and Table Record Filter. This topic describes how to configure each component.
Limitations
| Component | Limitation |
|---|---|
| JOIN | JOIN operations are supported between two tables only. |
| Field Calculator | Single table schema only. |
| Table Record Filter | Single table schema only. |
Prerequisites
Before you begin, ensure that you have:
A source database configured. For details, see Configure source databases.
Get to the ETL canvas
All three components are configured from an ETL task canvas. To open it:
Go to the ETL page.
You can also reach this page from the Data Management (DMS) console: click DTS in the top navigation bar, then choose Data integration > Streaming ETL in the left-side navigation pane. Click
, enter a name in the Data Flow Name field, set Development Method to DAG, and click OK.In the upper-left corner, select the region for your ETL task.
ETL tasks can only be created in the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Guangzhou).
In the left-side navigation pane, click ETL.
On the ETL page, click Create Task (Pay-as-you-go).
Configure the JOIN component
The JOIN component merges data from two stream tables. It supports inner join, left join, and right join operations, including temporal joins against dimension tables.
In the Transform section on the left side of the page, drag JOIN onto the canvas.
Move the pointer over the destination database, then click the dot to draw a line connecting the destination database to the JOIN component.
Click JOIN on the canvas.
In the Transform JOIN section, click the Node Settings tab and configure the following parameters.
Temporal joins require time attributes to be defined on all stream tables. The right table must have a primary key. If the right table is a dimension table, the primary key must be included in the join condition.
Section Parameter Description Transformation Name Enter Transformation Name Enter a descriptive name for easy identification. The name does not need to be unique. JOIN Settings Left Table in JOIN Clause Select the left table for the JOIN operation. JOIN Settings Temporal Join Time Attribute Select the time attribute for temporal joins. Default value: Join. Options: Based on Event Time Watermark or Based on Processing Time. JOIN Settings Select JOIN Operation Select a join type. See the table below for a description of each type. JOIN Conditions + Add Condition Click + Add Condition to add join conditions. Fields on the left side of the equal sign (=) belong to the left table; fields on the right side belong to the right table. JOIN operation types:
Operation Output Inner join The data is the intersection of two tables. Left join The data in the left table remains unchanged. The data in the right table is the intersection of two tables. Right join The data in the left table is the intersection of two tables. The data in the right table remains unchanged. 
After configuring the join conditions, click the Output Fields tab. Select the column names to include in the output and set the related parameters.
Configure the Field Calculator component
The Field Calculator component derives new fields from existing data using expressions. Use it to compute values and write the result to a destination field.
Go to the Data Migration page of the new DTS console.
You can also reach this page from the Data Management (DMS) console: click DTS in the top navigation bar, then choose DTS (DTS) > Data Migration in the left-side navigation pane.
In the upper-left corner, select the region for your ETL task.
ETL tasks can only be created in the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Guangzhou).
In the left-side navigation pane, click ETL.
On the ETL page, click Create Task (Pay-as-you-go).
In the Transform section on the left side of the page, drag Field Calculator onto the canvas.
Move the pointer over the destination database, then click the dot to draw a line connecting the destination database to the Field Calculator component.
Click Field Calculator on the canvas.
In the Transform Field Calculator section, configure the component.
In the Conversion Name section, enter a name for the component. The name does not need to be unique.
In the Calculator Settings section, click + Add Field.
Find the added field and click Calculator Settings in the Actions column.
In the Value Settings section, configure the following parameters.
Parameter Description Field name DTS generates a default name. Enter a descriptive name for easy identification. The name does not need to be unique. Select ETL Data Type Select the output data type for the calculated field. Destination Field: *Field Name*, Value Settings Specify the calculation expression for the destination field. Enter the expression directly, or build it by clicking options in the Field Input, Function Set, or Operator sections. 
Configure the Table Record Filter component
The Table Record Filter component filters rows based on a condition, similar to a SQL WHERE clause. Only rows that match the condition are passed to the destination database.
Go to the Data Migration page of the new DTS console.
You can also reach this page from the Data Management (DMS) console: click DTS in the top navigation bar, then choose DTS (DTS) > Data Migration in the left-side navigation pane.
In the upper-left corner, select the region for your ETL task.
ETL tasks can only be created in the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Guangzhou).
In the left-side navigation pane, click ETL.
On the ETL page, click Create Task (Pay-as-you-go).
In the Transform section on the left side of the page, drag Table Record Filter onto the canvas.
Move the pointer over the destination database, then click the dot to draw a line connecting the destination database to the Table Record Filter component.
Click Table Record Filter on the canvas.
In the Transform Table Record Filter section, configure the component.
In the Transformation Name section, enter a name for the component. The name does not need to be unique.
In the WHERE Condition section, specify the filter condition. Enter the condition directly, or build it by clicking options in the Field Input or Operator sections.
Verify configuration
After you configure a transformation component, the 
icon appears on the right side of the component on the canvas.