hoxo-m/dplyr.teradata

A Teradata Backend for dplyr

dplyr.teradata's Issues

paste0 in mutate

I am not sure if this should be reported here.

I am trying to create a new column from a date and time column :

mutate (dtime = paste0(date_col, time_col))

but I get this error message:

Error in new_result(connection@ptr, statement) :
nanodbc/nanodbc.cpp:1344: 42000: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and a string or a Unicode character literal.

compute functionality

in dplyr you can create a temporary table in database with the compute.

in teradata, one uses the VOLATILE keyword instead of TEMPORARY.

Could this be added to this package to allow for the creation of volatile/temporary tables in database?

Cannot work with dplyr 1.0.0

Sorry now fixing...
Please use the old version.

install.packages("https://cran.r-project.org/src/contrib/Archive/dplyr/dplyr_0.8.5.tar.gz", repos = NULL, type = "source")

Unable to install the package -

devtools::install_github('hoxo-m/dplyr.teradata')
(skipping a few compilation msg)
.... 
installing to /Library/Frameworks/R.framework/Versions/3.4/Resources/library/odbc/libs
** R
** inst
** tests
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded
* DONE (odbc)
'/Library/Frameworks/R.framework/Resources/bin/R' --no-site-file --no-environ --no-save --no-restore  \
  --quiet CMD INSTALL  \
  '/private/var/folders/72/j1qmrjt11cg7tsg0byvjkjh00000gp/T/RtmpJNjKGn/devtools1ec27ad81c3c/hoxo-m-dplyr.teradata-0a12e75'  \
  --library='/Library/Frameworks/R.framework/Versions/3.4/Resources/library' --install-tests 

* installing *source* package ‘dplyr.teradata’ ...
Warning in as.POSIXlt.POSIXct(x, tz) :
  unknown timezone 'default/America/Chicago'
** R
** tests
** preparing package for lazy loading
Error in setMethod("dbUnQuoteIdentifier", c("Teradata", "SQL"), getMethod("dbUnQuoteIdentifier",  : 
  no existing definition for function ‘dbUnQuoteIdentifier’
Error : unable to load R code in package ‘dplyr.teradata’
ERROR: lazy loading failed for package ‘dplyr.teradata’
* removing ‘/Library/Frameworks/R.framework/Versions/3.4/Resources/library/dplyr.teradata’
Installation failed: Command failed (1)
Warning message:
In strptime(x, fmt, tz = "GMT") :
  unknown timezone 'default/America/Chicago'

Implement like()

Now works col %like% str in R.
But not works like(col, str).

issues with summarise

I have the feeling that our companies version of teradata is just old and so it is going to cause problems regardless. But when I try to summarise without a group_by I always get the error

library(dplyr.teradata)
library(tdplyr)
library(bcbsncR)
con <- DBI::dbConnect(odbc::odbc(), "<internal DSN>", timeout = 10)

copy_to(con,mtcars,"mtcars", temporary = T)

tbl(con,"mtcars") %>% summarise(mean(gears))
#> Warning: Missing values are always removed in SQL.
#> Use `mean(x, na.rm = TRUE)` to silence this warning
#> This warning is displayed only once per session.
#> Error in new_result(connection@ptr, statement, immediate): nanodbc/nanodbc.cpp:1374: HY000: [Teradata][ODBC Teradata Driver][Teradata Database](-5628)Column NANA not found in temp.mtcars.

tbl(con,"mtcars") %>% summarise(mean(gears)) %>% show_query()
#> <SQL>
#> SELECT NANA, AVG("gears") AS "mean(gears)"
#> FROM "mtcars"
#> GROUP BY NANA

^{Created on 2021-02-25 by the reprex package (v0.3.0)}

Has anyone else ever run across this problem? I can generally get around it by group_by(flag = 1) but there are times where the false grouping occurs deep in packages that I can't preempt.

configuration for JDBC connection?

is there anyway to utilize the package with JDBC connection?

Acceralate anti_join()

TODO: Acceralate anti_join()

db_list_tables returns length limited table names

with probably 30 characters

DBI::dbListTables(con)
#> [1] "1234567890123456789012345678901234567890"

db_list_tables(con) 
#> [1] "123456789012345678901234567890"

`count_if()`

I want count_if(cond) that is a short version of sum(ifelse(cond, 1L, 0L)).

Or n_if()

slice_sample() is now broken due to dbplyr package update on 20220604

When I installed R in new machine on 20220609 and run code including function slice_sample(), an error occur saying op_single() function is not found.

Turns out that the dependent package dbplyr updated on 20220604 to version 2.2.0 which decommissioned op_single() function which was available from previous version 2.1.1

Can we use SAMPLE ?

http://www.teradatawiki.net/2013/10/Teradata-SAMPLE-Function.html

Implement case_when()

copy_to functionality

Hello,
Does the teradata backend allow for dplyr's copy_to to function?

After establishing a connection to the database, the following code fails:

testDB<-mtcars
copy_to(con, testDB, 'WORK_DATABASE.testDB')
Error in db_data_type.RODBC(dest$con, df) : Unimplemented

Is this a general issue or specific to my system setup? What is the SQL query that copy_to refers to (e.g. CREATE TABLE)?

Support DSN

dbConnect(dsn = "my_dsn")

Now

DSN is unsupported yet.