Databricks にて JDBC によりビューを参照する際に必要な権限に関する検証

## 概要

Databricks にて BigQuery から JDBC によりビューを参照する際に必要となる Google Cloud の権限に関する検証結果を共有します。検証の結果、下記の権限が必要となります。

- BigQuery Data Viewer
- BigQuery Job User
- BigQuery Read Session User（4 万レコード以上の場合）

Databricks Federation の動作も確認しています。Databricks Runtime 16.1 以降のクラスターでは Spark Connector が利用される仕様変更がされたため、 16.4 と 15.4 のバージョンでそれぞれ実行しています。仕様変更内容について下記の記事で整理しています。

- [Databricks の Lakehouse federation にて BigQuery をソースにしている場合に Databricks Runtime 16.1 以降のクラスターでは Spark Connector が利用される仕様に変更 #Databricks - Qiita](https://qiita.com/manabian/items/4e87c7d185840847c674)

## 事前準備

### BigQuery にオブジェクトを作成

```sql
-- データセットを作成
CREATE SCHEMA IF NOT EXISTS `auth_test_04`;

-- テーブルを作成
CREATE OR REPLACE TABLE `auth_test_04.table_01` AS
SELECT *
FROM UNNEST([
  STRUCT(1 AS id, 'Alice' AS name, 100 AS score),
  STRUCT(2 AS id, 'Bob'   AS name,  95 AS score),
  STRUCT(3 AS id, 'Carol' AS name,  98 AS score)
]);

-- ビューを作成
CREATE OR REPLACE VIEW `auth_test_04.view_01` AS
SELECT * FROM `auth_test_04.table_01`;


SELECT * FROM `auth_test_04.view_01`;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/418d27b3-3ea3-41e3-aa0c-d7f7a433085f.png)

### サービスアカウント作後にキーを取得

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/fe694934-1bce-4dde-8d43-0fc4c65550a5.png)


### BigQuery を Databricks の外部カタログに登録

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c0294a3a-c769-45ca-8e62-38c3ce180e08.png)


### Databricks にてノートブック作成後に実行するコードを記述

```python
json_str = """{json_key}"""
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/dc3f9372-bbfe-444e-87fb-e6bd315e4cc1.png)

```python
import base64

encoded_string = base64.b64encode(json_str).decode('utf-8')

# プロジェクト ID を設定
parent_project_id = "axial-triode-368703"

# データ取得元のテーブル名を設定
table_name = "auth_test_02.view_01"

df = (
    spark.read
    .format("bigquery")
    .option("parentProject", parent_project_id)
    .option("credentials", encoded_string)
    .option("viewsEnabled", True)
    .option("table",table_name)
    .load()
)

df.limit(50).display()
```

## 4万レコード未満のビューにおける権限検証

### `BigQuery Data Viewer`権限の追加

権限不足によるエラーが発生します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6aa92cbe-3258-4941-82e8-026735b1f585.png)

> Py4JJavaError: An error occurred while calling o900.load.
> : java.sql.SQLException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : 403 Forbidden
> POST https://bigquery.googleapis.com/bigquery/v2/projects/axial-triode-368703/jobs
> {
>   "code": 403,
>   "errors": [
>     {
>       "domain": "global",
>       "message": "Access Denied: Project axial-triode-368703: User does not have bigquery.jobs.create permission in project axial-triode-368703.",
>       "reason": "accessDenied"
>     }
>   ],
>   "message": "Access Denied: Project axial-triode-368703: User does not have bigquery.jobs.create permission in project axial-triode-368703.",
>   "status": "PERMISSION_DENIED"
> }.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ffb93286-5312-44f1-a2df-37c0aa06862e.png)


### `BigQuery Job User`権限の追加

データを取得できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a2456c31-6895-4e39-aa2d-07a88da140f3.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4777c35c-8744-4e59-b565-3173984065e2.png)


## 4万レコード以上のビューにおける権限検証

### BigQuery にて 4 万レコード以上あるビューを作成後にデータを取得

権限不足によるエラーが発生します。

```sql
CREATE OR REPLACE VIEW `auth_test_04.shakespeare` AS
SELECT * FROM bigquery-public-data.samples.shakespeare;

SELECT COUNT(*) FROM `auth_test_04.shakespeare`;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9ed7c4b3-5721-4b17-88c9-000b13a08924.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/04a5ae6a-300f-4ecf-9a84-8572ab2cd258.png)

```python
# レコードが多いビューを指定
table_name = "bigquery-public-data.samples.shakespeare"

jdbc_url = (
    "jdbc:bigquery://https://www.googleapis.com/bigquery/v2;"
    f"ProjectId={project_id};"
    "OAuthType=0;"
    f"OAuthServiceAcctEmail={service_account_mail};"
    f"OAuthPvtKey={json_str};"
    "LogLevel=0;"
    "Timeout=600;"
)

bq_df = (
    spark.read.format("jdbc")
    .option("url", jdbc_url)
    .option("driver", driver)
    .option("dbtable", table_name)
    .load()
)

bq_df_count = bq_df.limit(40000).count()
print(bq_df_count)
```

> Py4JJavaError: An error occurred while calling o1045.count.
> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 25.0 failed 4 times, most recent failure: Lost task 0.3 in stage 25.0 (TID 29) (10.139.64.11 executor driver): java.sql.SQLException: [Simba][BigQueryJDBCDriver](100210) Error initializing the Storage API. Message : bigquery.shaded.io.grpc.StatusRuntimeException: PERMISSION_DENIED: request failed: the user does not have 'bigquery.readsessions.create' permission for 'projects/axial-triode-XXXXX'

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2ff082ab-742f-4621-ad6a-f23fe2e3f851.png)

:::note info

4万レコード未満であれば、エラーになりません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6938945b-cd54-41da-a7fd-428274d3f179.png)

:::

### `BigQuery Read Session User`権限を追加

データを取得できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/bcbca38b-c9e1-428c-980d-28aa973842f2.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e4052f3e-431a-4d76-bbed-c00863bb3ef6.png)

## Databricks Lakehouse Fedearation の検証

### Databricks Runtime 16.4 での実行可否確認

権限不足によるエラーが発生します。

> Py4JJavaError: An error occurred while calling t.addCustomDisplayData.
: bigquery.storageapi.shaded.com.google.cloud.bigquery.connector.common.BigQueryConnectorException: Error creating destination table using the following query: [SELECT `id`, `name`, `score` FROM `axial-triode-368703`.`auth_test_04`.`view_01` LIMIT 10001 ]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2c7c79aa-8b90-4563-90e6-f5571a5e6b81.png)

### Databricks Runtime 15.4 での実行可否確認

データを取得できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d4495918-bf73-47c0-986f-a0ddbfff47b2.png)

:::note info

`BigQuery Read Session User`権限がない状態で、 4万レコードを取得しようとするとエラーとなります。

> SparkException: Job aborted due to stage failure: Task 0 in stage 7.0 failed 4 times, most recent failure: Lost task 0.3 in stage 7.0 (TID 10) (10.139.64.10 executor 0): java.sql.SQLException: [Simba][BigQueryJDBCDriver](100210) Error initializing the Storage API. Message : bigquery.shaded.io.grpc.StatusRuntimeException: PERMISSION_DENIED: request failed: the user does not have 'bigquery.readsessions.create' permission for 'projects/axial-triode-XXXXX'

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/61875577-efd5-4255-b504-a8097ef110b3.png)


:::