Phoenix - E-MapReduce - Alibaba Cloud Documentation Center

Apache Phoenix is an SQL intermediate layer built on top of HBase. Apache Phoenix allows you to execute standard SQL statements to query and manage HBase data.

Prerequisites

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

Use Phoenix

Log on to your 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 open the Phoenix CLI:
```
/opt/apps/PHOENIX/phoenix-current/bin/sqlline.py
```

Execute SQL statements to manage data. Common operations:

Create a table.

CREATE TABLE IF NOT EXISTS 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 IF EXISTS 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>

The version specified by ${phoenix.version} must be the same as that you use in the cluster.

Sample code

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: