Workflow: Impala connector (or Hive connector) -> Database Table Selector (select * from db.table) -> Database Query (default select * from #table# query)
Database Query Node is producing sql with invalid syntax. Error message:
ERROR Database Query 0:358 Execute failed: AnalysisException: Syntax error in line 2:
Expected: AS, DEFAULT, IDENTIFIER
CAUSED BY: Exception: Syntax error
Driver: Open-Source Impala Driver (org.apache.hive.jdbc.HiveDriver, version: 1.1)
Other kinds of database nodes are working, database column filter, database groupby, database connection table reader, etc.
did you check, if your query runs flawless when executed directly to the server?
Are there any workflow variables involved?
As far as I understand the error message, the exception was thrown by the database. Did you checked your query for errors?
Have you tried to use the data base query node directly without the table select node?
And also if you have a hive connection in knime the query node does not like a semicolon „;“ at the end. No idea why that is.
There is no way I am aware of to run this exact query on the server. The problem is that using a Hive or Impala connector, you must first use a database table selector, then you can run a database query. (Doing this immediately is not my goal but trying to limit the # of variables involved.)
No user-created workflow variables are involved, although I understand the database query node uses a special placeholder for the incoming table.
I have checked the portion of the query I wrote for errors (actually its just the out of the box “SELECT * FROM #table#” query). I have verified that the incoming data table is working by viewing a sample of rows.
One cannot skip the Database Table Selector. The Hive/Impala connectors are not compatible with the database table manipulation nodes until a table is selected.
No semicolons added by the user to these queries
The error message suggests to me that in the nested sql that results from this sequence of blocks, the server is expecting an intermediate lock to be named (as x) but it is not. Is there a way to see the resulting SQL when the block fails (I know how if it executes successfully).