CODEWITHSUNDEEP
sqlapache-nifi
Para_Conscious
Para_Conscious

Reputation: 61

Executing "Select *" query for multiple tables using Apache NiFi

Requirement: From an UI I am getting selected list of tables of a database. Data from these tables are to extracted and stored in file location. We are expected to use NiFi Rest APIs as there is a requirement for custom UI. So we are invoking NiFi processors using REST APIs.

Issue: With ExecuteSQL processor I can execute one SQL query at a time. Since the query is same for all of the tables (select * ...) I can pass in the table name as an attribute to the processor. But I am getting the table names from a REST API call so its delimited string.

the issue is how to invoke the ExecuteSQL in a loop for each table name received in a delimited String.

Kindly let me know if additional information is required.

Upvotes: 1

Views: 2176

Answers (1)

mattyb
mattyb

Reputation: 12083

ExecuteSQL can't loop on a single flow file. Instead you can use ReplaceText to put the delimited string into the body of the flow file, then use SplitText to split on the delimiter. You will get multiple flow files, each with the body containing one of the fields. Then you can use ReplaceText again to match the entire text, and replace with the SELECT statement around the group:

SELECT * from $1

Then you can send all flow files to ExecuteSQL, it will execute each SELECT one at a time (or concurrently if you set multiple concurrent tasks.

Upvotes: 2

Related Questions