How to insert few columns from multiple CSV files on S3 bucket into a snowflake table at a time (in a single query)

Question

I want to insert specific column from multiple CSV file which are at S3 location to a snowflake table . Suppose 1st column of 1st CSV file to 1st column of snowflake table, 5th column of 2nd csv file to 2nd column of snowflake table ... Etc.... So it possible to create a query for this ?

Answer 1

From little you provided as requirements that can be achieved with using $1, $2 for column names from S3 files.

To give you an idea

copy into table from (
select $1,'',...
from file1
union
select '',$5,...
from file2
)

Answer 2

You need to provide more info =)

Generally, how would you link/relate the info two CSV files? You need at least a key of some sort that's avail from both sources.

I would think of it in steps and ELT mindset instead of ETL:

1. Load CSV1 into Table1
2. Load CSV2 into Table2
3. CREATE or REPLACE Table3 (CommonKey datatype, Column1 datatype, Column2 datatype)
4. INSERT INTO Table3 SELECT T1.CommonKey, Column1, Column2 FROM Table1 T1 JOIN Table2 T2 ON T1.CommonKey = T2.CommonKey

Answer 3

I don't think there is way to load data for one table from multiple files at a time, Instead you can do below:

We can specify the column order in copy command: copy into table(col1, col2,...,coln) from ( select $1,$2,....$n from file1 ) file format = 'my_file_format'