はじめに
日々の業務でたくさんのクエリを書かないといけない人は多いと思います。
例えば以下のような（架空の）シーン。
大量の購買データに対して、月ごとに異なるカテゴリのログを24か月分抽出したい。
手元には以下のようなデータがある。
category yyyymm food 201601 drink 201602 ... ... furniture 201712
合計24個のクエリを作成することになると思いますが、どう作っているでしょうか。
エクセル使ったり、bashで作ったりしている人もいるかと思います。
※クエリ一発でも書けますが、データベースへの負荷の観点からクエリを分けたいケースもあったりするのではないでしょうか。
glueを使えば簡単にこういったデータからクエリを作成できます。
また、安全にクエリを作成できる仕組みが備わっていたり、クエリのひな形と元になるデータをRmarkdown上で一緒に管理することで、クエリ作成の再現性が高まったり、元データに変更が入った際のクエリ再作成の手間が格段に減るといったメリットもあります。
すごく便利なのにglueに関する日本語の記事が少なかったので書くことにしました。
glueパッケージ
glueパッケージはデータを元に文章やクエリといった文字列を作成するのに便利なパッケージです。文章やクエリの特定の箇所をデータを元に補完します。baseのRのsprintfをより使いやすくしたイメージです。
tidyverseに含まれているので、tidyverseをインストールすればglueパッケージもインストールされています。
ただしコアパッケージではないので、個別にlibraryで読み込む必要があります。
glue::glue
基本となる関数glueをクエリ作成を題材に使ってみます。glueパッケージにはクエリ作成に特化した派生形の関数(glue_sqlとglue_data_sql)が用意されていますが、それらについては後述します。
glueの第一引数には、データをもとに補完したい箇所をブラケットをつかって{変数名}という形で記載した文字列を渡します。
ブラケットの中に書いた変数で該当箇所が保管された文字列ベクトルを生成してくれます。
baseのpaste0やsprintfなどよりも格段に可読性が高い書き方ができるのもうれしいです。
category <- c("food", "drink")
yyyymm <- c(201701, 201702)
glue("select * from log_data where category = '{category}' and yyyymm = '{yyyymm}'")
# 結果
# select * from log_data where category = 'food' and yyyymm = '201701'
# select * from log_data where category = 'drink' and yyyymm = '201702'
第2引数以降に明示的にベクトルを指定することもできます。
glue("select * from log_data where category = '{category}' and yyyymm = '{yyyymm}'",
category = c("food", "drink"),
yyyymm = c(201703, 201704))
# select * from log_data where category = 'food' and yyyymm = '201701'
# select * from log_data where category = 'drink' and yyyymm = '201702'
ブラケットの中には式を書くこともできます！！
ここではin句用にベクトルを整形する関数inclauseを作っています。
inclause <- function(x) paste0("'", paste0(x, collapse = "','"), "'")
glue("select * from log_data where category in ({inclause(category)}) and yyyymm = '{yyyymm}'",
category = c("food", "drink"),
yyyymm = c(201703, 201704))
# 結果
# select * from log_data where category in ('food','drink') and yyyymm = '201703'
# select * from log_data where category in ('food','drink') and yyyymm = '201704'
glue::glue_data
データフレームを元に文章やクエリを作成する用にglue__dataという関数が用意されています。
冒頭の想定シーンのようにあらかじめデータがテーブルの形で用意されている場合はこちらを使いましょう。
tidyverseに入っているだけあってパイプラインで使いやすいです。
data <- tibble::data_frame(category = c("food", "drink"), yyyymm = c(201701, 201702))
data %>% glue_data("select * from log_data where category = '{category}' and yyyymm = '{yyyymm}'")
# select * from log_data where category = 'food' and yyyymm = '201701'
# select * from log_data where category = 'drink' and yyyymm = '201702'
パイプで渡したデータフレームの列名をブラケットの中に記載すると参照される仕組みです。
データフレームの行数分だけクエリが作られます。便利！
glue_sqlとglue_data_sql
今までクエリ作成を題材にglueを紹介してきましたが、glueにはクエリ作成専用の関数が用意されています。
クエリを安全に、便利に作成できるようになっています。以下glue_sqlを使いますが、glue_data_sqlも同様です。
安全 ： SQLインジェクションを防ぐ
第三者の入力したinputを補完してクエリを作成したいケースがあると思います。
glueを使っていると、テーブルがドロップされたり危険なクエリが生成される可能性があります。
input <- "food'; drop table 'log_data"
glue("SELECT * FROM log_data where category = '{input}'")
# select * from log_data where category = 'food'; drop table 'log_data'
glue_sqlはその辺をうまくquoteの処理をして守ってくれます。
シングルクォートがしっかりエスケープされて出力されるので、入力全体を一つの文字列として扱うことができています。
なお、glue_sqlにはDBとのコネクションオブジェクトを渡す必要があります。
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
glue_sql("SELECT * FROM log_data where category = {input}", .con = con)
# <SQL> SELECT * FROM log_data where category = 'food''; DROP TABLE ''log_data'
便利1 : in句用のcollapseが簡単にできる。
ブラケットの中に記載する変数名の最後に「*」を付けるとベクトルをin句用にcollapseして補完してくれます。
ベクトルが文字列型ならしっかりクォートもしてくれます。（*を付けないケースでも文字列型なら勝手にクォートしてくれたりします。）
glue_sql("select * from log_data where category in ({category*}) and yyyymm in ({yyyymm*})",
category = c("food", "drink"),
yyyymm = c(201703, 201704), .con = con)
# <SQL> select * from log_data where category in ('food', 'drink') and yyyymm in (201703, 201704)
私は普段の業務でRからDBに直接接続できなかったりしてコネクションオブジェクトを作るのが面倒なためglueとglue_dataでクエリ作成を行ってしまうことが多いですが、第三者のinputを元にクエリを作成するケースでは必ず_sqlを使った方が良いと思います。（ダミーのコネクションオブジェクトを作ればいいのですが...）
まとめ
glueについてクエリ作成をテーマに書いてみました。Rをメインに使う方で、エクセルやテキストエディタ、Rのその他の関数（paste0やsprintf、stringr::str_interp）などをクエリ作成に使っている人はglueに乗り換えてみてはいかがでしょうか。