HDDの記事を見て、sqliteじゃなくて別のDBで読み込むにはどうするんだろう？と思ったので、手元のUbuntu14.04でやってみて、ついでにdplyrでつないでみたときのメモ。
MariaDBをインストール
ここの指示に従えばインストールできます。
MariaDB - Setting up MariaDB Repositories - MariaDB
私の環境の場合は、こんな感じでした。山形大学ありがとうございます！
sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db sudo add-apt-repository 'deb http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.0/ubuntu trusty main' sudo apt-get update sudo apt-get install mariadb-server
MariaDBの起動確認
よく分かりませんが、管理コマンドやプロセス名はmysqlになるみたいです。ややこしい。。
$ mysql -uroot -p -e "show databases;" Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ $ sudo service mysql status * /usr/bin/mysqladmin Ver 9.1 Distrib 10.0.16-MariaDB, for debian-linux-gnu on x86_64 Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Server version 10.0.16-MariaDB-1~trusty Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 7 min 47 sec Threads: 1 Questions: 698 Slow queries: 0 Opens: 572 Flush tables: 2 Open tables: 28 Queries per second avg: 1.494
データを読み込む
ZIPファイルを展開したら、こんな感じのSQLを流します（gist）。mysqlimportというコマンドもあるみたいですが、オプションの指定がよく分からなかったので無難にSQLで。うちの貧弱な回線だと2014年のデータを落としてくるのは厳しかったので、ここでは2013年のデータだけを読み込むようにしています。
あと、インデックスの張り方とかどうすればいいのかよく知らないので適当です。詳しい方教えてください。。
CREATE DATABASE misc; use misc; CREATE TABLE drive_stats ( date DATE NOT NULL, serial_number VARCHAR(32) NOT NULL, model VARCHAR(32) NOT NULL, capacity_bytes TINYINT NOT NULL, failure TINYINT NOT NULL, smart_1_normalized TINYINT, smart_1_raw TINYINT, ...snip... PRIMARY KEY (date, serial_number) ); LOAD DATA LOCAL INFILE './2013/2013-04-10.csv' INTO TABLE drive_stats FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; LOAD DATA LOCAL INFILE './2013/2013-04-11.csv' INTO TABLE drive_stats FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; LOAD DATA LOCAL INFILE './2013/2013-04-12.csv' INTO TABLE drive_stats FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; LOAD DATA LOCAL INFILE './2013/2013-04-13.csv' INTO TABLE drive_stats FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; ...snip...
ここで忘れそうなのでちょっとメモっておきます。
LOAD DATA LOCAL INFILE:LOCALを付けないとサーバが動いているディレクトリからの相対パスになってしまうようです。今回は、クライアントがいるディレクトリのファイルを読み込みたいのでLOCALを付けます。LINES TERMINATED BY '\r\n': このCSVファイルは改行コードがCRLFなのでこうしています。tr -d "\r"とか前処理するのもありみたい。IGNORE 1 LINES: これつけないとヘッダも読み込んでしまうとのこと。
RMySQLをインストールする
RMySQLをインストールしていなければインストールしておきます。
$ sudo apt-get install libmariadbclient-dev libdbd-mysql
$ sudo /path/to/repo/littler/examples/install.r RMySQL
dplyをつかう
dplyrのバックエンドとしてDBを使うやりかたは、この公式ドキュメントにまとまってます。
まず、src_mysql()でDBとのコネクションを張ります。
library(dplyr) db <- src_mysql("misc", user = "root", password = "xxxx")
次に、目的のテーブルをtblオブジェクトにします。
drive_stats <- tbl(db, "drive_stats")
このtblオブジェクトは、ふつうのdata.frameのように扱えます。が、毎回DBに問い合わせにいくので結果が返ってくるまでにはちょっと時間がかかります。
colnames(drive_stats) #> [1] "date" "serial_number" "model" "capacity_bytes" "failure" #> [6] "smart_1_normalized" "smart_1_raw" "smart_2_normalized" "smart_2_raw" "smart_3_normalized" #> [11] "smart_3_raw" "smart_4_normalized" "smart_4_raw" "smart_5_normalized" "smart_5_raw" ...snip... drive_stats #> Source: mysql 10.0.16-MariaDB-1~trusty [root@localhost:/misc] #> From: drive_stats [5,091,501 x 85] #> #> date serial_number model capacity_bytes failure smart_1_normalized smart_1_raw smart_2_normalized #> 1 2013-04-10 5XW004Q0 ST31500541AS 127 0 0 127 0 #> 2 2013-04-10 5XW008MX ST31500541AS 127 0 0 127 0 #> 3 2013-04-10 5XW00B95 ST31500541AS 127 0 0 127 0 #> 4 2013-04-10 5XW00EY9 ST31500541AS 127 0 0 127 0 #> 5 2013-04-10 5XW00F4P ST31500541AS 127 0 0 127 0 #> 6 2013-04-10 5XW00F5X ST31500541AS 127 0 0 127 0 #> 7 2013-04-10 5XW00J2Z ST31500541AS 127 0 0 127 0 #> 8 2013-04-10 5XW00K1P ST31500541AS 127 0 0 127 0 #> 9 2013-04-10 5XW00L6T ST31500541AS 127 0 0 127 0 #> 10 2013-04-10 5XW00Q54 ST320005XXXX 127 0 0 127 0 #> .. ... ... ... ... ... ... ... ... #> Variables not shown: smart_2_raw (int), smart_3_normalized (int), smart_3_raw (int), smart_4_normalized (int), smart_4_raw #> (int), smart_5_normalized (int), smart_5_raw (int), smart_7_normalized (int), smart_7_raw (int), smart_8_normalized (int), #> smart_8_raw (int), smart_9_normalized (int), smart_9_raw (int), smart_10_normalized (int), smart_10_raw (int), #> smart_11_normalized (int), smart_11_raw (int), smart_12_normalized (int), smart_12_raw (int), smart_13_normalized (int), ...snip...
遅延評価
ふつうに手元のデータを扱うときと一番感覚が違うのは、実際に結果を表示しようとするときまで結果が評価されないことです。
# この時点では一切DBに接続しない drive_stats_avril <- drive_stats %>% filter(date < "2013-05-01") %>% select(date, model, serial_number, failure) %>% group_by(date) %>% summarize(sum(failure)) # 表示しようとするとDBに問い合わせるので少し待たされて、結果が返ってくる drive_stats_avril #> Source: mysql 10.0.16-MariaDB-1~trusty [root@localhost:/misc] #> From: <derived table> [?? x 2] #> #> date sum(failure) #> 1 2013-04-10 1 #> 2 2013-04-11 0 #> 3 2013-04-12 0 #> 4 2013-04-13 0 #> 5 2013-04-14 0 #> 6 2013-04-15 2 #> 7 2013-04-16 12 #> 8 2013-04-17 3 #> 9 2013-04-18 0 #> 10 2013-04-19 12 #> .. ... ...
遅延評価を避けるには、collect()を使えば手元にデータを落としてくることができます。（ほか、DBに一時テーブルをつくるcompute()とか共通テーブル式（？）に変換するcollapse()とかあるらしいですが、上級者向けらしく私にはよく分かりません）
# ここでDBに問い合わせるのでしばらく待たされる drive_stats_avril_collected <- drive_stats %>% filter(date < "2013-05-01") %>% select(date, model, serial_number, failure) %>% group_by(date) %>% summarize(sum(failure)) %>% collect # すでに手元にデータがあるのでこれは一瞬 drive_stats_avril_collected #> Source: local data frame [21 x 2] #> #> date sum(failure) #> 1 2013-04-10 1 #> 2 2013-04-11 0 #> 3 2013-04-12 0 #> 4 2013-04-13 0 #> 5 2013-04-14 0 #> 6 2013-04-15 2 #> 7 2013-04-16 12 #> 8 2013-04-17 3 #> 9 2013-04-18 0 #> 10 2013-04-19 12 #> .. ... ...
クエリを見る
実際にどんなクエリが投げられてるかは$queryを見たり、explain()することができます。
drive_stats_avril$query #> <Query> SELECT `date`, `sum(failure)` #> FROM (SELECT `date`, SUM(`failure`) AS `sum(failure)` #> FROM `drive_stats` #> WHERE `date` < '2013-05-01' #> GROUP BY `date`) AS `_W1` #> <MySQLConnection:0,0> explain(drive_stats_avril) #> <SQL> #> SELECT `date`, `sum(failure)` #> FROM (SELECT `date`, SUM(`failure`) AS `sum(failure)` #> FROM `drive_stats` #> WHERE `date` < '2013-05-01' #> GROUP BY `date`) AS `_W1` #> #> #> <PLAN> #> id select_type table type possible_keys key key_len ref rows Extra #> 1 1 PRIMARY <derived2> ALL <NA> <NA> <NA> <NA> 1088828 #> 2 2 DERIVED drive_stats range PRIMARY PRIMARY 3 <NA> 1088828 Using where
で、なにこのイケてないクエリ！と思ったら、自分でクエリを投げることもできるらしいです。SQLガチ勢向け。
drive_stats_diy <- tbl(db, sql("まじでいい感じのクエリ"))
MariaDB/MySQLでできないこと
曰く、
In terms of functionality, MySQL lies somewhere between SQLite and PostgreSQL. It provides a wider range of built-in functions, but it does not support window functions (so you can’t do grouped mutates and filters).
とあります。group()してさらにmutate()とかfilter()とかするのはできないらしいです。
感想
でも、がんばった割に、sqliteとさほど速度差ない雰囲気でした。しかも、ドキュメントをよく見ると、一番最後に「このDBはこういうところがいいよね」と解説してるとこで、MySQL/MariaDBだけは
Don’t bother with MySQL/MariaDB: it’s a pain to set up and the documentation is subpar.
とけちょんけちょんにけなされています。なにそれウケるwww
ということで、こんな記事を書いといてなんですが、もともと使ってないのにわざわざMySQL/MariaDBを持ち出すのはあんまオススメじゃないです。おしまい。