CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
Shivkumar kondi
Shivkumar kondi

Reputation: 6762

how to copy data when delimiters are missing in source file

Lets say I have a 4 table in redshift with 4-columns as :

Create Table m.mytab(
 col_1 BIGINT NOT NULL
 col_2 Varchar(200)
 col_3 Varchar(200)
 col_4 INT
);

And my Source row file contains data as:

 col_1^col_2^col_3^col_4
 myrowdata1^myrowdata2
 myrowdata3^myrowdata4
 .....

Here I want to load this data in mytab I tried copy command of redshift as :

copy m.mytab
from 's3://mybucket/folder/fileA.gz '
credentials 'aws_access_key_id=somexxx;aws_secret_access_key=somexxx'
DELIMITER '^'
GZIP
IGNOREHEADER 1
ACCEPTINVCHARS;

Since last 2-delimiters are missing in each row , I am unable to load the data here, can someone suggest me how to resolve this issue?

Thanks

Upvotes: 0

Views: 1271

Answers (1)

AlexYes
AlexYes

Reputation: 4208

1) Try adding FILLRECORD parameter to your COPY statement

For more information, see Data Conversion Parameters documentation

2) If all rows are missing col3 and col4 you can just create a staging table with col1 and col2 only, copy data to staging table and then issue

ALTER TABLE target_tablename
APPEND FROM staging_tablename
FILLTARGET;

This will move data to the target_tablename very efficiently (just changing the pointer without writing or deleting data) and take care about missing col3 and col4.

More information about the command: ALTER TABLE APPEND

Upvotes: 1

Related Questions