Skip to main content

Lightweight ETL Sync: Process Data Before Loading to the Target Database | NineData

When source data needs to be filtered, mapped, calculated, or transformed before it is written to the target database, traditional migration tools often fall short. NineData fits lightweight ETL scenarios such as transformed migration, cleansed loading, and heterogeneous synchronization.

When to use this workflow​

  • Cleansed loading: Filter invalid values, remove unwanted characters, or normalize field formats.
  • Field transformation and mapping: Transform date, string, JSON, and numeric fields before writing to the target.
  • Heterogeneous synchronization: Use expressions to reduce extra development work when source and target models differ.

Use ETL when source data needs to be extracted, transformed, and then loaded into the target system. Common cases include data cleaning, date standardization, field extraction, JSON processing, and column reshaping.

NineData Data Replication supports ETL and provides more than 300 ETL functions for common string, time, JSON, numeric, and column operations. The example below shows how to trim a timestamp down to the date part before loading it into the target.

Before you begin​

  • Make sure you can access the NineData console and create data sources and replication tasks.
  • Prepare the source and target table fields that need filtering, mapping, calculation, or transformation.
  • Confirm the expected transformed value before you configure the ETL expression.

Step 1: Add Data Sources​

Add the source and target data sources to NineData.

image-20240719152341194

  1. Sign in to the NineData Console, click Data Replication > Data Replication, and then click Create Replication.

    image-20240319150313727

  2. When the task configuration process reaches the Mapping Configuration step, click Mapping and Filtering next to the target table.

    image-20240808094319141

  3. To truncate the created_date column to year, month, and day and discard the hour, minute, and second values, use the built-in function to transform this column. The example below shows the ETL configuration.

    image-20240809103724646

    1. Click the Field Expression text box in the middle of the target field.
    2. In the dialog, find the function you want to use, and copy the function body. In this example, dateutils_truncate is selected.
    3. Paste the function body into the expression box after the equal sign (=), and configure the parameters. This function accepts two parameters: the time field name and the time precision. In the example, dateutils_truncate(created_date, 5) truncates the created_date field to year, month, and day.
    4. Click Submit.

  4. After the configuration is complete, click OK in the lower-right corner to finish the ETL settings. When the task starts, NineData processes the source created_date value with this ETL rule and loads the result into the target.

    Source image-20240809105230551

    Target image-20240809105300798

Result​

After migration, the target keeps the date at year, month, and day precision. In an incremental task, newly written created_date values are transformed the same way, so the ETL flow can keep running in a stable, unattended way.

The same pattern applies to other ETL functions for string, time, JSON, and numeric transformations.

iShot_2024-08-09_14.55.36