RからBigQueryに接続する

R BigQuery

概要

RからBigQueryに接続する方法としては大きく2種類ある。

  1. odbcパッケージおよびODBCドライバを使用する方法
  2. bigrqueryパッケージを使用する方法

RStudioのドキュメント(Google BigQuery)を読みながら両方試してみたので手順をメモっておく。ちなみに2の方が使い勝手が良い。

前提と説明しないこと

  • 前提
    • BigQueryは使える状態（課金が有効なプロジェクトが存在する）とする。
    • RはRStudioから使うものとする。
    • データベースの操作はdplyrおよびdbplyrパッケージを利用する。
  • 説明しないこと
    • BigQueryそのものについて。
    • RおよびRStudioそのものについて。
    • dplyrやdbplyrの使い方。

方法1 odbcパッケージを使う

結構準備が必要になる。最初に書いたようにあまり便利ではないのでおすすめはしないが、一応確認したので手順を書いておく。

必要なパッケージ

odbcの他に接続のためDBIも必要。

サービスアカウントの準備

ODBC経由で操作する場合はサービスアカウントを利用して接続するのが比較的手軽。

詳細は省略するが、サービスアカウントはGoogle Cloud Platformのナビゲーションメニューの「IAMと管理 > サービスアカウント」から作成し、鍵を発行してキーファイルをダウンロードしておく。

ODBCドライバのインストールとセットアップ

RStudioのProfessionalシリーズの製品を使っている場合はRStudio Professional Driversを使用することができるらしい。追加料金は不要らしいので利用可能であれば検討してみると良いかもしれない。

そうでない場合は他のドライバを用意する必要がある。公式のものがあるのでこれが選択肢になるだろう。

手元(mac)で試したところ、/etc/odbcinst.iniに設定が記述されていた。よく確認していなかったがインストーラがやってくれるっぽい。

接続する際は次のようなコードを実行する。

con <- DBI::dbConnect(odbc::odbc(),
                      Driver         = "[your driver's name]",
                      Catalog        = "[BigQuery project name]",
                      Email          = "[Google service account email]",
                      KeyFilePath    = "[Full path to key file]",
                      OAuthMechanism = 0)
  • Driver... odbcinst.iniに記載されているドライバ名を記載する。（手元のodbcinst.iniではSimba GoogleBigQuery ODBC Driverとなっていた）
  • Catalog... BigQueryのプロジェクト名
  • Email...サービスアカウントのメールアドレス
  • KeyFilePath... キーファイルへのパス
  • OAuthMechanism...0を指定するとサービスアカウントでの認証となる。

また、~/odbc.iniに上記と同様の内容を書いておけばConnectionsペインから接続操作ができるようになる（cf. ODBCドライバをセットアップしてRからデータベースに接続する - Qiita）。ただ、テーブル名やフィールドは表示されない。

クエリの実行

tbl(con, "project_name.dataset_name.table_name")

のように、テーブル名はBigQueryのSQLワークスペース上でクエリを書く際と同様にプロジェクト名やデータセット名を記述する必要がある。

方法2 bigrqueryパッケージを使う

ODBCパッケージを使う方法に比べると手順が少ないのでおすすめ。

bigrquery.r-dbi.org

インタラクティブに使うのであればサービスアカウントの準備も不要。また、あまり試していないがAPI経由でBigQueryを操作する各種の関数も用意されている。

インストール

CRANからインストールできるのでinstall.packages("bigrquery")でOK。

認証

インタラクティブに利用する場合はブラウザを通じて権限を承認する。bigrquery::bq_auth()を実行するとブラウザが開くが、おそらく明示的に実行せずとも必要な場面になれば開くと思う。

非インタラクティブに操作する場合はサービスアカウントを利用する。この場合はbigrquery::bq_auth("path/to/service-account.json")のように、サービスアカウントのキーファイルへのパスをbq_auth()関数に渡せばよい。

コネクションの作成

DBI::dbConnect()を使用し、次のように接続する。

con <- DBI::dbConnect(
    bigrquery::bigquery(),
    project = "project_name",
    dataset = "dataset_name",
    billing = "billing_project_name"
)

project, dataset, billingには次のものを指定する。

  • project: 接続するデータセットを含むプロジェクトID。
  • dataset: データセット名。
  • billing: 課金が有効になっているプロジェクトID。

クエリの実行

bigrqueryパッケージを利用する場合は、tbl(con, "table_name")のようにテーブル名のみの指定でもクエリを実行できる。単に省略可というだけなので、プロジェクト名やデータセット名を指定しても良い。

処理されるデータサイズ表示

BigQueryのSQLワークスペース上でクエリを書くと、処理されるデータ量がクエリ実行前に表示される。

これと同等のことはbq_perform_query_dry_run()関数で実現できる。引数としてはqueryとbillingが最低限必要で、queryにはクエリ文字列、billingには課金が有効なプロジェクトIDを指定する。

dplyrとdbplyrを使って生成したクエリを文字列にするにはsql_render()を使えば良い。つまり、例えば次のようにすれば処理されるデータサイズが表示される。

tbl(con, "dataset_name.table_name") %>%
    sql_render() %>%
    bq_perform_query_dry_run("billing_project_name")

なお、プロジェクト名やデータセット名を省略するとsql_render()で生成されるクエリにはこれらが含まれなくなる。したがってこの方法でデータ処理量を推定する場合はテーブル指定の記述を省略してはならず、BigQueryが解釈できるように記述しておく必要がある。

Connections paneに表示する

ODBCを使う方法でもbigrqueryパッケージを使う方法でもRStudioのConnectionsペインでテーブルの内容を確認することができない。これを可能にするパッケージとしてconnectionsがある。

rstudio.github.io

ただ、現在はCRANから削除されておりGitHubからインストールする必要がある。

使用方法はDBI::dbConnect()をconnections::connection_open()に置き換えるだけ。

その他