Database Table selector not able to process more complex select statement

[Biermann]

Hello,

Im trying to use an HiveSQL Statement on the Table selector node but it fails with the following Error Message: “Failed to recognize predicate ‘SELECT’. Failed rule: ‘queryStatementExpression’ in subquery source:17:16”

I´ve tried out the statement in HUE and it works.

Here is the statement:

WITH
all_Dates AS
(SELECT DISTINCT DATE_FORMAT(createdAt_ISO,‘yyyy-MM-dd’) AS date
FROM TABLE1
WHERE createdAt_ISO IS NOT NULL),
all_Status AS
(SELECT DISTINCT state, 0 AS Count
FROM TABLE1),
basis AS
(SELECT * FROM all_Dates FULL OUTER JOIN all_Status ON 1=1),
data AS
( SELECT DATE_FORMAT(createdAt_ISO,‘yyyy-MM-dd’) AS date
, state
, count(*) AS Count
FROM TABLE1 ord
WHERE createdAt_ISO IS NOT NULL
GROUP BY DATE_FORMAT(createdAt_ISO,‘yyyy-MM-dd’), State
)
SELECT basis.date, basis.state, basis.count + NVL(data.count, 0) AS count
FROM basis FULL OUTER JOIN data ON (basis.date = data.date) AND (basis.state = data.state)
ORDER BY basis.date, basis.state

Normal select * from TABLE1 works fine…

Does anyone have an idea why its not working? Does it not work with more complex structures?
It does also work fine in the Database SQL Executor node, but I dont know how to transform the result into a table

Greetings

[izaychik63]

It is not clear what do you mean by table? The result of Database SQL Executor node is a KNIME table. If you need to write result to DB use NSERT INTO SELECT statement or Database Writer node.

[mlauber71]

well it says it is missing an SELECT statement and I think you would need one in order to get some data. You could either use the Database Reader to get some data from HIVE to KNIME (download the data)

Database Reader (legacy) — NodePit

Or you could create a new table on the BigData cluster just by using the Database SQL Executor to create a new table right on the Big Data cluster

Database SQL Executor (legacy) — NodePit

[Biermann]

Hello,

it worked with the Database Reader Node.

Thank you