100132 data data_error ai_generated true

Snowflake: Error parsing JSON — Object size exceeds maximum length of 16777216 bytes

ID: data/snowflake-semi-structured-field-exceeds-limit

Also available as: JSON · Markdown · 中文
85%Fix Rate
88%Confidence
1Evidence
2023-06-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Snowflake 7.0 active — — —
Snowflake 8.0 active — — —

Root Cause

A single JSON object in a VARIANT column exceeds the 16 MB size limit for semi-structured data fields in Snowflake.

generic

中文

VARIANT列中的单个JSON对象超过了Snowflake中半结构化数据字段的16 MB大小限制。

Official Documentation

https://docs.snowflake.com/en/sql-reference/data-types-semistructured#size-limits

Workarounds

  1. 90% success Split the large JSON object into multiple smaller objects (e.g., array of objects) and load them as separate rows using FLATTEN. 
    Split the large JSON object into multiple smaller objects (e.g., array of objects) and load them as separate rows using FLATTEN.
  2. 80% success Store the JSON as a TEXT column (VARCHAR) instead of VARIANT, then use PARSE_JSON() only when querying smaller subsets. 
    Store the JSON as a TEXT column (VARCHAR) instead of VARIANT, then use PARSE_JSON() only when querying smaller subsets.

中文步骤

  1. 将大的JSON对象拆分为多个较小的对象（例如，对象数组），并使用FLATTEN函数将其加载为单独的行。
  2. 将JSON存储为TEXT列（VARCHAR）而非VARIANT，仅在查询较小的子集时使用PARSE_JSON()。

Dead Ends

Common approaches that don't work:

  1. Increasing the VARIANT column size or using a different data type like TEXT 100% fail

    Snowflake's semi-structured limit is a hard limit; changing column type does not bypass it.

  2. Compressing the JSON data before loading 80% fail

    Compression reduces storage but the parsed object size remains the same; the limit is on the parsed object, not the file.