メモ:RのデータをSQLに変換するDBIの関数

dplyr::copy_to()とかどうやってるんだろう、と思ってたら、DBIパッケージになんか色々便利関数があるらしい。この辺を眺めていて知ったやつ。

sqlCreateTable()

データフレームの定義をクエリに変えてくれるやつ。中身を見ると、こんな感じで、コネクションを第一引数に取るS4の総称関数。

library(DBI)

sqlCreateTable
#> standardGeneric for "sqlCreateTable" defined from package "DBI"
#> 
#> function (con, table, fields, row.names = NA, temporary = FALSE, 
#>     ...) 
#> standardGeneric("sqlCreateTable")
#> <environment: 0x00000000096743c0>
#> Methods may be defined for arguments: con, table, fields, row.names, temporary
#> Use  showMethods("sqlCreateTable")  for currently available ones.

コネクションを作って渡す必要があるけど、ちゃんとしたDBIのコネクションをつくるにはつなぐ先のDBが必要でめんどくさい。軽く試したいだけならANSI()を使ってAnsiConnectionクラス(S4)のオブジェクトをつくる。他の方法は後述。

こんな感じ。

sqlCreateTable(ANSI(), "iris", iris)
#> <SQL> CREATE TABLE "iris" (
#>   "Sepal.Length" DOUBLE,
#>   "Sepal.Width" DOUBLE,
#>   "Petal.Length" DOUBLE,
#>   "Petal.Width" DOUBLE,
#>   "Species" TEXT
#> )

sqlData()

この関数は、

This is a generic method that coerces R objects into vectors suitable for upload to the database

とのこと。使ってみるとこんな感じ。

sqlData(ANSI(), head(iris))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width  Species
#> 1          5.1         3.5          1.4         0.2 'setosa'
#> 2          4.9           3          1.4         0.2 'setosa'
#> 3          4.7         3.2          1.3         0.2 'setosa'
#> 4          4.6         3.1          1.5         0.2 'setosa'
#> 5            5         3.6          1.4         0.2 'setosa'
#> 6          5.4         3.9          1.7         0.4 'setosa'

分かりづらいのでglimpse()してみると、すべての列が文字列型(<chr>)になっていることがわかる。

#> library(dplyr, warn.conflicts = FALSE)
#> sqlData(ANSI(), head(iris)) %>% glimpse()
#> Observations: 6
#> Variables: 5
#> $ Sepal.Length <chr> "5.1", "4.9", "4.7", "4.6", "5", "5.4"
#> $ Sepal.Width  <chr> "3.5", "3", "3.2", "3.1", "3.6", "3.9"
#> $ Petal.Length <chr> "1.4", "1.4", "1.3", "1.5", "1.4", "1.7"
#> $ Petal.Width  <chr> "0.2", "0.2", "0.2", "0.2", "0.2", "0.4"
#> $ Species      <chr> "'setosa'", "'setosa'", "'setosa'", "'setosa'", "'setosa'", "'setosa'"

ドキュメントによるとデフォルトだと以下のことを行うらしい。

  • Converts factors to characters
  • Quotes all strings
  • Converts all columns to strings
  • Replaces NA with NULL

文字列になっても大丈夫なのは、最終的に次に紹介するsqlAppendTable()の中で結合されるから。

    rows <- do.call(paste, c(sql_values, sep = ", "))

(https://github.com/rstats-db/DBI/blob/376b5e5e451f456c236d433b70f8d5ffc6b09ccb/R/table-insert.R#L34)

do.call()なので、こんな感じになる。

paste("5", "3.6", "1.4", "0.2", "'setosa'", sep = ", ")
#> [1] "5, 3.6, 1.4, 0.2, 'setosa'"

sqlAppendTable()

データベースにデータをつっこむクエリを生成するやつ。

sqlAppendTable(ANSI(), "iris", head(iris))
#> <SQL> INSERT INTO "iris"
#>   ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
#> VALUES
#>   (5.1, 3.5, 1.4, 0.2, 'setosa'),
#>   (4.9, 3, 1.4, 0.2, 'setosa'),
#>   (4.7, 3.2, 1.3, 0.2, 'setosa'),
#>   (4.6, 3.1, 1.5, 0.2, 'setosa'),
#>   (5, 3.6, 1.4, 0.2, 'setosa'),
#>   (5.4, 3.9, 1.7, 0.4, 'setosa')

似たようなやつで、テンプレートを生成してくれるのもある。これを使うならdbBind()も実装する必要がある。

sqlAppendTableTemplate(ANSI(), "iris", head(iris))
#> <SQL> INSERT INTO "iris"
#>   ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
#> VALUES
#>   (?1, ?2, ?3, ?4, ?5)

Tips

S4のメソッドのデバッグ

(あんまり真面目に調べてないのでここの記述は間違ってるかも。変な部分あればご指摘ください。)

S4のメソッドにはdebug()が効かない。そこで、trace()を使うらしい。kohskeさんの記事にあった。

Debug in R #6: Debug in S4/R5&nbsp;classes

例えば、PqConnectionsqlCreateTableの挙動を追うにはこんな感じ。

trace("sqlCreateTable", browser, signature=c("PqConnection"))

ただしめんどくさいのは、継承とかの関係で、実際に呼ばれるのはスーパークラスのメソッドだったりする。例えばsqlAppendTablePqConnectionにメソッドはなくてDBIConnectionにある。

selectMethod("sqlAppendTable", "PqConnection")
#> Object with tracing code, class "MethodDefinitionWithTrace"
#> Original definition: 
#> Method Definition:
#> 
#> function (con, table, values, row.names = NA, ...) 
#> {
#>     stopifnot(is.data.frame(values))
#>     sql_values <- sqlData(con, values, row.names)
#>     table <- dbQuoteIdentifier(con, table)
#>     fields <- dbQuoteIdentifier(con, names(sql_values))
#>     rows <- do.call(paste, c(sql_values, sep = ", "))
#>     SQL(paste0("INSERT INTO ", table, "\n", "  (", paste(fields, 
#>         collapse = ", "), ")\n", "VALUES\n", paste0("  (", rows, 
#>         ")", collapse = ",\n")))
#> }
#> <environment: namespace:DBI>
#> 
#> Signatures:
#>         con            
#> target  "PqConnection" 
#> defined "DBIConnection"
#> 
#> ## (to see the tracing code, look at body(object))

つまり、PqConnectionオブジェクトに対してsqlAppendTable()を実行したときの挙動を追いたいときは、こうではなくて、

trace("sqlAppendTable", browser, signature = c("PqConnection"))

こう。

trace("sqlAppendTable", browser, signature = c("DBIConnection"))

コネクションのダミー

dbplyrパッケージにsimutate_<DB名>()という関数が用意されているのでこれを使えばいい。

たとえば、RMySQLのコネクション(MySQLConnection)を試したければsimuate_mysql()

conn <- dbplyr::simulate_mysql()
conn
#> list()
#> attr(,"class")
#> [1] "MySQLConnection" "DBIConnection"

ただし、メソッドはあくまでRMySQLパッケージに保持されているので、これはエラーになる(これちょっとよく分からない点があるけど後述)。

sqlCreateTable(conn, "iris", iris)
#> Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'sqlCreateTable' for signature '"MySQLConnection"'

RMySQLパッケージを読み込めば成功する。

library(RMySQL)
sqlCreateTable(conn, "iris", iris)
#> <SQL> CREATE TABLE `iris` (
#>   `Sepal.Length` double,
#>   `Sepal.Width` double,
#>   `Petal.Length` double,
#>   `Petal.Width` double,
#>   `Species` text
#> )

simutate_<DB名>()は、structure()でS3のオブジェクトを呼び出すだけの簡単な関数なので、用意されてないクラスに対しては自作すればいい。たとえばPrestoConnectionに対してはこんな感じ。

library(RPresto)

simulate_presto <- function() {
  structure(list(), class = c("PrestoConnection", "DBIConnection"))
}

sqlCreateTable(simulate_presto(), "iris", iris)
#> <SQL> CREATE TABLE "iris" (
#>   "Sepal.Length" DOUBLE,
#>   "Sepal.Width" DOUBLE,
#>   "Petal.Length" DOUBLE,
#>   "Petal.Width" DOUBLE,
#>   "Species" TEXT
#> )

というかふつうにnew()すればいいんだった。

sqlAppendTable(new("PostgreSQLConnection"), "iris", head(iris))
#> <SQL> INSERT INTO "iris"
#>   ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
#> VALUES
#>   (5.1, 3.5, 1.4, 0.2, 'setosa'),
#>   (4.9, 3, 1.4, 0.2, 'setosa'),
#>   (4.7, 3.2, 1.3, 0.2, 'setosa'),
#>   (4.6, 3.1, 1.5, 0.2, 'setosa'),
#>   (5, 3.6, 1.4, 0.2, 'setosa'),
#>   (5.4, 3.9, 1.7, 0.4, 'setosa')

よく分からない点

これ、作ってるのはS3オブジェクトなのにS4のメソッドがディスパッチできるの? あと、DBIConnectionを継承していてもそっちはディスパッチされないのはなぜ? S4のディスパッチまじでわからない...