question Could NIfi - executeSQL process run sql to join tables from two differeent Databases? in Archives of Support Questions (Read Only)

Could NIfi - executeSQL process run sql to join tables from two differeent Databases?

toandyliang — Tue, 17 Jul 2018 23:02:19 GMT

Could NIfi Nifi - executeSQL process run sql to join two Databases?

Re: Could NIfi - executeSQL process run sql to join tables from two differeent Databases?

MattWho — Wed, 18 Jul 2018 00:52:25 GMT

@Andy Liang

The ExecuteSQL processor only supports "Select" statements.

Also see:

https://community.hortonworks.com/questions/64667/joining-tables-within-nifi.html

Thank you,

Matt

If you found this Answer addressed your original question, please take a moment to login and click "Accept" below the answer.

Re: Could NIfi - executeSQL process run sql to join tables from two differeent Databases?

toandyliang — Wed, 18 Jul 2018 04:01:51 GMT

Thank you for the quick reply, Matt

Re: Could NIfi - executeSQL process run sql to join tables from two differeent Databases?

mburgess — Mon, 23 Jul 2018 19:49:05 GMT

You can use a JOIN clause in the select statement, but it will only work for a single RDBMS. You may find you can join two tables from two databases/schemas in the same RDBMS (if that system lets you), but you can't currently join two tables from totally separate database systems. You could investigate Presto, it allows for joining of tables across multiple systems, and you could have a single connection to it from NiFi in ExecuteSQL. That way it will look like a single RDBMS to NiFi, but Presto can be configured to do the cross-DB join.