Bulk Data Transfer from Oracle to Cassandra with NiFi and Automatic Table Creation Using Avro Schemas

[#1]

Hi everyone,

I'm new to NiFi and I'm trying to ingest data from more than 20 tables in an Oracle database into Cassandra. I have a few questions regarding the process:

1. Can I transfer data from all the tables at once, without having to process each table individually? If so, what is the best approach to achieve this in NiFi?

2. Is there a method to automatically create tables in Cassandra using Avro schemas within the NiFi flow? This is particularly important because in some use cases, we need to overwrite the table instead of appending data. How can we handle this scenario efficiently?

[#2]

1. Transferring Data from Multiple Tables in NiFi:

   • NiFi provides processors that allow you to pull data from database tables using JDBC drivers. For Oracle, you can use processors like ExecuteSQL, QueryDatabaseTable, and GenerateTableFetch.
   • To transfer data from multiple tables, consider the following approaches:
     • Individual Flows for Each Table: You can create separate NiFi flows for each table. This approach is straightforward but may require more management.
     • Dynamic SQL Generation: Use the ListDatabaseTables processor to list tables dynamically. Then, use ReplaceText to create SQL statements for each table (using NiFi Expression Language). Finally, send these statements to ExecuteSQL for fetching data.
     • Parallel Fetching: If you have a NiFi cluster, route GenerateTableFetch into a Remote Process Group pointing at an Input Port on the same cluster. 

2. Automatic Table Creation in Cassandra Using Avro Schemas:

   • To create tables in Cassandra, you can use the PutCassandraRecord processor. It allows you to put data directly into Cassandra without writing CQL.
   • For schema management, consider using Avro schemas. You can define Avro schemas for your data and use them within your NiFi flow.
   • To handle overwriting tables, you’ll need to manage the logic in your flow. 

Can Nifi load data from DB2 to Cassandra? - Stack Overflow