Zenn
🚀

RDBMS形式のオープンデータをBigQueryで使う方法

に公開
MySQL
SQL
BigQuery
RDBMS
LLM
tech

はじめに

ChatGPTなどの生成AIの登場により、単一テーブルを対象とした集計やグラフ作成は簡単にできるようになりました。
しかした、世の中の多くのWebサービスやプロダクトでは、RDBMSを基盤に複数のテーブルに分割されたデータ構造が採用されています。したがって、分析においても複数のテーブルをJOINして意味のある情報を導く、いわゆるリレーショナルなデータ操作が依然として重要なスキルとなっています。

そこで本記事では、以下のような方に向けて、実際に複数テーブルで構成されたRDBサンプルデータを使って、BigQuery上で複雑なSQL分析を試せる環境の構築方法をご紹介します。

対象読者

  • 実務的なデータ分析スキル（JOIN・GROUP BY・設計力）を鍛えたい方
  • SQLでRDBのテーブル操作に慣れておきたいデータ分析学習者
  • Text-to-SQL（自然言語→SQL）をLLMで試したい方
    • 複数テーブルのスキーマ理解とJOIN処理の自動生成を試す題材を探している方に最適です

探してもなかなか見つからないRDBMSのサンプル

BigQueryやKaggleなどで公開されている多くのデータセットは、1枚もの（＝単一テーブル）のCSV形式で提供されていることがほとんどです。
そのため、JOIN や正規化テーブルを前提としたリレーショナルな構造を扱った分析や学習を行うには、そもそも適切な教材が見つからないという問題があります。

RELATIONAL DATASET REPOSITORYとは？

こうした背景で一般公開されているRDBMSにRELATIONAL DATASET REPOSITORYがあります

URL：https://fit.cvut.cz/cs/veda-a-vyzkum/cemu-se-venujeme/projekty/relational

RELATIONAL DATASET REPOSITORY は、チェコ工科大学（CTU）が公開している、実際にRDBMS（MariaDB）として稼働している構造付きデータのリポジトリです。

複数のRDBMSのデータセットのサンプルがあります

特徴

  • 複数のテーブルからなる構造化データを、実際にRDBとして接続・クエリ実行できる
  • データは教育・研究・LLM向けなど検証用に設計されたサンプルも多い
  • MariaDBに直接接続し、MySQL互換ツール（MySQL Workbenchなど）で閲覧・エクスポート可能
  • ダウンロードしてBigQueryやPostgreSQLなど任意のRDB環境に移行して使える
  • 特に「複雑なスキーマを前提としたSQL練習」や「Text-to-SQLなどのLLM検証」をしたい人にとって、最適な教材となります。

今回使うデータ：Lahman Baseball Database

URL：https://relational.fel.cvut.cz/dataset/Lahman

  • 野球の選手、球団、成績などの情報が十数個のテーブルに正規化されて格納
  • スキーマの例：
    • people（選手情報）
    • batting（打撃成績）
    • salaries（年俸）
    • teams（所属チーム）
  • 実務でよくある「人」×「業績」×「組織」型のJOIN分析に近い構造です

この記事でやること

LahmanのRDB形式データを、MySQL経由でローカルに取得し、BigQueryに格納し、SQL分析やLLM活用ができる状態にする。

1. データをCSVでエクスポートする

エクスポート方法：Table Data Export Wizard（GUI）

  1. MySQL Workbenchを起動し、対象サーバー（CTU Lahman）に接続
  2. lahman_2014 スキーマから任意のテーブルを右クリック → Table Data Export Wizard
  3. 以下のようにエクスポート設定：
項目 推奨設定 補足
出力形式 CSV デフォルトでOK
区切り文字 ;（セミコロン） カンマだと列ズレのリスクあり
文字列囲み " デフォルトでOK
保存先 任意（例：C:\Users\you\lahman_2014） 日本語パスでも今回はOKでした

2. BigQueryに一括アップロードする

事前準備

GCP プロジェクトを用意

  • Google Cloud Consoleからプロジェクトを新規作成
  • プロジェクトID（例：my-project-123456）を控えておく

Cloud SDKのインストール（gcloud, bq）

gcloud init
gcloud config set project my-project-123456

BigQuery APIの有効化

Cloud Consoleの「APIとサービス」→「BigQuery API」を有効化

3. PowerShellを使ってCSVを一括アップロード

CSVファイルが格納されているフォルダ内で、以下のようなPowerShellスクリプトを実行します。

# あなたのGCPプロジェクトID
$project = "your-gcp-project-id"

# 作成済みのBigQueryデータセット名
$dataset = "lahman_2014"

# CSVファイルが保存されているローカルフォルダ
$folder = "C:\path\to\your\csv\folder"

Set-Location $folder

Get-ChildItem -Filter *.csv | ForEach-Object {
    $file = $_.FullName
    $table = $_.BaseName

    Write-Host "Importing $table from $file"

    bq load `
        --autodetect `
        --source_format=CSV `
        --field_delimiter=";" `
        --skip_leading_rows=1 `
        "${project}:${dataset}.${table}" `
        "$file"
}

アップロード後に確認すること

  • BigQueryのWeb UIで lahman_2014 データセットを確認
  • テーブルが正しく作成されているか
  • 各カラムが正しい型（INTEGER, STRINGなど）で認識されているか

次のステップ：実践分析やLLM活用へ

ここまで来れば、以下のような応用が可能になります：

  • SQLで複雑なJOIN分析（例：選手の年俸と成績を横断比較）
  • Looker Studioと連携してダッシュボード化
  • LLMでのText-to-SQL実験
    • 「2010年以降で最もホームランを打ったチームは？」と自然言語で聞いて、SQLを自動生成させる
    • RAG・Agentなどと組み合わせてプロンプトベース分析を模索

Discussion