SQLiteのためのメモ（Rユーザーであり、かつMacユーザーである人向け）

SQLiteは小規模なデータに適したデータベースとされる。

長所：始めるのが簡単
短所：大規模なデータに向かない

どの程度までが小規模なデータなのかはよくわからないが、SQLiteでなにか困ることがあれば、そのとき代替（MySQL, PostgreSQL, Google BigQuery,...?）を検討することにする。

SQLite3のインストール

Macには最初からインストールされている。
ターミナルで "sqlite3 -version" とタイプすると次のように表示されるはずだ。

$ sqlite3 -version
3.28.0 2019-04-15 14:49:49

バージョンが古い場合は素直に新しいものを入れ直したほうが無難である。
現時点で 2021-06-18 (3.36.0) が最新。
https://www.sqlite.org/changes.html

brew install sqlite3

パスを通す。bashを使っている場合、

echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> .bash_profile

zshを使っている場合、

echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> .zprofile

確認。

$ sqlite3 -version
3.36.0 2021-06-18 18:36:39

SQLite3を起動する

データベースのファイル "products.sqlite" を指定して起動する。ファイルの置き場はどこでもいい。"products.sqlite" が存在しない場合、これでカレントディレクトリに新規作成される。

$ sqlite3 products.sqlite
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.

table を作る

SQLite のデータ型には次のものがある:

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.

Datatypes In SQLite Version 3

Boolean 型はない。

データ型を指定しなくても table を作ることはできる。その場合、型は適当に推測されるようである（ここでの「適当」は必ずしも「妥当」とか「適切」という意味ではない）。

create table Tokyo(
    AA text,
    BB text,
    CC integer);

CSVファイルを読み込む

CSVファイルを読み込み場合、mode を csv にしておく（試したことはないが JSON などの mode もあるようだ）。

.mode csv

import する。

.import --skip 1 dat1.csv Tokyo

確認。

PRAGMA table_info(Tokyo);

確認。

select * from Tokyo limit 6;

確認。

select count(*) from Tokyo;

SQLiteを終了する

.quit または .exit

失敗した場合

Tokyo という table を削除する場合は次のようにする。

drop table Tokyo;

ついでに vacuum; もしとくといいかも。VACUUM

SQLiteではデータベースは一個のファイル（ここでは "products.sqlite"）のみで管理されているようである。そのため、どんなに派手に失敗した最悪の場合でもファイル（"products.sqlite"）を削除してやり直せばよい。

バッチ処理

「このフォルダのファイルを全部インポート」というような形で、複数のCSVファイルを読み込みたい。しかし、うまいやりかたがわからなかった。素直に以下のようにする。

Command Line Shell For SQLite

"create_table.sql" などのファイル名でファイルを作ってコマンドをまとめて書く。上述のコマンドの .import するファイルと table の部分を置き換えて何度もコピー・アンド・ペーストすればよい。コピー・アンド・ペーストに関しては適当なプログラムを使えば手作業をなくせる。ターミナルから次のように実行。

sqlite3 products.sqlite ".read create_table.sql"

以下Rの話

RからSQLiteを呼ぶ。クエリを直に書くならこれでいい。

library(DBI)
library(RSQLite)
con <- dbConnect(SQLite(),"products.sqlite")
test <- dbGetQuery(con, "select * from Tokyo limit 10")
test2 <- dbGetQuery(con, "select * from Osaka limit 10") 
dbDisconnect(con)

しかし、dplyrを使ったほうが便利。Using dplyr with databases

例えば、

library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), "products.sqlite")
DBI::dbListTables(con)
tokyo_db <- tbl(con, "Tokyo")
osaka_db <- tbl(con, "Osaka")

print(colnames(tokyo_db))
print(colnames(osaka_db))

q1 <- dplyr::select(osaka_db, KEY, BB, CC) %>% 
  dplyr::filter(BB=="MOU4444") %>% 
  inner_join(tokyo_db, by=c("KEY" = "AA"))

S1 <- collect(q1) #collect()したときはじめてデータを呼びに行く

dbDisconnect(con) #閉じる

show_query(S1) #実際に実行されるクエリはこれ。

head(S1)

おしまい。

ジョンとヨーコのイマジン日記

キョウとアンナのラヴラブダイアイリー改め、ジョンとヨーコのイマジン日記です。