Connect to Phoenix Query Server through Apache Knox

You can connect to Phoenix Query Server (PQS) using the JDBC thin client through the Apache Knox gateway. Apache Knox requires your thin client connection to be over HTTPS.

Ensure that you have access to Apache Phoenix and Apache HBase, and you have the required permissions configured in Ranger to connect to PQS.

  • Get the PQS Knox endpoint to connect to PQS from the CDP Data Hub user interface. Go to Data Hub cluster > Endpoints > Cloudera Manager Info > Endpoints > Phoenix Query Server.
    PQS URL
  • Use the JDBC URL in the following sytax to connect to PQS through the Apache Knox gateway:

    jdbc:phoenix:thin:url=https://[***KNOX ENDPOINT***]:[***PORT***]/[***CLUSTER NAME***]/cdp-proxy-api/avatica/;serialization=PROTOBUF;authentication=BASIC;avatica_user=[***WORKLOAD USERNAME*];avatica_password=[***WORKLOAD PASSWORD***];truststore=[***PATH TO THE KNOX TRUSTSTORE .jks FILE***];truststore_password=[***TRUSTSTORE PASSWORD***]

    The standard Oracle Java JDK distribution includes a default truststore (cacerts) that contains root certificates for many well-known CAs, including Symantec. Rather than using the default truststore, Cloudera recommends using the alternative truststore, jssecacerts. The alternative truststore is created by copying cacerts to that filename (jssecacerts). Certificates can be added to this truststore when needed for additional roles or services. This alternative truststore is loaded by Hadoop daemons at startup. Password (if there is one for the truststore) stored in a plaintext file readable by all (OS filesystem permissions set to 0440). For more information about truststores, see Understanding Keystores and Truststores.

jdbc:phoenix:thin:url=https://[***https://pqs.knox.endpoint:8443/gateway/cdp-proxy-api/avatica/***]:[***8765***]
/[***clusteropdb***]/cdp-proxy-api/avatica/;serialization=PROTOBUF;
authentication=BASIC;avatica_user=[***WORKLOADUSERNAME***]
;avatica_password=[***WORKLOADPASSWORD***]
;truststore=[***/home/path/truststore.jks***];truststore_password=[***TRUSTSTOREPASSWORD***]