Zenn
👏

『ビッグデータ分析・活用のためのSQLレシピ』をBigQueryでストレスなく動かすために

2024/10/13に公開
SQL
BigQuery
tech

はじめに

『ビッグデータ分析・活用のためのSQLレシピ』は、データ分析者にとって必読の一冊であり、巷で「SQL黒魔術大全」と称されるほど高度なクエリテクニックが詰まった一冊です。

手元で動かしながら学ぶために公式サイトでサンプルデータ(というかそれを作るためのクエリ)が提供されていますが、若干BigQueryと構文が違うため都度手作業で修正する必要で面倒です。

なので、サンプルデータ作成用クエリを一括でBigQuery用に変換するためのスクリプトを作成しました。これにより、手間をかけずに本書の内容をBigQueryで学べるようになります。

対象読者

  • 『ビッグデータ分析・活用のためのSQLレシピ』をBigQueryで学びたい方
  • データ作成用クエリを手元でちまちま治すのが面倒だと感じている人

※BigQuery触るのが初めて、という人は下記を参考

解決したい課題

公式サイトに掲載されているサンプルデータ作成用クエリは、HiveやRedshift、SparkSQLなどにも対応していますが、これらはBigQueryの構文と若干異なります。そのため、手作業で修正する手間がかかります。具体的な課題としては以下のような点が挙げられます：

  1. 構文の違い：「DROP TABLE IF EXISTS」や「CREATE TABLE」といった構文がBigQueryでは対応していません。
  2. データ型の違い：「varchar」や「integer」といったデータ型もBigQuery独自のものに変換が必要です。

これらの違いを正規表現を用いて自動的に変換するコードを作成しました。このコードを使うことで、公式のSQLサンプルをBigQuery環境で簡単に試すことが可能です。

変換内容

本書に含まれるSQLとBigQueryの違いを、以下のように自動で置換します。

種別 通常のSQL BigQuery用SQL
拡張子 *.sql *.bqsql
🔗 BigQuery Runner for VSCodeを使用するために必要
構文 DROP TABLE IF EXISTS テーブル名
CREATE TABLE テーブル名		 CREATE OR REPLACE TABLE データセット名.テーブル名
構文 INSERT INTO テーブル名 INSERT INTO データセット名.テーブル名
型 varchar/text string
型 integer int

実装コード

以下のPythonコードを使うと、BigQueryに対応したファイル名とSQLクエリの自動変換が可能です。

実行手順

  1. ここからデータ作成用クエリをDL
  2. BQ上で適当にデータセットを作成
  3. 下記変数をセットしてプログラム実行
    • dir_path : データ作成用クエリのディレクトリ
    • dataset_name : 2で作成したデータセット名
import os
import re
from glob import glob
from pprint import pprint

# ファイルの拡張子をBigQuery用に変換
def convert_file_extention(dir_path: str):
    file_paths = sorted(glob(f"{dir_path}/*/*.sql"))
    for file_path in file_paths:
        new_file_path = file_path.replace(".sql", ".bqsql")
        os.rename(file_path, new_file_path)
        print(f"file renamed: {file_path} -> {new_file_path}")

# SQLクエリの構文とデータ型をBigQuery用に変換
def convert_query(dir_path: str, dataset_name: str):
    drop_pattern = ["DROP TABLE IF EXISTS [^;]+;\n", ""]
    create_pattern = ["CREATE TABLE ", f"CREATE OR REPLACE TABLE {dataset_name}."]
    insert_pattern = ["INSERT INTO ", f"INSERT INTO {dataset_name}."]
    str_pattern = ["varchar", "string"]
    txt_pattern = ["text", "string"]
    int_pattern = ["integer", "int"]

    file_paths = sorted(glob(f"{dir_path}/*/*.bqsql"))
    for file_path in file_paths:
        with open(file_path, "r") as f:
            query = f.read()
        # 置換処理
        query = re.sub(drop_pattern[0], drop_pattern[1], query)
        query = re.sub(create_pattern[0], create_pattern[1], query)
        query = re.sub(insert_pattern[0], insert_pattern[1], query)
        query = re.sub(str_pattern[0], str_pattern[1], query)
        query = re.sub(txt_pattern[0], txt_pattern[1], query)
        query = re.sub(int_pattern[0], int_pattern[1], query)
        pprint(f"query converted -> \n{query}")
        with open(file_path, "w") as f:
            f.write(query)

if __name__ == "__main__":
    dir_path = "SQL_Recipe_sample-code_20170325" # サンプルデータのディレクトリのパスを指定する
    dataset_name = "sqlRecipe"  # BigQuery上のDataset名
    convert_file_extention(dir_path=dir_path)
    convert_query(dir_path=dir_path, dataset_name=dataset_name)

まとめ

この記事の方法を使うことで、BigQueryで『ビッグデータ分析・活用のためのSQLレシピ』のサンプルクエリを手軽に実行できるようになります。

Discussion