phoenix on hbase

[#1]

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:

[#2 · Accepted solution]

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.

[#3]

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

[#4]

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.

[#5]

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)

[#6]

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

[#7]

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