2.2.3 Migration using Apache NiFi

Migration form MySQL to MongoDB using Apache NiFi Dataflow.

This page explores the option of migrating data from MySQL to MongoDB using Apache NiFi.

Data Migration from Mysql to MongoDB using Apache-Nifi:

Installing Apache-Nifi:

• Download Apache-Nifi. Only download the binaries.

• Extract it anywhere, it's path independent.

• After Extraction is complete navigate to [Extract Location]\nifi-1.7.1-bin\nifi-1.7.1\conf and open the Properties file "nifi.properties". We would need to edit some attributes in this file.

• Before editing make sure you have JAVA Home Path defined as your Environment Variable, if not already do it.

• Open "nifi.properties":

  • Edit: nifi.ui.banner.text = [ Put whatever name you like ]
  • Edit: nifi.web.http.port = 8080 (or whatever port is free).
  • Edit: nifi.sensitive.props.key = (Set up a pass key of Your Choice).
  • After Editing all above three properties Save & Close.

• Navigate to [Extract Location]\nifi-1.7.1-bin\nifi-1.7.1\bin and right click on run-nifi and "Run as Administrator".

NOTE: It takes time for Nifi to start so be advised not to try and open moments after executing it. Wait for few minutes and then Start.

• You can see the status of Nifi by executing status-nifi.

• To start Nifi:

  • Open any web-browers and type localhost:[Port Number you set in the nifi.web.http.port] For example: http://localhost:8080/nifi/

Creating First Simple one table-to-collection Migration.

• Before we start creating out Migration Template we need to Configure our Database connections for both MySQL and MongoDB:

  • Right Click on the Workspace and click on Configure.

  • On the NiFi Flow Configuration page under Control Services click on the +.

  • Select DBCPConnectionPool for MySQL and later after Configuring it Select MongoDBControllerService for MongoDB.

  

  • Click on the Settings Button on the right:

  • Follow the Configuration settings for both MySQL and MongoDB from the images below:

  • After you are done configuring the Database Controller Services it will automatically validate for connection and Enable it.

• Drag and Drop Processor onto the Wokrspace and Add an ExecuteSQL processor.

• Configure ExecuteSQL, Select MySQLConnection as the value for Database Connection Pooling Service and the Add a SQL Query for the Data pull:

• The Output of the ExecuteSQL component will be a Avro Flat File which needs to be converted into JSON before moving it to MongoDB Collection.

• Add a ConvertAvroToJSON component for the conversion process.

• Now we have all the Data pulled from MySQL Table in a JSON Format. We need to put the Record in MongoDB. To Achieve that we need Couple of Reader and Writer components.

• To add an AvroSchemaRegistry which would have the Schema of the JSON records - Right Click and Configure. Under Controller Service add a AvroSchemaRegistry service. Add a Schema and give it a name of your Choice. To convert the JSON into Avro Schema use this tool: JSON-to-Avro

• Add a JsonTreeReader and configure. Add the name of the schema you created in the above step.

• The final list of Controller Services will look like this.

• Finally add a PutMongoRecord componenet which is used to bulk insert into a MongoDB Collection.

• Configure the PutMongoRecord component. Set the Property Record Reader to the JsonTreeReader which it will use to parse the incoming JSON File.

• Final Migration Template will look something like this.

• To run the Migration click on the Play button:

• After the Migration is Successful all the component will turn Green.