CSVで読んだものをSQLiteに入れて、クエリを書かずに扱う。

CSVで定期的に更新されるデータを読んで集計する時など、
都度都度過去のCSVを読み込んでやるのって面倒だし、どんどん読み込みサイズが大きくなるなあと思って、
DBに入れてしまっていたらいいのでは？と思って、調べてみたら思いのほか、簡単そうだったのでメモメモ

SQLiteにつなぐ

# インストールしてから
# ライブラリの読み込み
library("tidyverse")
library("RSQLite")

# SQLite に接続する
con = dbConnect(SQLite(), "test.db", synchronous="off")

作業ディレクトリの配下に "test.db" ができる。すでにファイルが存在する時はそのファイルにつなぐ。
ファイル名を ":memory:" にすると、メモリ上に作るとかなんとからしい。

CSVを読み込んで、それをSQLに投げ込む。

# CSVを読み込む
# 適当なサンプルデータ①
input_file = "https://www.e-stat.go.jp/stat-search/file-download?statInfId=000031849356&fileKind=1"
read_data <- read_csv(input_file, locale=locale(encoding="CP932"))

# データフレームをDBに投入する
con %>% dbWriteTable("seika", read_data, row.names=F)

同じ形式のデータをテーブルに追加する時は、append=T とすれば、よいみたい。

# CSVを読み込む
# 適当なサンプルデータ②
input_file = "https://www.e-stat.go.jp/stat-search/file-download?statInfId=000031849386&fileKind=1"
read_data <- read_csv(input_file, locale=locale(encoding="CP932"))
# データフレームをDBに投入する
con %>% dbWriteTable("seika", read_data, row.names=F,append=TRUE)

入れたテーブルのデータを使う

本懐はこれ。SQLを書かなくてdplyr的な操作ができる。知らなかった。
データフレームをパイプでつなぐ代わりに、接続→テーブル選択として、
それ以降はdplyr的に操作して、最後に collect() で取得

# dplyr 的な操作で抜く
tmp <- con %>% tbl("seika") %>%   # 接続→テーブル名 という感じで指定
filter(品目名 == "だいこん") %>%
arrange(日) %>%
collect()   # ここでクエリ投げて、データ取得

複雑なことはできるかは調べていないけど、
どんなSQLが生成されるかは、最後を show_query() にすればOK

# dplyr 的な操作で抜く
tmp <- con %>% tbl("seika") %>%   # 接続→テーブル名 という感じで指定
filter(品目名 == "だいこん") %>%
arrange(日) %>%
show_query() # どんなSQL投げているかを返してくれる

# == 実行結果 ==
# <SQL>
# SELECT *
# FROM `seika`
# WHERE (`品目名` = 'だいこん')
# ORDER BY `日`

どこまでをSQLでどこまでをRでやるかという部分にはなるけど、可読できるSQLを吐くあたりが、線引きラインかな？と思ったり。
絞り込んでデータ抜いてくるまでやって、そこから先はRでとか？
とはいえ、SQLを書かなくてもいいのは、DB苦手な人には精神衛生上かなり嬉しい。

そのほか

きちんとクエリを書くなら

# クエリ書いて条件絞って抜くならこれ
tmp <- con %>% dbGetQuery( "SELECT * FROM seika" )

# フェッチで投げる
rs <- con %>% dbSendQuery( "SELECT * FROM seika" )
tmp <- rs %>% dbFetch()
rs %>% dbClearResult()

さいご

最後はDBから切断。

con %>% dbDisconnect()