Modes for Writing Data to DataArts Fabric Using SQL Statements

Overview

DataArts Migration provides two modes for writing upstream data to DataArts Fabric using SQL statements: Insert Into and Insert Overwrite. In both modes, FabricWriter uses a two-level commit mechanism, including file flushing to disks and data import to atomic libraries. First, a temporary Fabric table is created, and upstream data is written to the Huawei Cloud OBS directory in the file format corresponding to the temporary table. Then, the INSERT/OVERWRITE SQL statement is executed to load the data in the temporary table to the formal FabricSQL table in transaction mode at a time. Data is directly written to OBS files, which provides excellent write performance and achieves high-throughput batch data integration that features less table locking and can be rolled back.

Modes for Writing Data to Fabric

• Insert Into mode

  This is a basic write mode of Fabric. In this mode, data is appended to a destination Fabric table, and existing data in the table is not cleared.

  • Characteristics
    • Destination table not to be cleared: The destination table is not cleared before data is written to it. New data is appended to the table.
    • Incremental write: This mode is suitable for adding new data to a destination table that already contains data.
  • Example

    The source table is the MySQL table named data, the destination table is a Fabric table named data, and the partition field is dt.

    You can configure a job to write incremental data in the MySQL table to the Fabric table. Fabric appends the data to the corresponding partition based on the partition field dt.

    Figure 1 Configuring source and destination tables
    

    Source MySQL table

    CREATE TABLE `data` (  `id` varchar(10) DEFAULT NULL,  `dt` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Destination Fabric table

    create table if not exists "default"."data"("id" varchar(10))STORE AS PARQUET PARTITION BY ("dt" date)

• Insert Overwrite mode

  Data is first written to a temporary table, and then the data in the temporary table is written to the destination table using the INSERT OVERWRITE syntax of Fabric and overwrites existing data in the destination table.

  • Implementation

    This mode is implemented in Fabric through the following steps:

    1. Creating a temporary table: Create a temporary table to store data.
    2. Writing data to the temporary table: Write data to the temporary table.
    3. Overwriting existing data in the destination table: Use the INSERT OVERWRITE syntax to overwrite existing data in the destination table with the data in the temporary table.
  • Characteristics
    • Temporary table: The table temporarily stores data.
    • Overwriting existing data in the destination table: The INSERT OVERWRITE syntax of Fabric is used to overwrite existing data in the destination table with the data in the temporary table.
    • Writing data to dynamic and static partitions: The Insert Overwrite mode can write data to dynamic and static partitions.
      • Writing data to dynamic partitions: Values of partition fields in the source data are automatically identified and used to determine the destination partitions. Data in multiple partitions can be overwritten at a time. This meets your requirements for updating data in multiple partitions at the same time.
      • Writing data to static partitions: You need to specify the destination partition in the write operation. Data in only one partition can be overwritten at a time. This method is suitable for updating data in a specific partition.
  • Example

    The source table is the MySQL table named data, the destination table is a Fabric table named data, and the partition field is dt.

    Source MySQL table

    CREATE TABLE `data` (  `id` varchar(10) DEFAULT NULL,  `dt` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Destination Fabric table

    create table if not exists "default"."data"("id" varchar(10))STORE AS PARQUET PARTITION BY ("dt" date)

    • Static partition

      If you want to write all data in the MySQL table to the dt=2025-10-21 partition of the Fabric table, you can select the INSERT OVERWRITE mode and set the partition filter to dt=2025-10-21 in the write operation.

    • Dynamic partition

      If the MySQL table contains data from multiple partitions and you want the data to be automatically identified and to overwrite data in the destination partition based on the partition field value in the source data, you can select the Insert Overwrite mode. In this case, you do not need to specify the partition filter. You need to configure mapping of the dt partition field.

Summary

You can select the Insert Into or Insert Overwrite mode as needed to efficiently migrate and update Fabric data. You are advised to follow the recommendations in this practice to ensure data consistency and high system performance.