Phoenix - E-MapReduce - Alibaba Cloud Documentation Center

Prerequisites

You have created a DataServing or Custom cluster with the Phoenix and HBase services selected. For more information, see Create a cluster.

Use Phoenix from the command line

Connect to the cluster using SSH. For more information, see Log on to a cluster.
Run the following command to use the Phoenix command line tool.
```
/opt/apps/PHOENIX/phoenix-current/bin/sqlline.py
```

You can use SQL to query data. Common operations include:

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

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

SELECT * FROM example;

The query returns the following output.

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

Drop the table
```
DROP TABLE IF EXISTS example;
```

Connect to Phoenix using JDBC

Configure the Maven dependency

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

The ${phoenix.version} must match the Phoenix version on your cluster.

Code 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();
    }
}

Connect to a Kerberos-enabled Phoenix via JDBC

If your cluster uses Kerberos authentication, this section explains how to write a JDBC client that securely connects to the Phoenix service on a Kerberos-enabled EMR cluster. The client uses a JDBC URL with principal and keytab information to authenticate, then runs basic DDL and DML operations to verify the connection.

Step 1: Prepare environment and credentials

Before you write the code, configure your environment and create Kerberos credentials on the cluster's master node.

Connect to the master node using SSH. For more information, see Log on to a cluster.
Identify the Kerberos realm.

Each Kerberos-enabled cluster has a unique realm.

Run the following command to retrieve the Kerberos realm. Record the realm for later use.
```
cat /etc/krb5.conf | grep default_realm
```
The following is a sample response.
```
default_realm = EMR.C-4FC5FDDE3759****.COM
```
Create a client principal.

A principal is the unique identity of a client in Kerberos. You must create a principal for the Java application.
1. On the master node, run the following command to use the kadmin.local tool.
```
sudo kadmin.local
```
2. In the kadmin.local interactive session, run the following command to create the principal.
```
addprinc phoenix_client@EMR.C-4FC5FDDE3759****.COM
```
  When prompted, set and remember a password for the principal. Although the keytab file enables password-free logon, this password might still be required in some cases.
Export the keytab file.
1. In the kadmin.local tool, run the following command to export the keytab file.
```
xst -k /tmp/phoenix_client.keytab phoenix_client@EMR.C-4FC5FDDE3759****.COM
```
2. Run the following command to exit kadmin.local.
```
exit
```
  Important
  - Permissions: Ensure that the user running the Java program has read permissions on the keytab file.
  - Distribution: If your Java program runs on a different machine, securely copy the phoenix_client.keytab file and the /etc/krb5.conf file to it, and ensure they are in a path accessible to the application.

Step 2: Write and package the application

Method 1: Use a precompiled JAR file (quick verification)

hbase-phoenix-kerberos-1.0-SNAPSHOT.jar

Method 2: Compile and package manually (recommended for production)

You can use the following code example for manual compilation and packaging.

Core code example (PhoenixKerberosDemo.java)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * A client that connects to a Kerberos-secured Phoenix cluster using JDBC.
 * All connection parameters are provided through a complete JDBC URL passed from the command line.
 */
public class PhoenixKerberosDemo {
    /**
     * The main entry point for the application.
     *
     * @param args Command-line arguments. The program expects one argument: the complete Phoenix JDBC URL.
     */
    public static void main(String[] args) {
        // --- 1. Validate command-line input: Expect one argument, the JDBC URL ---
        if (args.length != 1) {
            System.err.println("ERROR: Invalid number of arguments.");
            System.err.println("Usage: java PhoenixKerberosDemo \"<full_jdbc_url>\"");
            System.err.println("Example: \"jdbc:phoenix:zk1,zk2:2181:/hbase:user@REALM.COM:/path/to/user.keytab\"");
            System.exit(1); // Exit with an error code
        }
        String jdbcUrl = args[0];
        System.out.println("Attempting to connect to Phoenix...");
        System.out.println("Using JDBC URL: " + jdbcUrl);
        try {
            // --- 2. Load the Phoenix driver ---
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
        } catch (ClassNotFoundException e) {
            System.err.println("FATAL ERROR: Phoenix JDBC driver not found in the classpath.");
            e.printStackTrace();
            System.exit(1);
        }
        // --- 3. Use a try-with-resources statement to establish a connection and execute SQL. This syntax automatically closes resources. ---
        try (Connection con = DriverManager.getConnection(jdbcUrl);
             Statement stmt = con.createStatement()) {
            System.out.println("Connection established successfully.");
            final String tableName = "TEST";
            System.out.println("Creating table '" + tableName + "'...");
            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " (mykey INTEGER NOT NULL PRIMARY KEY, mycolumn VARCHAR)");
            con.commit();
            System.out.println("Upserting data...");
            stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES (1, 'Hello')");
            stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES (2, 'World-Kerberos!')");
            con.commit();
            System.out.println("Data upserted successfully.");
            String sql = "SELECT * FROM " + tableName;
            System.out.println("Querying for results with: " + sql);
            try (PreparedStatement statement = con.prepareStatement(sql);
                 ResultSet rset = statement.executeQuery()) {
                System.out.println("Query results:");
                while (rset.next()) {
                    System.out.println(rset.getInt("mykey") + " -> " + rset.getString("mycolumn"));
                }
            }
            System.out.println("Cleaning up the test table...");
            stmt.executeUpdate("DROP TABLE IF EXISTS " + tableName);
            con.commit();
        } catch (SQLException e) {
            // Catch SQL exceptions and provide helpful troubleshooting tips
            System.err.println("\n--- FAILED TO EXECUTE DATABASE OPERATION ---");
            System.err.println("Please check the following:");
            System.err.println("1. The JDBC URL is correct (format, principal, keytab path).");
            System.err.println("2. Network connectivity to ZooKeeper and HBase.");
            System.err.println("3. The keytab file exists and has correct read permissions.");
            System.err.println("4. The principal has sufficient permissions on HBase tables and namespaces.");
            e.printStackTrace();
        }
        System.out.println("\nExecution finished.");
    }
}

Maven configuration (pom.xml)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                             http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.aliyun.emr.doctor</groupId>
    <artifactId>hbase-phoenix-kerberos</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>Archetype - hbase-phoenix-kerberos</name>
    <url>http://maven.apache.org</url>
    <properties>
        <phoenix.version>5.2.1</phoenix.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>${phoenix.version}</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <!--  Java Compiler  -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.2.4</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <filters>
                                <filter>
                                    <artifact>*:*</artifact>
                                    <excludes>
                                        <exclude>META-INF/*.SF</exclude>
                                        <exclude>META-INF/*.DSA</exclude>
                                        <exclude>META-INF/*.RSA</exclude>
                                    </excludes>
                                </filter>
                            </filters>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

Step 3: Run the application

Choose a runtime environment.

Choose one of the following two runtime environments:
- Run on a cluster node (recommended):
  - Cluster nodes have the required Hadoop, HBase, and Phoenix dependency libraries preinstalled, so no additional configuration is needed. Network connectivity is also available by default, providing a complete and stable environment.
  - Best for: Quick verification and testing during development and debugging.
- Run outside the cluster
  
  To run the program outside the cluster, ensure the following conditions are met:
  - Network connectivity: Ensure network connectivity between the machine running the program and the cluster's ZooKeeper, HBase Master, and RegionServer nodes.
  - Kerberos configuration: Copy the cluster's krb5.conf file and the generated keytab file to the machine where the program runs.
  - Dependency management: The classpath for the run command must include all required Hadoop, HBase, and Phoenix client dependency JAR files. This is typically more complex than running on a cluster node. We recommend using a tool such as Maven or Gradle for dependency management.

Run the script.

The following kerberos-phoenix.sh script includes all required settings. You can edit and run it as needed.

#!/bin/bash
# ======================= 1. User configuration (modify based on your environment) =======================
# Directory for Hadoop and HBase configuration files
HADOOP_CONF_DIR="/etc/taihao-apps/hadoop-conf"
HBASE_CONF_DIR="/etc/taihao-apps/hbase-conf"
# Path to the Phoenix client JAR file. Using a symbolic link is a best practice to stay resilient to version changes.
# First, confirm this file exists by using `ls -l /opt/apps/PHOENIX/phoenix-current/`. This path may need to be modified for different versions.
PHOENIX_JAR="/opt/apps/PHOENIX/phoenix-current/phoenix-client-lite-hbase-2.6.jar"
# Your application's JAR file name.
YOUR_JAR_FILE="hbase-phoenix-kerberos-1.0-SNAPSHOT.jar"
# Path to the Kerberos configuration file.
KRB5_CONF_PATH="/etc/krb5.conf"
# --- [Core] JDBC URL configuration ---
# Format: jdbc:phoenix:[ZK Address]:[ZK Port]:[HBase ZNode]:[Principal]:[Absolute Keytab Path]
# Replace the ZK address, REALM, and keytab path below with your actual information.
ZK_QUORUM="master-1-1" # If there are multiple ZooKeeper nodes, separate them with commas, for example, "zk1,zk2,zk3"
ZK_PORT="2181"
HBASE_ZNODE="/hbase" # For a secure cluster, it might be /hbase-secure
PRINCIPAL="phoenix_client@EMR.C-4FC5FDDE3759****.COM" # Replace with your principal
KEYTAB_PATH="/tmp/phoenix_client.keytab" # Absolute path to the keytab file
JDBC_URL="jdbc:phoenix:${ZK_QUORUM}:${ZK_PORT}:${HBASE_ZNODE}:${PRINCIPAL}:${KEYTAB_PATH}"
# =================================================================================
# ======================= 2. Execution (usually no changes needed) =================================
echo "================================================="
echo "Starting Phoenix Kerberos JDBC Demo..."
echo "Using JDBC URL: ${JDBC_URL}"
echo "================================================="
# Build the classpath. Order: current directory -> configuration directories -> your JAR -> dependency JARs
# `hbase classpath` automatically loads core Hadoop/HBase dependencies
CLASS_PATH=".:${HADOOP_CONF_DIR}:${HBASE_CONF_DIR}:${YOUR_JAR_FILE}:${PHOENIX_JAR}:$(hbase classpath)"
# Execute the Java program
java -cp "${CLASS_PATH}" \
     -Djava.security.krb5.conf="${KRB5_CONF_PATH}" \
     PhoenixKerberosDemo "${JDBC_URL}"
# Check the exit code
if [ $? -eq 0 ]; then
    echo -e "\n[SUCCESS] Program finished successfully."
else
    echo -e "\n[FAILED] Program terminated with an error."
fi
# =================================================================================

Upload the JAR file packaged in Step 2 and the kerberos-phoenix.sh script to a target directory on the master node.
Run the following command to grant execute permissions to the script.
```
chmod +x kerberos-phoenix.sh
```

Run the following command to run the script.

./kerberos-phoenix.sh

The following is sample output:

2025-08-13 13:22:30,825 INFO query.GuidePostsCacheProvider: Sucessfully loaded class for GuidePostsCacheFactor of type: org.apache.phoenix.query.DefaultGuidePostsCacheFactory
2025-08-13 13:22:30,939 INFO connectionqueryservice.ConnectionQueryServicesMetricsManager: Created object for NoOp Connection query service metrics manager
Connection established successfully.
Creating table 'TEST'...
2025-08-13 13:22:33,433 INFO client.HBaseAdmin: Operation: CREATE, Table Name: default:TEST, procId: 144 completed
Upserting data...
Data upserted successfully.
Querying for results with: SELECT * FROM TEST
Query results:
1 -> Hello
2 -> World-Kerberos!
Cleaning up the test table...
2025-08-13 13:22:33,597 INFO client.HBaseAdmin: Started disable of TEST
2025-08-13 13:22:34,209 INFO client.HBaseAdmin: Operation: DISABLE, Table Name: default:TEST, procId: 147 completed
2025-08-13 13:22:34,521 INFO client.HBaseAdmin: Operation: DELETE, Table Name: default:TEST, procId: 151 completed
Execution finished.
2025-08-13 13:22:34,559 INFO log.QueryLoggerDisruptor: Shutting down QueryLoggerDisruptor..
2025-08-13 13:22:34,559 INFO client.ConnectionImplementation: Closing master protocol: MasterService
2025-08-13 13:22:34,563 INFO hbase.ChoreService: Chore service for: AsyncConn Chore Service had [ScheduledChore name=RefreshCredentials, period=30000, unit=MILLISECONDS] on shutdown
2025-08-13 13:22:34,563 INFO query.ConnectionQueryServicesImpl: hconnection-0x1fdf1c5 HConnection closed. Stacktrace for informational purposes: java.lang.Thread.getStackTrace(Thread.java:1564)
org.apache.phoenix.util.LogUtil.getCallerStackTrace(LogUtil.java:55)
org.apache.phoenix.query.ConnectionQueryServicesImpl.closeConnection(ConnectionQueryServicesImpl.java:537)
org.apache.phoenix.query.ConnectionQueryServicesImpl.close(ConnectionQueryServicesImpl.java:649)
org.apache.phoenix.jdbc.PhoenixDriver.close(PhoenixDriver.java:349)
...
org.apache.phoenix.jdbc.PhoenixDriver.closeInstance(PhoenixDriver.java:138)
org.apache.phoenix.jdbc.PhoenixDriver.access$000(PhoenixDriver.java:68)
org.apache.phoenix.jdbc.PhoenixDriver$1$1.run(PhoenixDriver.java:94)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
java.util.concurrent.FutureTask.run(FutureTask.java:266)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:750)
[SUCCESS] Program finished successfully.

References

For more information about Phoenix, see the official documentation: