bigquery db specific syntax giving Malformed SQL Statement error

I am using Google Bigquery with Knime. I used cdata.com jdbc connector for bigquery to connect with database. However when running standard sql like queries, it works but when running Bigquery specific "join each" clause it says ERROR Database Reader      4:20       Execute failed: Malformed SQL Statement: Syntax error near [cl] . But just removing "join each" it agains starts to work. So the culprit is BQ specific syntax. How can i make Knime to run BigQuery Specific queries?????

Hello,

could you please post some more information about the queries you try to execute. In some nodes KNIME issues modifies the original query that you enter to get the structure of the result e.g. number and types of the result columns. This is usually done by wrapping the original query into a limit statement query e.g. select * from (<ORIGINAL QUER>) limit 0. So it might be that the problem stemms from these structure queries. To have a look at all the queries that are send to the database open KNIME and go to View->Open KNIME log.

You  might only need to change the syntax a bit. FOr example some dbs require a as statement in the inner for such as select originalCol from originalTable AS T which will result in a structure query like select * from (select originalCol from originalTable AS T) limit 0.

Bye,

Tobias

Thanks for reply Tobias. Here is the query i am trying to run.

select clm.yr FROM DataSetId.codes cd
join each DataSetId.claims clm on cd.yr = clm.yr and cd.setting = clm.setting and cd.claimno = clm.claimno
where clm.op_npi is not null and cd.pxordx ='DP' and cd.seq <= 25

If i run 'join" instead of "join each" it goes to Bigquery server and get the expected appropriate responce message. but when i place "join each" it straight forward gives below error without going to Bigquery Server. 

ERROR Database Reader      0:20       Execute failed: Malformed SQL Statement: Syntax error near [clm]
Statement:select clm.yr FROM ODS_DATA_TEST.codes cd
join each ODS_DATA_TEST.claims clm on cd.yr = clm.yr and cd.setting = clm.setting and cd.claimno = clm.claimno
where clm.op_npi is not null and cd.pxordx ='DP' and cd.seq <= 25

 

I also Looked into Knime log but its not showing any other query other than my origional Query.

Hello,

the exception is either thrown by the jdbc driver or the database itself. Maybe it is a limitation of the jdbc. I had a brief look at the documentation and could not find a join each statement reference.

Have you tryed to execute the statment with another Java application using the same driver?

Bye,

Tobias

I am using same driver to connect to same DB. The problem i have is in error:

 Execute failed: Encountered duplicate row ID  "Row0" at row number

the only think i can use is DB connection table reader with unchecked "use db row id". But can not use DB looping or DB reader. Is there any chance to use same option in looping node?

thanks

Hi Audrius,

it seems the db driver you are using does not support rowids. Unfortunately the DB Looper and the DB Reader node do allow you to ignore the row id. I will open a bug for this problem. As a workaround you could execute the database query within a KNIME loop that replaces the where value prior execution.

Bye,

Tobias