HBase SQL Query Sample Code

Function Description

Phoenix is an intermediate structured query language (SQL) layer built on HBase. Phoenix provides a JDBC driver that can be embedded in a client. The Phoenix query engine converts input SQL statements to one or more HBase scans, and compiles and executes the scan tasks to generate a standard JDBC result set.

Sample Code

• A temporary directory for storing intermediate query results is configured in hbase-example/conf/hbase-site.xml on the client. If a client program executes the temporary directory on Linux, configure a Linux path. If a client program executes the temporary directory on Windows, configure a Windows path.

  <property>
       <name>phoenix.spool.directory</name>
       <value>[1] Temporary directory for storing intermediate query results</value>
  </property>

• Java example: Using the JDBC interface to access HBase

           public String getURL(Configuration conf) 
            {  
               String phoenix_jdbc = "jdbc:phoenix"; 
               String zkQuorum = conf.get("hbase.zookeeper.quorum");      
               return phoenix_jdbc + ":" + zkQuorum; 
            } 
             
            public void testSQL() 
            { 
               String tableName = "TEST"; 
               // Create table 
               String createTableSQL = "CREATE TABLE IF NOT EXISTS TEST(id integer not null primary key, name varchar, account char(6), birth date)"; 
             
               // Delete table 
               String dropTableSQL = "DROP TABLE TEST"; 
             
               // Insert 
               String upsertSQL = "UPSERT INTO TEST VALUES(1,'John','100000', TO_DATE('1980-01-01','yyyy-MM-dd'))"; 
             
               // Query 
               String querySQL = "SELECT * FROM TEST WHERE id = ?"; 
             
               // Create the Configuration instance 
               Configuration conf = getConfiguration(); 
                
               // Get URL 
               String URL = getURL(conf); 
             
               Connection conn = null; 
               PreparedStatement preStat = null; 
               Statement stat = null; 
               ResultSet result = null; 
             
               try 
               { 
                   // Create Connection 
                   conn = DriverManager.getConnection(URL); 
                   // Create Statement 
                   stat = conn.createStatement(); 
                   // Execute Create SQL 
                   stat.executeUpdate(createTableSQL); 
                   // Execute Update SQL 
                   stat.executeUpdate(upsertSQL); 
                   // Create PrepareStatement 
                   preStat = conn.prepareStatement(querySQL); 
                   // Execute query 
                   preStat.setInt(1,1); 
                   result = preStat.executeQuery(); 
                   // Get result 
                   while (result.next())  
                   { 
                       int id = result.getInt("id"); 
                       String name = result.getString(1); 
                   } 
               }  
               catch (Exception e) 
               { 
                   // handler exception 
               } 
               finally 
               { 
                   if(null != result){ 
                        try { 
                            result.close(); 
                            } catch (Exception e2) { 
                                // handler exception 
                                } 
                        } 
                   if(null != stat){ 
                       try { 
                           stat.close(); 
                        } catch (Exception e2) { 
                            // handler exception 
                        } 
                   } 
                   if(null != conn){ 
                       try { 
                           conn.close(); 
                        } catch (Exception e2) { 
                            // handler exception 
                        } 
                   } 
                } 
            }

Precautions

• A temporary directory for storing intermediate query results must be configured in hbase-site.xml. The size of the query result set is restricted by the directory size.
• Phoenix implements most java.sql interfaces. SQL follows the ANSI SQL standard.
• For versions later than MRS 1.9.2, download and configure the open source phoenix package by referring to HBase Phoenix APIs.