Contents
Introduction
Apache Phoenix provides an SQL interface to HBase. It’s like an SQL layer on top of HBase architecture. In this tutorial, we are going to walk you through the development of a Apache Phoenix client application in Java. Phoenix enables developers to use standard SQL and JDBC APIs by leveraging HBase as its backing store. Although there are quite a few tutorials out there which describes how to use JDBC in order to get through to the HBase engine, most of them focus on the standard JDBC Phoenix client driver, which definitely has its pitfalls. In order to circumvent those pitfalls, we will show you the most simple approach on connecting a client Phoenix driver to HBase. If you’re having difficulties with your previous approach, be assured that this solution will definitely work right out of the box.
The first problem developers may encounter is the fact that there are different drivers available in the Apache Phoenix project. Choosing the right one will clear the path to success. So let’s take a look what’s in store for us. One of the instructions on the Apache Phoenix project site says:
- “add the phoenix client jar to the classpath of your HBase client”
There is a download link to:
apache-phoenix-4.8.1-HBase-1.2-bin.tar.gz 2016-09-28 07:16 194M
This tarball contains, among other things, JDBC client drivers. You might be tempted to download the first client driver.jar file listed, which would be:
phoenix-4.8.1-HBase-1.2-client.jar
This is where things get complicated. By looking into this jar file, you’ll see hundreds of dependent classes. For a simple database client program using JDBC, this is overkill. In addition, you’ll probably face a few build problems because of some inherent dependency issues. So, if you don’t want to get yourself into trouble, forget about this file and use this one instead:
phoenix-queryserver-client-4.8.1-HBase-1.2.jar
For an explanation about the whole issue around the two different drivers, see https://community.hortonworks.com/articles/2663/phoenix-queryserver.html. The phoenix-queryserver-client driver connects to the Phoenix Query Server (PQS). PQS specifically refers to an HTTP server which can interact with a custom JDBC driver that translates JDBC API calls to HTTP requests.
Don’t forget that PQS must be installed on the server. The most simple installation option would be that Phoenix SQL switch you can toggle in the HBase configuration section of the Ambari UI.
For this tutorial we need to setup a test table in HBase. Fortunately, Phoenix Query Server comes with a few test data files which contain the necessary DDL statements to create such a table. You can find one on the server at the following path:
/usr/hdp/2.4.3.0-227/phoenix/doc/examples/STOCK_SYMBOL.sql
In order to create the STOCK_SYMBOL table, we need to run the Phoenix command line interface on the server. For more information, see https://phoenix.apache.org/installation.html. Make sure HBase is running before proceeding this tutorial. If you have an Ambari installation, the necessary services are usually started by the following command:
$ sudo ambari-server start
Change the current directory to /usr/hdp/2.4.3.0-227/phoenix/bin. There you enter the command
sqlline.py localhost
As soon as the CLI has started correctly, you can enter the following SQL commands to create the table STOCK_SYMBOLS.
CREATE TABLE IF NOT EXISTS STOCK_SYMBOL (SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);
UPSERT INTO STOCK_SYMBOL VALUES ('AAPL,','APPLE Inc.');
UPSERT INTO STOCK_SYMBOL VALUES ('HPQ','Hewlett-Packard');
UPSERT INTO STOCK_SYMBOL VALUES ('MSFT','Microsoft');
SELECT * FROM STOCK_SYMBOL;
You are done.
You can check whether the table has been correctly created in HBase by using the HBase CLI. Change directory to
/usr/hdp/2.4.3.0-227/hbase/bin
and enter this command:
$ hbase shell
Then you can enter the commands
list describe 'STOCK_SYMBOL'
Links:
https://learnhbase.wordpress.com/