CODEWITHSUNDEEP
rapache-sparkdplyrsparklyr
nachti
nachti

Reputation: 1100

Access table in other than default scheme (database) from sparklyr

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

Upvotes: 6

Views: 1653

Answers (3)

dalloliogm
dalloliogm

Reputation: 8940

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

e.g.:

tbl_change_db("other_db")

Upvotes: 0

Robert Overman
Robert Overman

Reputation: 11

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") %>% ...

Upvotes: 0

zero323
zero323

Reputation: 330203

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")

Upvotes: 6

Related Questions