Phoenix - E-MapReduce - Alibaba Cloud Documentation Center

Apache Phoenix is an SQL middle layer built on top of HBase. Phoenix allows you to execute SQL statements to query HBase data.

Prerequisites

A DataServing cluster is created, and Phoenix is selected when you create the cluster. For more information, see Create a cluster.

Use Phoenix

Log on to the DataServing cluster in SSH mode. For more information, see Log on to a cluster.
Run the following command to switch to the hadoop user:
```
su hadoop
```
Run the following command to go to the bin directory:
```
cd /opt/apps/PHOENIX/phoenix-current/bin
```
Run the following command to open the Phoenix CLI:
```
sqlline.py
```

Execute SQL statements to manage data.

Create a table.

CREATE TABLE example(
    my_pk bigint not null,
    m.first_name varchar(50),
    m.last_name varchar(50) 
    CONSTRAINT pk PRIMARY KEY (my_pk));

Insert data into the table.

UPSERT INTO example(my_pk,m.first_name,m.last_name) VALUES(100,'Jack','Ben');
UPSERT INTO example(my_pk,m.first_name,m.last_name) VALUES(200,'Jack3','Ben3');

Query data from the table.

SELECT * FROM example;

The following output is returned:

+--------+-------------+------------+
| MY_PK  | FIRST_NAME  | LAST_NAME  |
+--------+-------------+------------+
| 100    | Jack        | Ben        |
| 200    | Jack3       | Ben3       |
+--------+-------------+------------+

Drop the table.
```
DROP TABLE example;
```

Use JDBC to access Phoenix

Configure the Maven dependency

<dependency>
     <groupId>org.apache.phoenix</groupId>
     <artifactId>phoenix-core</artifactId>
     <version>${phoenix.version}</version>
</dependency>

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class TestPhoenixJdbc {

    public static void main(String[] args) throws SQLException {
        Statement stmt = null;
        ResultSet rset = null;

        Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
        Connection con = DriverManager.getConnection("jdbc:phoenix:[zookeeper quorum hosts]");
        stmt = con.createStatement();

        stmt.executeUpdate("create table test (mykey integer not null primary key, mycolumn varchar)");
        stmt.executeUpdate("upsert into test values (1,'Hello')");
        stmt.executeUpdate("upsert into test values (2,'World!')");
        con.commit();

        PreparedStatement statement = con.prepareStatement("select * from test");
        rset = statement.executeQuery();
        while (rset.next()) {
            System.out.println(rset.getString("mycolumn"));
        }
        statement.close();
        con.close();
    }
}

The syntax of the Java Database Connectivity (JDBC) URL is jdbc:phoenix:[comma-separated ZooKeeper Quorum Hosts[: ZK port [:hbase root znode [:kerberos_principal [:path to kerberos keytab] ] ] ].

References

For more information about Phoenix, see the following topics in the official documentation: