Hey there, I’m a fair way into using KNIME to build an ELT pipeline between a MySQL DB and a redshift datawarehouse but I’m really struggling with mismatched datatypes.
Specifically we have a few TinyINT columns in the MySQL DB which are being interpreted as booleans by KNIME - this causes a mismatch when using the db loader to get data into redshift. I see this has been discussed before here - has there been any progress? Or an appropriate workaround?
I’d sooner not have to CAST specific columns as I wanted the loader to be generic at design time. Any thoughts appreciated
I’m glad to hear you’re making progress with KNIME for your ELT pipeline. Unfortunately, the issue with TinyINT columns being interpreted as booleans is still unresolved. The only current workaround is to CAST the specific columns to the appropriate datatype.
For internal reference, the ticket number for this issue is AP-22653.
Hi @rosspyres , can you assume that any boolean columns should actually be Integers, and that true should be 1, whilst false should be 0?
If that’s the case, you may be able to make use of my Rule Engine (multi column) component.
Give it the following script, and untick the “Append column” option. Not ideal, but it may at least be sufficiently generic. It may be a little slow on large datasets though.
"<CURRENTCOLUMNTYPE>" LIKE "Boolean*" AND $CURRENTCOLUMN$ = "true" => 1
"<CURRENTCOLUMNTYPE>" LIKE "Boolean*" AND $CURRENTCOLUMN$ = "false" => 0
TRUE => $CURRENTCOLUMN$
thanks, this looks useful. I’ve tried using the datatype column selector which is working well, but I get an error if there are no columns of the specified type in the dataset, any ideas to overcome this?
Hi @rosspyres , I have uploaded a fix for this to the hub, so if you update the component it should resolve this problem. If you continue to have a problem, can you upload screenshot of the config, and the error, in case I’ve misunderstood. thanks.
