How To Insert Data from One Database into Another Database Using Apache Nifi

Introduction: –

In modern data management, transferring data between different databases is a common requirement. Apache NiFi, an open-source data integration tool, provides a powerful and flexible solution for this task. This guide will explore how to insert data from one database into another using Apache NiFi, specifically focusing on Oracle Database (Oracle DB) and MySQL Database (MySQL DB).

Apache NiFi facilitates data flow automation through its intuitive graphical interface, enabling seamless data movement, transformation, and ingestion between diverse systems. By leveraging NiFi’s processors and connectors, users can efficiently configure and manage data transfers between Oracle DB and MySQL DB, ensuring data consistency and accuracy across different database platforms.

In this guide, we will cover:

1. Setting Up Apache NiFi: Installation and configuration of NiFi for your data integration needs.
2. Connecting to Oracle DB and MySQL DB: Configuring NiFi to connect to both Oracle and MySQL databases.
3. Extracting Data from Source Database: Using NiFi processors to query and extract data from the source database (e.g., Oracle DB).
4. Transforming and Mapping Data: Applying necessary transformations and mappings to ensure compatibility between different database schemas.
5. Inserting Data into Target Database: Using NiFi to insert the transformed data into the target database (e.g., MySQL DB).
6. Monitoring and Troubleshooting: Techniques for monitoring the data flow and addressing common issues.

By the end of this guide, you will have a clear understanding of how to leverage Apache NiFi for efficient data migration and integration between Oracle DB and MySQL DB, streamlining your data workflows and enhancing operational efficiency.

The following technologies have been used to achieve the same: –

Apache Nifi, Oracle DB, MySQL DB

 

Why we need to do: –

1. Data Integration Across Platforms
2. Efficient Data Migration
3. Improved Data Management
4. Scalability and Flexibility
5. Real-Time Data Processing
6. Reducing Data Integration Complexity
7. Automation and Monitoring

 

How do we solve: –

Following steps explains in detail,

Prerequisites for Inserting data

1. NiFi is installed and running.
2. Oracle and MySQL databases are set up and accessible from the NiFi instance.
3. JDBC drivers for both Oracle and MySQL are available and configured in NiFi.
4. SQL Developer or another tool for testing SQL queries.

Step 1: Add Oracle and MySQL JDBC Drivers to NiFi

1. Download JDBC Drivers:

Oracle: Download   ->  ojdbc8.jar

MySQL: Download  ->  mysql-connector-j-8.0.33.jar

2. Place JDBC Drivers in NiFi Library:

Copy the JAR files to Nifi Installed folder.

3. Restart NiFi: Restart NiFi to load the new drivers.

 

Step 2: Create a New Process Group

1. Open NiFi:

• Access the NiFi UI (usually at http://localhost:8443/nifi/login).

 

 

2. Create a Process Group:

• Right-click on the canvas and select “Create Process Group.”

• Name the group, for example, “Oracle to MySQL”.

 

Step 3: Add and Configure Processors

1. QueryDatabaseTableRecord Processors (To Query Oracle)

• Add Processor: Drag the QueryDatabaseTableRecord processor onto the canvas.

          

Configure the Processor:

Properties:

Database Connection Pooling Service: Create or select a DBCPConnectionPool configured for Oracle.

Table Name: Set your table name to select data from Oracle.

Record writer: Set CSV Record Set Writer

2. ConvertRecord Processors (To Covert Record)

• Add Processor: Drag the ConvertRecord processor onto the canvas.

Configure the Processor:

Properties:

Record Reader: Set CSV Reader

Record writer: Set CSV Record Set Writer

Relationships:

Failure: Choose to terminate

 

3. PuDatabaseRecord Processors (To Covert Record)

• Add Processor: Drag the PuDatabaseRecord processor onto the canvas.

 

Configure the Processor:

Properties:

Record Reader: Set CSV Reader

Statement Type: INSERT

Database Connection Pooling Service: Create or select a DBCPConnectionPoolForMYSQL connection service to configure for MySQL.

Schema Name: Set MySQL database name.

Table Name: Set MySQL table Name.

     

 

Relationships:

Failure: Choose to terminate

Retry: Choose to terminate

   

Note: At end of the processor, we can add funnel processor for getting the final output.

4. Create Connection Between Processors (To Connect all the processor)

 

4. Run Processors

Output: –

 

Conclusion: –

 

Using Apache NiFi, we can efficiently transfer data from one database to another by leveraging the QueryDatabaseTableRecord, ConvertRecord, and PutDatabaseRecord processors. This workflow enables seamless extraction, transformation, and loading (ETL) of data between databases, ensuring data consistency and integrity throughout the process. The QueryDatabaseTableRecord processor extracts data, ConvertRecord formats it as needed, and PutDatabaseRecord inserts the transformed data into the target database, making NiFi a powerful tool for database migration and synchronization tasks.