Copying Data from an S3 Stage¶
Load data from your staged files into the target table.
In this Topic:
Loading Your Data¶
Execute COPY INTO <table> to load your data into the target table.
Loading data requires a warehouse. If you are using a warehouse that is not configured to auto resume, execute ALTER WAREHOUSE to resume the warehouse. Note that starting the warehouse could take up to five minutes.
ALTER WAREHOUSE mywarehouse RESUME;
The following example loads data from files in the named
my_ext_stage stage created in Creating an S3 Stage. Using pattern matching, the statement only loads files whose names start with the string
COPY INTO mytable FROM @my_ext_stage PATTERN='.*sales.*.csv';
Note that file format options are not specified because a named file format was included in the stage definition.
The following example loads all files prefixed with
data/files in your S3 bucket using the named
my_csv_format file format created in Preparing to Load Data:
COPY INTO mytable FROM s3://mybucket/data/files credentials=(AWS_KEY_ID='$AWS_ACCESS_KEY_ID' AWS_SECRET_KEY='$AWS_SECRET_ACCESS_KEY') FILE_FORMAT = (FORMAT_NAME = my_csv_format);
The following ad hoc example loads data from all files in the S3 bucket. The COPY command specifies file format options instead of referencing a named file format. This example loads CSV files with a pipe (
|) field delimiter. The COPY command skips the first line in the data files:
COPY INTO mytable FROM s3://mybucket credentials=(AWS_KEY_ID='$AWS_ACCESS_KEY_ID' AWS_SECRET_KEY='$AWS_SECRET_ACCESS_KEY') FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|' SKIP_HEADER = 1);
Validating Your Data¶
Before loading your data, you can validate that the data in the uploaded files will load correctly.
To validate data in an uploaded file, execute COPY INTO <table> in validation mode using the VALIDATION_MODE parameter. The VALIDATION_MODE parameter returns errors that it encounters in the file. You can then modify the data in the file to ensure it loads without error.
In addition, COPY INTO <table> provides the ON_ERROR copy option to specify an action to perform if errors are encountered in a file during loading.
Monitoring Data Loads¶
Snowflake retains historical data for COPY INTO commands executed within the previous 14 days. The metadata can be used to monitor and manage the loading process, including deleting files after upload completes: