How to COPY specific columns from csv files into redshift table using lambda

Question

I'm trying to load S3 data which is in .csv format and the S3 Bucket has many files each with a different number of columns and different column sequence and when trying to use the copy command the data is getting stored in wrong columns.

Example: File1

client_id | event_timestamp | event_name
  aaa1    |   2020-08-21    | app_launch  
  bbb2    |   2020-10-11    | first_launch

File2

 a_sales| event_timestamp | client_id |  event_name
 2039   |   2020-08-27    |   ccc1    |  app_used  
 3123   |   2020-03-15    |   aaa2    |  app_uninstalled

Desired OUTPUT:

a_sales | client_id |    event_name      | event_timestamp   
  2039  |   ccc1    |     app_used       |   2020-08-27
  3123  |   aaa2    |  app_uninstalled   |   2020-03-15
        |   aaa1    |    app_launch      |   2020-08-21
        |   bbb2    |   first_launch     |   2020-10-11

I have tried the below SQL script which basically runs successfully but doesn't give the desired output can someone help me out with this issue.

COPY public.sample_table
FROM 's3://mybucket/file*' 
iam_role 'arn:aws:iam::99999999999:role/RedShiftRole' 
FILLRECORD DELIMITER ',' IGNOREHEADER 1;

Eralper · Accepted Answer · 2020-10-13 19:15:03Z

1

You can COPY data from S3 bucket into corresponding structure mapping staging tables. Then either you can move data into a combined table from these 2 tables with different columns, or you can create a view which reads data into a unified structure from these staging tables

answered Oct 13, 2020 at 19:15

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Weiner · Accepted Answer · 2020-10-13 15:50:47Z

0

So the COPY command does NOT align data to columns based on the text in the header row of the CSV file. You need to specify which columns of the table you want to populate from the CSV file in the same order as the data is specified in the csv file.

See: Copy-command

Since your two types of files have different column orders (and columns) you will need to have a different column list for each type.

answered Oct 13, 2020 at 15:50

Bill Weiner

11.4k2 gold badges12 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to COPY specific columns from csv files into redshift table using lambda

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related