Support Questions
Find answers, ask questions, and share your expertise

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

Highlighted

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

New Contributor

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.

4 REPLIES 4
Highlighted

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

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

Highlighted

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

New Contributor

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

Highlighted

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

New Contributor

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?

Highlighted

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

Super Guru

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.