Bridge the SQL-NoSQL gap with Apache Phoenix

Java programming with Phoenix

Connecting to and interacting with HBase through Phoenix is as simple as connecting to any database using a JDBC driver:

• Add the JDBC driver to your CLASSPATH.
• Use the DriverManager to obtain a connection to the database.
• Execute queries against the database.

I have uploaded the source code for this example to GitHub. I first set up a new Maven project and configured my POM file as shown in Listing 1.

Listing 1. pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.geekcap.javaworld</groupId>
    <artifactId>phoenix-example</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>phoenix-example</name>
    <url>http://maven.apache.org</url>

    <properties>
        <java.version>1.6</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>4.6.0-HBase-1.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.0.2</version>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <classpathPrefix>lib/</classpathPrefix>
                            <mainClass>com.geekcap.javaworld.phoenixexample.PhoenixExample</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-dependency-plugin</artifactId>
                <executions>
                    <execution>
                        <id>copy</id>
                        <phase>install</phase>
                        <goals>
                            <goal>copy-dependencies</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>${project.build.directory}/lib</outputDirectory>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

This POM file imports the Phoenix Core Maven dependency, which provides access to the Phoenix JDBC driver:

        
<dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>4.6.0-HBase-1.1</version>
        </dependency>

The POM file does some housekeeping work next: it sets the source compilation to Java 6, specifies that dependencies should be copied to the target/lib folder during the build, and makes the resulting JAR file executable for the main class, com.geekcap.javaworld.phoenixexample.PhoenixExample.

Listing 2 shows the source code for the PhoenixExample class.

Listing 2. PhoenixExample.java

package com.geekcap.javaworld.phoenixexample;

import java.sql.*;

public class PhoenixExample {

    public static void main(String[] args) {
        // Create variables
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        PreparedStatement ps = null;

        try {
            // Connect to the database
            connection = DriverManager.getConnection("jdbc:phoenix:localhost");

            // Create a JDBC statement
            statement = connection.createStatement();

            // Execute our statements
            statement.executeUpdate("create table javatest (mykey integer not null primary key, mycolumn varchar)");
            statement.executeUpdate("upsert into javatest values (1,'Hello')");
            statement.executeUpdate("upsert into javatest values (2,'Java Application')");
            connection.commit();

            // Query for table
            ps = connection.prepareStatement("select * from javatest");
            rs = ps.executeQuery();
            System.out.println("Table Values");
            while(rs.next()) {
                Integer myKey = rs.getInt(1);
                String myColumn = rs.getString(2);
                System.out.println("\tRow: " + myKey + " = " + myColumn);
            }
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            if(ps != null) {
                try {
                    ps.close();
                }
                catch(Exception e) {}
            }
            if(rs != null) {
                try {
                    rs.close();
                }
                catch(Exception e) {}
            }
            if(statement != null) {
                try {
                    statement.close();
                }
                catch(Exception e) {}
            }
            if(connection != null) {
                try {
                    connection.close();
                }
                catch(Exception e) {}
            }
        }
    }
}

Listing 2 first creates a database connection by passing jdbc:phoenix:localhost as the JDBC URL to the DriverManager class, as shown here:

connection = DriverManager.getConnection("jdbc:phoenix:localhost");

Just like in the shell console, localhost refers to the server running Zookeeper. If you were connecting to a production HBase instance, you would want to use the Zookeeper server name or IP address for that production instance. With a javax.sql.Connection, the rest of the example is simple JDBC code. The steps are as follows:

1. Create a Statement for the connection.
2. Execute a series of statements using the executeUpdate() method.
3. Create a PreparedStatement to select all the data that we inserted.
4. Execute the PreparedStatement, retrieve a ResultSet, and iterate over the results.

You can build the project as follows: mvn clean install.

Then execute it with the following command from the target directory:

java -jar phoenix-example-1.0-SNAPSHOT.jar

You should see output like the following (note that I excluded the Log4j warning messages):

Table Values Row: 1 = Hello Row: 2 = Java Application 

You can also verify this from the Phoenix console. First execute a !tables command to view the tables and observe that JAVATEST is there:

0: jdbc:phoenix:localhost> !tables


+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+--------------------------+
|                TABLE_CAT                 |               TABLE_SCHEM                |                TABLE_NAME                |                TABLE_TYPE                |                 REMARKS  |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+--------------------------+
|                                          | SYSTEM                                   | CATALOG                                  | SYSTEM TABLE                             |                          |
|                                          | SYSTEM                                   | FUNCTION                                 | SYSTEM TABLE                             |                          |
|                                          | SYSTEM                                   | SEQUENCE                                 | SYSTEM TABLE                             |                          |
|                                          | SYSTEM                                   | STATS                                    | SYSTEM TABLE                             |                          |
|                                          |                                          | JAVATEST                                 | TABLE                                    |                          |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+--------------------------+

Finally, query the JAVATEST table to see your data:

0: jdbc:phoenix:localhost> select * from javatest;

+------------------------------------------+------------------------------------------+
|                  MYKEY                   |                 MYCOLUMN                 |
+------------------------------------------+------------------------------------------+
| 1                                        | Hello                                    |
| 2                                        | Java Application                         |
+------------------------------------------+------------------------------------------+

Note that if you want to run this example multiple times you will want to drop the table using either the console or by adding the following to the end of Listing 2:

statement.executeUpdate("drop table javatest")

As you can see, using Phoenix is a simple matter of creating JDBC connection and using the JDBC APIs. With this knowledge you should be able to start using Phoenix with more advanced tools like Spring's JdbcTemplate or any of your other favorite JDBC abstraction libraries!

Conclusion

Apache Phoenix provides an SQL layer on top of Apache HBase that allows you to interact with HBase in a familiar manner. You can leverage the scalability that HBase derives from running on top of HDFS, along with the multi-dimensional data model that HBase provides, and you can do it using familiar SQL syntax. Phoenix also supports high performance by leveraging native HBase APIs rather than MapReduce processes; implementing co-processors to reduce client/server data transfer; and providing custom filters that improve the execution, navigation, and speed of data querying.

Using Phoenix is as simple as adding a JAR file to HBase, adding Phoenix's JDBC driver to your CLASSPATH, and creating a standard JDBC connection to Phoenix using its JDBC URL. Once you have a JDBC connection, you can use HBase just as you would any other database.

This Open source Java projects tutorial has provided an overview of both HBase and Phoenix, including the specific motivation for developing each of these technologies. You've set up and integrated Phoenix and HBase in your local working environment, and learned how to interact with Phoenix using the Phoenix console and through a Java application. With this foundation you should be well prepared to start building applications on top of HBase, using standard SQL.