odbc
odbc copied to clipboard
r-dbi
Cassandra throws error due to missing primary key
Issue Description and Expected Result
Cassandra requires that all tables have a primary key (see docs). When DataTypes.R is updated for Cassandra with the following:
`odbcDataType.Cassandra` <- function(con, obj, ...) {
switch_type(obj,
factor = "TEXT",
datetime = "TIMESTAMP",
time = "TIME",
date = "DATE",
binary = "BLOB",
integer = "BIGINT",
double = "DOUBLE",
character = "TEXT",
logical = "BOOLEAN",
stop("Unsupported type", call. = FALSE)
)
}
And then dbWriteTable(con, "cars", mtcars) is used, the following error is returned:
Error: <SQL> 'CREATE TABLE "cars" ( "row_names" TEXT, "mpg" DOUBLE, "cyl" DOUBLE, "disp" DOUBLE, "hp" DOUBLE, "drat" DOUBLE, "wt" DOUBLE, "qsec" DOUBLE, "vs" DOUBLE, "am" DOUBLE, "gear" DOUBLE, "carb" DOUBLE ) ' nanodbc/nanodbc.cpp:1587: HY000: [RStudio][CassandraODBC] (120) Primary key need to be defined for CREATE TABLE statement.
Database
Cassandra 3.11.3
Session Info
devtools::session_info()
#> ─ Session info ──────────────────────────────────────────────────────────
#> setting value
#> version R version 3.5.1 (2018-07-02)
#> os macOS 10.14.1
#> system x86_64, darwin15.6.0
#> ui X11
#> language (EN)
#> collate en_US.UTF-8
#> ctype en_US.UTF-8
#> tz America/Denver
#> date 2018-11-05
#>
#> ─ Packages ──────────────────────────────────────────────────────────────
#> package * version date lib source
#> assertthat 0.2.0 2017-04-11 [1] CRAN (R 3.5.0)
#> backports 1.1.2 2017-12-13 [1] CRAN (R 3.5.0)
#> base64enc 0.1-3 2015-07-28 [1] CRAN (R 3.5.0)
#> callr 3.0.0 2018-08-24 [1] CRAN (R 3.5.1)
#> cli 1.0.1 2018-09-25 [1] CRAN (R 3.5.1)
#> crayon 1.3.4 2017-09-16 [1] CRAN (R 3.5.0)
#> debugme 1.1.0 2017-10-22 [1] CRAN (R 3.5.0)
#> desc 1.2.0 2018-06-15 [1] Github (r-lib/desc@4f60833)
#> devtools 2.0.1 2018-10-26 [1] CRAN (R 3.5.1)
#> digest 0.6.18 2018-10-10 [1] CRAN (R 3.5.0)
#> evaluate 0.12 2018-10-09 [1] CRAN (R 3.5.1)
#> fs 1.2.6 2018-08-23 [1] CRAN (R 3.5.1)
#> glue 1.3.0 2018-09-04 [1] Github (tidyverse/glue@4e74901)
#> htmltools 0.3.6 2017-04-28 [1] CRAN (R 3.5.0)
#> knitr 1.20 2018-02-20 [1] CRAN (R 3.5.0)
#> magrittr 1.5 2014-11-22 [1] CRAN (R 3.5.0)
#> memoise 1.1.0 2017-04-21 [1] CRAN (R 3.5.0)
#> pkgbuild 1.0.2 2018-10-16 [1] CRAN (R 3.5.0)
#> pkgload 1.0.2 2018-10-29 [1] CRAN (R 3.5.1)
#> prettyunits 1.0.2 2015-07-13 [1] CRAN (R 3.5.0)
#> processx 3.2.0 2018-08-16 [1] CRAN (R 3.5.1)
#> ps 1.2.0 2018-10-16 [1] CRAN (R 3.5.0)
#> R6 2.3.0 2018-10-04 [1] CRAN (R 3.5.1)
#> Rcpp 0.12.19 2018-10-01 [1] CRAN (R 3.5.0)
#> remotes 2.0.2 2018-10-30 [1] CRAN (R 3.5.1)
#> rlang 0.3.0.1 2018-10-25 [1] CRAN (R 3.5.0)
#> rmarkdown 1.10 2018-06-11 [1] CRAN (R 3.5.0)
#> rprojroot 1.3-2 2018-01-03 [1] CRAN (R 3.5.0)
#> sessioninfo 1.1.0 2018-09-25 [1] CRAN (R 3.5.0)
#> stringi 1.2.4 2018-07-20 [1] CRAN (R 3.5.0)
#> stringr 1.3.1 2018-05-10 [1] CRAN (R 3.5.0)
#> testthat 2.0.1 2018-10-13 [1] CRAN (R 3.5.0)
#> usethis 1.4.0 2018-08-14 [1] CRAN (R 3.5.1)
#> withr 2.1.2 2018-09-21 [1] Github (jimhester/withr@8b9cee2)
#> yaml 2.2.0 2018-07-25 [1] CRAN (R 3.5.1)
#>
#> [1] /Library/Frameworks/R.framework/Versions/3.5/Resources/library
I guess this would imply a cassandra database would need it's own dbWriteTable() method, with an additional argument specifying the column(s) that are the primary key.
Alternatively we just document how to create a table with cassandra with raw SQL, as supporting all the various ways of defining the primary key will be difficult to fit into the existing framework.
Sounds good - this issue was raised just to see if there was a relatively simple solution to support writing to Cassandra via dbWriteTable(). If the solution is defining a new dbWriteTable() method, I agree that may involve more work than it's worth.
We don't appear to have had many customer requests for cassandra support, so I'm closing these issues.
