An example for explaining how to connect and run CRUD queries against Phoenix in a Kerberized environment.
Phoenix is a SQL engine on the top of Apache HBase.
Phoenix supports thick and thin connection types:
- Thick client is faster, but must connect directly to ZooKeeper and HBase RegionServers.
- Thin client has fewer dependencies and connects through a Phoenix Query Server instance.
Prerequisites
- OS: Linux (RHEL 7.9)
- Hadoop: Cloudera (CDP 7.1.7 SP1)
- Authentication via Kerberos
- OpenJDK 64-Bit 1.8.0_292
- Python 2.7.5
Phoenix – sqlline (Test Phoenix JDBC SQL engine)
*** Caused by: org.apache.hadoop.hbase.TableNotFoundException: SYSTEM.CATALOG ***
Ranger Policy
- A User needs Read/Write/Create privileges on SYSTEM.CATALOG to create table in Phoenix.
- To create a Phoenix table, you MUST have the ability to create the HBase table and you MUST have the ability to write to the SYSTEM.CATALOG table.
$ su <user>
$ kinit -kt /etc/security/keytabs/<user>.keytab <user>
$ cd /opt/cloudera/parcels/CDH/lib/phoenix/bin
$ python sqlline.py -fc FASTCONNECT
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect -p driver org.apache.phoenix.jdbc.PhoenixDriver -p user "none" -p password "none" "jdbc:phoenix:"
Connecting to jdbc:phoenix:
23/05/20 11:00:30 WARN impl.MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-phoenix.properties,hadoop-metrics2.properties
Connected to: Phoenix (version 5.1)
Driver: PhoenixEmbeddedDriver (version 5.1)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
sqlline version 1.9.0
0: jdbc:phoenix:> select * from SYSTEM.CATALOG;
+-----------+-------------+-----------------+------------------------------------+---------------+---------------+------------+---------+--------------+--------------+-----------------+-------------+----------------+----------------+---+
| TENANT_ID | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | COLUMN_FAMILY | TABLE_SEQ_NUM | TABLE_TYPE | PK_NAME | COLUMN_COUNT | SALT_BUCKETS | DATA_TABLE_NAME | INDEX_STATE | IMMUTABLE_ROWS | VIEW_STATEMENT | D |
+-----------+-------------+-----------------+------------------------------------+---------------+---------------+------------+---------+--------------+--------------+-----------------+-------------+----------------+----------------+---+
| | | CUSTOMER | | | 0 | u | | 3 | null | | | false | | |
| | | CUSTOMER | | 0 | null | | | null | null | | | | | |
| | | CUSTOMER | ADDRESS | 0 | null | | | null | null | | | | | |
| | | CUSTOMER | ID | | null | | | null | null | | | | | |
| | | CUSTOMER | NAME | 0 | null | | | null | null | | | | | |
| | | ITEM | | | 0 | u | | 3 | null | | | false | | |
| | | ITEM | | 0 | null | | | null | null | | | | | |
| | | ITEM | ID | | null | | | null | null | | | | | |
| | | ITEM | NAME | 0 | null | | | null | null | | | | | |
| | | ITEM | QUANTITY | 0 | null | | | null | null | | | | | |
| | | ORDER | | | 0 | u | | 3 | null | | | false | | |
| | | ORDER | | 0 | null | | | null | null | | | | | |
| | | ORDER | CREATION_TIME | 0 | null | | | null | null | | | | | |
| | | ORDER | CUSTOMER_ID | 0 | null | | | null | null | | | | | |
| | | ORDER | ID | | null | | | null | null | | | | | |
| | | ORDER_LINE_ITEM | | | 0 | u | PK | 3 | null | | | false | | |
| | | ORDER_LINE_ITEM | | 0 | null | | | null | null | | | | | |
| | | ORDER_LINE_ITEM | ITEM_ID | | null | | PK | null | null | | | | | |
| | | ORDER_LINE_ITEM | ORDER_ID | | null | | PK | null | null | | | | | |
| | | ORDER_LINE_ITEM | SALE_QUANTITY | 0 | null | | PK | null | null | | | | | |
| | SYSTEM | CATALOG | | | 0 | s | PK | 68 | null | | | false | | |
...
0: jdbc:phoenix:> !tables
+-----------+-------------+-----------------+--------------+---------+-----------+---------------------------+----------------+------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STAT |
+-----------+-------------+-----------------+--------------+---------+-----------+---------------------------+----------------+------------+
| | SYSTEM | CATALOG | SYSTEM TABLE | | | | | |
| | SYSTEM | CHILD_LINK | SYSTEM TABLE | | | | | |
| | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | |
| | SYSTEM | LOG | SYSTEM TABLE | | | | | |
| | SYSTEM | MUTEX | SYSTEM TABLE | | | | | |
| | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | |
| | SYSTEM | STATS | SYSTEM TABLE | | | | | |
| | SYSTEM | TASK | SYSTEM TABLE | | | | | |
| | | CUSTOMER | TABLE | | | | | |
| | | ITEM | TABLE | | | | | |
| | | ORDER | TABLE | | | | | |
| | | ORDER_LINE_ITEM | TABLE | | | | | |
+-----------+-------------+-----------------+--------------+---------+-----------+---------------------------+----------------+------------+
0: jdbc:phoenix:> !quit
Closing: org.apache.phoenix.jdbc.PhoenixConnection
Phoenix – JDBC Thin Client – CRUD
ClientConnect,java (path: /phoenix-crud/src/main/java/eu/placko/examples/hbase/phoenix)
package eu.placko.examples.hbase.phoenix;
import java.sql.Connection;
import java.sql.DriverManager;
public class ClientConnect {
public static void main(String[] args) throws Exception {
if (args.length < 1) {
throw new IllegalArgumentException("Usage: eu.placko.examples.hbase.phoenix.ClientConnect \"jdbc:phoenix:thin:url=https://<pqs.endpoint>:8765;serialization=PROTOBUF;authentication=SPNEGO;principal=<user@realm>;keytab=/etc/security/keytabs/<user>.keytab\"");
}
connect(args[0]);
}
private static void connect(String jdbcUrl) throws Exception {
Class.forName("org.apache.phoenix.queryserver.client.Driver");
try (Connection conn = DriverManager.getConnection(jdbcUrl)){
System.out.println("Connection created");
ClientOperations cl = new ClientOperations();
cl.run(conn);
}
}
}
ClientOperations,java (path: /phoenix-crud/src/main/java/eu/placko/examples/hbase/phoenix)
package eu.placko.examples.hbase.phoenix;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
public class ClientOperations {
private void createTables(Connection conn) throws SQLException {
dropTablesIfExists(conn);
System.out.println("Creating tables");
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE IF NOT EXISTS ITEM " +
" (id varchar not null primary key, name varchar, quantity integer)");
stmt.execute("CREATE TABLE IF NOT EXISTS CUSTOMER " +
" (id varchar not null primary key, name varchar, address varchar)");
stmt.execute("CREATE TABLE IF NOT EXISTS \"ORDER\" " +
" (id varchar not null primary key, customer_id varchar, creation_time varchar)");
stmt.execute("CREATE TABLE IF NOT EXISTS ORDER_LINE_ITEM " +
" (order_id varchar not null, item_id varchar not null, sale_quantity integer, "
+ " constraint pk primary key(order_id, item_id))");
}
private void dropTablesIfExists(Connection conn) throws SQLException {
System.out.println("Dropping tables");
Statement stmt = conn.createStatement();
stmt.execute("DROP TABLE IF EXISTS ITEM");
stmt.execute("DROP TABLE IF EXISTS CUSTOMER");
stmt.execute("DROP TABLE IF EXISTS \"ORDER\"");
stmt.execute("DROP TABLE IF EXISTS ORDER_LINE_ITEM");
}
private void populateData(Connection conn) throws SQLException {
System.out.println("Populating tables");
populateItemData(conn);
populateCustomerData(conn);
}
private void populateItemData(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
stmt.execute("UPSERT INTO ITEM VALUES('ITM001','Book', 5)");
stmt.execute("UPSERT INTO ITEM VALUES('ITM002','Pen', 5)");
stmt.execute("UPSERT INTO ITEM VALUES('ITM003','Soap', 5)");
stmt.execute("UPSERT INTO ITEM VALUES('ITM004','Shampoo', 5)");
stmt.execute("UPSERT INTO ITEM VALUES('ITM005','Phone', 5)");
stmt.execute("UPSERT INTO ITEM VALUES('ITM006','Charger', 5)");
conn.commit();
}
private void populateCustomerData(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
stmt.execute("UPSERT INTO CUSTOMER VALUES('CU001','John', 'foo')");
stmt.execute("UPSERT INTO CUSTOMER VALUES('CU002','Angel', 'faa')");
stmt.execute("UPSERT INTO CUSTOMER VALUES('CU003','David', 'soo')");
stmt.execute("UPSERT INTO CUSTOMER VALUES('CU004','Robert', 'laa')");
stmt.execute("UPSERT INTO CUSTOMER VALUES('CU005','James', 'naa')");
conn.commit();
}
/**
*
* @param conn: connection used for performing operation
* @param orderId: Order ID of the Creating Order
* @param customerId: Customer ID of the customer made an order
* @param itemVsQuantity: Items selected with quantities for order
* @throws SQLException
*/
private void createOrder(Connection conn, String orderId, String customerId,
Map<String, Integer> itemVsQuantity) throws SQLException {
Statement stmt = conn.createStatement();
stmt.execute("UPSERT INTO \"ORDER\" VALUES('" + orderId + "','" + customerId + "',"
+ " CURRENT_DATE()||' '|| CURRENT_TIME())");
for(Entry<String, Integer> item: itemVsQuantity.entrySet()) {
String itemID = item.getKey();
int saleQuantity = item.getValue();
stmt.execute("UPSERT INTO ORDER_LINE_ITEM VALUES('"+ orderId+"','" +itemID+"',1)");
stmt.execute("UPSERT INTO ITEM(ID, QUANTITY)"
+ " SELECT '"+itemID+"', QUANTITY - " + saleQuantity + " FROM ITEM "
+ " WHERE ID = '" + itemID + "'");
}
}
public void run(Connection conn) throws SQLException {
conn.setAutoCommit(false);
createTables(conn);
populateData(conn);
System.out.println("*** CREATING ORDER***");
Map<String, Integer> orderItems = new HashMap<>();
orderItems.put("ITM001", 2);
orderItems.put("ITM002", 3);
orderItems.put("ITM003", 2);
createOrder(conn, "OR001", "CU001", orderItems);
try {
conn.commit();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
System.out.println("Found " + countRows("\"ORDER\"", conn) + " record(s) in table ORDER");
System.out.println("*** RESULTS ***");
showTables("ITEM", conn);
showTables("CUSTOMER", conn);
showTables("\"ORDER\"", conn);
showTables("ORDER_LINE_ITEM", conn);
conn.close();
}
private void showTables(String tableName, Connection conn) throws SQLException {
System.out.println("SELECT * FROM " + tableName);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
while (rs.next()) {
System.out.println(rs.getString(1) + " | " + rs.getString(2) + " | " + rs.getString(3));
}
}
private int countRows(String tableName, Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
try (ResultSet results = stmt.executeQuery("SELECT COUNT(1) FROM " + tableName)) {
if (!results.next()) {
throw new RuntimeException("Query should have result!");
}
return results.getInt(1);
}
}
}
pom.xml (path: /phoenix-crud/)
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>eu.placko.examples.hbase.phoenix</groupId>
<artifactId>phoenix-crud</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>phoenix-crud</name>
<description>An example for explaining how to work with HBase/Phoenix JDBC Thin Client – CRUD</description>
<packaging>jar</packaging>
<properties>
<revision>Local-SNAPSHOT</revision>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<jar.main.class>eu.placko.examples.hbase.phoenix.ClientConnect</jar.main.class>
</properties>
<!-- Phoenix -->
<dependencies>
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-queryserver-client</artifactId>
<version>6.0.0.7.1.7.67-1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<configuration>
<archive>
<manifest>
<mainClass>eu.placko.examples.hbase.phoenix.ClientConnect</mainClass>
</manifest>
</archive>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
</plugin>
</plugins>
<pluginManagement />
</build>
</project>
README.md (path: /phoenix-crud/)
HOW TO CONFIGURE THE PROJECT
Building and Running
Build
To build the application it is required to have this installed:
Java 9
Maven 3.x
Then just run this:
mvn clean install assembly:single
Run
$ su <user>
$ cd /home/<user>
$ chmod 770 ./hbase/phoenix-crud-0.0.1-SNAPSHOT-jar-with-dependencies.jar
$ chown <user>:<user> ./hbase/phoenix-crud-0.0.1-SNAPSHOT-jar-with-dependencies.jar
$ kinit -kt /etc/security/keytabs/<user>.keytab <user>
$ java -jar ./hbase/phoenix-crud-0.0.1-SNAPSHOT-jar-with-dependencies.jar "jdbc:phoenix:thin:url=https://<pqs.endpoint>:8765;serialization=PROTOBUF;authentication=SPNEGO;principal=<user>@<realm>;keytab=/etc/security/keytabs/<user>.keytab"
Result
Connection created
Dropping tables
Creating tables
Populating tables
*** CREATING ORDER***
Found 1 record(s) in table ORDER
*** RESULTS ***
SELECT * FROM ITEM
ITM001 | Book | 3
ITM002 | Pen | 2
ITM003 | Soap | 3
ITM004 | Shampoo | 5
ITM005 | Phone | 5
ITM006 | Charger | 5
SELECT * FROM CUSTOMER
CU001 | John | foo
CU002 | Angel | faa
CU003 | David | soo
CU004 | Robert | laa
CU005 | James | naa
SELECT * FROM "ORDER"
OR001 | CU001 | 2023-05-20 14:45:44
SELECT * FROM ORDER_LINE_ITEM
OR001 | ITM001 | 1
OR001 | ITM002 | 1
OR001 | ITM003 | 1
Source Code
https://github.com/mplacko/phoenix-crud
Additional Info
- https://docs.cloudera.com/runtime/7.2.2/phoenix-access-data/phoenix-access-data.pdf
- https://docs.cloudera.com/cdp-private-cloud-base/7.1.3/phoenix-access-data/topics/phoenix-orchestrating-sql.html
- https://docs.cloudera.com/cdp-private-cloud-base/7.1.3/phoenix-access-data/topics/phoenix-connect-pqs.html
- https://docs.cloudera.com/cdp-private-cloud-base/7.1.3/phoenix-access-data/topics/phoenix-understanding-spark-connector.html
- https://docs.cloudera.com/cdp-private-cloud-base/7.1.3/phoenix-access-data/topics/phoenix-understanding-hive-connector.html
- https://zeppelin.apache.org/docs/0.7.1/interpreter/jdbc.html#thick-client-connection
- https://zeppelin.apache.org/docs/0.7.1/interpreter/jdbc.html#thin-client-connection
- https://mvnrepository.com/artifact/org.apache.phoenix/phoenix-queryserver-client
- https://calcite.apache.org/avatica/docs/client_reference.html
- https://github.com/cloudera/cod-examples/tree/main/phoenix-read-write
- https://github.com/stanislawbartkowski/ConnectPhoenix
- https://github.com/joshelser/phoenix-queryserver-jdbc-client
Placko's database KB 