Merge Hive tables using RJDBC package

Question

I am using RJDBC package to connect to Hive.

library(rJava)
library(RJDBC)
-
-
-
conn <- dbConnect(drv, "jdbc:hive2://ip:port","***", "****")

After getting connected I type this command to see the list of tables:

dbListTables(conn)

[1] "m_11"                     "m_12"                    
 [3] "m_16"                     "m_18"                    
 [5] "m_19"                     "m_20"     

Other command i.e.

dbGetTables (conn, "m_11")
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1                report m_11      TABLE    <NA>

But when I want to read table it shows error as "Table not found":

dbGetQuery(conn, "select * from m_11")
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for select * from m_11 (Error while compiling statement: FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'm_11')

Also,

d <- dbReadTable(conn, "m_11")
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT * FROM "m_11" (Error while compiling statement: FAILED: ParseException line 1:14 cannot recognize input near '"m_11"' '<EOF>' '<EOF>' in join source)

I want to know following commands:

a) How to read table i.e. m_11
b) Merge two table i.e m_11 & m_12 by "Consm_ID"

Is there any other easy alternative to get it done in R.

Answer 1

you should append the database name to the table name to access it. i think rjdbc has "default" db as default.

dbGetQuery(conn, "select * from dbName.m_11")

Also, you should be using hive querys to merge or join two tables, instead of merging them after you get the data-frames in R, as the data source (here hive) would be mostly smarter than join/merge functions in R as datasources as more knowledge about how the data is laid out.