CODEWITHSUNDEEP
amazon-web-servicesamazon-s3copyamazon-redshift
Xevi
Xevi

Reputation: 399

Copy Redshift without table structure

I would like to load a stagging table from S3 to Redshift via a copy instruction and then select some fields and create a final table, but I don't know how to load a file from s3 without knowing all the structure.

Let me explain with an example:

I have my file in s3

col1   col2   col3
a       1     b
b       4     c

I want a final table with only the col2 so I do the following:

CREATE TEMPORARY TABLE stg(col1 varchar, col2 integer, col3 varchar);
COPY stg FROM myfile
CREDENTIALS
'aws_access_key_id=***;aws_secret_access_key=***'
 NULL as '' IGNOREHEADER AS 1
;
create table FINAL as
select
              col2
from stg

and I would like something like that (so I don't need to know the structure, only that it will have the col2):

CREATE TEMPORARY TABLE stg as (
COPY stg FROM myfile
CREDENTIALS
'aws_access_key_id=***;aws_secret_access_key=***'
 NULL as '' IGNOREHEADER AS 1
);

create table FINAL as
select
              col2
from stg

Upvotes: 0

Views: 1124

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270274

You are doing the correct process.

It is not possible to "skip over" columns when loading data via the COPY command.

The alternative would be to pre-process the files and remove the undesired columns before loading the data into Redshift.

Upvotes: 2

Related Questions