まずは蝋の翼から。

学んだことを書きながら確認・整理するためのメモブログ。こういうことなのかな?といったことをふわっと書いたりしていますが、理解が浅いゆえに的はずれなことも多々あると思うのでツッコミ歓迎

RのDB操作でハマったので備忘録

RPostgresを使った処理で、割と初歩的なことを2連続でハマったのでメモ。

CollectしたものとしていないものはJOINできない

library(RPostgres)
library(dbplyr)


tbl_A = tbl(con, in_schema("hoge", "tbl_a")) 
tbl_B = tbl(con, in_schema("hoge", "tbl_b")) 

tbl_A %>% 
  collect() %>% 
  inner_join(tbl_B)
=>
エラー: `x` and `y` must share the same src, set `copy` = TRUE (may be slow)

ものすごく簡略化すると上記のような状態でjoinをおこないエラーが出た。
純化するとcollectしたtbl_Aにjoinしたからエラーになるのは当たり前だが、tbl_Aを加工してグラフ化用に一回collectした後に、tbl_Bとも結合したものも見たいなーと思ってこのエラーが出たのでしばらく格闘してた。

エラー文をテキトーに読んで、同じDBからですけど!とか思ったから余計混乱した。
localのオブジェクトか、DB上のテーブル(に対するクエリ)か、という意味で share the same srcということだったらしい。

ちなみに、雑に書くと以下のような感じだったのでやっているときはしばらく気づかなかった。

tbl_A = tbl(con, in_schema("hoge", "tbl_a")) 

# tbl_Aを加工
tbl_A2 = tbl_A %>% mutate(hoge ~~~~~~~~~) %>% collect()


# tbl_Aを可視化
tbl_A2 %>% ggplot(hoge ~~~~~~`

tbl_B = tbl(con, in_schema("hoge", "tbl_b")) 

tbl_A2 %>% 
  inner_join(tbl_B)
=>
エラー: `x` and `y` must share the same src, set `copy` = TRUE (may be slow)

クエリなので、Rの関数はそのまま使えない(ことがある)

tbl_A %>% 
  inner_join(tbl_B) %>% 
  mutate(str_colums = substring(hoge,1,3)) # hogeから文字列抜き取り
=>
Joining, by = c("aggregated_cm_id", "cast_name")
 result_create(conn@ptr, statement) でエラー: 
  Failed to fetch row: ERROR:  Not implemented
DETAIL:  
  -----------------------------------------------
  error:  Not implemented
  code:      1001
  context:   'false' - Use of complex SQL function substring() is not supported 
  query:     852183
  location:  cg_expr.cpp:306
  process:   padbmaster [pid=23823]
  -----------------------------------------------

collectする前のものは、内部的にはデータは入っていなくて、あくまでクエリ(SQL)となっている。
そのため、SQLに変換できる指定された関数しか使うことができない。

今回ややこしかったのは、substring関数を使っていたが、SQL上で使えるのはsubstr関数で微妙に名前が違う。
他にも、パッケージから読み込んだ関数なども当たり前だが使えない。

使えるものは以下を見よう。 SQL translation • dbplyr

もちろん、一度collectを使ってデータをオブジェクトに格納してからおこなうのは可能。

tbl_A %>% 
  inner_join(tbl_B) %>% 
  collect() %>%
  mutate(str_colums = substring(hoge,1,3)) # hogeから文字列抜き取り

同じことでハマっている人が。。

r - dbplyr mutate character to date format in temp table - Stack Overflow