Loading data | snowflake documentation (2023)

file lists¶

oCOPY TO <table>The command includes a FILES parameter to upload files with a specific name.

For example:

COPY OF EM charged1 VON @%charged1/fecha1/ FILES=('test1.csv', 'test2.csv', 'test3.csv')

(Video) Data Pipelines Explained

File lists can be combined with paths for more control over data loading.

pattern matching¶

oCOPY TO <table>The command includes a PATTERN parameter to upload files using a regular expression.

For example:

COPY OF EM people_data VON @%people_data/fecha1/ STANDARD='.*Personal data[^0-9{1,3}$$].csv';

Regular expression pattern matching is generally the slowest of the three options for identifying/specifying data files for a stage to load. However, this option works well if you have exported your files from your external application in the order listed and want to batch upload the files in the same order.

Pattern matching can be combined with routes to provide more control over data loading.

upload metadata¶

Snowflake maintains detailed metadata for each table that data is loaded into, including:

• Name of each file from which the data was loaded

• File size

• ETag to file

• Number of lines parsed in the file

• Timestamp of the last upload of the file

• Information about errors found in the file during upload

This upload metadata expires after 64 days. If the LAST_MODIFIED date for a given data file is less than or equal to 64 days, the COPY command can determine the load status of a particular table and prevent reloads (and data duplication). The LAST_MODIFIED date is the timestamp of when the file was initially prepared or when it was last modified, whichever is later.

If the LAST_MODIFIED date is older than 64 days, the state of charge is still known whenanyof the following events that occurred less than 64 days before the current date:

• The file has been uploaded successfully.

• The initial record of the table (that is, the first batch after the table was created) was loaded.

However, the COPY command cannot positively determine if a file has already been uploaded if the LAST_MODIFIED date is older than 64 days.mithe original record was loaded into the table more than 64 days ago (miif the file was uploaded to the spreadsheet, that was also more than 64 days ago). In this case, the command skips the file by default to prevent an accidental reload.

hanging problem¶

To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true. The copy option references upload metadata if available to avoid data duplication, but also attempts to upload files with expired upload metadata.

Alternatively, set the FORCE option to upload all files and ignore upload metadata if present. Note that this option reloads files and may duplicate data in a table.

examples¶

In this example:

• A table is created inJanuary 1and the table is initially loaded the same day.

• 64 days pass. Around7 of March, the upload metadata expires.

• A file is prepared and loaded into the table.July 27thmi28, respectively Since the file was prepared the day before the upload, the LAST_MODIFIED date was within 64 days. The state of charge was known. There are no data or format problems with the file and the COPY command loads it correctly.

• 64 days pass. Around28.09, the LAST_MODIFIED date of the tested file is greater than 64 days. Around29.09, the upload metadata expires for a successful file upload.

• Trying to load file into the same table as reloadNovember 1st. Since the COPY command cannot determine if the file has already been loaded, the file is ignored. The LOAD_UNCERTAIN_FILES copy option (or the FORCE copy option) is required to load the file.

  (Video) Module 2 - Day 1 - 4 - EpiData Entry: Data documentation sheet

In this example:

• A file is being tested.January 1.

• 64 days pass. Around7 of March, the LAST_MODIFIED date of the tested file is greater than 64 days.

• A new table is created in29.09and the prepared file is loaded into the table. Because the table was initially loaded less than 64 days ago, the COPY command may determine that the file has not been loaded yet. There are no data or format problems with the file and the COPY command loads it correctly.