Accessing column names from linked tibbles (remote tables)

tidyverse
1

When I link a tibble to a remote database, e.g. train_data <- tbl(myDb, "remote_table") I am unable to list the column names the way I am used from local data frames by calling names(train_data); it works, but does not give me the result I expect.

Is there another way to get vector of tibble column names (it is not practical for me to list them via glimpse and go through them manually).

2

The DBI package has a function dbListFields that can help here.
An example where I use your name myDb and remote_table using examples from dbplyr website

myDb <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
dplyr::copy_to(myDb, nycflights13::flights, "remote_table",
        temporary = FALSE, 
        indexes = list(
          c("year", "month", "day"), 
          "carrier", 
          "tailnum",
          "dest"
        )
)
DBI::dbListTables(myDb)
#> [1] "remote_table" "sqlite_stat1" "sqlite_stat4"
DBI::dbListFields(myDb, "remote_table")
#>  [1] "year"           "month"          "day"            "dep_time"      
#>  [5] "sched_dep_time" "dep_delay"      "arr_time"       "sched_arr_time"
#>  [9] "arr_delay"      "carrier"        "flight"         "tailnum"       
#> [13] "origin"         "dest"           "air_time"       "distance"      
#> [17] "hour"           "minute"         "time_hour"
2 Likes
3

Thank you! I was looking in the documentation of the tibble package and it did not occur to me to check DBI. All is clear now :slight_smile:

4

Hi, using colnames() instead of names() also works:

colnames(dplyr::tbl(myDb, "remote_table"))
 [1] "year"           "month"          "day"            "dep_time"       "sched_dep_time" "dep_delay"      "arr_time"       "sched_arr_time" "arr_delay"      "carrier"        "flight"         "tailnum"       
[13] "origin"         "dest"           "air_time"       "distance"       "hour"           "minute"         "time_hour"
1 Like