Azure Data Factory从BigQuery导出数据时Copy Data活动报错

错误现象 #

使用Azure Data Factory（ADF）的Copy Data活动从BigQuery的Google Analytics events_2023*通配符表导出数据时，触发以下错误：

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][BigQuery] (310) Storage API Error: Error thrown in GAvroDataCell:BuildDefaultAvroCell.Invalid BigQueryType specified for copy/move operation. Type is : 0,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][BigQuery] (310) Storage API Error: Error thrown in GAvroDataCell:BuildDefaultAvroCell.Invalid BigQueryType specified for copy/move operation. Type is : 0,Source=Microsoft ODBC Driver for Google BigQuery,'

• BigQuery链接服务测试正常，数据集预览能返回数据，但Copy Data活动的预览和执行均失败；
• 指定具体日期表（如events_20230520）时，Copy Data活动可正常运行。

原因分析 #

通配符查询会扫描所有匹配的表，若不同表之间存在字段类型不一致（比如某字段在部分表中为NULL类型、或字段定义有差异），BigQuery的ODBC驱动在解析统一Schema时会识别出无法处理的类型（代码0），导致ADF Copy活动报错。

解决方案 #

方案1：显式强制统一字段类型 #

在查询中对所有字段用CAST指定明确类型，避免通配符表之间的类型差异干扰驱动识别：

@concat('SELECT
  CAST(event_date AS STRING) AS event_date,
  CAST(event_timestamp AS INT64) AS event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  TO_JSON(event_params) AS event_params,
  CAST(event_previous_timestamp AS INT64) AS event_previous_timestamp,
  CAST(event_value_in_usd AS FLOAT64) AS event_value_in_usd,
  CAST(event_bundle_sequence_id AS INT64) AS event_bundle_sequence_id,
  CAST(event_server_timestamp_offset AS INT64) AS event_server_timestamp_offset,
  TO_JSON(privacy_info) AS privacy_info,
  TO_JSON(user_properties) AS user_properties,
  CAST(user_first_touch_timestamp AS INT64) AS user_first_touch_timestamp,
  TO_JSON(user_ltv) AS user_ltv,
  TO_JSON(device) AS device,
  TO_JSON(geo) AS geo,
  TO_JSON(app_info) AS app_info,
  TO_JSON(traffic_source) AS traffic_source,
  CAST(stream_id AS STRING) AS stream_id,
  CAST(platform AS STRING) AS platform,
  TO_JSON(event_dimensions) AS event_dimensions,
  TO_JSON(ecommerce) AS ecommerce,
  TO_JSON(items) AS items
FROM `xxxxxxxproject-bigquery.analytics_310106672.events_2023*`'
)

方案2：用BigQuery视图封装通配符查询 #

让BigQuery提前处理Schema一致性问题，再让ADF查询视图：

1. 在BigQuery中创建视图，基于通配符表构建统一Schema：

CREATE OR REPLACE VIEW analytics_310106672.vw_events_2023 AS
SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  TO_JSON(event_params) AS event_params,
  event_previous_timestamp,
  event_value_in_usd,
  event_bundle_sequence_id,
  event_server_timestamp_offset,
  TO_JSON(privacy_info) AS privacy_info,
  TO_JSON(user_properties) AS user_properties,
  user_first_touch_timestamp,
  TO_JSON(user_ltv) AS user_ltv,
  TO_JSON(device) AS device,
  TO_JSON(geo) AS geo,
  TO_JSON(app_info) AS app_info,
  TO_JSON(traffic_source) AS traffic_source,
  stream_id,
  platform,
  TO_JSON(event_dimensions) AS event_dimensions,
  TO_JSON(ecommerce) AS ecommerce,
  TO_JSON(items) AS items
FROM `xxxxxxxproject-bigquery.analytics_310106672.events_2023*`

2. 在ADF的Copy活动中直接查询该视图，不再使用通配符表名。

方案3：分批次遍历具体表名 #

通过ADF的ForEach活动逐个处理具体表，规避通配符的Schema问题：

1. 用Lookup活动查询BigQuery的信息架构，获取所有匹配的表名：

SELECT table_name FROM `xxxxxxxproject-bigquery.analytics_310106672.INFORMATION_SCHEMA.TABLES` WHERE table_name LIKE 'events_2023%'

2. 将Lookup结果作为ForEach的输入，在Copy活动中动态替换表名：

@concat('SELECT ... FROM `xxxxxxxproject-bigquery.analytics_310106672.', item().table_name, '`')

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