将大查询DDL转换为雪花DDL

Question

有一个带有嵌套列的大查询表，表的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);

此表中的示例数据如下所示：

[{
  "pipeline_metadata": {
    "uuid": "d2aae738-ddbe-43fc-b1a0-a0a3d7f4a66c",
    "timestamp": "2022-10-06 00:44:53.804000 UTC",
    "restream_count": "0",
    "pubsub_subscription_name": "raw-user-events-data-ingestor-subscription"
  },
  "event_name": "search",
  "insertId": "5751196922008325",
  "eventID": "rCz_ZG70T-CYlJ7uAgWhvxOeRKqoevLb",
  "timestamp": "2022-10-06 00:44:52.792000 UTC",
  "metadata": {
    "userID": "2235338",
    "uuid": "3cf13e3f499339f45bc1a344bd5c83866ebae85b6c89255ff203467552157aaa",
    "sessionID": "140b56af96c6331e8e5cc88721143d788a30b3c35fe28dac2c2e3ceeb1ee4948",
    "platform": "web-app",
    "prime_status": "subscription",
    "created_at": "2022-10-06 00:44:52.767000 UTC",
    "city": "Woodside",
    "country": "United States",
    "embed_url": null,
    "embed_domain": null,
    "deviceID": "db0f3955624d5541b587ac4661a25dfd",
    "device_type": "desktop",
    "state": "New York",
    "metadata_schema_version": "latest",
    "schema_version": "latest",
    "ipAddress": null
  },
  "properties": "{\"action\":\"focus-on-input\",\"source\":\"dashboard\",\"page_path\":\"/home/dashboard\"}"
}]

我试图在雪花中创建一个类似的表，嵌套的列结构应该保留在雪花中，因为这是从大查询到雪花的迁移，因此这两个仓库之间的结构应该是相同的。

我能够为雪花准备DDL表，但是列不是嵌套的，类似于。

CREATE or replace TABLE test.dbt."USER_EVENTS"
(
pipeline_metadata variant,
event_name STRING,
insertId STRING,
eventID STRING,
time_stamp TIMESTAMP,
metadata variant,
properties STRING
)
CLUSTER BY (DATE(time_stamp));

如何为雪花表构建一个DDL，其中该表的结构与类似。

Answer 1

根据定义“相似”的方式(在DBMSes之间没有给定功能之间的直接对应)，您可以简单地将STRUCT扁平化

CREATE TABLE stg.raw_data.user_events
(
  uuid STRING,
  meta_timestamp TIMESTAMP,
  restream_count INT,
  pubsub_subscription_name STRING,
  event_name STRING,
  insertId STRING,
  eventID STRING,
  time_stamp TIMESTAMP,
  userID INT, 
  useer_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 VARIANT
)
 CLUSTER BY (time_stamp)

这当然有点相似。如果有足够的相似之处，我们就无法做出决定。