GMOアドマーケティングのT.Oです。
SQL式を共有したいと思ったことはありませんか？
今回はGoogle Colaboratoryを使用して、BigQueryのSQL式を共有するための手順をご紹介します。
0.Google Colaboratoryとは？
Google Colaboratory（略称:Google Colab）とは、ブラウザ上でプログラム（主にPython）を記述、実行できるクラウド上のサービスです。機械学習やデータ分析の分野で利用されているJupyter Notebookというツールをベースにしていて以下のような特徴があります。
- 環境構築が不要（ローカル環境へのインストールは不要です）
- GPUへの無料アクセス（計算を高速に行いたい場合にGPUを利用します）
- 簡単に共有（プログラムや説明の文章、実行結果をノートブックと呼ばれるファイルにまとめて管理、共有できます）
今回はGoogle ColabをSQL式の共有に利用します。前提はSQLを作成するユーザ、参照するユーザともにBigQueryが利用可能、かつ同じプロジェクトに対して参照権限があることです。
1.Google Colabを使いはじめる
Google アカウントにログインした状態で、Colaboratory へようこそページへアクセスしてください。
ファイルメニューから「ノートブックを新規作成」を選択します。
新しいノートブックファイルが作成されます。
ファイル名部分に入力カーソルを移動するとファイル名を変更することができます。今回はMyExample.ipynbに変更しました。
ノートブックにはセルという単位でコンテンツを記述します。このセルには以下の2種類があります。
| コードセル | プログラムコードを記述します。 |
| テキストセル | 説明、見出しなどのテキストをMarkdown形式で記述します。 |
Google Colabでは「+コード」を選択するとコードセルを追加することができます。また「+テキスト」を選択するとテキストセルを追加することができます。
2.BigQueryを利用するための認証を行う
ColabからBigQueryを利用するためには、認証を行う必要があります。
「+コード」を選択してコードセルを追加し、以下のように認証のためのPythonプログラムを記述します。
|
1 2 |
from google.colab import auth auth.authenticate_user() |
記述したプログラムを実行し、認証を行います。
コードセルの左端に表示されている実行ボタンを選択します。
以下のようにアカウントの選択画面が表示されるので、認証に利用するアカウントを選択します。
Google Cloud SDKにGoogleアカウントへのアクセスを許可するための画面が表示されるので、許可を選択します。
ログイン画面に、認証用のコードが表示されるのでコピーします。
Google Colabの画面に戻り、「Enter verfification code」の入力欄の部分にコピーした認証用のコードをペーストします。
ペーストしたらキーボードのリターンキーを押下してください。
エラーが発生しなければ認証成功です。
3.SQLの記述、実行
「+コード」を選択してSQLを記述するためのコードセルを追加します。以下のようにSQLをGoogle Colabで実行するためのマジックコマンド%%bigqueryを記述し、マジックコマンドの引数にはGCPのプロジェクトID、SQLの実行結果を格納するための変数を指定します。その後にSQLを記述します。
以下は記述例です。
|
1 2 3 4 5 6 7 8 |
#この例ではマジックコマンドの引数として、プロジェクトID:hoge_proj, 結果を格納するための変数:dfを設定しています。 %%bigquery --project hoge_proj df SELECT TIMESTAMP( FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 day), "Asia/Tokyo") ) AS log_date; |
「+コード」を選択してSQLの実行結果を確認するためのコードセルを追加します。
以下のように記述します。マジックコマンドの引数指定でSQLの実行結果を格納する変数dfを設定しましたが、この変数はPythonのデータ分析用のライブラリであるpandasのDataFrame型となっており、headメソッドを使うことでデータを登録順に表示することができます。
|
1 |
df.head() |
SQLを実行し結果を確認するには、SQLのコードセル、結果を確認するためのコードセルの実行ボタンを順に選択します。以下は実行例です。
またSQLの実行結果をCSVファイルに出力するには以下のように、DataFrameのto_csvメソッドを使用します。Google Colabではクラウド環境にCSVファイルが出力されますので、それをローカルにダウンロードするための処理を記述しています。
|
1 2 3 4 |
# index=Falseを指定しDataFrame型の配列のインデックスナンバーを出力しないようにしています df.to_csv("example.csv",index=False) from google.colab import files files.download('example.csv') |
上記のコードセルの実行ボタンを選択すると、CSVファイルをローカル環境にダウンロードすることができます。以下はダウンロードしたCSVファイルの例です。
4.共有設定
Google Colabのノートブックの共有設定を行います。
右上部分に「共有」ボタンがありますので選択します。以下のように共有設定の画面が表示されます。
今回は「リンクを取得」の方を利用します。
「リンクを取得」の「変更」を選択します。以下のように共有のためのリンクの表示や権限を設定するための画面が表示されます。
各権限の詳細は以下の表のとおりです。
| 閲覧者 | ノートブックを参照することができ、SQLを実行することができます。
ノートブックの編集はできません。 |
| 閲覧者（コメント可） | 閲覧者の権限に加え、ノートブックにコメントを記入することができます。 |
| 編集者 | ノートブックの編集をすることができます。 |
適切な権限を設定し、共有したい方にリンクを通知すればノートブックの共有設定は完了です。
5.まとめ
Google Colabを利用したBigQuery SQLの共有方法をご紹介しました。今回はコードセルのみを利用しましたが、テキストセルの方も利用することでテーブルの各カラムの説明や、SQLプログラムの説明をすることなども可能なので便利です。今回の内容が皆さんのお役に立てれば幸いです。
