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
NAwithNULL
文字列になっても大丈夫なのは、最終的に次に紹介する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 classes
例えば、PqConnectionのsqlCreateTableの挙動を追うにはこんな感じ。
trace("sqlCreateTable", browser, signature=c("PqConnection"))
ただしめんどくさいのは、継承とかの関係で、実際に呼ばれるのはスーパークラスのメソッドだったりする。例えばsqlAppendTableはPqConnectionにメソッドはなくて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のディスパッチまじでわからない...
