How to Connect to Apache Phoenix Shell from Terminal

Apache Phoenix allows you to run SQL queries on top of HBase tables. We can use JDBC or SQLLINE command line utility to access phoenix

In this article, We are going to access phoenix using the SQLLINE command line utility

There are 2 ways to connect the phoenix shell

• SQLLINE using Fast Connect
• SQLLINE THIN Client

SQLLINE using Fast Connect

This utility can help us to connect to the phoenix query server with minimal arguments, but we need to run this command from the server, Where the phoenix query server is running

ssh to phoenix query server

export JAVA_HOME=

/opt/cloudera/parcels/CDH/lib/phoenix/bin/sqlline.py -fc FASTCONNECT

EXAMPLE:

]# export JAVA_HOME=/usr/java/jdk1.8.0_232-cloudera

~]# /opt/cloudera/parcels/CDH/lib/phoenix/bin/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:
22/12/05 13:49:50 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:> show tables;
+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+----------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME |  TABLE_TYPE  | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TE |
+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+----------+
|           | SYSTEM      | CATALOG    | SYSTEM TABLE |         |           |                           |                |             | false          | null         | false    |
|           | SYSTEM      | CHILD_LINK | SYSTEM TABLE |         |           |                           |                |             | false          | null         | false    |
|           | SYSTEM      | FUNCTION   | SYSTEM TABLE |         |           |                           |                |             | false          | null         | false    |
|           | SYSTEM      | LOG        | SYSTEM TABLE |         |           |                           |                |             | true           | 32           | false    |
|           | SYSTEM      | MUTEX      | SYSTEM TABLE |         |           |                           |                |             | true           | null         | false    |
|           | SYSTEM      | SEQUENCE   | SYSTEM TABLE |         |           |                           |                |             | false          | null         | false    |
|           | SYSTEM      | STATS      | SYSTEM TABLE |         |           |                           |                |             | false          | null         | false    |
|           | SYSTEM      | TASK       | SYSTEM TABLE |         |           |                           |                |             | false          | null         | false    |
+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+----------------+--------------+----------+
8 rows selected (0.067 seconds)
0: jdbc:phoenix:> 

SQLLINE THIN client

the sqlline-thin client uses a JDBC driver with minimal dependency, Which helps to connect the Phoenix query server from a terminal

Example:

The shown example is from the Cloudera distribution, just have to replace the patch with sqlline-thin.py path

export PHOENIX_OPTS="-Dsun.security.krb5.principal="
export JAVA_HOME=/usr/java/jdk1.8.0_232-cloudera
/opt/cloudera/parcels/CDH/lib/phoenix/bin/sqlline-thin.py http://:8765

Good Luck with your Learning !!!