ローカル版BigQuery(もどき)とJupyter Notebookでデータ分析したい
はじめに
普段は、ひたすらBigQueryとdbtでデータ分析をしていますが現状に不満が溜まってきたので、分析環境を検討します。
本記事は、そんな不満と向き合いながら放浪する記録です。
放浪しすぎて、必要なライブラリや認証周りの設定の説明漏れているかもしれませんがご愛嬌で
ふわふわした要件
- Jupyter Notebookで分析したい
- やっぱりデータ分析をする上で、Jupyter Notebookは一番気持ち良い使い心地だなと改めて感じています。直感的な操作感と視覚的なフィードバックが、試行錯誤を繰り返す分析作業にぴったりです。
- BigQueryにクエリを実行するより、ローカルにデータを取り込んで分析したい
- クエリコストを毎回意識せずにクエリを実行しデータ分析したい。
ローカル版BigQuery(もどき)の定義
- BigQueryのSQL(方言)でクエリ実行したい
- ローカルにDBを構築したい
BigQueryと接続してJupyter Notebookで分析する話
BigQuery Notebook使えって声が聞こえてくるが、やっぱりVScode上の方が見やすい。
jupytext使ってGit管理もしたい気持ちもあるので、ローカルで構築する
Jupyter Notebookの環境構築は割愛。
1. 必要なライブラリをインストール
pip install google-cloud-bigquery pandas pydata-google-auth
2. Jupyter Notebookを開き、以下のコードを実行して認証を行う
import pydata_google_auth
credentials = pydata_google_auth.get_user_credentials(
['https://www.googleapis.com/auth/bigquery'],
)
このコードを実行すると、GCPへのログイン画面が表示されます。BigQueryの権限を持つユーザーを選択してログインし、表示された認証コードをコピーしてセルの実行結果に入力します
3. 次に、取得した認証情報をBigQueryのマジックコマンドに設定
from google.cloud.bigquery import magics
magics.context.credentials = credentials
マジックコマンドを有効化
%load_ext google.cloud.bigquery
4. これで%%bigqueryマジックコマンドを使用できるようになります
例えば、
このようにして、ローカル環境のJupyter NotebookでBigQueryを直接操作できるようになります
データフレームに取り込む以外にも変数設定できたりするみたいです
BigQueryのデータをDuckDB転送する話
1. BigQueryからデータを取得し、DuckDBに保存する
まず、BigQueryからデータを取得して、dltライブラリを使ってDuckDBに保存する方法を見ていきます。
必要なライブラリをインストール
pip install 'dlt[duckdb,bigquery]'
下記を実行するとサンプルコードが出力されるので参考になる
dlt init bigquery duckdb
データ取得と保存の関数
query, pipeline_name, dataset_name, table_nameの部分は適宜設定
dev_mode=Trueだと、table_name+タイムスタンプのテーブルが作成されてしまいテーブルの洗い替えができなくなる
from google.cloud import bigquery
import dlt
def load_sql_data() -> None:
"""BigQuery からデータを取得し、dlt を使って DuckDB に保存する"""
# BigQuery クライアントを初期化
client = bigquery.Client()
# クエリを定義
query = """
SELECT * FROM `work.test_001`
"""
# クエリを実行し、データを取得
query_job = client.query(query) # クエリジョブを送信
rows = query_job.result() # 結果を取得
# dlt パイプラインを構築
pipeline = dlt.pipeline(
pipeline_name="test_pipeline",
destination="duckdb",
dataset_name="work",
dev_mode=False,
# progress="log"
)
# 行データを辞書形式に変換してロード
load_info = pipeline.run(
map(lambda row: dict(row.items()), rows),
table_name="test_001",
)
# ロード情報を表示
print(load_info)
if __name__ == "__main__":
load_sql_data()
この関数では、Google CloudのBigQueryからデータを取得し、dltライブラリを使って取得したデータをDuckDBに保存します。dlt.pipelineを使ってデータパイプラインを構築し、BigQueryのデータをDuckDBにロードします。
2. DuckDBに接続してデータを確認
次に、同じ階層にあるtest_pipeline.duckdbファイルに接続し、データを確認する方法を説明します。
DuckDBへの接続
import duckdb
import pandas as pd
%load_ext sql
conn = duckdb.connect('test_pipeline.duckdb')
%sql conn --alias duckdb
%config SqlMagic.autopandas = True # SQLクエリの結果を自動的にpandas.DataFrameに変換する設定
%config SqlMagic.feedback = False # SQLクエリを実行した際に、フィードバックメッセージを非表示にする設定
%config SqlMagic.displaycon = False # データベースへの接続情報を表示しない設定
これで、test_pipeline.duckdbというDuckDBのデータベースに接続できます。接続後、Jupyterノートブック内でSQLコマンドを使ってデータを操作できます。
SQLコマンドの実行例
これは、DuckDB内のすべてのテーブルを表示します。
%sql SHOW ALL TABLES
これは、テーブルのメタデータを確認するSQLコマンドです。
%sql SELECT * FROM information_schema.tables
DuckDBには、データベースのストレージ情報を確認するためのPRAGMAコマンドがあります。これを使うことで、テーブルのストレージに関する情報を得ることができます。
%%sql
PRAGMA storage_info('table_name');
CALL pragma_storage_info('table_name');
3. SQLの実行とデータ探索
次に、DuckDB内のデータに対して統計情報を取得するためのSQLクエリを実行します。
結果はresult変数に格納され、PandasのDataFrameに変換して詳細な統計情報を表示することができます。
%%sql result <<
SELECT * FROM work.test_001
df = pd.DataFrame(result)
df.describe(include='all')
これで、テーブルの基本的な統計情報が表示されます。
4. ydata-profilingを使ったデータ探索
ydata-profilingを使うことで、データの詳細な探索を行うことができます。これを使うと、データフレームの統計情報や相関関係、分布などを簡単に可視化することができます。
必要なライブラリのインストール
pip install ydata_profiling
データプロファイルの作成
import pandas as pd
from ydata_profiling import ProfileReport
# 例: 以前に取得したdf（Pandas DataFrame）を使ってプロファイルを作成
profile = ProfileReport(df)
# Jupyter Notebook上でインタラクティブなプロファイルを表示
profile.to_notebook_iframe()
これで、データのプロファイリングレポートがJupyterノートブック内にインタラクティブに表示されます。これにより、データの分布や欠損値、相関関係などを視覚的に確認することができます。
ローカル版BigQuery(もどき)とJupyter Notebookでデータ分析する話
BigQueryのSQLをDuckDB用に変換し実行するカスタムマジックコマンド %%bq_duckdb を作成しました。
このマジックコマンドを使用すれば、BigQueryのSQLクエリをDuckDBで実行可能な形式に変換し、実行結果を簡単にデータフレームに格納することができます。
設定を変更すれば、SnowflakeのSQLクエリもDuckDBで実行可能な形式に変換し、実行できます。
クエリの方言変換にはSQLGlotを使いました
SQLGlotは、依存関係のないSQLパーサー、トランスパイラー、最適化ツールで、24種類のSQL方言間の変換やフォーマットが可能です。
%%bq_duckdb マジックコマンドのイマイチな点は、クエリを一回変換して実行しているので、変換過程でクエリが修正されたり実行エラーのエラー箇所の確認が分かりにくかったりします。あまり初学者向きではないかも
1. 必要なライブラリのインストール
pip install sqlglot
2. マジックコマンドの作成
~~に既存のDuckDBを設定
import duckdb
import pandas as pd
import sqlglot
from IPython.display import HTML, display
%load_ext sql
conn = duckdb.connect('~~')
%sql conn --alias duckdb
# pandas の表示設定：最大表示行数を設定（例えば 10 行）
pd.set_option('display.max_rows', 10) # 最大行数を100に設定
# BigQuery SQL を DuckDB 用に変換するヘルパー関数
def convert_bq_to_duckdb(sql):
try:
return sqlglot.transpile(sql, read="bigquery", write="duckdb", pretty=True)[0]
except Exception as e:
print(f"Error in SQL conversion: {e}")
return sql
# 変換した SQL を表示する関数（折りたたみ対応）
def display_converted_sql(sql):
html_content = f"""
<details>
<summary>Converted SQL (Click to expand)</summary>
<pre>{sql}</pre>
</details>
"""
display(HTML(html_content))
# 変換した SQL を実行するためのカスタムマジック
from IPython.core.magic import register_cell_magic
@register_cell_magic
def bq_duckdb(line, cell):
# 変数名を取得
var_name = line.strip()
# BigQuery SQL を DuckDB SQL に変換
converted_sql = convert_bq_to_duckdb(cell)
display_converted_sql(converted_sql)
# 変換後の SQL を実行
result = conn.execute(converted_sql).fetchdf()
if not var_name:
return result
else:
# 結果を指定した変数に格納
globals()[var_name] = result
print(f'Store the result in a DataFrame : {var_name}')
3. 使用例
以下は、作成した %%bq_duckdb マジックコマンドの使用例です。
変換したSQLを表示し実行
上記のクエリはBigQuery用のSQLですが、このコマンドを実行すると、内部で sqlglot を使用してDuckDB用に変換され、実行されます。また、変換後のSQLはクリックで展開できる形式で表示されます。
結果を変数に格納
クエリの実行結果を変数に格納することも可能です。次のように、変数名を指定して結果をデータフレームに格納できます。
ここでは、result という名前で実行結果をデータフレームに格納しています。結果が格納された後、result を使ってデータ操作を続けることができます。
これで、ローカル版BigQuery(もどき)を実現できますw
おわりに
本記事では、ローカル環境でBigQueryライクな分析環境を構築する方法を紹介しました。
記事内で取り上げたdltやSQLGlot、DuckDBは、ドキュメントを読むことでさらに多様な使い方が見えてきます。それらを活用しながら、知識を深めていきたいと感じています。
今回試してみて、「これじゃない感」があった部分もありますが、それも一つの発見だと思っています。引き続き試行錯誤を続け、新たな道を探していきたいと思います
また放浪します。。。
参考
Discussion