BigQuery创建Parquet表时嵌套字段过深（17层）的解决方法咨询

错误信息 #

Failed to create table: Error while reading data, error message: Field 'produit.elements.bag.array_element.couche_matiere.bag.array_element.composition_coloris.bag.array_element.composition.bag.array_element.libelle_compo.bag.array_element.code_langue' is nested too deeply. It has definition level 17 while maximum allowed definition level is 15.

以下是无需修改原schema逻辑的可行方案：

方案1：外部表+自定义Schema映射深层字段为JSON字符串 #

手动指定外部表的Schema，将超过15层的嵌套结构映射为STRING类型（存储JSON格式），规避层级限制。查询时通过JSON函数解析回原结构。

创建外部表示例

CREATE OR REPLACE EXTERNAL TABLE `your-project.your-dataset.external_parquet_table`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://your-bucket/path/to/your/file.parquet'],
  schema = """
    [
      {
        "name": "produit",
        "type": "STRUCT",
        "fields": [
          {
            "name": "elements",
            "type": "STRUCT",
            "fields": [
              {
                "name": "bag",
                "type": "ARRAY",
                "fields": [
                  {
                    "name": "array_element",
                    "type": "STRUCT",
                    "fields": [
                      {
                        "name": "couche_matiere",
                        "type": "STRUCT",
                        "fields": [
                          {
                            "name": "bag",
                            "type": "ARRAY",
                            "fields": [
                              {
                                "name": "array_element",
                                "type": "STRUCT",
                                "fields": [
                                  {
                                    "name": "composition_coloris",
                                    "type": "STRUCT",
                                    "fields": [
                                      {
                                        "name": "bag",
                                        "type": "ARRAY",
                                        "fields": [
                                          {
                                            "name": "array_element",
                                            "type": "STRUCT",
                                            "fields": [
                                              {
                                                "name": "composition",
                                                "type": "STRUCT",
                                                "fields": [
                                                  {
                                                    "name": "bag",
                                                    "type": "ARRAY",
                                                    "fields": [
                                                      {
                                                        "name": "array_element",
                                                        "type": "STRUCT",
                                                        "fields": [
                                                          {
                                                            "name": "libelle_compo",
                                                            "type": "STRING"
                                                          }
                                                        ]
                                                      }
                                                    ]
                                                  }
                                                ]
                                              }
                                            ]
                                          }
                                        ]
                                      }
                                    ]
                                  }
                                ]
                              }
                            ]
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  """
);

查询解析示例

SELECT
  -- 解析深层字段
  JSON_EXTRACT_SCALAR(
    produit.elements.bag[OFFSET(0)].array_element.couche_matiere.bag[OFFSET(0)].array_element.composition_coloris.bag[OFFSET(0)].array_element.composition.bag[OFFSET(0)].array_element.libelle_compo,
    '$.code_langue'
  ) AS code_langue,
  -- 保留原结构的JSON字段
  produit.elements.bag[OFFSET(0)].array_element.couche_matiere.bag[OFFSET(0)].array_element.composition_coloris.bag[OFFSET(0)].array_element.composition.bag[OFFSET(0)].array_element.libelle_compo AS libelle_compo_json
FROM `your-project.your-dataset.external_parquet_table`

方案2：预处理Parquet文件，打包深层嵌套为JSON #

用PySpark等工具读取原Parquet文件，将超过15层的嵌套结构转为JSON字符串后重新写入Parquet，再导入BigQuery。这种方式从源文件层面规避层级问题，同时保留原schema的信息。

PySpark处理示例

from pyspark.sql import SparkSession
import pyspark.sql.functions as F

spark = SparkSession.builder.appName("ProcessDeepNestedParquet").getOrCreate()

# 读取原Parquet文件
df = spark.read.parquet("gs://your-bucket/path/to/original/file.parquet")

# 将深层嵌套的libelle_compo结构转为JSON字符串
df_transformed = df.withColumn(
  "produit.elements.bag.array_element.couche_matiere.bag.array_element.composition_coloris.bag.array_element.composition.bag.array_element.libelle_compo",
  F.to_json("produit.elements.bag.array_element.couche_matiere.bag.array_element.composition_coloris.bag.array_element.composition.bag.array_element.libelle_compo")
)

# 写入处理后的Parquet文件
df_transformed.write.mode("overwrite").parquet("gs://your-bucket/path/to/processed/file.parquet")

处理完成后，直接用新的Parquet文件创建BigQuery表即可，查询时通过JSON函数解析深层字段。

方案3：创建视图重组原Schema结构 #

先创建一个兼容BigQuery层级限制的基础表，再通过视图将JSON字段解析回完整的嵌套结构，对外展示原schema，让用户查询时体验一致。

创建视图示例

CREATE OR REPLACE VIEW `your-project.your-dataset.original_schema_view` AS
SELECT
  STRUCT(
    STRUCT(
      ARRAY(
        SELECT AS STRUCT
          STRUCT(
            ARRAY(
              SELECT AS STRUCT
                STRUCT(
                  ARRAY(
                    SELECT AS STRUCT
                      STRUCT(
                        ARRAY(
                          SELECT AS STRUCT
                            STRUCT(
                              ARRAY(
                                SELECT AS STRUCT
                                  STRUCT(
                                    -- 解析JSON字段回原结构
                                    JSON_EXTRACT_SCALAR(libelle_compo, '$.code_langue') AS code_langue,
                                    JSON_EXTRACT_SCALAR(libelle_compo, '$.libelle') AS libelle
                                  ) AS array_element
                                FROM UNNEST(p.composition.bag)
                              ) AS bag
                            ) AS composition
                          ) AS array_element
                        FROM UNNEST(cc.bag)
                      ) AS composition_coloris
                    ) AS array_element
                  FROM UNNEST(cm.bag)
                ) AS couche_matiere
              ) AS array_element
            FROM UNNEST(e.bag)
          ) AS elements
        FROM UNNEST([produit]) p
      ) AS produit
    ) AS produit
FROM `your-project.your-dataset.base_table`

内容的提问来源于stack exchange，提问作者Fares DAOUD