LayerX エンジニアブログ

LayerX の エンジニアブログです。

Google ドライブにある Excel ファイルをシュッと BigQuery にロードしたときの備忘録

GCP BigQuery データ基盤

こんにちは。機械学習・データ部の @irotoris です。

どこからかダウンロードしてきた Excel ファイルのデータを BigQuery に入れてほしいという話があり、Python と pandas で Excel を読み込んでシュッと BigQuery にロードしたときの作業備忘録です。

TL;DR

  • Google Colaboratory に Google ドライブをシュッとマウントできて便利
  • pandas の ExcelFile() で Excel ファイルがシュッと読めて便利

備忘録

まずは人に聞いたりファイルをいくつか眺めてデータの仕様を把握します。どうやら以下のようなファイルのようです。

  • ファイル数は200以上、合計 5GiB 程度
  • Excel 内のシート数は 3つ
  • Excel のシート名は「データの内容を表す文字列_ランダム文字列のサフィックス」※
    • 例: シート1: transaction_d9e00ce987f41b69901c980c15e55c14f083724b
    • 例: シート2: master_008096a5844467b9263eb429d828a93ad9d3b9af
    • 例: シート3: node_1cb96a13353d33eeca709c914762d40be01aa370
  • シート名の「ランダム文字列」はファイルごとに異なる
  • 全 Excel の「データの内容を表す」シートの順番は同じ
  • 全 Excel のシートの列名と列数は同じ
  • Google ドライブに入っている
  • 各シートのメタ情報が同一シートの最後のセルに書いてある
  • BigQuery へのロード作業は半年から1年に一回くらい

※ シート名はダミーです。

複数の Excel ファイルに跨るシート単位でそれぞれ意味のあるデータのまとまりだったので、3テーブルを作成することになりそうです。

全 Excel ファイルは Google ドライブにあるとのことなので、Google Colablatory (以下 Colab) で作業することにしました。とりあえずドライブを Colab のランタイムに接続します。

from google.colab import drive, files

drive.mount('/content/drive')

実行するとポップアップが開き、 OAuth 認証で Google ドライブの操作権限を Colab に付与し、ワークスペースに drive というディレクトリが出現します。このディレクトリ配下にはマイドライブと共有ドライブがあり、ファイルとして見れるようになります。自分が権限を持つ共有ドライブやファイルがすべてマウントされるので注意してください。

drive.mount()

とりあえずワークスペースにファイルをコピーしてきます。今回は hoge という共有ドライブに *.xlsx が入っています。

!mkdir -p input
!cp /content/drive/Shareddrives/hoge/* input/

pandas でファイルを読み込んで CSV に変換しつつ BigQuery のスキーマファイルを作ります。DataFrame 操作で簡易的なクレンジング処理をしています。ちなみに Parquet で出力しようとしたらゴミデータが全然まだあって型エラーで生成できなかったので諦めて CSV にしました。また、大したデータ量ではないので単一の CSV ファイルで出力しています。

from pathlib import Path
import os
import json

import pandas as pd


def get_bq_col_type_from_df(df_column: pd.Series) -> str:
    if df_column.dtype == "int64":
        return "INTEGER"
    elif df_column.dtype == "float64":
        return "FLOAT"
    elif df_column.dtype == "bool":
        return "BOOLEAN"
    else:
        return "STRING"


output_dir = Path("output")
input_dir = Path("input")
# テーブル名とシートの順番の定義
target_table_names = ["transaction", "master", "node"]
excel_files = list(input_dir.glob("*.xlsx"))
dfs = {}

# output files
# output/<sheet_name(table_name)>/data.csv
# output/<sheet_name(table_name)>/bq_schema.json
for table in target_table_names:
    os.makedirs(os.path.join(output_dir, table), exist_ok=True)

# DataFrame per sheet
print(f"Reading Excel files...")
for file in excel_files:
    book = pd.ExcelFile(file)
    for i, table in enumerate(target_table_names):
        if table not in dfs:
            dfs[table] = pd.DataFrame()
        df = pd.read_excel(file, sheet_name=book.sheet_names[i])
        # 全カラムが NaN なレコードであったり、データの取扱説明が最後の行1つ目のカラムに入ってしまっているので2つ目のカラムが NaN なレコードを削除
        df.dropna(how="all", inplace=True)
        df.dropna(subset=[df.columns[1]], inplace=True)
        dfs[table] = pd.concat([dfs[table], df])

print(f"Create CSV(GZIP) file per sheet and bq schema file...")
for table, df in dfs.items():
    json_schema_file_name = output_dir.joinpath(table, "bq_schema.json")
    output_file_name = output_dir.joinpath(table, "data.csv.gz")
    bq_schema = [
        {
            "name": f"col{k}",
            "type": get_bq_col_type_from_df(df.columns),
            "description": v,
        } for k, v in enumerate(df.columns)
    ]
    with open(json_schema_file_name, "w") as json_file:
        json.dump(bq_schema, json_file, ensure_ascii=False, indent=4)
    print(f"bq schema file has been saved as {json_schema_file_name}")
    df.to_csv(output_file_name, index=False, quotechar='"', doublequote=True, compression="gzip")
    print(f"CSV file has been saved as {output_file_name}")

最後に BigQuery にデータをロードします。

bq load するので Google Cloud の認証を通します。実行するとポップアップが開き BigQuery の操作権限が求められます。

from google.colab import auth
auth.authenticate_user()

bq load コマンドで作業用のデータセットにロードします。この辺は Python コードにせず、手癖でいつも打ってるコマンドをそのまま Colab で実行して横着しました。

!for t in "transaction" "master" "node"; do bq load  --project_id=<project_id> --source_format=CSV --replace --allow_quoted_newlines=true --schema=output/${t}/bq_schema.json <dataset_id>.${t} output/${t}/data.csv.gz; done

DataFrame 操作で10分くらいかかってしまいますが、年1回の単発作業あればこれで十分でしょう。より精微なデータクレンジングとデータ定義は BigQuery にロードした後 dbt でやることにして、Colab での作業はここまでにしておきます。

しかし Google Colaboratory は bq コマンドや pandas などデータにまつわるものや Google ドライブ操作のためのライブラリが最初から入ってるので楽ちんですね。