Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Read Configuration properties in QueryDatabaseTable

avatar
Contributor

Hi Team,

I started exploring Nifi. I created my first workflow to read the data from MySQL database and put that data into S3 bucket. So far everything is going fine and I'm able to read the data and place in S3. I hard coded the properties(Table Name, Database and few other) . Now I want to make workflow to run for multiple tables, so I need to read the properties from some configuration and pass table one by one to the workflow. I'm using QueryDatabaseTable processor to achieve the requirement. Could someone help me out on reading the comma separated table names and pass this table names one by one to QueryDatabaseTable as an argument to pull the data.

64494-screen-shot-2018-03-05-at-12043-pm.png

1 ACCEPTED SOLUTION

avatar
Master Guru

QueryDatabaseTable does not support incoming connections, so you wouldn't be able to support multiple tables. The "Table Name" property does support NiFi Expression Language, but that is so you can migrate flows from dev to test to production using different table names, each environment would have its own (static) variable set.

Instead, you can use GenerateTableFetch, it supports incoming connections and thus you can use flow file attributes in the expression(s) for Table Name, Columns to Return, Maximum-value Columns, etc. It works like QueryDatabaseTable, but instead of generating and executing the SQL, it only generates the SQL statements. This allows you to send the statements downstream to something like ExecuteSQL, possibly distributing the flow files among nodes in the cluster (using a Remote Process Group -> Input Port, if you have a cluster vs a standalone NiFi instance).

You can populate your incoming flow files from wherever you get your configuration (ListFile -> FetchFile if it is a file on disk, ListDatabaseTables if you want to get a list of tables from the database itself).

View solution in original post

11 REPLIES 11

avatar
Explorer

Sounds great. what are you trying to implement if you don't mind. I'm totally new to Nifi and my requirement is to extract data from Oracle DB from multiple tables and load it in Oracle DB. The source and target DBs are same and table structure is also same. Except that they are 2 different environments. As far as the knowledge i received from google on Nifi, most of the usecases on internet are extracting data from a single Table but not from Multiple tables. I appreciate any inputs.

avatar
New Contributor

Hi, 

 

I am having exactly the same problem, I was wondering if it's possible to share the configuration did you use to get that result. 

 

Thank you in advance!