RとSnowflake

ノバセルではDWH（データウェアハウス）としてSnowflakeを使っており、データ分析の際はSnowflake上のデータを使うことが非常に多いです。また、自分を含めデータサイエンティスト職の人はPythonを使うことが多いですが、案件によってはRも普通に使います。 SnowflakeにはSnowsightというWeb UIがあるため、そこでSQLを書いて結果を取得、csvでダウンロードといったことができまるので、そのcsvをPythonやRで読み込めば分析はできます。しかし、PythonやRのコード上でデータの取得まで完結すると、他のメンバーにコードを共有したり、GitHubにコードをアップした際に他の人が同じコードで同じ結果を再現することが容易になります。

Pythonの場合、Snowflakeに接続してデータを取得する専用のコネクタが存在するのですが、Rは自分が探した限りでは専用のパッケージ等がありません。そこで、この記事ではRからSnowflakeに接続しデータを取得する方法について、2通りの方法を紹介をします。

1. SQL文を書いてODBC経由で実行する

ODBCはMicrosoft社が定めた、アプリケーションからデータベースにアクセスするための仕組みです。Snowflakeはこの規格に対応しており、ODBCドライバーが提供されています。

docs.snowflake.com

このようなODBCドライバーをRから操作するものとして、odbcパッケージがあります。

ODBCドライバーや依存ライブラリのインストール

以下では、macOSなどUnix系のOSでの方法に絞って説明をします。

まず、Snowflake用のODBCドライバーをインストールします。

macOS用 ODBC ドライバーのインストールと構成 — Snowflake Documentation

そして、Rなどのプログラミング言語からODBCを操作するために必要な unixodbc をインストールします。 macOSであれば、ターミナルからbrew install unixodbcでインストールできます。

Rパッケージのインストール

次に、必要なRパッケージをインストールします。Rコンソール上で、以下のコマンドでインストールができます。

install.packages(c("odbc", "DBI"))

DBIパッケージは、ODBCとは別で、Rから何らかのデータベースに接続するとき必要なパッケージです。

Snowflakeに接続してクエリを投げる

準備が整ったので、実際にSnowflakeに接続をします。

まず、Snowflakeへの接続情報を作成します。

# Snowflakeへの接続情報を作成
conn <- DBI::dbConnect(odbc::odbc(),
                       Driver = "Snowflake",
                       Server = "<Account Name>.snowflakecomputing.com",
                       UID = "<User Name>",
                       PWD = "<Password>"
  )

この接続情報を作る時点で、以下のようにデータベース名やスキーマ名を指定することもできます。

conn_db <- DBI::dbConnect(odbc::odbc(),
                          Driver = "Snowflake",
                          Server = "<Account Name>.snowflakecomputing.com",
                          Database = "<Database Name>",
                          SCHEMA = "<Schema Name>"
                          UID = "<User Name>",
                          PWD = "<Password>"

SnowflakeでMFA（多要素認証）を利用している場合は、このコードを実行した時点で、モバイルへのプッシュ通知送信などが行われます。作成した接続情報を使えば、DBI::dbGetQuery()関数で任意のクエリに対して結果をデータフレームの形で取得することができます。

# SQL文を書いてデータを取得
query <- "
select
  column1, column2
from
  tbl_name
where
  some conditions
"
df <- DBI::dbGetQuery(conn_db, query)

データ取得や分析が終わったら、作成した接続情報は解除しておくと安全です。

# Snowflake接続解除
DBI::dbDisconnect(conn)
DBI::dbDisconnect(conn_db)

2. dbplyrパッケージでデータを操作する

Rユーザーであれば、SQLを書くよりもtidyverse（特にdplyrパッケージによう列選択や行の絞り込み、集計処理など）を使ってデータ操作をしたいと思う方もいるかも知れません。

kazutan.github.io

dbplyrパッケージ（パッケージ名にbがついてることに注意）はデータベース上のデータに対してSQLを書かずにdplyrの文法でデータ操作が行える機能を提供しています。この場合もデータベースへは接続するので、接続情報を作るのは同じです。

library(dbplyr)
library(magrittr) # パイプ演算子のため

# Snowflakeへの接続情報を作成
conn_db <- DBI::dbConnect(odbc::odbc(),
                          Driver = "Snowflake",
                          Server = "<Account Name>.snowflakecomputing.com",
                          Database = "<Database Name>",
                          SCHEMA = "<Schema Name>"
                          UID = "<User Name>",
                          PWD = "<Password>"

# Rオブジェクト上にテーブルへの接続を作る
source_table <- dplyr::tbl(conn_db, "<tbl_name>")

# dplyr の文法でデータを取得
garapon_genres13 <- source_table %>%
  # 列の選択
  dplyr::select(column1,
                column2) %>%
  # 条件に依る行の絞り込み
  dplyr::filter(some condition) %>%
  # データベースから結果の出力
  dplyr::collect()

このように、Rユーザーであれば慣れ親しんだ文法でSnowflake上のデータを取得することができます。

おまけ: ユーザー名やパスワードをコードに残したくない

さて、Snowflakeへの接続やデータ操作ができることは分かったのですが、どのパッケージを使うにしてもユーザー名やパスワードを使って接続情報を作る必要がありました。ユーザー名やパスワードなどの認証情報、特にパスワードは他の人に知られるてはならず、GitHubなどのホスティングサービス上には決して置いてはいけないものです。 Rでコーディングするとき、このような場面で、コード上に認証情報を直接書かなくても良くなる方法を2つ紹介します。

rstudioapiパッケージでインタラクティブに入力

1つ目はIDE（統合開発環境）としてRStudioを用いている場合に限定されますが、インタラクティブに認証情報を入力するものです。先程のSnowflakeへの認証情報を作成する部分で、認証情報に当たる部分を以下のように書き換えます。

conn <- DBI::dbConnect(odbc::odbc(),
                       Driver = "Snowflake",
                       Server = "<Account Name>.snowflakecomputing.com",
                       # ユーザー名、パスワードはインタラクティブに入力
                       UID = rstudioapi::askForPassword("Database user"),
                       PWD = rstudioapi::askForPassword("Database password")
  )

このコードを実行すると、以下のようなポップアップが出現し、ユーザー名を入力することができます（ユーザー名の入力が終わるとパスワードの入力ポップアップが開きます）。

keyringパッケージを使う

もう一つ、keyringパッケージを使う方法があります。これは、macOSならキーチェーン、WindowsならCredential StoreといったOS側の仕組みを使って認証情報を管理する方法を提供するパッケージです。こちらは、コード上に認証情報を書かなくても良いですが、実行環境のOSに依存してしまうため、他の人とコードを共有する目的のときは使いづらいかもしれません。そのため、参考記事の紹介に留めます。バックエンドとしてOSのkeyringだけではなく.envファイルを使った環境変数の読み込みにも対応しているので、個人での開発の際は使ってみると良いかもしれません。

qiita.com