dplyrのバックエンドにMariaDBを使う(Ubuntu 14.04)

R dplyr MySQL MariaDB

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を使うやりかたは、この公式ドキュメントにまとまってます。

Databases

まず、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を持ち出すのはあんまオススメじゃないです。おしまい。