This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am trying to use Alteryx as an ETL tool to load data into AWS Redshift.
One of the columns in the table is setup with the DEFAULT data option
datetimeinserted datetime NOT NULL DEFAULT convert_timezone('Australia/Sydney'::text, getdate()),
This column is the last column in the table.
It does not work when using the bulk redshift upload tool in Alteryx
stl_load_errors table in Redshift shows "Delimiter not found"
Any ideas? I've tried passing through a "" (blank) and NULL but fails because I need the columns as a NOT NULL
The column is designed to represent the true date time inserted of records into the table.
Using the DATETIMENOW tool in Alteryx is not an option, because there is no way to control the execution of tools in a workflow. Well, it could be done by wrapping the tool inside a macro, but still it is not a true datetimeinserted value.
Thanks for testing in your environment Michael. You are definitely correct in that the COPY command done by the bulk upload tool is leaving out the explicit field list
@Alteryx, please change bulk upload tool to do this, or at least have an option to enable it (I'll wack a post in the Product Ideas forum)
The raw_line column in stl_load_errors table showed something like this:
1|1|2000-01-01 00:00:00|2000-01-01 00:00:00|1|1|1
So i create a .txt file with this inside it, put it on s3 and ran the copy command using the explicit field list as per your example Michael, as well as set delimiter to '|'.
I'll be using the below macro until the bulk upload tool is enhanced
I initially tried using the "date time now" tool, but it was executing at the same time the main workflow was executing, creating a large difference in time when compared to the actual start time of the bulk upload part. The "date time now" tool does not accept inputs, so it could not be postponed using the "block until done" tool.
It will be placed directly before the bulk upload tool so the datetimenow() generated in the macro is the closest time to the start of the bulk upload to redshift.
The name of the column in the redshift table will be changed from datetimeinserted to bulkupload_starttime, with the DEFAULT option removed.