Streamlining Data Updates/Inserts with Bulk Upsert Flows in Data Migrator

[Greg Weinheimer]

Introduction:

Data Migrator offers a powerful "bulk upsert" feature to simplify and accelerate the process of updating/inserting large volumes of data in your target system. This article provides a guide on how to leverage this capability.

What is Bulk Upsert?

Bulk upsert is a data operation that combines the actions of "insert" and "update" into a single process. It allows you to efficiently add new records to your target database or update existing records based on a defined matching condition.

Benefits of Bulk Upsert Flows in Data Migrator:

• Efficiency: Process large volumes of data updates in a single operation, reducing processing time and resource usage.
• Data Integrity: Ensure data consistency by either adding new records or updating existing ones based on matching criteria.
• Flexibility: Customize your upsert flows to handle various data scenarios and integration requirements.
• Automation: Schedule and automate your bulk upsert flows for regular data updates, saving time and effort.

 

Step-by-Step Guide:

   1.  Prerequisites:

• Configure adapter for Data Source.

            From Get Data, on the web console, select the appropriate adapter and configure.

• Create Synonyms for source and target data.

            Right click on the adaptor configuration you just created and select Show DBMS Objects.

           

 

           Select the tables that you are using to create the flow, select the Application folder you want to create the  synonyms in and click add.               This will create the synonyms in the selected application folder(in this case demo/synonyms).

 

 

  2.  Create a New Flow:

• Navigate to the folder you want to create the new flow in( in this case  demo/flows), right click the folder, select New then Flow.

• Drag the source synonym to the left side of the flow and the target synonym to the right hand of the flow.

• Right click the SQL object and select Edit. Select all fields you want to load to the target table by dragging them from the left side to columns.

 

• Map source fields to target fields. Right click the Target synonym, select merge editor, Map the source to target fields. Click ok.
• Configure load type. Right click the Target synonym, select load options. Select Load Option of ‘Merge into Existing’. Change other settings to match your desired results. In this case existing records will be updated and new records will be inserted. This will perform a bulk upsert into the target table. Save as flow with desired name. In this case df_ld_bulk_merge_1 

 

  3.  Execute the Flow:

• Run the bulk upsert flow to process your data updates. Navigate to the folder that the flow is in, right click the flow, select Run Advanced and then Submit with E-Mail. 

• View logs of the Flow. Right click the flow and select Logs/Last Log.

 

• 

This will display the details for this flow.  In this case 2488361 records were inserted into the target table. We also see that the elapsed time was 9.822 seconds.

When we submit this flow a second time we see that this flow updated 2488361 records and ran in 9.789 seconds.

 

Conclusion:

Using bulk upsert in Data Migrator provides a powerful tool for streamlining your data loading processes. It allows for far more efficient processing of data than was previously available.

 

Note:

Adaptors that support bulk update:

• Salesforce
• Vertica
• Sybase
• Snowflake
• PostgreSQL
• Netezza
• MariaDB
• Informix
• Hyperstage (PG)
• Greenplum DB
• Google BigQuery
• EXASol
• Apache Hive
• Amazon Redshift
• Teradata
• Oracle
• MySQL
• MS SQL Server OLE DB/AzureDB
• MS SQL Server ODBC/AzureDB
• MS Azure Synapse Analytics (formerly SQL DW)