Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

phoenix on hbase

Solved Go to solution

phoenix on hbase

Super 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

Accepted Solutions

Re: phoenix on hbase

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.

6 REPLIES 6

Re: phoenix on hbase

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.

Re: phoenix on hbase

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

Re: phoenix on hbase

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.

Highlighted

Re: phoenix on hbase

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


Re: phoenix on hbase

New Contributor

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

Re: phoenix on hbase

Super 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 = ""))
}


Don't have an account?
Coming from Hortonworks? Activate your account here