加载数据

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

本主题内容：

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

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

  • 按路径（内部暂存区）/前缀（Amazon S3 桶）。有关信息，请参阅 按路径组织数据。

  • 指定要加载的特定文件的列表。

  • 使用模式匹配按模式识别特定文件。

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

文件列表

COPY INTO <table> 命令包含一个 FILES 参数，用于按特定名称加载文件。

小技巧

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

例如：

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

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

模式匹配

COPY INTO <table> 命令包含一个 PATTERN 参数，用于使用正则表达式加载文件。

例如：

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

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

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

备注

正则表达式应用于批量数据加载与 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 s3://mybucket/path1/, then Snowpipe trims s3://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 选项。有关为每个云提供商配置事件筛选的更多信息，请参阅以下页面：

  • 使用对象键名称筛选配置事件通知 – Amazon S3 (https://docs.aws.amazon.com/AmazonS3/latest/userguide/notification-how-to-filtering.html)

  • 了解事件网格订阅的事件筛选 – Azure (https://docs.microsoft.com/en-us/azure/event-grid/event-filtering)

  • 筛选消息 – Google Pub/Sub (https://cloud.google.com/pubsub/docs/filtering)

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

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

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

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

加载较旧的文件

本部分介绍 COPY INTO <table> 命令如何根据文件的加载状态是已知还是未知来防止数据重复。如果使用按日期的逻辑粒度路径将数据分区存储在暂存区（如 按路径组织数据 中的建议），并在暂存数据后的短时间内加载数据，则此部分在很大程度上不适用于您。但是，如果 COPY 命令跳过数据加载中的旧文件（即历史数据文件），则本部分将介绍如何绕过默认行为。

加载元数据

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

  • 从中加载数据的每个文件的名称

  • 文件大小

  • 文件的 ETag

  • 文件中解析的行数

  • 文件上次加载的时间戳

  • 有关加载过程中文件中遇到的任何错误的信息

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

如果 LAST_MODIFIED 日期距今超过 64 天，则如果以下 任一 事件的发生日期距当前日期不超过 64 天，则加载状态仍为已知：

  • 文件已成功加载。

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

但是，如果 LAST_MODIFIED 日期距今超过 64 天，并且 初始数据集在 64 天前加载到表中（以及 如果文件加载到表中的时间也在 64 天之前），则 COPY 命令无法确定文件是否已加载。在这种情况下，为了防止意外重新加载，该命令默认跳过该文件。

解决方法

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

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

示例

../_images/data-load-status1.svg

在此示例中：

  • 表在 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 复制选项）。

../_images/data-load-status2.svg

在此示例中：

  • 文件在 3 月 1 日 暂存。

  • 64 天之后。在 5 月 4 日，暂存文件的 LAST_MODIFIED 日期超过 64 天。

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

JSON 数据：移除“null”值

在 VARIANT 列中，NULL 值存储为包含单词“null”的字符串，而不是 SQL NULL 值。如果 JSON 文档中的“null”值表示缺少值并且没有其他特殊含义，我们建议在加载 JSON 文件时将 COPY INTO <table> 命令的文件格式选项 STRIP_NULL_VALUES 设置为 TRUE。保留“null”值通常会浪费存储空间并减慢查询处理速度。

CSV 数据：修剪前导空格

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

可使用 TRIM_SPACE 文件格式 选项，在数据加载期间移除不需要的空格。

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

"value1", "value2", "value3"
Copy

以下 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 |
+--------+--------+--------+
Copy
语言: 中文