Azure Databricks中DLT调用BigQuery连接失败，Notebook可正常运行

问题详情 #

在Azure Databricks环境中，同一个BigQuery连接在Notebook中可正常执行，但在Delta Live Table（DLT）流水线中调用时抛出异常：

Notebook中可正常运行的代码（无服务器/标准计算资源均适用） #

spark.read.table('bq_con_name.db_name.tbl_name').display()

DLT流水线中执行失败的代码 #

@dlt.table()
def tbl():
    return (
        spark.read.table('bq_con_name.db_name.tbl_name')
    )

报错信息 #

bigquery.storageapi.shaded.com.google.cloud.bigquery.connector.common.BigQueryConnectorException:
Error creating destination table using the following query: [SELECT
col_1, col_2, col_3, (...) FROM
[project_name].[db_name].[tbl_name] ]

bigquery.storageapi.shaded.com.google.cloud.spark.bigquery.repackaged.com.google.common.util.concurrent.UncheckedExecutionException:
bigquery.storageapi.shaded.com.google.cloud.spark.bigquery.repackaged.com.google.cloud.bigquery.BigQueryException:
Not found: Dataset
[project_name]:databricks_materialization_dataset_[guid] was not found
in location EU

bigquery.storageapi.shaded.com.google.cloud.spark.bigquery.repackaged.com.google.cloud.bigquery.BigQueryException:
Not found: Dataset
[project_name]:databricks_materialization_dataset_[guid] was not found
in location EU

bigquery.storageapi.shaded.com.google.cloud.spark.bigquery.repackaged.com.google.api.client.googleapis.json.GoogleJsonResponseException:
404 Not Found GET
https://bigquery.googleapis.com/bigquery/v2/projects/%5Bproject_name%5D/queries/%5Bother_guid%5D?location=EU&maxResults=0&prettyPrint=false { "code": 404, "errors": [
{
"domain": "global",
"message": "Not found: Dataset [project_name]:databricks_materialization_dataset_[guid] was not found
in location EU",
"reason": "notFound"
} ], "message": "Not found: Dataset [project_name]:databricks_materialization_dataset_[guid] was not found
in location EU", "status": "NOT_FOUND" }

核心原因 #

DLT处理BigQuery数据源时，会自动尝试在BigQuery中创建临时物化数据集（databricks_materialization_dataset_[guid]）用于查询优化，但该数据集不存在或权限不足导致报错；而Notebook模式不会触发此自动物化逻辑，因此可正常运行。

具体修复步骤 #

1. 手动创建物化数据集
   在BigQuery的EU区域，为指定项目（[project_name]）创建报错中提到的databricks_materialization_dataset_[guid]数据集（替换[guid]为实际值）。若不想固定GUID，可创建通用物化数据集后通过配置指定。

2. 配置自定义物化数据集
   在DLT流水线配置中添加Spark参数，指定自定义物化数据集：

   spark.conf.set("spark.databricks.delta.live.materialization.dataset", "your-project.your-custom-materialization-dataset")
   

   或直接在DLT代码中配置：

   @dlt.table(
       spark_conf={"spark.databricks.delta.live.materialization.dataset": "your-project.your-custom-materialization-dataset"}
   )
   def tbl():
       return spark.read.table('bq_con_name.db_name.tbl_name')
   

3. 检查权限配置
   确保DLT流水线使用的身份（服务主体/托管身份）拥有BigQuery以下权限：

   • 物化数据集的bigquery.datasets.create、bigquery.tables.create权限
   • 源BigQuery表的bigquery.tables.getData权限

4. 临时禁用自动物化
   若无需物化优化，可临时禁用该逻辑（注意：会影响查询性能）：

   spark.conf.set("spark.databricks.delta.live.materialization.enabled", "false")
   

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