Azure Synapse Pipeline复制BigQuery层级结构数据遇问题求助

一、BigQuery源端查询语法（展开嵌套列） #

针对Google Cloud账单导出表的嵌套STRUCT类型列（如service、sku），必须在源端查询中显式展开这些字段，才能让Copy Data Pipeline识别所有层级列。示例SQL如下：

SELECT
  -- 顶层基础列
  billing_account_id,
  project_id,
  cost,
  currency,
  -- 展开嵌套的service结构
  service.id AS service_id,
  service.description AS service_description,
  -- 展开嵌套的sku结构
  sku.id AS sku_id,
  sku.description AS sku_description,
  -- 修复日期时间格式（转换为Azure兼容的UTC时间戳）
  TIMESTAMP_MICROS(usage_start_time) AS usage_start_time_utc,
  TIMESTAMP_MICROS(usage_end_time) AS usage_end_time_utc,
  -- 按需展开其他嵌套字段
  labels,
  system_labels
FROM `your-project-id.billing_dataset.billing_export_v1_XXXXXX`

关键说明：

• 替换your-project-id.billing_dataset.billing_export_v1_XXXXXX为你的实际账单表路径
• 嵌套STRUCT字段通过.访问子字段，用别名重命名避免列名冲突
• 账单表的usage_start_time/usage_end_time为微秒级整数，用TIMESTAMP_MICROS()转换为标准时间戳，解决Azure端时间值不准确问题

二、Copy Data Pipeline配置调整 #

1. 源端设置：

   • 在Copy Data工具中选择「Query」作为数据提取模式，粘贴上述查询语句
   • 点击预览验证：此时应能看到所有展开后的列

2. 接收器设置：

   • 优先选择Parquet格式：原生支持嵌套结构，压缩比和查询性能远优于CSV/JSON
   • 若需扁平结构，选择CSV格式并确保字段映射完整

3. 复制行为配置：

   • 选择Flatten Hierarchy：自动将嵌套结构展开为扁平列，彻底避免接收器端列缺失
   • 若需保留嵌套结构，选择Preserve Hierarchy（仅适用于Parquet/JSON格式）

三、Serverless SQL Pool后续查询处理 #

若需用Serverless SQL Pool查询ADLS Gen2中的数据：

• 针对Parquet格式，可直接用OPENROWSET查询，嵌套字段通过JSON_VALUE或CROSS APPLY展开：

SELECT
  billing_account_id,
  service_id,
  service_description,
  JSON_VALUE(labels, '$.key1') AS label_key1
FROM OPENROWSET(
  BULK 'https://youradlsgen2.dfs.core.windows.net/container/path/*.parquet',
  FORMAT = 'PARQUET'
) AS [result]

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