I’ve started using the DB Loader node. It’s a big performance improvement in comparison with other approaches. Since I introduced it, I can write 5,5M rows table in less than 3 minutes which is huge.
Today I faced a situation the data saving failed with a message:
2020-01-13 13:08:20,574 ERROR DB Loader 0:836 The input and database columns do not match. 4 columns are different:
2020-01-13 13:08:20,576 ERROR DB Loader 0:836 The input column type at index 16 is “INTEGER”, the database column type is “SMALLINT”.
…
There is actually a workaround I could change the database columns but I’d preffer avoiding that.
Output Type Mapping tab of database connector node has options to define rules to map from KNIME types to database types. So I would do mapping by name for those 4 columns.
But still. This means I either always want to map a particular type (Integer) to a particular type (SMALLINT, for instance) or I’m supposed to list all the relevant columns. Which could appear a bit trickie in a situation I don’t know these columns at design time which is unfortunately my case. As almost always in my integration workflows, I walk over a list of tables whose names I get from a configuration and transform & save their data to different places. So I don’t know in advance which columns to handle that way. So either I need you as KNIME to introduce small integer or Byte type to the platform or I have to read source table definition and set the mapping at runtime via flow variables. This would need some portion of elaboration at my side.
Before I even start the elaboration I mentioned recently, Is there anybody who could give me a hand with such a non-trivial flow-variable-based configuration of the PostgreSQL Connector or other nodes where there appear obviously collections of structures to be configured. Let’s have look at an example:
How to configure a mapping of several columns here? Let’s imagine I revealed at runtime I’m working with columns A, B and C of KNIME type Integer I need to save as SMALLINT type to my database. Please note there can be number of other columns whose mappings must remain untouched.
this does seem a bit tricky. I would try following approach (which I haven’t tested): As you said you need to determine columns within the table that are type SMALLINT. Then rename them using Column Rename (Regex) node (for example append “_SMALLINT” to those column names) so you will be able to use Mapping by Name with regex option which will create proper mapping rules for such columns. Once data is loaded you can use DB Column Rename (Regex) to get your original column names back if needed.
Oh wait, I save the data to the database. Renaming the columns has occurred to my mind too but it doesn’t solve anything unless I had column type propagated to column names which would a be very ugly design.
