将BigQuery含嵌套列的表DDL转换为Snowflake等效DDL

需要将带有嵌套STRUCT的BigQuery表迁移到Snowflake时，需用Snowflake的结构化STRUCT语法替代原DDL中模糊的VARIANT类型，以此保留与原表完全一致的嵌套结构和字段约束。

原BigQuery表DDL #

CREATE TABLE stg.raw_data.user_events
(
pipeline_metadata STRUCT<uuid STRING, timestamp TIMESTAMP, restream_count INT64, pubsub_subscription_name STRING>,
event_name STRING,
insertId STRING,
eventID STRING,
timestamp TIMESTAMP,
metadata STRUCT<userID INT64, uuid STRING, sessionID STRING, platform STRING, prime_status STRING, created_at TIMESTAMP, city STRING, country STRING, embed_url STRING, embed_domain STRING, deviceID STRING, device_type STRING, state STRING, metadata_schema_version STRING, schema_version STRING, ipAddress STRING>,
properties STRING
)
PARTITION BY DATE(timestamp);

对应Snowflake表DDL（保留嵌套结构） #

CREATE OR REPLACE TABLE test.dbt."USER_EVENTS"
(
    "pipeline_metadata" STRUCT(
        "uuid" STRING,
        "timestamp" TIMESTAMP_NTZ,
        "restream_count" INT,
        "pubsub_subscription_name" STRING
    ),
    "event_name" STRING,
    "insertId" STRING,
    "eventID" STRING,
    "timestamp" TIMESTAMP_NTZ,
    "metadata" STRUCT(
        "userID" INT,
        "uuid" STRING,
        "sessionID" STRING,
        "platform" STRING,
        "prime_status" STRING,
        "created_at" TIMESTAMP_NTZ,
        "city" STRING,
        "country" STRING,
        "embed_url" STRING,
        "embed_domain" STRING,
        "deviceID" STRING,
        "device_type" STRING,
        "state" STRING,
        "metadata_schema_version" STRING,
        "schema_version" STRING,
        "ipAddress" STRING
    ),
    "properties" STRING
)
CLUSTER BY (DATE("timestamp"));

关键细节说明 #

• 嵌套结构实现：Snowflake使用STRUCT()语法定义嵌套列，与BigQuery的STRUCT<>逻辑一致，通过明确的子字段定义保留结构化约束，避免VARIANT类型的模糊性。
• 类型映射匹配：
  • BigQuery的INT64对应Snowflake的INT（或NUMBER(38,0)，INT为简写）
  • BigQuery的TIMESTAMP对应Snowflake的TIMESTAMP_NTZ（无时区时间戳，与BigQuery默认TIMESTAMP行为匹配；若需时区支持可改用TIMESTAMP_TZ）
  • STRING类型直接对应，保持一致
• 列名大小写保留：Snowflake默认会将未加双引号的列名转为大写，若要保留原表的驼峰式列名（如insertId、eventID），必须用双引号包裹列名。
• 分区/聚类适配：BigQuery的PARTITION BY DATE(timestamp)在Snowflake中可用CLUSTER BY (DATE("timestamp"))实现类似的分区优化；若需Snowflake原生分区策略，也可改用PARTITION BY DATE("timestamp")。

内容的提问来源于stack exchange，提问作者Teja Goud Kandula