加载数据

本主题提供加载暂存数据的最佳实践、一般准则和重要注意事项。

用于选择暂存数据文件的选项

COPY 命令支持用于从暂存区加载数据文件的多个选项：

By path (internal stages) / prefix (Amazon S3 bucket). See Organizing data by path for information.
指定要加载的特定文件的列表。
使用模式匹配按模式识别特定文件。

通过这些选项，您可以使用单个命令将一部分暂存数据复制到 Snowflake 中。这允许您利用并行操作来执行与文件子集匹配的并发 COPY 语句。

文件列表

The COPY INTO <table> command includes a FILES parameter to load files by specific name.

Tip

在用于识别/指定要从暂存区加载的数据文件的三个选项中，提供离散的文件列表通常是最快的；但是，FILES 参数最多支持 1,000 个文件，这意味着使用 FILES 参数执行的 COPY 命令最多只能加载 1,000 个文件。

例如：

COPY INTO load1 FROM @%load1/data1/ FILES=('test1.csv', 'test2.csv', 'test3.csv')

文件列表可以与路径相结合，以进一步控制数据加载。

模式匹配

The COPY INTO <table> command includes a PATTERN parameter to load files using a regular expression.

例如：

COPY INTO people_data FROM @%people_data/data1/
   PATTERN='.*person_data[^0-9{1,3}$$].csv';

使用正则表达式的模式匹配通常是用于识别/指定要从暂存区加载的数据文件的三个选项中最慢的；但是，如果您从外部应用程序按命名顺序导出文件，并且希望以相同的顺序批量加载文件，则此选项非常有效。

模式匹配可以与路径相结合，以进一步控制数据加载。

Note

正则表达式应用于批量数据加载与 Snowpipe 数据加载的方式不同。

Snowpipe trims any path segments in the stage definition from the storage location and applies the regular expression to any remaining path segments and filenames. To view the stage definition, execute the DESCRIBE STAGE command for the stage. The URL property consists of the bucket or container name and zero or more path segments. For example, if the FROM location in a COPY INTO <table> statement is @s/path1/path2/ and the URL value for stage @s is s3china://mybucket/path1/, then Snowpipe trims s3china://mybucket/path1/path2/ from the storage location in the FROM clause and applies the regular expression to the remaining filenames in the path.
批量数据加载操作将正则表达式应用于 FROM 子句中的整个存储位置。

Snowflake 建议您为 Snowpipe 启用云事件筛选，以降低成本、事件噪音和延迟。仅当云提供商的事件筛选功能不足时，才使用 PATTERN 选项。有关为每个云提供商配置事件筛选的更多信息，请参阅以下页面：

Configuring event notifications using object key name filtering - Amazon S3 (https://docs.aws.amazon.com/AmazonS3/latest/userguide/notification-how-to-filtering.html)
Understand event filtering for Event Grid subscriptions - Azure (https://docs.microsoft.com/en-us/azure/event-grid/event-filtering)
Filtering messages - Google Pub/Sub (https://cloud.google.com/pubsub/docs/filtering)

执行引用相同数据文件的并行 COPY 语句¶

执行 COPY 语句时，Snowflake 会在语句中引用的数据文件的表元数据中设置加载状态。这样可以防止并行 COPY 语句将相同的文件加载到表中，从而避免数据重复。

COPY 语句处理完成后，Snowflake 会根据需要调整数据文件的加载状态。如果一个或多个数据文件无法加载，Snowflake 会将这些文件的加载状态设置为加载失败。这些文件可用于要加载的后续 COPY 语句。

如果您的工作负载由高度并发且将数据加载到同一个表的 COPY 语句组成，请使用 Snowpipe，因为该服务专为处理并发的 COPY 语句而设计，可以更好地利用包括表元数据管理在内的并行操作。随着时间的推移，由于数据量和执行的负载频率的变化，您可能需要考虑将现有的 COPY 语句工作负载迁移到 Snowpipe。与此同时，您可以将 COPY 语句间隔开来，以降低并发性，从而提高性能。

加载较旧的文件

This section describes how the COPY INTO <table> command prevents data duplication differently based on whether the load status for a file is known or unknown. If you partition your data in stages using logical, granular paths by date (as recommended in Organizing data by path) and load data within a short period of time after staging it, this section largely does not apply to you. However, if the COPY command skips older files (i.e. historical data files) in a data load, this section describes how to bypass the default behavior.

加载元数据

Snowflake 维护加载数据的每个表的详细元数据，包括：

从中加载数据的每个文件的名称
文件大小
文件的 ETag
文件中解析的行数
文件上次加载的时间戳
有关加载过程中文件中遇到的任何错误的信息

此加载元数据将在 64 天后过期。如果暂存数据文件的 LAST_MODIFIED 日期距今不超过 64 天，则 COPY 命令可以确定给定表的加载状态并防止重新加载（和数据重复）。LAST_MODIFIED 日期是文件最初暂存或上次修改时的时间戳，以较晚者为准。

If the LAST_MODIFIED date is older than 64 days, the load status is still known if either of the following events occurred less than or equal to 64 days prior to the current date:

文件已成功加载。

表的初始数据集（即创建表后的第一批数据）已加载。

However, the COPY command cannot definitively determine whether a file has been loaded already if the LAST_MODIFIED date is older than 64 days and the initial set of data was loaded into the table more than 64 days earlier (and if the file was loaded into the table, that also occurred more than 64 days earlier). In this case, to prevent accidental reload, the command skips the file by default.

解决方法

若要加载元数据已过期的文件，请将 LOAD_UNCERTAIN_FILES 复制选项设置为 true。复制选项引用加载元数据（如果可用）以避免数据重复，但也会尝试加载具有过期加载元数据的文件。

或者，设置 FORCE 选项以加载所有文件，忽略加载元数据（如果存在）。请注意，此选项会重新加载文件，可能会复制表中的数据。

示例

在此示例中：

表在 3 月 1 日 创建，初始表加载在同一天发生。
64 天之后。在 5 月 4 日，加载元数据过期。
文件分别于 7 月 1 日 和 2 日 暂存并加载到表中。由于文件是在加载前一天暂存的，因此 LAST_MODIFIED 日期在 64 天内。加载状态是已知的。该文件没有数据或格式问题，并且 COPY 命令已成功加载该文件。
64 天之后。在 9 月 3 日，暂存文件的 LAST_MODIFIED 日期超过 64 天。在 9 月 4 日，成功加载文件的加载元数据过期。
尝试在 11 月 1 日 将文件重新加载到同一表中。由于 COPY 命令无法确定文件是否已加载，因此将跳过该文件。加载文件需要 LOAD_UNCERTAIN_FILES 复制选项（或 FORCE 复制选项）。

在此示例中：

文件在 3 月 1 日 暂存。
64 天之后。在 5 月 4 日，暂存文件的 LAST_MODIFIED 日期超过 64 天。
在 9 月 29 日 创建一个新表，并将暂存文件加载到该表中。由于初始表加载发生在不到 64 天前，因此 COPY 命令可以确定文件尚未加载。该文件没有数据或格式问题，并且 COPY 命令已成功加载该文件。

JSON 数据：移除“null”值¶

In a VARIANT column, NULL values are stored as a string containing the word “null,” not the SQL NULL value. If the “null” values in your JSON documents indicate missing values and have no other special meaning, we recommend setting the file format option STRIP_NULL_VALUES to TRUE for the COPY INTO <table> command when loading the JSON files. Retaining the “null” values often wastes storage and slows query processing.

CSV 数据：修剪前导空格¶

如果外部软件导出用引号括起来的字段，但在每个字段的开始引号字符之前插入一个前导空格，则 Snowflake 会读取前导空格而不是开始引号字符作为字段的开头。引号中的字符被解释为字符串数据。

Use the TRIM_SPACE file format option to remove undesirable spaces during the data load.

例如，示例 CSV 文件中的以下每个字段都包含一个前导空格：

"value1", "value2", "value3"

以下 COPY 命令修剪前导空格并移除每个字段的引号：

COPY INTO mytable
FROM @%mytable
FILE_FORMAT = (TYPE = CSV TRIM_SPACE=true FIELD_OPTIONALLY_ENCLOSED_BY = '0x22');

SELECT * FROM mytable;

+--------+--------+--------+
| col1   | col2   | col3   |
+--------+--------+--------+
| value1 | value2 | value3 |
+--------+--------+--------+