Change names(tbl) on a dbplyr tbl connected to our Hive schema

dbplyr
tidyverse
tbl

#1

I have a tbl I pulled from our hive instance:

library(odbc)
library(tidyverse)
library(dbplyr)

con <- dbConnect(odbc::odbc(), "HiveProd")
prosym <- tbl(con, in_schema("my_schema_name", "pros_year_month"))

The table pros_year_month is in the schema my_schema_name. When I glimpse prosym, all the fields are prepended with my_schema_name e.g. my_schema_name.session_id, my_schema_name.country etc etc.

I'd like to remove my_schema_name from the names of the fields. If I was working with a regular df I would just do this:

corrected_names <- str_replace_all(names(prosym), "my_schema_name.", "")
names(prosym) <- corrected_names

However, prosym shows as a list of 2 with names "src" and "ops" so the above doesn't work.

How can I change the names of prosym?

The reason that I'm trying to do this is that I'm applying several dplyr transformations such as filter and mutate before pulling it all in with collect(). Since I need to reference the data frames features to make these transformations, typing my_schema_name.[fieldname]) all the time is looking unsightly


#2

I just faced this issue myself.

The cause was IT instructing us to change our odbc driver settings to use native queries (under Advanced Options on Windows or via the odbc.ini file on a Linux server, I think). This caused Hive to prepend the table names as you have demonstrated.

As this proved such a pain we reversed the instruction for now and will wait to see if we are alone in complaining. I had thought of renaming all columns returned, too, but I hope there is a better solution.


#3

Thanks for the suggestion Martin. I just experimented with my odbc.ini file and changed the setting UseNativeQuery=0 to UseNativeQuery=1.
So actually, we were already using Not native however I've now tried both options with no success.
However, the issue persists. Thanks all the same for the suggestion.


#4

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.