CODEWITHSUNDEEP
dplyrodbcdbitrinodbplyr
willshen
willshen

Reputation: 63

R DBI with ODBC to cast datatype for additional database

I'm using below DBI connection with ODBC driver to connect to a Trino database

DBI::dbConnect(
  drv = odbc::odbc(),
  'trino'
)

It failed to translate as.character().

m_tbl %>% mutate(col1 = as.character(''))

The sql it renders is CAST('' AS STRING). However STRING is not an accepted data type for Trino. It should cast into VARCHAR.

I found files driver-{db_name}.R in r-dbi/odbc, which has the implementation for data type casting. Here's an example for postgres: https://github.com/r-dbi/odbc/blob/main/R/driver-postgres.R

What's the best practice to implement similar logic for additional databases that's not currently supported by DBI ODBC?

Upvotes: 0

Views: 79

Answers (1)

Simon.S.A.
Simon.S.A.

Reputation: 6941

It appears you can connect to the Trino database, so the problem is not with DBI or ODBC. You are encountering the limits of what is currently available in dbplyr.

dbplyr translates R commands into the equivalent SQL. As different SQL versions use slightly different keywords/structure, dbplyr contains translations rules for different types of connections. To the best of my testing Trino translations do not already exist in dbplyr.

If the database type you are using is not setup in dbplyr you can define your own translations.

Be warned that if these resources are insufficient, there is minimal further guidance on defining your own translations. I am not aware of any dbplyr questions on Stackoverflow that discuss this in more detail.

If defining custom translations is too much. There are three other approaches you can consider:

  • Creating custom SQL functions: example here
  • Using the sql() function to bypass translation: mutate(col1 = sql("CAST('' AS STRING)")
  • Modifying the SQL translation. Something like: tbl %>% sql_render() %>% gsub(... details here ...) %>% tbl(src = connection). Where you would use gsub to replace AS STRING with AS TEXT.

Upvotes: 0

Related Questions