Getting Started with Snowflake (Copy Data into target tables):
To transfer your staged data into the target table, execute the command COPY INTO .
This COPY INTO command leverages the virtual warehouse created in the "Create Snowflake Objects" step to copy the files.
COPY INTO emp_basic
FROM @%emp_basic
FILE_FORMAT = (type = csv field_optionally_enclosed_by='"')
PATTERN = '.*employees0[1-5].csv.gz'
ON_ERROR = 'skip_file';
In the provided context:
- The FROM clause designates the location containing the data files, which is the internal stage designated for the table.
- The FILE_FORMAT clause defines the file type as CSV and designates the double-quote character (") for enclosing strings. Snowflake accommodates various file types and options, detailed in the CREATE FILE FORMAT documentation.
- The PATTERN clause specifies that the command should load data from filenames matching a defined regular expression (.*employees0[1-5].csv.gz).
- The ON_ERROR clause outlines the course of action when the COPY command encounters errors in the files. By default, the command halts data loading upon encountering the first error. However, in this example, any file containing an error is skipped, and the command proceeds to load the next file. It's important to note that none of the files in this tutorial contain errors; this inclusion is for illustrative purposes.
The COPY command offers an option to validate files before loading. Refer to the COPY INTO topic and other data loading tutorials for supplementary instructions on error checking and validation.
Upon execution, the COPY command provides a result displaying the list of copied files along with relevant information:
