SQL Query

Updated on 2022-09-14 GMT+08:00

View PDF

Function

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 multiple HBase scans, and compiles and executes the scan tasks to generate a standard JDBC result set.

Example Code

The hbase-example/conf/hbase-site.xml file on the client is used to configure the temporary directory for storing query results. If the client program configures the temporary directory in a Linux environment, configure a Linux path. If the client program configures the temporary directory in a Windows environment, configure a Windows path.
```
<property>
     <name>phoenix.spool.directory</name>
     <value>[1] Temporary directory for storing intermediate query results</value>
</property>
```

JAVA Example: Use 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); 
                 conn.commit();
                 // 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 
                      } 
                 } 
              } 
          }

Precaution

You need to configure a temporary directory for storing intermediate query results in hbase-site.xml. The size of the query result set is restricted by the directory size.
Phoenix provides most java.sql interfaces and follows the ANSI SQL standard.

Parent topic: More Information

Previous topic: More Information

Next topic: HBase Dual-Read Configuration Items