Specifying the database when using dbWriteTable

[patzaw]

Hi

I would like to write tables in different databases.
The way I found, and which is described in the example below, works when the table does not exist yet but not anymore when I want to append some records.
(I've used the "default" database in order to facilitate the reproduction of the issue but obviously this issue is more annoying with other databases.)

library(RClickhouse)
library(DBI)

con <- dbConnect(clickhouse(), port=9000)

## Create a new table ----
dbWriteTable(con, name=SQL("`default`.`test`"), value=data.frame(name="a", stringsAsFactors=FALSE))

## Append records to the table ----
dbWriteTable(con, name=SQL("`default`.`test`"), value=data.frame(name="b", stringsAsFactors=FALSE), append=T)
#> Error in select(conn@ptr, statement) : 
#>  DB::Exception: Table default.test already exists.

I think that the issue comes from testing the existence of the table at line 179 of the ClickhouseConnection.R file (definition of the dbWriteTable function). dbListTables only returns table names from the "default" database and those names are not prefixed with the database name.

Do you know a way to handle this issue with the current version of the package?
If not, do you think you could adapt the package in order to allow such functionality?

Thank you

Best regards

Patrice

[tridelt]

Dear Patzaw,

thank you for pointing out that issue. Your are almost certainly right. The encoding needs to be fixed to be able to accurately represent the passed arguments between Rclickhouse and Clickhouse and back.
I am currently working on it.

An immediate solution might be to explicitly designate the database when establishing the connection.
The following code-snippet shows a workaround for the database carsdb which might work for you.

library(RClickhouse)
library(DBI)

con <- DBI::dbConnect(RClickhouse::clickhouse(), db = "carsdb", port=9000)


dbWriteTable(con, name=SQL("`combustion`"), value=data.frame(name="a", stringsAsFactors=FALSE))

dbWriteTable(con, name=SQL("`combustion`"), value=data.frame(name="b", stringsAsFactors=FALSE), append=T)

Best regards
tridelt

[patzaw]

Dear tridelt

Thanks for your answer. It made me think to another possible solution: change the default database. This solution avoid connecting several times when you want to edit several databases.

In your example it should work this way:

library(RClickhouse)
library(DBI)

con <- DBI::dbConnect(RClickhouse::clickhouse(), port=9000)

dbSendQuery(con, "USE carsdb")

dbWriteTable(con, name=SQL("`combustion`"), value=data.frame(name="a", stringsAsFactors=FALSE))

dbWriteTable(con, name=SQL("`combustion`"), value=data.frame(name="b", stringsAsFactors=FALSE), append=T)

## Return to default once you've finished
dbSendQuery(con, "USE default")

Thanks again for your quick and very helpful reply.

Best regards

Patrice