ExcelをBigQueryに連携する処理をシームレスに実現してみた

# はじめに

こんにちは、京セラコミュニケーションシステム　西田（@kccs_hiromi-nishida）です。

Excelファイルで作成された表形式のデータをそのままBigQueryに取り込みたいと思ったとき、毎回手作業でCSVファイルに変換したり、CSVファイルを手作業でBigQueryに取り込むのは面倒ですよね。
できればシームレスに一連の処理を実行したい！

今回、ExcelファイルをCloud Storageにアップロードし、Cloud FunctionsでCSVファイルに変換してBigQueryに連携するという一連の処理を作成してみたので、ご紹介します。

:::note warn
本記事は2023年3月ごろに作成しております。よって、引用している文章などはこの時点での最新となります。ご了承ください。
:::

## この記事の対象者

- ExcelファイルをCSVファイルに変換しBigQueryに連携してみたい方
- Cloud Functionsによる自動化に興味のある方

## 処理の構成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/845d6146-db17-c56a-8036-b76cb3670080.png)
ExcelファイルをCloud Storageにアップロードすると、それをトリガーとしてCloud Functionsが実行され、ExcelファイルをCSVファイルに変換します。
そのCSVファイルをBigQueryの外部テーブルとして登録しておくという流れになります。

## 前提

Google Cloudの利用開始がまだの方は以下サイトより利用登録を行うことができます。

:::note info
［参照］公式サイト・ドキュメント
  [無料トライアルと無料枠](https://cloud.google.com/free)</br>
　　上記サイトの「無料で開始」ボタンから登録が可能
  [Google Cloud の無料プログラム](https://cloud.google.com/free/docs/free-cloud-features)
　　Google Cloudの無料プログラムの詳細を確認できます
:::

## バケットの作成

:::note warn
使用画像について
画像はGoogle Cloud - Cloud Storage画面をキャプチャしたものとなります
:::

Excelファイルをアップロードするバケットと変換したCSVを配置するバケットをそれぞれCloud Storageに作成します。

Google Cloudにアクセスしてハンバーガーメニューを開き、Cloud Storage ⇒ バケットを選択します。
<img width="400" alt="Cloud Storage-バケット" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/ad345d4b-4deb-3a87-484d-01e44eb23a69.png">
画面上部の［作成］を選択してください。
<img width="400" alt="Cloud Storage-バケット" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/5e180b09-8401-1dba-b4ed-8404417785ce.png">
バケットの名前を入力し、［続行］を選択してください。
<img width="400" alt="Cloud Storage-バケット作成画面1" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/2809d8ae-44d4-4ca4-7d03-181bdd5ca75e.png">
ロケーションタイプは今回Regionとし、asia-northeast1を選択しました。
［続行］を選択してください。
<img width="400" alt="Cloud Storage-バケット作成画面2" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/f12050ac-4f63-6594-18bc-7b67f1f3bb90.png">
後の項目はとくに変更の必要はないので、［続行］を選択していき、最後［作成］を選択してください。バケットが作成されました。
<img width="400" alt="Cloud Storage-バケット作成画面3" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/a279258c-8a45-0e02-1e5b-353eed6d1bab.png">
同じ手順でもう1つ、CSVファイルを配置するバケットを作成しておきます。
<img width="400" alt="Cloud Storage-バケット作成画面4" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/99bc849f-5924-3e6d-3d70-6da23f4dee38.png">

## アップロードするExcelファイルについて

今回、以下のルールでExcelファイルが作成されている前提で変換処理を作成します。

* アップロードするExcelのデータは表形式であること
* シート名が同じであること

今回は観光庁より公表されている宿泊旅行統計調査[^1]から、各都道府県ごとの年間のべ宿泊者数と宿泊者の居住地（県内か県外）をピックアップしてサンプルを作成しました。

<img width="400" alt="Excel" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/35ce0ace-cf9a-746a-e829-1e517f857831.png">

このレイアウトのExcelファイルを2022年、2021年、2020年の3ファイル準備しました。
（シート名はすべて"データ連携"で統一）

このように同じレイアウト・同じシート名で作成されたExcelファイルを、Cloud Functionsで1ファイルのCSVファイルに変換する処理を作成します！

## Cloud Functionsの作成

では、Cloud StorageへのアップロードをトリガーにCSVファイル変換処理を行うCloud Functionsを作成していきましょう。
今回はCloud Functionsの第1世代＋Pythonで開発します。

:::note warn
使用画像について
画像はGoogle Cloud - Cloud Functions画面をキャプチャしたものとなります
:::

### イベントの作成

サーバレス → Cloud Functionsを選択します。
<img width="400" alt="Cloud Functions" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/f16be5f7-07ad-391a-b838-de98d8d3398f.png">
関数の作成を選択します。
<img width="400" alt="Cloud Functions" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/73f1fcbd-e6af-f1cb-cd8d-c74c56e8dfa0.png">
作成画面で画像のように入力・選択します。

* トリガーのタイプ：Cloud Storage
* Event type：選択したバケット内のファイル（最終処理/作成）
* バケット：先ほど作成したExcelファイルをアップロードするバケット

上記を指定することで、指定したバケットにファイルが新規作成もしくは上書きされたことをトリガーに処理を実行するプログラムを作成できます。
<img width="400" alt="Cloud Functions" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/bc16490b-cb45-8d52-b195-a422e79b3675.png">
保存を選択し、［次へ］を選択してください。

### コードの作成

続いてコードを作成します。
ランタイムは今回、Python 3.11を選択しました。
まず、必要なライブラリを定義します。requirements.txtを選択し、以下を貼り付けます。
```txt:requirements.txt
openpyxl==3.*
google-cloud-storage
```
<img width="400" alt="Cloud Functions" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/23a36c52-6ef4-7e59-cfbb-2ccad4e45d1c.png">

次に処理をmain.pyに記述します。
以下のコードを貼り付け、エントリポイントを［conv_csv］に変更し、デプロイを選択してください。
※長いので折りたたんでいます。クリックすると展開します。

<details><summary>プログラムコード</summary><div>

```python:main.py
import io
import openpyxl
import re
import csv
from google.cloud import storage as storage

def conv_csv(event, context):
    
    file = event

    # トリガーとなったバケットを取得
    BUCKET_NAME = file['bucket']
    # CSVファイルを出力するバケット
    TARGET_BUCKET_NAME = "csv_output"
    # 対象のシート名
    TARGET_SHEET_NAME = "データ連携"
    
    OUT_NAME = 'merge.csv'
    client = storage.Client()

    # バケットの取得
    bucket = client.get_bucket(BUCKET_NAME)
    # バケット内のオブジェクト一覧を取得
    blobs = client.list_blobs(BUCKET_NAME)

    # 取得対象行（ヘッダーは不要なので2行目から）
    start_row = 2
    pattern = r".*\.(xlsx|xls)$"

    si = io.StringIO()
    writer = csv.writer(si, quoting=csv.QUOTE_ALL)

    for blob in blobs:
    # .xlsx,.xlsファイルだけを処理対象とする
        if re.match(pattern, blob.name, re.IGNORECASE):
            buffer = io.BytesIO()
            blob.download_to_file(buffer)
            wb = openpyxl.load_workbook(buffer)
            
            if TARGET_SHEET_NAME in wb.sheetnames:
                targetSheet = wb[TARGET_SHEET_NAME]
                
                # 対象シートの最大行数・最大列数を取得
                max_column = targetSheet.max_column
                max_row = targetSheet.max_row
                
                # 開始行～最大行までループしながら1～最大列数分の値を取得して書き込み
                for r in range(start_row, max_row+1):
                    row_data = []
                    for c in range(1, max_column+1):
                        row_data.append(targetSheet.cell(row=r, column=c).value)
                    writer.writerow(row_data)
            wb.close()

    # CSVアップロード対象のバケットにCSVファイルをアップロード
    bucket = client.get_bucket(TARGET_BUCKET_NAME)
    blob = bucket.blob(OUT_NAME)
    blob.upload_from_string(data=si.getvalue(), content_type='text/csv')
    client.close()
    si.close()
    return "OK"

```
</div></details>

<img width="400" alt="Cloud Functions" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/5b989b78-9931-93f9-1122-931fc7cc142f.png">

デプロイが成功すると、以下のように緑色のチェックマークがつきます。
<img width="400" alt="Cloud Functions" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/0e981c6e-7442-ee69-71fc-3b9f45a9b435.png">

### 動作確認をしてみよう

デプロイが成功したら、動作確認してみましょう。
Excelファイルアップロード用のバケットにExcelファイルをアップロードします。
<img width="400" alt="Cloud Storage" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/36deb17e-1420-1d80-73b7-37cda2ac3720.png">
Cloud Functionsが実行され、正常に完了しています。
<img width="400" alt="Cloud Storage" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/1036c421-6251-83c0-edb5-664d32e954bd.png">
CSVファイルを配置するバケットを確認すると、CSVファイルが作成されています。
<img width="400" alt="Cloud Storage" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/4a1fb946-97d8-a938-8ccf-8a47a750865a.png">

## BigQueryへ連携してみよう

では次に、CSVファイルをBigQueryに連携してみましょう。
今回はCloud Storage上のCSVファイルから外部テーブルを作成してBigQueryで参照できるようにします。

私の考える外部テーブルのメリット・デメリットは以下です。

**【メリット】**
・データソースを直接参照するため、最新のデータをクエリすることができる
・BigQuery側でのデータ更新処理が不要になる
・BigQuery以外のデータストレージ上のデータをBigQueryから参照できる

**【デメリット】**
・パフォーマンスは内部テーブルからのクエリ実行より低くなる
・データ変更の制限（クエリ実行中に元データを変更すると予期しない動作が起きる場合も）

詳しい制限内容に関しては公式ドキュメントを参照してください。

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

### 外部テーブルを作成する

:::note warn
使用画像について
特別な記載のない限り、画像はGoogle Cloud - BigQuery画面をキャプチャしたものとなります
:::

Google Cloudにアクセスしてハンバーガーメニューを開き、BigQuery ⇒ SQLワークスペースを選択します。
<img width="400" alt="BigQuery画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/bd504727-cc2a-6708-ef7e-e89e207175e3.png">
データセットを作成するプロジェクトを選択 ⇒ データセットを作成を選択します。
<img width="400" alt="BigQuery画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/da32bce6-de28-2381-e886-cdafdd754ee8.png">
データセットIDを入力し、データのロケーションにはasia-northeast1を選択し、［データセットを作成］を選択してください。
※作成したCloud Storageのバケットのロケーションと合わせておいてください
<img width="400" alt="BigQuery画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/46bf9f2e-922a-b575-909a-9143b39462bc.png">

### 作成したデータセットにテーブルを作成する

作成したデータセットを選択 ⇒ テーブルを作成を選択します。
<img width="400" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/934df5c9-63c5-dd08-9a1c-c242c4c51fbf.png">
テーブルの作成元にはGoogle Cloud Storageを選択してください。
選択すると、バケットからファイルを選択するための参照ボタンが表示されるので、［参照］ボタンを選択します。
<img width="400" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/e004e31b-0495-1747-5c6c-b544528c950a.png">
バケットを選択 ⇒ CSVファイルを選択して、画面下部の［選択］ボタンを押下してください。
<img width="300" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/0800f6b6-0b7c-b956-595a-52f327e90d06.png">　<img width="300" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/880a52b4-cd2e-2260-0dc7-c2e5bdcf04f7.png">
テーブル名を入力し、テーブルタイプは［外部テーブル］を選択してください。
<img width="400" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/d87c5350-567d-5404-4f31-c068e395ad1c.png">
スキーマは［テキストとして編集］をONにすると、入力できる状態になるので、
<img width="400" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/1f539d71-31e0-7d38-b3ec-2ea38c6ce729.png">
以下のスキーマ定義を貼り付けて、［テーブルを作成］ボタンを選択してください。
※折りたたんでいます。クリックすると展開します。
<details><summary>スキーマ定義</summary><div>

```json:スキーマ定義
[
  {
    "mode": "NULLABLE",
    "name": "year",
    "type": "STRING",
    "description": "年"
  },
  {
    "mode": "NULLABLE",
    "name": "area",
    "type": "STRING",
    "description": "都道府県"
  },
  {
    "mode": "NULLABLE",
    "name": "total_guest_number",
    "type": "INTEGER",
    "description": "延べ宿泊者数"
  },
  {
    "mode": "NULLABLE",
    "name": "residence_inside",
    "type": "INTEGER",
    "description": "居住地：県内"
  },
  {
    "mode": "NULLABLE",
    "name": "residence_outside",
    "type": "INTEGER",
    "description": "居住地：県外"
  }
]
```
</div></details>

テーブルが作成されました。
<img width="400" alt="テーブル作成画面" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/58b30379-ec72-8053-99fa-e2f0c9852e1a.png">

### データを確認しよう

最後にデータを確認しましょう。
先ほど作成したテーブルを選択し、クエリを選択します。
<img width="400" alt="データ確認" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/8868430c-8f45-8a19-4aff-472684c850ac.png">
今回はデータ量も少ないので、全項目取得するSQLを画像のように入力し、実行を選択します。
CSVのデータがきちんと表示されていますね！
<img width="400" alt="データ確認" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/53540580-da14-1fcb-5a42-0ec2e5443b21.png">

今回はデータ量が少ないので問題ないのですが、データ量が多いとクエリの実行料金が発生します。
BigQueryの料金体系に関しては以下の記事にまとめています。

https://qiita.com/kccs_hiromi-nishida/items/00ddcfe1873df8f5ca60

また、BigQueryのデータをスプレッドシートで簡単に参照したり、分析できます。
こちらも以下の記事にまとめているので、興味のある方はぜひご覧ください！

https://qiita.com/kccs_hiromi-nishida/items/fe33f23ff6fdd896acdb

## まとめ

いかがでしたか？
ExcelをCSVデータに変換し、BigQueryに連携するという一連の処理の作成は意外と簡単にできるな、と思って頂けたのではないでしょうか。
表形式データのExcelがあれば、ぜひ一度チャレンジしてみてくださいね！

[^1]: [宿泊旅行統計調査（観光庁）](https://www.mlit.go.jp/kankocho/siryou/toukei/shukuhakutoukei.html
 "宿泊旅行統計調査")