Snowflake - copy from S3 but include a loaddate

Question

I want to load a file from S3 but whilst loading, I want to add a load date/time into the snowflake table. How can I do this?

My code currently is doing this: copy into "DATABASE"."PUBLIC"."TABLENAME" from @S3_RAW/FILENAME.csv FILE_FORMAT = '"DATABASE"."PUBLIC"."CSV"' ON_ERROR = 'CONTINUE' PURGE = FALSE;

The Tablename I am copying into has an extra column called LoadDateTime which i just want to populate as it loads - is this possible?

Cheers

Rich Murnane Rich Murnane · Accepted Answer · 2021-01-05T14:54:39

Snowflake's COPY INTO TABLE command has two "variations", the first is "standard" and the second is "with transform".

You can define your column with a default value when you create the table, and the default will be honored in the COPY INTO command in "transform" type of COPY INTO commands, such as in the following example:

# create a table
CREATE OR REPLACE TABLE my_csv_data (
    id   INTEGER,
    str  VARCHAR(100),
    dt   TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP);

# create a file to load - call it my_csv_data.csv
id,str
1,"Hello World"
2,"lots of good stuff"
3,"goodbye"

# put the file up into your internal stage 
PUT file://~/my_csv_data.csv @~/testing123/my_csv_data.csv auto_compress=false;

# this unfortunately puts a NULL value in dt :-(
COPY INTO rich_db.rich_schema.my_csv_data
  FROM @~/testing123/my_csv_data.csv 
  FILE_FORMAT = (type = 'CSV' field_optionally_enclosed_by='"' SKIP_HEADER = 1 error_on_column_count_mismatch=false )
  on_error = 'continue';

# but this works, 3 records created with default timestamps :-) 
COPY INTO rich_db.rich_schema.my_csv_data (id, str)
  FROM (SELECT $1, $2 FROM @~/testing123/my_csv_data.csv )
  FILE_FORMAT = (type = 'CSV' field_optionally_enclosed_by='"' SKIP_HEADER = 1)
  ON_ERROR = 'continue';

link to docs: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html

I hope this helps...Rich

p.s. If this (or another) answer helps you, please take a moment to "accept" the answer that helped by clicking on the check mark beside the answer to toggle it from "greyed out" to "filled in".

Snowflake - copy from S3 but include a loaddate

1 Answers