I currently have a project where a table is imported into EM, a table is created on Redshift (RS) and then the data in EM is loaded to the newly create RS table.
We use the “Database Command” action to create a table on the destination DB.
The issue we have is with the data type for date fields.
In EM, with the Create table command, these data fields are created in RS as ‘date’ data types, however the format we need this to be in is ‘timestamp’ which is the ‘datetime’ format for RS.
The field is detected by EM as a datetime data type field at the time of the import from database action.
I’ve tried adding transformations to the date field to include the time formats with the date in view of forcing the creation of the datetime field based on data type in EM but without any success.
Ultimately the work around is to create the table with custom sql which is not ideal but the only option.
Can you advise if there is an easier way to create a table in a DB (redshift) with the date field type as “datetime” OR aka "timestamp’ in AWS Redshift.
You can’t select (or make EasyMorph to autodetect) something other than Text, Number or Date in Create table mode of “Database Command” action. But you can change which column types will be created in the target database for each of those data types.
There is a file named DataTypesMap.xml in the EasyMorph installation directory. This file contains default column types for different databases. You can edit that file and change DATE to DATETIME for Redshift. You’ll have to restart EasyMorph afterward.
Please have in mind that new column type will be used by all the “Database command” actions in every project.
Also, we are planning to add Advanced data types to “Create table” mode of “Database command” actions in the next version of EasyMorph.
EM Server uses it’s own DataTypesMap.xml file for all the spaces. The file is located in the config subfolder of EM Server installation folder. By default it’s C:\Program Files\EasyMorph Server\config\DataTypesMap.xml.