6

After I managed it to connect to our (new) cluster using sparklyr with yarn-client method, now I can show just the tables from the default scheme. How can I connect to scheme.table? Using DBI it's working e.g. with the following line: dbGetQuery(sc, "SELECT * FROM scheme.table LIMIT 10") In HUE, I can show all tables from all schemes.

~g

zero323
  • 322,348
  • 103
  • 959
  • 935
nachti
  • 1,086
  • 7
  • 20

3 Answers3

6

You can either use a fully qualified name to register temporary view:

spark_session(sc) %>% 
  invoke("table", "my_database.my_table") %>%
  invoke("createOrReplaceTempView", "my_view")

tbl(sc, "my_view")

or use sql method to switch databases

spark_session(sc) %>% invoke("sql", "USE my_database")

and access table directly with dplyr:tbl:

tbl(sc, "my_table")
zero323
  • 322,348
  • 103
  • 959
  • 935
  • Thank you very much! I used the second option, which worked fine for me. – nachti May 22 '17 at 09:48
  • Just a heads up to avoid confusion: if you've already loaded the tidyverse, purrr::invoke() will override sparklyr::invoke() unless the function is prefixed. – Joe Apr 26 '22 at 20:58
0

You can also use DBI'sdbgetQuery to change the database. This is useful bc it will also update your view in Connections to the specific data base rather than the default.

DBI::dbGetQuery(sc, "use <database>")

Lastly you can just reference the database within a tbl statement

dplyr::tbl(sc,"want_db.have_data") %>% ...
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Robert Overman
  • 11
  • 1
0

Another option is to use tbl_change_db to change the default database for the session.

e.g.:

tbl_change_db("other_db")
dalloliogm
  • 8,718
  • 6
  • 45
  • 55