Support Questions

Find answers, ask questions, and share your expertise

phoenix on hbase

avatar
Master Collaborator

Hi:

Iam trying to connect from rstudio to phonenix hbase but i am receiving this error:

drv <-
  JDBC(
    "org.apache.phoenix.jdbc.PhoenixDriver",
    "/usr/hdp/current/phoenix-client/phoenix-client.jar"
  )
for (l in list.files("/usr/hdp/current/phoenix-client/")) {
  .jaddClassPath(paste("/usr/hdp/current/phoenix-client/", l, sep = ""))
}
for (l in list.files("/usr/hdp/current/hbase-client/lib/")) {
  .jaddClassPath(paste("/usr/hdp/current/hbase-client/lib/", l, sep = ""))
}
for (l in list.files("/usr/hdp/2.3.2.0-2950/hadoop/lib/")) {
  .jaddClassPath(paste("/usr/hdp/2.3.2.0-2950/hadoop/lib/", l, sep = ""))
}


conn <- dbConnect(drv,
                  "jdbc:phoenix:lnxbig04.cajarural.gcr,lnxbig05.cajarural.gcr,lnxbig06.cajarural.gcr:2181:/hbase-unsecure")
query <- paste("SELECT * FROM canal")




B <- dbGetQuery(conn, query)
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT * FROM canal (ERROR 1012 (42M03): Table undefined. tableName=CANAL)

but the table is create and has data:

1 ACCEPTED SOLUTION

avatar
Super Collaborator

Could you please provide the DDL how you created table? Whether it was quoted or not? And a simple observation. To use phoenix-client you don't need all the jars in the classpath. phoenix-client.jar would be enough.

View solution in original post

6 REPLIES 6

avatar
Super Collaborator

Could you please provide the DDL how you created table? Whether it was quoted or not? And a simple observation. To use phoenix-client you don't need all the jars in the classpath. phoenix-client.jar would be enough.

avatar
Master Collaborator
conn <- dbConnect(drv,
                  "jdbc:phoenix:lnxbig04.cajarural.gcr,lnxbig05.cajarural.gcr,lnxbig06.cajarural.gcr:2181:/hbase-unsecure")
query <- paste("CREATE TABLE phoenix (a_key VARCHAR PRIMARY KEY, a_col VARCHAR)")
B <- dbGetQuery(conn, query)
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for CREATE TABLE phoenix (a_key VARCHAR PRIMARY KEY, a_col VARCHAR) (ERROR 1103 (XCL03): executeQuery may not be used. Query: CREATE TABLE phoenix (a_key VARCHAR PRIMARY KEY, a_col VARCHAR))

avatar
Super Collaborator

This error is predictable. dbGetQuery executes executeQuery() which is supposed to be used to retrieve ResultSet. Queries that are working with meta data are supposed to be run with execute(). In R there is a set of separate functions like sqlCreateTable should be used.

First of all to check whether it's connected correctly try to use dbListTables(conn). It's supposed to show system tables. After you may try to use sqlCreateTable to create the table.

avatar
Master Collaborator

it work now like this:

first create the table from command line:

/usr/hdp/current/phoenix-client/bin
./sqlline.py zookeeperHostname

create table test (mykey integer not null primary key, mycolumn varchar);
upsert into test values (1,'Hello');
upsert into test values (2,'World!');
select * from test;

and from RStudio like this:
conn <- dbConnect(drv,"jdbc:phoenix:lnxbig04.cajarural.gcr,lnxbig05.cajarural.gcr,lnxbig06.cajarural.gcr:2181:/hbase-unsecure")
query <- paste("select * from test")
B <- dbGetQuery(conn, query)
dbListTables(conn)


avatar
Explorer

You need some more in classpath especially if you are using some other features such as UDF, etc. You can add these into your classpath: hbase_config_path, hadoop_common_jar, hadoop_hdfs_jar, hadoop_conf, hadoop_classpath

,

You need more in classpath:

hbase_config_path, hadoop_common_jar, hadoop_hdfs_jar, hadoop_conf, hadoop_classpath

avatar
Master Collaborator
for (l in list.files("/usr/hdp/current/phoenix-client/")) {
  .jaddClassPath(paste("/usr/hdp/current/phoenix-client/", l, sep = ""))
}
for (l in list.files("/usr/hdp/current/hbase-client/lib/")) {
  .jaddClassPath(paste("/usr/hdp/current/hbase-client/lib/", l, sep = ""))
}
for (l in list.files("/usr/hdp/2.3.2.0-2950/hadoop/lib/")) {
  .jaddClassPath(paste("/usr/hdp/2.3.2.0-2950/hadoop/lib/", l, sep = ""))
}
for (l in list.files("/etc/hbase/2.4.0.0-169/0/")) {
  .jaddClassPath(paste("/etc/hbase/2.4.0.0-169/0/", l, sep = ""))
}