Getting Started with Apache Phoenix: Installation, Basic Usage, and Java API

Apache Phoenix is an open‑source SQL middleware for HBase that lets you interact with HBase data using standard JDBC; its slogan is "we put sql back in nosql" and it can be integrated with common persistence frameworks such as Spring Data JPA and MyBatis.

Phoenix offers excellent performance by translating SQL queries into one or more HBase scans, executing them in parallel, and supporting features like secondary indexes that are not available in raw HBase, making it the most capable SQL layer for HBase.

Installation follows the official guide: download and expand the tarball, copy the Phoenix server JAR to the lib directory of every RegionServer, restart the RegionServers, add the Phoenix client JAR to the HBase client classpath, and install SQuirrel as an SQL client.

# Download
wget http://mirror.bit.edu.cn/apache/phoenix/apache-phoenix-4.14.0-cdh5.14.2/bin/apache-phoenix-4.14.0-cdh5.14.2-bin.tar.gz
# Extract
tar -xzf apache-phoenix-4.14.0-cdh5.14.2-bin.tar.gz

Copy the server JAR:

cp /usr/app/apache-phoenix-4.14.0-cdh5.14.2-bin/phoenix-4.14.0-cdh5.14.2-server.jar /usr/app/hbase-1.2.0-cdh5.15.2/lib

Restart RegionServers:

# Stop HBase
stop-hbase.sh
# Start HBase
start-hbase.sh

Start Phoenix client (adjust Zookeeper address as needed):

# ./sqlline.py hadoop001:2181

After launching, you can use !tables or !table to list tables.

Basic SQL usage includes creating tables, upserting data, updating, deleting, and querying. Example statements:

CREATE TABLE IF NOT EXISTS us_population (
    state CHAR(2) NOT NULL,
    city VARCHAR NOT NULL,
    population BIGINT,
    CONSTRAINT my_pk PRIMARY KEY (state, city)
);

UPSERT INTO us_population VALUES('NY','New York',8143197);
-- ... other UPSERT statements ...

DELETE FROM us_population WHERE city='Dallas';

SELECT state AS "州", count(city) AS "市", sum(population) AS "热度"
FROM us_population
GROUP BY state
ORDER BY sum(population) DESC;

Java API example shows loading the Phoenix driver, obtaining a JDBC connection, executing a SELECT, and printing results:

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

public class PhoenixJavaApi {
    public static void main(String[] args) throws Exception {
        Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
        Connection connection = DriverManager.getConnection("jdbc:phoenix:192.168.200.226:2181");
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM us_population");
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString("city") + " " + resultSet.getInt("population"));
        }
        statement.close();
        connection.close();
    }
}

For Maven projects, add the phoenix-core dependency; for non‑Maven projects, manually include the JAR from the Phoenix distribution. The article also points to official Phoenix documentation for grammar, functions, data types, sequences, and joins.

References: http://phoenix.apache.org/ and the GitHub repository BigData-Notes .