[L4-BD] exercise 1 odyssey

Hi

Maybe it is a bug, maybe I am just not clever enough. I am doing the L4-BD and run in the first exercise into troubles.
01 Practicing with Traditional Databases.knwf (26.6 KB)

First, I was puzzled, that dragging in a DB Table Creator did only sport a DB Input port. I created a second port using the magic … and was worried that it was KNIME Data Table port (filled triangle) and not some kind of KNIME Data Table Specs port (empty triangle) as shown in the video of the course (Database Structure Manipulation - YouTube). I choose to ignore that, thinking I need the data anyway.
image

Then, I executed the table creator, and wanted to see what happened to the database, to notice, I could not find the table.


But surely, I would get an error when inserting the data… I did not. And the data looks clean, too.

Ignoring that, I created a DB Row Filter. However, KNIME tells me, there was an error. I did take a moment, until I remembered to look in the console. And there is the error message, put quite noisily, and it triggered a firework of questions.

WARN  DB Row Filter        7:63       org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_4655089729863673237""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_6673901315614516116"" WHERE 1 = 0"; erwartet "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"
Syntax error in SQL statement "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_4655089729863673237""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_6673901315614516116"" WHERE 1 = 0"; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT * FROM (SELECT * FROM ( SELECT * FROM "airline" ) AS "tempTable_4655089729863673237"
WHERE ( "Cancelled" = 0 AND "Dest" = ''AUS'' )
) AS "tempTable_6673901315614516116" WHERE 1 = 0 [42001-196]
WARN  DB Row Filter        7:63       org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_5898744010872012163""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_7328043405671067671"" WHERE 1 = 0"; erwartet "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"
Syntax error in SQL statement "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_5898744010872012163""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_7328043405671067671"" WHERE 1 = 0"; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT * FROM (SELECT * FROM ( SELECT * FROM "airline" ) AS "tempTable_5898744010872012163"
WHERE ( "Cancelled" = 0 AND "Dest" = ''AUS'' )
) AS "tempTable_7328043405671067671" WHERE 1 = 0 [42001-196]
WARN  DB Row Filter        7:63       org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_2859893531269370669""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_1060529789303461657"" WHERE 1 = 0"; erwartet "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"
Syntax error in SQL statement "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_2859893531269370669""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_1060529789303461657"" WHERE 1 = 0"; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT * FROM (SELECT * FROM ( SELECT * FROM "airline" ) AS "tempTable_2859893531269370669"
WHERE ( "Cancelled" = 0 AND "Dest" = ''AUS'' )
) AS "tempTable_1060529789303461657" WHERE 1 = 0 [42001-196]

Why is the SQL multiple times there? Where does the [*] after AUS come from when it was not configured like that?
l4-bd_ex1_5

Why is the SQL not always the same? The last one does not sport this [*].

Why is the single quote doubled as well when the whole SQL is put into double quotes?

Would it be possible to get the SQL straight (no doubling/escaping of stuff) so we can copy, paste and run it in our DB tool of choice without having to fiddle with it?

And the showstopper for me. If I remove the doubling and run the query, it runs without syntax error. Having that said, because I do not know where the table is supposed to have been created, it complains about the table not being there, to little surprise.

Well, here I am stuck. I did not mean to rant, and my apologies, if you feel that way. It is just a bunch of question marks having popped up over my head.

Kind regards

Thiemo

@Thiemo.Kellner my suggestion would be to toy around with this workflow to see various functions and how they would work:

The ‘brown’ database connections are indeed a combination of SQL VIEWS that are put together without optimization (that would be the job of the database). I would assume this is because they are meant to function across a large number of databases and the combinations could be quite extensive (think: Hive, Impala, Big Data). So like with every database the user will also have to keep in mind what a database might be able to do.

What the nodes do is to try and adapt to several different SQL ‘dialects’ that also might include the usage of escaping special charachters like quotation marks. Using double marks as escapes is one way to do it otherf might be backslashes . This very much might depend on the database.

Like shown in the example it is possible to extract the SQL syntax one has created in the workflow from the brown connections and use it in another node.

Then a more general remark: over the last days you let us participate in your learning journey with KNIME. Thank you for that and for the suggestions you made.

If I may add a hint from my side: A lot of (open source) software will need settings and adaptions and has special syntaxes. Often it can be useful to toy around with settings and familiarize oneself with the syntax before assuming something is a bug or simply wrong :slight_smile:

About KNIME and databases you might want to take a look at this Database Extension Guide

https://docs.knime.com/latest/db_extension_guide/index.html

1 Like