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

GET_DDL¶

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

SELECT GET_DDL('function', 'udf_varchar(VARCHAR)');

变更前的元数据:

CREATE OR REPLACE FUNCTION "UDF_VARCHAR"("G1" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS '
  ''Hello'' || g1
';

变更后的元数据:

CREATE OR REPLACE FUNCTION "UDF_VARCHAR"("G1" VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS '
  ''Hello'' || g1
';

DESCRIBE FUNCTION¶

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

DESCRIBE FUNCTION udf_varchar(VARCHAR);

变更前的元数据:

+-----------+-------------------+
| property  | value             |
|-----------+-------------------|
| signature | (G1 VARCHAR)      |
| returns   | VARCHAR(16777216) |
| language  | SQL               |
| body      |                   |
|           |   'Hello' || g1   |
|           |                   |
+-----------+-------------------+

变更后的元数据:

+-----------+-------------------+
| property  | value             |
|-----------+-------------------|
| signature | (G1 VARCHAR)      |
| returns   | VARCHAR           |
| language  | SQL               |
| body      |                   |
|           |   'Hello' || g1   |
|           |                   |
+-----------+-------------------+

事件表¶

对于返回 VARCHAR 或 BINARY 值作为输出的新用户定义函数，事件表 的 RESOURCE_ATTRIBUTES 列中的 snow.executable.name 属性会发生如下更改：

变更前的元数据:

{
  "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"
}

Copy

变更后的元数据:

{
  "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"
}

Copy

外部表¶

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

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');

变更前的元数据:

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)
;

变更后的元数据:

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)
;

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

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

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

• 从一个 XML 源加载一个完整的大型对象

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');

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');

使用 COPY INTO <table_name> ...FROM SELECT¶

当尝试使用 COPY INTO <table_name> ...FROM SELECT 语句来加载大于 16 MB（对于 VARCHAR、VARIANT、OBJECT 和 ARRAY）或大于 8 MB（对于 BINARY、GEOMETRY 或 GEOGRAPHY）的对象时，会出现不同的错误消息。错误取决于源的类型。

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

• 从一个 JSON 源加载大型嵌套对象

• 从一个 XML 源加载一个完整的大型对象

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'));

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'));

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'));

使用 COPY INTO <table_name> ...FROM <stage_or_location> 加载整个大型对象¶

当尝试使用 COPY INTO <table_name> ...FROM <stage_or_location> 语句来加载大于 16 MB（对于 VARCHAR、VARIANT、OBJECT 和 ARRAY）或大于 8 MB（对于 BINARY、GEOMETRY 或 GEOGRAPHY）的对象时，会出现不同的错误消息。错误取决于源的类型。

如果使用 COPY 命令来处理大型对象，即使将 ON_ERROR 参数设置为 CONTINUE，查询也可能会失败。有关更多信息，请参阅 COPY 命令的使用说明。

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

• 从一个 XML 源加载一个完整的大型对象

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);

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);

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

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

SELECT $1
  FROM @lob_int_stage/driver_status.json.gz (FILE_FORMAT => 'json_format');

变更前的错误消息:

100069 (22P02): Error parsing JSON: document is too large, max size 16777216 bytes

变更后的错误消息:

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');

变更前的错误消息:

100100 (22P02): Error parsing XML: document is too large, max size 16777216 bytes

变更后的错误消息:

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');

变更前的错误消息:

100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <object_size>

变更后的错误消息:

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');

变更前的错误消息:

100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is <object_size>

变更后的错误消息:

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 的对象¶

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

SELECT large_str || large_str FROM lob_strings;

变更前的错误消息:

100078 (22000): String '<preview_of_string>' is too long and would be truncated in 'CONCAT'

变更后的错误消息:

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;

变更前的错误消息:

100078 (22000): String '<preview_of_string>' is too long and would be truncated in 'CONCAT'

变更后的错误消息:

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.