BigQuery Omni を使って Azure Blob ストレージのデータにアクセスする

# BigQuery Omni を使って Azure Blob ストレージのデータにアクセスする

BigQuery Omni とは、Amazon S3 や Azure Blob ストレージにあるデータに対して BigQuery のクエリを実行できる、クロスクラウドの分析ソリューションです。
BigQuery を使いたいけど分析データは他クラウドから動かしたくない、といった場合に使うことができます。

https://cloud.google.com/bigquery/docs/omni-introduction?hl=ja

この記事では、BigQuery Omni を使って Azure Blob ストレージのデータに BigQuery のクエリを実行してみました。

## 事前調査
### 制限事項
ドキュメントに BigQuery Omni の制限事項が記載されています。

https://cloud.google.com/bigquery/docs/omni-introduction?hl=ja#limitations

BigQuery Omni に対応しているリージョンは、AWS/Azure に一つずつ用意されています。

また、接続先（Amazon S3/Azure Blob ストレージ）固有の制限事項は見つけられませんでした。

### 料金
※料金は 2022/11 時点の情報です。
BigQuery Omni を使うには、BigQuery Reservations で定額料金プランに加入する必要があります。
通常オンデマンド料金ではクエリ実行ができませんが、期間限定で Azure 環境へのアクセスは無料になっています。
> *Azure-US-East では BigQuery Omni をご利用のお客様に対して期間限定で料金が請求されません。ご請求に関する変更については、Google からご連絡いたします。

月額料金はこのようになっており、円安も相まって高額です。
|  | ドル | 円（146円/ドルとして計算） |
|:-:|:-:|:-:|
| AWS  | $2,500  | 365,000円  |
| Azure  | $2,920  | 426,320円  |

Flex Slots という短期間のコミットメントを用意されており、コミットメント購入後60秒経過すればいつでもキャンセルできます。
こちらは一時間ごとの料金が掲載されています。
|  | ドル | 円（146円/ドルとして計算） |
|:-:|:-:|:-:|
| AWS  | $5.00  | 730円  |
| Azure  | $5.80  | 846.8円  |

https://cloud.google.com/bigquery/pricing?hl=ja#bqomni


## 準備
### Azure Blob ストレージを用意する
Azure Blob ストレージアカウントを作成します。
作成時に気を付けることは2点あります。

 -「基本」の地域に `East US 2` を指定する
 -「詳細設定」の 階層型名前空間を有効にする

ストレージアカウントの作成が完了したら、左メニューの「コンテナー」から新規コンテナーを作成します。
![azurestorageコンテナー.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/e24fe086-ad62-74e5-f940-1873972dccf8.png)

作ったコンテナーに BigQuery で読み取りたいデータをアップロードします。
![azurestorageファイルアップロード.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/3c2ab93c-a37c-9252-eb2b-800b91beb836.png)



## 設定
### 接続先情報の作成
今回は非連携IDを使用しました。

https://cloud.google.com/bigquery/docs/omni-azure-create-connection?hl=ja

BigQuery の画面上部にある、「データを追加」から作成します。
![データ追加ボタン.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/cafab8f8-8b69-9ea1-1bcf-8941076daea2.png)

「外部データソースへの接続」を選びます。
![データ追加パネル.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/19847cd9-55da-6420-f4a6-873982184ee6.png)


必要項目を入力します。注意する項目を記載します。

 - 接続タイプ：「Azure 上の BigLake（BigQuery Omni 経由）」
 - 接続ID：BigQuery の外部接続一覧に表示される名前
 - Azure テナント ID：連携する Azure テナント ID。
     - 確認方法：https://learn.microsoft.com/ja-jp/partner-center/find-ids-and-domain-names

![外部データソースパネル.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/bf085e53-7c1a-4fc4-bf34-166693125c5d.png)


外部データソース作成が完了すると BigQuery の外部接続一覧に表示されます。
表示名をクリックすると詳細が確認できます。
![azure接続先詳細.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/d4534b84-d9d3-f52e-6ac4-3d673d3a2280.png)


接続情報一番下の「Azureアカウントへログイン」のリンクからサービスプリンシパルを作成します。
Azure のアクセス許可画面が表示されるので、「承諾」ボタンを押すを画面が遷移し、サービスプリンシパルが作成されます。
![サービスプリンシパルが正常に作成されました.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/4068dfde-00a3-6b78-c4f0-eae0872b5668.png)

### 作成した BigQuery の Azure AD アプリケーションにロールを割り当てる
※この操作をするにはAzure の `Application.ReadWrite.All`、`AppRoleAssignment.ReadWrite.All` 権限が必要になります。

Azure Portal に移動し、ストレージアカウントにロールの割り当てを行います。
ストレージアカウントの「アクセス制御（IAM）」に移動し、「追加」から 「ロールの割り当ての追加」をクリックします。


ロール一覧が表示されるので、「ストレージ BLOB データ所有者」か「ストレージ BLOB データ閲覧者」を選択し、ページ下部の「次へ」をクリックします。
![Azure_ロール選択.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/75342263-cba8-a12f-1e11-2f2ea1bc52f2.png)


「アクセスの割当先」を「ユーザー、グループ、またはサービスプリンシパル」に設定し、「＋メンバーを選択する」で、BigQuery に登録した接続情報の[Azure アプリID]に表示されている文字列を指定します。
![Azuer_ロール割り当て.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/252caac1-e113-ffe0-b22e-1a9212b5a41b.png)

内容に問題がなければ「レビューと割り当て」をクリックして完了です。

### 外部テーブルを作成する
リージョンを `azure-eastus2` にした、専用のデータセットを作成し、そのデータセットにテーブルを作成します。
注意する項目を記載します。

 - テーブルの作成元：Azure Blob Storage
 - Azure Blob Storage のパスを選択：azure://*AZURE_STORAGE_ACCOUNT_NAME*.blob.core.windows.net/*CONTAINER_NAME*/*FILE_PATH* 形式で入力
     - 参考ドキュメント：https://cloud.google.com/bigquery/docs/omni-azure-create-external-table?hl=ja#create_an_external_table
     - ファイル名が `.json` だったため、次の項目「ファイル形式」が自動入力された
 - 接続 ID：「外部データソースへの接続」で入力した接続 ID を指定する
 - スキーマ：自動検出にチェックを入れる

![外部テーブル作成.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/af850bfb-5672-1bd1-29c3-3a1ab32e9f8b.png)

作成したテーブルをクリックすると、スキーマと詳細は表示されますがプレビュータブはありませんでした。
![外部テーブル概要.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/87a6bc5b-7bd8-44a7-6f75-3a6ef76e1c21.png)

## 検証
### クエリの実行
FROM 句に先ほど作った外部テーブルを指定します。他、特別な設定は必要ありません。
BigQuery ストレージに対してクエリを実行する場合といくつか相違点がありました。
（Omni特有、というより外部テーブルとローカルストレージの違いだと思われます）

#### データスキャン量が表示されない
Console にクエリを入力すると、右上にクエリ実行時のデータスキャン量が表示されます。
外部テーブルを指定したときはスキャンできるデータがあっても0バイトと表示されます。
![0byte表示.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/e425b575-1a9f-9b38-d288-f12db03cea39.png)

#### クエリ結果のレコード数が不明
クエリ実行後、結果タブの右下にレコード数が表示されます。
外部テーブルを指定したときは、「多数」と表示され具体的なレコード数がわからない状態です。
![レコード数多数表示.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/da1f68e0-edd0-a403-0d2f-b8c901f92758.png)


### パフォーマンス
Azure Blob ストレージに置いたデータと同じものを BigQuery のローカルテーブルに配置し、同じクエリを実行したときの速度を計測しました。
行数は 120,699 行、合計バイト数は 101.87MB のデータを FROM 句に指定しました。
クエリ実行時に読み取ったデータ量は 1.92 MB です。

結果は以下のようになりました。
Azure Blob ストレージ：42秒
BigQuery のローカルテーブル：1秒
やはり速度は BigQuery ローカルテーブルのほうが速かったです。

## 参考文献
https://blog.g-gen.co.jp/entry/bigquery-omni-trial

以上です。参考になれば幸いです。