Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Joining tables by creating a view and querying the view using querydatabasetable

Solved Go to solution
Highlighted

Joining tables by creating a view and querying the view using querydatabasetable

Contributor

Hi All,

Thanks a lot this awesome community.

I read tha JOIN queries are not possible in NiFi,

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

so I thought of a workaround by creating a view in the database and then queriyng the view

so I will be using tablename as the view name in the properties of querydatabasetable, I do not have a sample database, is this something which can be done?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Joining tables by creating a view and querying the view using querydatabasetable

Super Guru
  1. Yes you can use the view name as table name in query database table processor and it's better to have Maximum-value column(s) then this processor will pull only the new records that got added (or) modified.
  2. if we don't have any max value columns mentioned in configurations then processor will keep track of all columns, which could have performance impact.
  3. You can view the state of the processor by Right clicking and click on view state button, if you want to clear the state then click on clear state at right corner.

Query database table Configs:-

43390-query.png

I have changed Max Rows Per Flow File as 100K,so the processor will fetches 100K records per file (or) you can leave that as 0(default) will give you all the records in 1 Flow File.

Connection pool Configs:-

43389-connection.png

above screenshot connection pool is an example for sql server as you can see the highlighted text, if you don't mention any database in the connection URL then it will connects to default database in source.

jdbc:sqlserver://<ip-address-server>:<port> //this connection string we won't mentioned database name so it connects to default database in sql server.
3 REPLIES 3

Re: Joining tables by creating a view and querying the view using querydatabasetable

Contributor

@Shu

Thanks a lot for confirming, appreciate it.

Re: Joining tables by creating a view and querying the view using querydatabasetable

Super Guru
  1. Yes you can use the view name as table name in query database table processor and it's better to have Maximum-value column(s) then this processor will pull only the new records that got added (or) modified.
  2. if we don't have any max value columns mentioned in configurations then processor will keep track of all columns, which could have performance impact.
  3. You can view the state of the processor by Right clicking and click on view state button, if you want to clear the state then click on clear state at right corner.

Query database table Configs:-

43390-query.png

I have changed Max Rows Per Flow File as 100K,so the processor will fetches 100K records per file (or) you can leave that as 0(default) will give you all the records in 1 Flow File.

Connection pool Configs:-

43389-connection.png

above screenshot connection pool is an example for sql server as you can see the highlighted text, if you don't mention any database in the connection URL then it will connects to default database in source.

jdbc:sqlserver://<ip-address-server>:<port> //this connection string we won't mentioned database name so it connects to default database in sql server.

Re: Joining tables by creating a view and querying the view using querydatabasetable

Contributor

@Shu Appreciate your help This is as clear an explanation as it can get Thanks again

Don't have an account?
Coming from Hortonworks? Activate your account here