What is best way to use QueryDatabaseTable for more than one DB tables?

[#1]

I want to use a data flow with QueryDatabaseTable to process more than one tables. The list of tables will be dynamic and thinking of a way to loop through the list of tables and trigger a QueryDatabaseTable dataflow for each of the table from list. Is this even feasible with said processor or any processor? I appreciate any pointers helping with this question.

[#2]

Hi @Sachin Jadhav

I am afraid you can not use QueryDatabaseTable with dynamic table name. This processor doesn't accept incoming flow files so you can not get you list before and feed it. Also, there's a constraint on the state management for dynamic items too.

Look to GenerateTableFetch or ExecuteSQL. Both accept incoming flow files where you can provide table names from other flows. For instance, you can use ListDatabaseTables if you want to get all tables or a subset of tables from a DB.

Thanks

[#3]

ok, thanks for your response Abdelkrim. I will try these.

[#4]

Hi @Abdelkrim Hadjidj, I looked at the suggested processors but not getting how to use for multiple tables. I would highly appreciate if you could share an example?

[#5]

ListDatabaseTables will generate a flow file for each table it finds (given your configuration in the processor). Each flow file will have a number of attributes set on it (db.table.name, e.g.) You can use these attributes in GenerateTableFetch and/or ExecuteSQL using NiFi Expression Language. For example you can set the Table Name property in GenerateTableFetch to "${db.table.name}". Then for each flow file coming into GenerateTableFetch, it will perform its work using the table name from the attribute in the flow file.