准备数据文件¶

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

本主题内容：

文件大小调整最佳实践和限制¶

为了获得最佳加载性能并避免大小限制，请考虑以下数据文件大小调整准则。请注意，这些建议适用于使用 Snowpipe 进行批量数据加载和连续加载。

常规文件大小调整建议¶

并行运行的加载操作数不能超过要加载的数据文件数。为了优化负载的并行操作数，建议生成的数据文件压缩大小约为 100-250 MB（或更大）。

备注

不建议加载非常大的文件（例如 100 GB 或更大）。

如果必须加载大文件，则仔细考虑 ON_ERROR 复制选项值。由于少量错误而中止或跳过文件可能会导致延迟和浪费 Credit。此外，如果数据加载操作继续超过允许的最大持续时间 24 小时，则该操作可能会被中止，而不会提交文件的任何部分。

聚合较小的文件可以尽可能减少每个文件的处理开销。将较大的文件拆分为更多较小的文件，以便在活跃仓库中的计算资源之间分发负载。并行处理的数据文件数由仓库中的计算资源量决定。我们建议按行拆分大文件，以避免出现跨块记录的情况。

如果源数据库不允许将数据文件导出为较小的块，则可以使用第三方实用程序拆分大的 CSV 文件。

Linux 或 macOS¶

split 实用程序使您能够将文件 CSV 拆分为多个较小的文件。

语法：

split [-a suffix_length] [-b byte_count[k|m]] [-l line_count] [-p pattern] [file [name]]

Copy

有关详细信息，请在终端窗口中键入 man split。

示例：

split -l 100000 pagecounts-20151201.csv pages

Copy

本示例按行的长度拆分名为 pagecounts-20151201.csv 的文件。假设单个大文件为 8 GB 并包含 1000 万行。除以 100,000，100 个较小的文件每个都是 80 MB（1000 万 / 10 万 = 100）。拆分的文件名为 pagessuffix。

Windows¶

Windows 不包括本机文件拆分实用程序。但 Windows 支持许多可以拆分大型数据文件的第三方工具和脚本。

加载前减小大于 16 MB 的对象的尺寸¶

备注

要使用此功能，您必须在您的账户中启用 2024_08 行为变更捆绑包。

要在账户中启用此捆绑包，请执行以下语句：

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_08');

Copy

如果试图将大于 16 MB 的对象从暂存区中的文件加载到以下类型的列中，将会出现错误：

VARCHAR（包括与 VARCHAR<label-data_types_string> 同义的类型）
BINARY（包括与 BINARY<label-data_type_varbinary> 同义的类型）
VARIANT
OBJECT
ARRAY
GEOGRAPHY
GEOMETRY

由于列中存储的对象的最大大小为 16 MB，因此发生以下错误：

Max LOB size (16777216) exceeded

过去，如果您尝试查询暂存区中的文件，并且文件包含大于 16 MB 的对象，则会发生此错误。

尽管您仍然不能在一列中存储大于 16 MB 的对象，您现在可以查询暂存区文件中最大 128 MB 的对象。然后，您可以在将对象存储在列中之前减小对象的大小。查询包含大于 16 MB 但小于 128 MB 的对象的文件时，不再出现错误。

例如，您可以将大对象拆分到多列或多行，将嵌套的 JSON 转换为表格格式，或简化复杂的几何形状。

示例：将大型 JSON 文件加载到单独的行中¶

通常， JSON 数据集是多个文档的简单串联。某些软件的 JSON 输出由包含多条记录的单个巨大数组组成。无需使用换行符或逗号分隔文档，但两者都受支持。

如果数据超过 16 MB，请启用 COPY INTO <table> 命令的 STRIP_OUTER_ARRAY 文件格式选项，以删除外部数组结构并将记录加载到单独的表行中：

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

Copy

示例：从 Parquet 文件加载和拆分 JSON 对象¶

假设您正在从某个暂存区加载 Parquet 文件，并且该 Parquet 文件包含大于 16 MB 的 JSON 对象：

{
  "ID": 1,
  "CustomerDetails": {
    "RegistrationDate": 158415500,
    "FirstName": "John",
    "LastName": "Doe",
    "Events": [
      {
        "Type": "LOGIN",
        "Time": 1584158401,
        "EventID": "NZ0000000001"
      },
      /* ... */
      /* this array contains thousands of elements */
      /* with total size exceeding 16 MB */
      /* ... */
      {
        "Type": "LOGOUT",
        "Time": 1584158402,
        "EventID": "NZ0000000002"
      }
    ]
  }
}

Copy

以下示例创建一个表来存储文件中的数据，并将数据加载到该表中。由于事件数组的大小可以超过 16 MB，该示例将事件数组扩展到单独的行（每个数组元素一行）。

CREATE OR REPLACE TABLE mytable AS
  SELECT
    t1.$1:ID AS id,
    t1.$1:CustomerDetails:RegistrationDate::VARCHAR AS RegistrationDate,
    t1.$1:CustomerDetails:FirstName::VARCHAR AS First_Name,
    t1.$1:CustomerDetails:LastName::VARCHAR AS as Last_Name,
    t2.value AS Event
  FROM @json t1,
    TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;

Copy

以下是结果表内容的示例。

+----+-------------------+------------+------------+------------------------------+
| ID | REGISTRATION_DATE | FIRST_NAME | LAST_NAME  | EVENT                        |
|----+-------------------+------------+------------+------------------------------|
| 1  | 158415500         | John       | Doe        | {                            |
|    |                   |            |            |   "EventID": "NZ0000000001", |
|    |                   |            |            |   "Time": 1584158401,        |
|    |                   |            |            |   "Type": "LOGIN"            |
|    |                   |            |            | }                            |
|                     ... thousands of rows ...                                   |
| 1  | 158415500         | John       | Doe        | {                            |
|    |                   |            |            |   "EventID": "NZ0000000002", |
|    |                   |            |            |   "Time": 1584158402,        |
|    |                   |            |            |   "Type": "LOGOUT"           |
|    |                   |            |            | }                            |
+----+-------------------+------------+------------+------------------------------+

将 FLATTEN 结果插入现有表中¶

要将 FLATTEN 函数的结果插入现有表中，请使用 INSERT 语句。例如：

CREATE OR REPLACE TABLE mytable (
  id VARCHAR,
  registration_date VARCHAR(16777216),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  event VARCHAR(16777216));

INSERT INTO mytable
  SELECT
    t1.$1:ID,
    t1.$1:CustomerDetails:RegistrationDate::VARCHAR,
    t1.$1:CustomerDetails:FirstName::VARCHAR,
    t1.$1:CustomerDetails:LastName::VARCHAR,
    t2.value
  FROM @json t1,
    TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;

Copy

示例：加载和拆分 XML¶

假设您正在从暂存区加载 XML 文件，其中包含一个大于 16 MB 的 XML 对象：

<?xml version='1.0' encoding='UTF-8'?>
<osm version="0.6" generator="osmium/1.14.0">
  <node id="197798" version="17" timestamp="2021-09-06T17:01:27Z" />
  <node id="197824" version="7" timestamp="2021-08-04T23:17:18Z" >
    <tag k="highway" v="traffic_signals"/>
  </node>
  <!--  thousands of node elements with total size exceeding 16 MB -->
  <node id="197826" version="4" timestamp="2021-08-04T16:43:28Z" />
</osm>

Copy

以下示例创建一个表来存储文件中的数据，并将数据加载到该表中。由于 XML 可以超过 16 MB，示例将 node 扩展到单独的行。

CREATE OR REPLACE TABLE mytable AS
  SELECT
    value:"@id" AS id,
    value:"@version" AS version,
    value:"@timestamp"::datetime AS TIMESTAMP,
    value:"$" AS tags
  FROM @mystage,
    LATERAL FLATTEN(INPUT => $1:"$")
  WHERE value:"@" = 'node';

Copy

以下是结果表内容的示例。

+--------+---------+-------------------------+---------------------------------------------+
| ID     | VERSION | TIMESTAMP               | TAGS                                        |
|--------+---------+-------------------------+---------------------------------------------|
| 197798 | 17      | 2021-09-06 17:01:27.000 | ""                                          |
| 197824 | 7       | 2021-08-04 23:17:18.000 | <tag k="highway" v="traffic_signals"></tag> |
|                   ... thousands of rows ...                                              |
| 197826 | 4       | 2021-08-04 16:43:28.000 | ""                                          |
+--------+---------+-------------------------+---------------------------------------------+

示例：在存储大型地理空间对象之前对其进行加载和简化¶

假设您正在从某个暂存区加载 Parquet 文件，并且该 Parquet 文件包含大于 16 MB 的地理空间对象。您可以在存储对象之前从暂存区加载文件并简化地理空间对象（通过使用 ST_SIMPLIFY）：

CREATE OR REPLACE TABLE mytable AS
  SELECT
    ST_SIMPLIFY($1:geo, 10) AS geo
  FROM @mystage;

Copy

示例：使用 COPY INTO <table>¶

如果您需要使用 COPY INTO <table> 从暂存区中的文件加载数据，您不能使用 FLATTEN 分割大对象。请改用 SELECT。例如：

CREATE OR REPLACE TABLE mytable (
  id VARCHAR,
  registration_date VARCHAR,
  first_name VARCHAR,
  last_name VARCHAR);

COPY INTO mytable (
  id,
  registration_date,
  first_name,
  last_name
) FROM (
    SELECT
      $1:ID,
      $1:CustomerDetails::OBJECT:RegistrationDate::VARCHAR,
      $1:CustomerDetails::OBJECT:FirstName::VARCHAR,
      $1:CustomerDetails::OBJECT:LastName::VARCHAR
    FROM @mystage
);

Copy

连续数据加载（即 Snowpipe）和文件大小调整¶

Snowpipe 会在发送文件通知后的一分钟内加载新数据。但是，对于非常大的文件，或者在需要异常数量的计算资源来解压缩、解密和转换新数据的情况下，加载可能需要更长的时间。

除了资源消耗之外，管理内部加载队列中的文件的开销也包含在 Snowpipe 的使用成本中。此开销会随着排队加载的文件数而增加。此开销会在账单中显示为 Snowpipe 费用，因为自动外部表刷新的事件通知会使用 Snowpipe。

为了获得最高效、最具成本效益的 Snowpipe 加载体验，我们建议遵循 `文件大小调整最佳实践和限制 `_ （本主题内容）中的文件大小调整建议。加载约为 100-250 MB 或更大的数据文件，可以显著降低相对于加载的总数据量的开销，以至于这部分额外成本变得几乎可以忽略不计。

如果在源应用程序中累积 MBs 数据所需的时间超过一分钟，可考虑每分钟创建一个新的（可能更小的）数据文件。这种方法通常可以在成本（即用于 Snowpipe 队列管理和实际负载的资源）和性能（即负载延迟）之间实现良好的平衡。

创建较小的数据文件并将其暂存到云存储中的频率超过每分钟一次，具有以下缺点：

无法保证减少暂存和加载数据之间的延迟。
管理内部加载队列中文件的开销包含在 Snowpipe 的使用成本中。此开销会随着排队加载的文件数而增加。

各种工具可以聚合和批处理数据文件。一个方便的选择是 Amazon Data Firehose。Firehose 允许定义所需的文件大小（称为 缓冲区大小）和发送新文件（在本例中为云存储）之后的等待间隔（称为 缓冲区间隔）。有关更多信息，请参阅 Amazon Data Firehose 文档 (https://docs.aws.amazon.com/firehose/latest/dev/create-configure.html)。如果源应用程序通常会在一分钟内积累足够的数据来填充大于建议的最大值的文件，以实现最佳并行处理，则可以减小缓冲区大小以触发较小文件的传递。将缓冲间隔设置保持在 60 秒（最小值）有助于避免创建过多文件或增加延迟。

准备带分隔符的文本文件¶

在准备要加载的带分隔符的文本 (CSV) 文件时，请考虑以下准则：

UTF-8 是默认字符集，但支持其他编码。使用 ENCODING 文件格式选项指定数据文件的字符集。有关更多信息，请参阅 CREATE FILE FORMAT。
包含分隔符的字段放在引号（单引号或双引号）内。如果数据包含单引号或双引号，则必须对这些引号进行转义。
回车符通常在 Windows 系统上与换行符一起引入，以标记行尾 (\r \n)。包含回车符的字段也应放在引号（单引号或双引号）内。
每行中的列数应保持一致。

半结构化数据文件和列化¶

将半结构化数据插入 VARIANT 列中时，Snowflake 会使用某些规则将尽可能多的数据提取为列形式。其余数据作为单个列存储在解析的半结构化结构中。

默认情况下，Snowflake 每个分区、每个表最多提取 200 个元素。要提高此限制，请联系 Snowflake 支持部门。

未提取的元素¶

具有以下特征的元素不会提取到列中：

即使包含单个“null”值的元素也不会提取到列中。这适用于具有“null”值的元素，而不适用于以列形式表示的缺少值的元素。

此规则可确保不会丢失任何信息（即，VARIANT“null”值和 SQL NULL 值之间的差异不会丢失）。
包含多种数据类型的元素。例如：

一行中的 foo 元素包含一个数字：
```
{"foo":1}
```
Copy
另一行中的同一元素包含一个字符串：
```
{"foo":"1"}
```
Copy

提取如何影响查询¶

当您查询半结构化元素时，Snowflake 的执行引擎的表现会根据是否提取元素而有所不同。

如果元素已提取到列中，则引擎仅扫描提取的列。
如果未将元素提取到列中，则引擎必须扫描整个 JSON 结构，然后对每一行遍历结构以输出值，从而影响性能。

为避免对未提取的元素产生性能影响，请执行以下操作：

在加载前，将包含“null”值的半结构化数据元素提取到关系列中。

或者，如果文件中的“null”值表示缺失值并且没有其他特殊含义，建议在加载半结构化数据文件时将文件格式选项 STRIP_NULL_VALUES 设置为 TRUE。此选项将删除 OBJECT 元素或包含“null”值的 ARRAY 元素。
确保每个唯一元素只存储该格式原生支持的单个数据类型（例如，对于 JSON 格式，可以是字符串或数字）。

数值数据准则¶

避免使用嵌入字符，例如逗号（例如 123,456）。
如果一个数字包含小数部分，则应使用小数点将其与整数部分分隔开（例如 123456.789）。
仅限 Oracle。Oracle NUMBER 或 NUMERIC 类型允许任意小数位数，这意味着即使在定义数据类型时没有指定精度或小数位数，它们也接受带有小数部分的值。而在 Snowflake 中，为具有小数部分的值设计的列必须使用小数位数定义以保留小数部分。

日期和时间戳数据准则¶

有关日期、时间和时间戳数据支持的格式的信息，请参见日期和时间输入和输出格式。
仅限 Oracle。Oracle DATE 数据类型可以包含日期或时间戳信息。如果您的 Oracle 数据库包含还存储时间相关信息的 DATE 列，请将这些列映射到 Snowflake 中的 TIMESTAMP 数据类型，而不是 DATE。

备注

Snowflake 在加载时检查时态数据值。无效的日期、时间和时间戳值（例如 0000-00-00）会产生错误。