SQL 数据类型：最大长度、输出和错误消息的变更

Attention

This behavior change is in the 2024_08 bundle.

For the current status of the bundle, refer to Bundle History.

随着此行为变更，编译后的 SQL 表达式和一些错误消息的行为如下所示：

Before the change:
  • In compiled SQL expressions and error messages, Snowflake explicitly specified the length of the data type (for example, VARCHAR(16777216)).
  • When loading objects larger than 16 MB, an error related to parsing or processing a large string or file is returned (for example, 100069 (22P02): Error parsing JSON: document is too large, max size 16777216 bytes).
After the change:
  • In compiled SQL expressions and error messages, Snowflake omits the length of the data type (for example, VARCHAR).
  • When loading objects larger than 16 MB, an error related to storing a large object is returned (for example, 100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>).

在过去，如果尝试在一个暂存区查询一个大于 16 MB（对于 BINARY、GEOMETRY 和 GEOGRAPHY 为 8 MB）的对象，则会发生错误。您现在可以读取和处理大小高达 128 MB 的对象。

Note

With the 9.17 release, you can now also store objects larger than 16 MB in a column. For more information, see Size limits for database objects.

新的大小限制没有在 SQL 查询输出或元数据中显式公开。但是，您可以通过创建或读取更大的对象来隐式观察新增加的长度，但不存储它们。启用此功能会带来以下行为变更：

  • VARCHAR and BINARY types appear without length in the output of GET_DDL, SHOW, and DESCRIBE commands for column expressions, UDFs, and stored procedures.

    For example, VARCHAR is shown instead of VARCHAR(16777216). This change applies only to newly created objects where you haven’t explicitly specified the length in the DDL statement. The change doesn’t apply to existing objects.

  • Some statements that failed before with a maximum size exceeded (or similar) error will now succeed.

现在，只加载或创建但不存储或返回较大值的语句将成功执行。

  • Some statements that before failed with a maximum size exceeded (or similar) error will keep failing, however, with a different error code or message.

    The new error code and message are still related to exceeding the 16 MB limit, but the error can originate from a different part of the execution plan. For example, cannot load value might change to cannot store value or cannot output value.

首次变更会影响所有客户。第二和第三次变更会影响那些试图加载或生成大于 16 MB 的对象的客户。

Important

We strongly advise against creating logic that depends on error messages associated with objects larger than 16 MB. Instead, you can build logic that uses the BIT_LENGTH function to check the size of the value.

元数据的变更

有一些行为变更会影响以下类型的操作：

对于这些类型的操作，结果集中的元数据会发生变更。

Note

此列表并不详尽。

返回 UDFs 的元数据

For new user-defined functions (UDFs) that use VARCHAR or BINARY values as input or output, changes in the metadata for DDL statements related to UDFs affect the output returned when you call the GET_DDL function, run the DESCRIBE FUNCTION statement, or query the event table. The following example creates a UDF:

CREATE OR REPLACE FUNCTION udf_varchar(g1 VARCHAR)
  RETURNS VARCHAR
  AS $$
    'Hello' || g1
  $$;

GET_DDL

调用 GET_DDL 函数所返回的元数据会发生如下更改：

SELECT GET_DDL('function', 'udf_varchar(VARCHAR)');
Metadata before the change:
CREATE OR REPLACE FUNCTION "UDF_VARCHAR"("G1" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS '
  ''Hello'' || g1
';
Metadata after the change:
CREATE OR REPLACE FUNCTION "UDF_VARCHAR"("G1" VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS '
  ''Hello'' || g1
';

DESCRIBE FUNCTION

DESCRIBE FUNCTION 语句返回的元数据会发生如下更改：

DESCRIBE FUNCTION udf_varchar(VARCHAR);
Metadata before the change:
+-----------+-------------------+
| property  | value             |
|-----------+-------------------|
| signature | (G1 VARCHAR)      |
| returns   | VARCHAR(16777216) |
| language  | SQL               |
| body      |                   |
|           |   'Hello' || g1   |
|           |                   |
+-----------+-------------------+
Metadata after the change:
+-----------+-------------------+
| property  | value             |
|-----------+-------------------|
| signature | (G1 VARCHAR)      |
| returns   | VARCHAR           |
| language  | SQL               |
| body      |                   |
|           |   'Hello' || g1   |
|           |                   |
+-----------+-------------------+

事件表

For new user-defined functions that return VARCHAR or BINARY values as output, the snow.executable.name attribute in the RESOURCE_ATTRIBUTES column of the event table changes as follows:

Metadata before the change:
{
  "db.user": "MYUSERNAME",
  "snow.database.id": 13,
  "snow.database.name": "MY_DB",
  "snow.executable.id": 197,
  "snow.executable.name": "UDF_VARCHAR(X VARCHAR):VARCHAR(16777216)",
  "snow.executable.type": "FUNCTION",
  "snow.owner.id": 2,
  "snow.owner.name": "MY_ROLE",
  "snow.query.id": "01ab0f07-0000-15c8-0000-0129000592c2",
  "snow.schema.id": 16,
  "snow.schema.name": "PUBLIC",
  "snow.session.id": 1275605667850,
  "snow.session.role.primary.id": 2,
  "snow.session.role.primary.name": "MY_ROLE",
  "snow.user.id": 25,
  "snow.warehouse.id": 5,
  "snow.warehouse.name": "MYWH",
  "telemetry.sdk.language": "python"
}
Metadata after the change:
{
  "db.user": "MYUSERNAME",
  "snow.database.id": 13,
  "snow.database.name": "MY_DB",
  "snow.executable.id": 197,
  "snow.executable.name": "UDF_VARCHAR(X VARCHAR):VARCHAR",
  "snow.executable.type": "FUNCTION",
  "snow.owner.id": 2,
  "snow.owner.name": "MY_ROLE",
  "snow.query.id": "01ab0f07-0000-15c8-0000-0129000592c2",
  "snow.schema.id": 16,
  "snow.schema.name": "PUBLIC",
  "snow.session.id": 1275605667850,
  "snow.session.role.primary.id": 2,
  "snow.session.role.primary.name": "MY_ROLE",
  "snow.user.id": 25,
  "snow.warehouse.id": 5,
  "snow.warehouse.name": "MYWH",
  "telemetry.sdk.language": "python"
}

返回具有列表达式的表的元数据

对于在列表达式中使用 VARCHAR 或 BINARY 的新表，与这些列相关的 DDL 语句的元数据变更会影响调用 GET_DDL 函数时返回的输出。

以下示例创建一个具有列表达式的表。

CREATE OR REPLACE TABLE table_with_default(x INT, v TEXT DEFAULT x::VARCHAR);

调用 GET_DDL 函数所返回的元数据会发生如下更改：

SELECT GET_DDL('table', 'table_with_default');
Metadata before the change:
create or replace TABLE TABLE_WITH_DEFAULT ( |
      X NUMBER(38,0),
      V VARCHAR(16777216) DEFAULT CAST(TABLE_WITH_DEFAULT.X AS VARCHAR(16777216))
);
Metadata after the change:
create or replace TABLE TABLE_WITH_DEFAULT ( |
      X NUMBER(38,0),
      V VARCHAR(16777216) DEFAULT CAST(TABLE_WITH_DEFAULT.X AS VARCHAR)
);

外部表

以下示例创建了一个外部表：

CREATE OR REPLACE EXTERNAL TABLE ext_table(
    data_str VARCHAR AS (value:c1::TEXT))
  LOCATION = @csv_stage
  AUTO_REFRESH = false
  FILE_FORMAT =(type = csv);

调用 GET_DDL 函数所返回的元数据会发生如下更改：

SELECT GET_DDL('table', 'ext_table');
Metadata before the change:
create or replace external table EXT_TABLE(
      DATA_STR VARCHAR(16777216) AS (CAST(GET(VALUE, 'c1') AS VARCHAR(16777216))))
location=@CSV_STAGE/
auto_refresh=false
file_format=(TYPE=csv)
;
Metadata after the change:
create or replace external table EXT_TABLE(
      DATA_STR VARCHAR(16777216) AS (CAST(GET(VALUE, 'c1') AS VARCHAR)))
location=@CSV_STAGE/
auto_refresh=false
file_format=(TYPE=csv)
;

返回 SYSTEM$TYPEOF 的元数据

The metadata returned for a call to the SYSTEM$TYPEOF function changes in the following way:

SELECT SYSTEM$TYPEOF(REPEAT('a',10));
Metadata before the change:
VARCHAR(16777216)[LOB]
Metadata after the change:
VARCHAR[LOB]

返回 SHOW COLUMNS 的元数据

This change affects both existing and new tables. The metadata returned for a SHOW COLUMNS statement changes in the following way:

CREATE OR REPLACE TABLE t AS
  SELECT TO_VARIANT('abc') AS col;

SHOW COLUMNS IN t;
Metadata before the change:
{
  "type":"VARIANT",
  "length":16777216,
  "byteLength":16777216,
  "nullable":true,
  "fixed":false
}
Metadata after the change:
{
  "type":"VARIANT",
  "nullable":true,
  "fixed":false
}

加载和处理大于 16 MB 的对象的变更

在尝试使用以下类型的操作加载或处理大于 16 MB 的对象时，有些行为变更会影响情况：

Note

此列表并不详尽。

通过扫描暂存区上的文件加载数据

当您尝试通过扫描暂存区上的文件来加载大于 16 MB 的数据时，将返回错误消息。

使用 CREATE TABLE AS SELECT 加载整个大型对象

当尝试使用 CREATE TABLE AS SELECT 语句来加载大于 16 MB（对于 VARCHAR、VARIANT、OBJECT 和 ARRAY）或大于 8 MB（对于 BINARY、GEOMETRY 或 GEOGRAPHY）的对象时，会出现不同的错误消息。错误取决于源的类型。在这种情况下，使用 INSERT INTO SELECT 语句时，也会出现同样的消息变更。

从一个 JSON 源加载整个大型对象

以下示例尝试使用 CREATE TABLE AS SELECT 从一个 JSON 源加载一个大于 16 MB 的整个对象：

CREATE OR REPLACE FILE FORMAT json_format TYPE = JSON;

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR) AS
  SELECT $1::VARCHAR
    FROM @lob_int_stage/driver_status.json.gz (FILE_FORMAT => 'json_format');
Error message before the change:
100069 (22P02): Error parsing JSON: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>
从一个 XML 源加载一个完整的大型对象

以下示例尝试使用 CREATE TABLE AS SELECT 从一个 XML 源加载一个大于 16 MB 的整个对象：

CREATE or REPLACE FILE FORMAT xml_format TYPE = XML;

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR) AS
  SELECT $1 AS XML
    FROM @lob_int_stage/large_xml.xte (FILE_FORMAT => 'xml_format');
Error message before the change:
100100 (22P02): Error parsing XML: document is too large, max size 16777216 bytes
Error message after the change:
100078 (22000): String '<string_preview>' is too long and would be truncated

Loading a whole large object using COPY INTO <table_name> … FROM SELECT

A different error message appears when you try to use a COPY INTO <table_name> … FROM SELECT statement to load objects that are larger than 16 MB for VARCHAR, VARIANT, OBJECT, and ARRAY (or larger than 8 MB for BINARY, GEOMETRY, or GEOGRAPHY). The error depends on the type of the source.

Important

If you attempt to load data that contains objects larger than 16 MB using the COPY INTO command with ON_ERROR=CONTINUE and rely on the error messages written in the error log, the change in the error message could cause problems in logic that depends on the error message.

从一个 JSON 源加载整个大型对象

The following example tries to load a whole object larger than 16 MB from a JSON source using COPY INTO <table_name> … FROM SELECT:

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR);

COPY INTO table_varchar FROM (
  SELECT $1::VARCHAR
    FROM @lob_int_stage/driver_status.json.gz (FILE_FORMAT => 'json_format'));
Error message before the change:
100069 (22P02): Error parsing JSON: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>
从一个 JSON 源加载大型嵌套对象

以下示例尝试在访问大型嵌套对象时加载 JSON 数据：

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR);

COPY INTO table_varchar FROM (
  SELECT $1:"Driver_Status"
    FROM @lob_int_stage/driver_status.json.gz (FILE_FORMAT => 'json_format'));
Error message before the change:
100069 (22P02): Max LOB size (16777216) exceeded, actual size of parsed column is <object_size>
Error message after the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>
从一个 XML 源加载一个完整的大型对象

The following example tries to load a whole object larger than 16 MB from an XML source using COPY INTO <table_name> … FROM SELECT:

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR);

COPY INTO table_varchar FROM (
  SELECT $1::VARCHAR AS lob_column
    FROM @lob_int_stage/large_xml.xte (FILE_FORMAT => 'xml_format'));
Error message before the change:
100100 (22P02): Error parsing XML: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <object_size>

Loading a whole large object using COPY INTO <table_name> … FROM <stage_or_location>

A different error message appears when you try to use a COPY INTO <table_name> … FROM <stage_or_location> statement to load objects that are larger than 16 MB for VARCHAR, VARIANT, OBJECT, and ARRAY (or larger than 8 MB for BINARY, GEOMETRY, or GEOGRAPHY). The error depends on the type of the source.

If you use the COPY command with large objects, queries might fail even when the ON_ERROR parameter is set to CONTINUE. For more information, see the usage notes for the COPY command.

Important

If you attempt to load data that contains objects larger than 16 MB using the COPY INTO command with ON_ERROR=CONTINUE and rely on the error messages written in the error log, the change in the error message could cause problems in logic that depends on the message.

从一个 JSON 源加载整个大型对象

The following example tries to load a whole object larger than 16 MB from a JSON source using COPY INTO <table_name> … FROM <stage_or_location>:

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR);

COPY INTO table_varchar (lob_column)
  FROM @lob_int_stage/driver_status.json.gz
  FILE_FORMAT = (FORMAT_NAME = json_format);
Error message before the change:
100069 (22P02): Error parsing JSON: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>
从一个 XML 源加载一个完整的大型对象

The following example tries to load a whole object larger than 16 MB from an XML source using COPY INTO <table_name> … FROM <stage_or_location>:

CREATE OR REPLACE TABLE table_varchar (lob_column VARCHAR);

COPY INTO table_varchar (lob_column)
  FROM @lob_int_stage/large_xml.xte
  FILE_FORMAT = (FORMAT_NAME = xml_format);
Error message before the change:
100100 (22P02): Error parsing XML: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>

从一个源文件查询整个大型对象

由于当前结果集中不允许大于 16 MB 的对象，因此当您尝试从源文件中查询大于 16 MB（对于 VARCHAR、VARIANT、OBJECT 和 ARRAY）或大于 8 MB（对于 BINARY、GEOMETRY 或 GEOGRAPHY）的对象时，会出现不同的错误消息。错误取决于源的类型。

从一个 JSON 源查询整个大型对象

以下示例尝试从一个 JSON 源查询一个大于 16 MB 的整个对象：

SELECT $1
  FROM @lob_int_stage/driver_status.json.gz (FILE_FORMAT => 'json_format');
Error message before the change:
100069 (22P02): Error parsing JSON: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): The data length in result column $1 is not supported by this version of the client. Actual length <actual_length> exceeds supported length of 16777216.

从一个 XML 源查询整个大型对象

以下示例尝试从一个 XML 源查询一个大于 16 MB 的整个对象：

SELECT $1 as lob_column
  FROM @lob_int_stage/large_xml.xte (FILE_FORMAT => 'xml_format');
Error message before the change:
100100 (22P02): Error parsing XML: document is too large, max size 16777216 bytes
Error message after the change:
100082 (22000): The data length in result column $1 is not supported by this version of the client. Actual length <actual_length> exceeds supported length of 16777216.

从一个 CSV 源查询整个大型对象

以下示例尝试从一个 CSV 源查询一个大于 16 MB 的整个对象：

SELECT $1
  FROM @lob_int_stage/driver_status.csv.gz (FILE_FORMAT => 'csv_format');
Error message before the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <object_size>
Error message after the change:
100082 (22000): The data length in result column $1 is not supported by this version of the client. Actual length <actual_length> exceeds supported length of 16777216.

从一个 Parquet 源查询整个大型对象

以下示例尝试从一个 Parquet 源查询一个大于 16 MB 的整个对象：

SELECT $1
  FROM @lob_int_stage/driver_status.parquet (FILE_FORMAT => 'parquet_format');
Error message before the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <object_size>
Error message after the change:
100082 (22000): The data length in result column $1 is not supported by this version of the client. Actual length <actual_length> exceeds supported length of 16777216.

在查询结果中包含大型对象

您现在可以在内存中创建大于16 MB 的对象。然而，您不能将这些对象包含在查询结果中或将它们存储在表中。当您尝试这样做时，将返回错误消息。

尝试在查询结果中包含一个大于16 MB 的对象

以下查询尝试连接两个大的字符串：

SELECT large_str || large_str FROM lob_strings;
Error message before the change:
100078 (22000): String '<preview_of_string>' is too long and would be truncated in 'CONCAT'
Error message after the change:
100067 (54000): The data length in result column <column_name> is not supported by this version of the client. Actual length <actual_size> exceeds supported length of 16777216.

尝试在表中存储一个大于16 MB 的对象

以下 CREATE TABLE AS SELECT 语句尝试连接两个大的字符串：

CREATE OR REPLACE TABLE table_varchar
  AS SELECT large_str || large_str as LOB_column
  FROM lob_strings;
Error message before the change:
100078 (22000): String '<preview_of_string>' is too long and would be truncated in 'CONCAT'
Error message after the change:
100067 (54000): The data length in result column <column_name> is not supported by this version of the client. Actual length <actual_size> exceeds supported length of 16777216.

使用聚合创建一个大型对象

当您尝试创建一个大于16 MB 的对象并返回其输出时，会返回一条错误消息。

The following example uses the ARRAY_AGG function in a query of a large object column:

SELECT ARRAY_AGG(status) FROM lob_object;
Error message before the change:
100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <actual_size>
Error message after the change:
100067 (54000): The data length in result column <column_name> is not supported by this version of the client. Actual length <actual_size> exceeds supported length of 16777216.

参考：1779