Can I use a subquery in a snowflake COPY INTO Command

Question

I have a csv file in an S3 bucket, and to import that data into snowflake. I built a snowflake stage and a snowpipe. Using this command:

create or replace pipe pipe_name as
copy into target_table
from @my_stage
FILE_FORMAT = (FIELD_OPTIONALLY_ENCLOSED_BY = '0x22');

This query worked perfectly.

However I would like to add in a new column called insert_date, to track when the rows are loaded into the table. Would this query work?

copy into target_table
from (select *, current_date() 
from @my_stage)
FILE_FORMAT = (FIELD_OPTIONALLY_ENCLOSED_BY = '0x22');

Note: I do not have sysadmin privileges to run this query, I ask a colleague who has the privileges to run it. So I want to make sure this query would run when I ask him to run. Any help is highly appreciated.

Answer 1

You cannot query the files from the stage using select *, you need to specify the columns. For CSV files it is easy to do by number, see the example below. Assuming your file has two columns:

copy into target_table
from (select $1, $2, current_date() 
from @my_stage)
FILE_FORMAT = (FIELD_OPTIONALLY_ENCLOSED_BY = '0x22');

You will need to add more columns in your select $1, $2,... depending on your source file. Then it would work.

Answer 2

Here is a documentation page with some examples

https://docs.snowflake.com/en/user-guide/data-load-transform.html#transforming-csv-data