This topic describes how to configure the transformation components supported by the extract, transform, and load (ETL) feature of Data Transmission Service (DTS).

Background information

The ETL feature supports the following transformation components:
  • JOIN
  • Field Calculator
  • Table Record Filter

Limits

The transformation components supported by the ETL feature have the following limits:
  • JOIN: You can perform JOIN operations only between two tables.
  • Field Calculator and Table Record Filter: Only a single table schema is supported.

Prerequisites

The source database is configured.

Configure the JOIN component

  1. Go to the Data Migration page of the new DTS console.
    Note You can also log on to the Data Management (DMS) console. In the top navigation bar, choose DTS > Data Migration.
  2. In the upper-left corner of the page, select the region where you create the ETL task.
    Note You can create an ETL task only in the China (Hangzhou) or China (Beijing) region. Select a region based on the actual scenario.
  3. In the left-side navigation pane, click ETL.
  4. On the ETL page, click Create Task (Pay-as-you-go).
  5. Configure the source database. For more information, see Configure a source database.
  6. In the Transform section on the left side of the page, select JOIN and drag it to the canvas on the right side of the page.
  7. Move the pointer over the destination database, and click the dot to draw a line between the destination database and the JOIN component.
  8. Click JOIN on the canvas.
  9. In the Transform: JOIN section, configure the JOIN component.
    1. On the Node Settings tab, set the required parameters.
      Transform: JOIN_Node Settings
      Section Parameter Description
      Transformation Name Enter Transformation Name We recommend that you specify an informative name for easy identification. You do not need to use a unique name.
      JOIN Settings Left Table in JOIN Clause Select the left table in the JOIN clause.
      Temporal Join Time Attribute Select the time attribute of temporal joins. Default value: Join.
      • Based on Event Time Watermark
      • Based on Processing Time
      Note A temporal join requires that time attributes must be defined for all stream tables, and the right table must have a primary key. If the right table is a dimension table, the primary key must be contained in the equal condition.
      Select JOIN Operation Select a JOIN operation.
      • 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.
      JOIN Conditions + Add Condition Click + Add Condition and select JOIN conditions from multiple data sources.
      Note The fields on the left side of the equal sign (=) belong to the left table of the data source. The fields on the right side of the equal sign (=) belong to the right table of the data source.
    2. After you configure the JOIN conditions, click the Output Fields tab. On this tab, select the column names based on your needs and set the related parameters.
      Transform: JOIN_Output Fields

Configure the Field Calculator component

  1. Go to the Data Migration page of the new DTS console.
    Note You can also log on to the Data Management (DMS) console. In the top navigation bar, choose DTS > Data Migration.
  2. In the upper-left corner of the page, select the region where you create the ETL task.
    Note You can create an ETL task only in the China (Hangzhou) or China (Beijing) region. Select a region based on the actual scenario.
  3. In the left-side navigation pane, click ETL.
  4. On the ETL page, click Create Task (Pay-as-you-go).
  5. Configure the source database. For more information, see Configure a source database.
  6. In the Transform section on the left side of the page, select Field Calculator and drag it to the canvas on the right side of the page.
  7. Move the pointer over the destination database, and click the dot to draw a line between the destination database and the Field Calculator component.
  8. Click Field Calculator on the canvas.
  9. In the Transform: Field Calculator section, configure the Field Calculator component.
    Transform: Field Calculator
    1. In the Transformation Name section, enter the name of the Field Calculator component.
      Note We recommend that you specify an informative name for easy identification. You do not need to use a unique name.
    2. In the Calculator Settings section, click + Add Field.
    3. Find the added field and click Calculator Settings in the Actions column.
    4. In the Value Settings section, set the following parameters.
      Parameter Description
      Field Name DTS automatically generates a field name. We recommend that you specify an informative name for easy identification. You do not need to use a unique field name.
      Select ETL Data Type Select the ETL data type.
      Destination Field: Field Name, Value Settings Specify a calculation method for the destination field by using one of the following methods:
      • Enter the calculation method.
      • Click an option in the Field Input, Function Set, or Operator section to specify the calculation method.

Configure the Table Record Filter component

  1. Go to the Data Migration page of the new DTS console.
    Note You can also log on to the Data Management (DMS) console. In the top navigation bar, choose DTS > Data Migration.
  2. In the upper-left corner of the page, select the region where you create the ETL task.
    Note You can create an ETL task only in the China (Hangzhou) or China (Beijing) region. Select a region based on the actual scenario.
  3. In the left-side navigation pane, click ETL.
  4. On the ETL page, click Create Task (Pay-as-you-go).
  5. Configure the source database. For more information, see Configure a source database.
  6. In the Transform section on the left side of the page, select Table Record Filter and drag it to the canvas on the right side of the page.
  7. Move the pointer over the destination database, and click the dot to draw a line between the destination database and the Table Record Filter component.
  8. Click Table Record Filter on the canvas.
  9. In the Transform: Table Record Filter section, configure the Table Record Filter component.
    Transform: Table Record Filter
    1. In the Transformation Name section, enter the name of the Table Record Filter component.
      Note We recommend that you specify an informative name for easy identification. You do not need to use a unique name.
    2. In the WHERE Condition section, specify a WHERE condition by using one of the following methods:
      • Enter the WHERE condition.
      • Click an option in the Field Input or Operator section to specify the WHERE condition.

Result

After a transformation component is configured, the Configured icon appears on the right side of the transformation component.

What to do next

Configure the destination database