I have a scenario in my redShift database, where my table has NOT NULL date column with default as SYSDATE. I am trying to load the data through COPY command without the date column in the csv file. But, when I load, the COPY command throws out an error. Please let me know do we have any other option to overcome this issue.
Add a comment
|
1 Answer
Since you have not provided exact error you are getting, I will try to navigate you through with simple use case. Then, I think your could try replicating it.
If you just skip the column with default from your copy command should work and resolve your issue, one more thing, if you are doing Copy via code(java/python etc) make sure you are committing the transaction.
Use following steps to make a simple working program.
create table sales(
salesid integer not null,
category varchar(10),
update_at timestamp DEFAULT SYSDATE);
Create a file Test.csv with following content.
1,Cat1
2,Cat2
3,Cat3
4,Cat1
5,Cat1
6,Cat3
7,Cat1
10,Cat1
Run the following command.
Copy sales(salesid,category) from 's3://***/Test.csv' credentials'aws_access_key_id=*******;aws_secret_access_key=********' delimiter ',';
Output: Copy sales(salesid,category) from 's3:////Test.csv' credentials'aws_access_key_id=*****;aws_secret_access_key=****' delimiter ','; INFO: Load into table 'sales' completed, 8 record(s) loaded successfully. COPY;
$Select * from Sales;
salesid | category | update_at
---------+----------+----------------------------
4 | Cat1 | 2018-08-14 05:56:33.644541
10 | Cat1 | 2018-08-14 05:56:33.644541
2 | Cat2 | 2018-08-14 05:56:33.644541
6 | Cat3 | 2018-08-14 05:56:33.644541
3 | Cat3 | 2018-08-14 05:56:33.644541
7 | Cat1 | 2018-08-14 05:56:33.644541
1 | Cat1 | 2018-08-14 05:56:33.644541
5 | Cat1 | 2018-08-14 05:56:33.644541
(8 rows)
4 Comments
Raam
Your work program is too clear and the same I tested. Now I am able to load the data with default sysdate. Also, we're using spark script for ingesting the data into redshift. Hope commit will be automatically placed once COPY command is complete, or we need to explicitly give a commit ? Thank you for ur prompt reply on this.
Red Boy
Good to know that it helped. You have to explicitly
commit or you have to set autocommit=true so that every statement gets committed.Raam
Ok I will check the same. Thanks once again :)
Raam
Is there is any other way apart this approach. Actually, we're planning not to touch the spark code. Any parameter for COPY command?. so that the date column automatically loads the timestamp when COPY is executed into that table.