0

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
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Xevi
  • 379
  • 1
  • 2
  • 11
  • [amazon web services - Skip columns while copying data into redshift from S3 using copy command - Stack Overflow](https://stackoverflow.com/questions/37712048/skip-columns-while-copying-data-into-redshift-from-s3-using-copy-command) – John Rotenstein Sep 23 '20 at 21:37

1 Answers1

1

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.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470