Hi
I am using Energy_Prepare_Data (Big Data) found in Example Databases. I got a sample data in csv format then input it in MYSQL. The first two nodes ("Database Connector" and "Data Table Selector") are working fine but the third node (Metanode) had an error like this:
"Execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'timestamp),
cast(substr(date_time, 1, 3) as int)) as my_date,"
don't now what this error mean. Is this about the sql?
The first 2 columns are in datatype Int while that last is Float
Thanks in advance
Hi,
this is a general problem with different functionalities and function name of different databases. KNIME tries to hide this from the end user but cannot do automatic function translation.
The workflow has been developed for Hive and Impala and thus uses Hive/Impala specific SQL functions, which are not supported or called differently in MYSQL.
If you want to run the workflow on MYSQL you would need to review the sql syntax of the failing node. I guess the biggest problem are the string function and type casts in the String to datetime metanode. Most of the succeeding nodes use standard sql and thus should run on MYSQL.
Bye,
Tobias
The cast looks okay for mysql, and so does the substr.
However the error you get suggests that a timestamp is used to store the date_time field, and as you can not do string operations on a timestamp field, it would have to be either stored as a string from the start, or converted on the fly:
date_format(date_time, '%Y-%m-%d %H:%i:%s') to get it in ISO string format.
see https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format for details on the date_format function, it depends on the output you expect what you need.
Actually it looks like a part of the date is extracted, this can also be done directly, see https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html for e.g. the YEAR() MONTH() and WEEK() functions.