LoginSignup
23
16

More than 1 year has passed since last update.

@kccs_hiromi-nishida(Hiromi Nishida)in京セラコミュニケーションシステム（デジタルソリューション）

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

Posted at

はじめに

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

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

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

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

この記事の対象者

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

処理の構成

image.png
ExcelファイルをCloud Storageにアップロードすると、それをトリガーとしてCloud Functionsが実行され、ExcelファイルをCSVファイルに変換します。
そのCSVファイルをBigQueryの外部テーブルとして登録しておくという流れになります。

前提

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

［参照］公式サイト・ドキュメント
無料トライアルと無料枠
　　上記サイトの「無料で開始」ボタンから登録が可能
Google Cloud の無料プログラム
　　Google Cloudの無料プログラムの詳細を確認できます

バケットの作成

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

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

Google Cloudにアクセスしてハンバーガーメニューを開き、Cloud Storage ⇒ バケットを選択します。
Cloud Storage-バケット
画面上部の［作成］を選択してください。
Cloud Storage-バケット
バケットの名前を入力し、［続行］を選択してください。
Cloud Storage-バケット作成画面1
ロケーションタイプは今回Regionとし、asia-northeast1を選択しました。
［続行］を選択してください。
Cloud Storage-バケット作成画面2
後の項目はとくに変更の必要はないので、［続行］を選択していき、最後［作成］を選択してください。バケットが作成されました。
Cloud Storage-バケット作成画面3
同じ手順でもう1つ、CSVファイルを配置するバケットを作成しておきます。
Cloud Storage-バケット作成画面4

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

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

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

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

Excel

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

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

Cloud Functionsの作成

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

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

イベントの作成

サーバレス → Cloud Functionsを選択します。
Cloud Functions
関数の作成を選択します。
Cloud Functions
作成画面で画像のように入力・選択します。

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

上記を指定することで、指定したバケットにファイルが新規作成もしくは上書きされたことをトリガーに処理を実行するプログラムを作成できます。
Cloud Functions
保存を選択し、［次へ］を選択してください。

コードの作成

続いてコードを作成します。
ランタイムは今回、Python 3.11を選択しました。
まず、必要なライブラリを定義します。requirements.txtを選択し、以下を貼り付けます。

requirements.txt
openpyxl==3.*
google-cloud-storage
Cloud Functions

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

プログラムコード
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"
Cloud Functions

デプロイが成功すると、以下のように緑色のチェックマークがつきます。
Cloud Functions

動作確認をしてみよう

デプロイが成功したら、動作確認してみましょう。
Excelファイルアップロード用のバケットにExcelファイルをアップロードします。
Cloud Storage
Cloud Functionsが実行され、正常に完了しています。
Cloud Storage
CSVファイルを配置するバケットを確認すると、CSVファイルが作成されています。
Cloud Storage

BigQueryへ連携してみよう

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

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

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

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

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

外部テーブルを作成する

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

Google Cloudにアクセスしてハンバーガーメニューを開き、BigQuery ⇒ SQLワークスペースを選択します。
BigQuery画面
データセットを作成するプロジェクトを選択 ⇒ データセットを作成を選択します。
BigQuery画面
データセットIDを入力し、データのロケーションにはasia-northeast1を選択し、［データセットを作成］を選択してください。
※作成したCloud Storageのバケットのロケーションと合わせておいてください
BigQuery画面

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

作成したデータセットを選択 ⇒ テーブルを作成を選択します。
テーブル作成画面
テーブルの作成元にはGoogle Cloud Storageを選択してください。
選択すると、バケットからファイルを選択するための参照ボタンが表示されるので、［参照］ボタンを選択します。
テーブル作成画面
バケットを選択 ⇒ CSVファイルを選択して、画面下部の［選択］ボタンを押下してください。
テーブル作成画面　テーブル作成画面
テーブル名を入力し、テーブルタイプは［外部テーブル］を選択してください。
テーブル作成画面
スキーマは［テキストとして編集］をONにすると、入力できる状態になるので、
テーブル作成画面
以下のスキーマ定義を貼り付けて、［テーブルを作成］ボタンを選択してください。
※折りたたんでいます。クリックすると展開します。

スキーマ定義
スキーマ定義
[
  {
    "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": "居住地：県外"
  }
]

テーブルが作成されました。
テーブル作成画面

データを確認しよう

最後にデータを確認しましょう。
先ほど作成したテーブルを選択し、クエリを選択します。
データ確認
今回はデータ量も少ないので、全項目取得するSQLを画像のように入力し、実行を選択します。
CSVのデータがきちんと表示されていますね！
データ確認

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

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

まとめ

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

  1. 宿泊旅行統計調査（観光庁） ↩

23
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
23
16