How to access AMS embedded Hbase in a secured environment (Kerberized)

[#1]

This is an article which describes how to access AMS embedded Hbase in a secured kerberized environment. This is extension of this document

For a unsecured cluster, please read the above document however in case of secured environment you may need run one extra export command. The final command will be similar to below syntax

<sqline.py path> <ams_collector_hostname>:<embedded_zk_port>:<znode>

/usr/hdp/2.5.0.0-1245/phoenix/bin/sqlline.py rchaman.ambari.apache.org:61181:/ams-hbase-unsecure

Please find the below steps to get the parameters as per your environment.

1. We need to make sure that we have the phoenix client packages installed on the host from where you are running command. however, I suggest you to run the command from AMS collector host.

If it is not installed then we can install it as following:

yum install phoenix -y

2. Search for sqlline.py script absolute path

[root@c4140-node4 ~]# rpm -qa |grep phoenix
phoenix_2_6_4_0_91-4.7.0.2.6.4.0-91.noarch
[root@c4140-node4 ~]# rpm -ql phoenix_2_6_4_0_91-4.7.0.2.6.4.0-91.noarch|grep sqlline.py
/usr/hdp/2.6.4.0-91/phoenix/bin/sqlline.py

3. Now get the value of "zookeeper.znode.parent" from the Ambari Metrics Collector configurations. In ambari we can get the value from : "Ambari Metrics" -> "config" -> "Advanced ams-hbase-site", Then search for "zookeeper.znode.parent". Normally the value in secured cluster will be "/ams-hbase-secure"

4. Use embedded zookeeper port as 61181 and here, ams_collector_hostname is the hostname on which AMS collector is running in embedded mode .

5. Finally, once we have this information we need to run export command which instructs phoenix to read AMS hbase configuration rather default hbase configuration.

export HBASE_CONF_DIR=/etc/ambari-metrics-collector/conf

6. Now run the command

[ams@c4140-node4 ~]$ /usr/hdp/2.6.4.0-91/phoenix/bin/sqlline.py localhost:61181:/ams-hbase-secure
Setting property: [incremental, false] Connected to: Phoenix (version 4.7)
Driver: PhoenixEmbeddedDriver (version 4.7)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
195/195 (100%) Done
Done
sqlline version 1.1.8
0: jdbc:phoenix:localhost:61181:/ams-hbase-se> !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_TENANT  | VIEW_STATEMENT  | VIEW_TYPE  | INDEX_TYPE  | TRANSACTIONAL  | IS_NAME |
+------------+--------------+--------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+---------+
|            | SYSTEM       | CATALOG                  | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |             | false          | false   |
|            | SYSTEM       | FUNCTION                 | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |             | false          | false   |
|            | SYSTEM       | SEQUENCE                 | SYSTEM TABLE  |          |            |                            |                 |              | false           | 2             | false         |                 |            |             | false          | false   |
|            | SYSTEM       | STATS                    | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |             | false          | false   |
|            |              | CONTAINER_METRICS        | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | HOSTED_APPS_METADATA     | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |            |             | false          | false   |
|            |              | INSTANCE_HOST_METADATA   | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |            |             | false          | false   |
|            |              | METRICS_METADATA         | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_AGGREGATE         | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_AGGREGATE_DAILY   | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_AGGREGATE_HOURLY  | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_AGGREGATE_MINUTE  | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_RECORD            | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_RECORD_DAILY      | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_RECORD_HOURLY     | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
|            |              | METRIC_RECORD_MINUTE     | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |            |             | false          | false   |
+------------+--------------+--------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+---------+
0: jdbc:phoenix:localhost:61181:/ams-hbase-se> 

Note: In case we do not export AMS hbase config, phoenix will read default hbase config and show below error

[ams@c4140-node4 ~]$ /usr/hdp/2.6.4.0-91/phoenix/bin/sqlline.py localhost:61181:/ams-hbase-secure
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:localhost:61181:/ams-hbase-secure none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:localhost:61181:/ams-hbase-secure
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.4.0-91/phoenix/phoenix-4.7.0.2.6.4.0-91-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.4.0-91/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
18/10/18 22:37:52 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
18/10/18 22:37:53 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded.
18/10/18 22:37:54 WARN ipc.AbstractRpcClient: Exception encountered while connecting to the server : javax.security.sasl.SaslException: GSS initiate failed [Caused by GSSException: No valid credentials provided (Mechanism level: Failed to find any Kerberos tgt)]
18/10/18 22:37:54 FATAL ipc.AbstractRpcClient: SASL authentication failed. The most likely cause is missing or invalid credentials. Consider 'kinit'.
javax.security.sasl.SaslException: GSS initiate failed [Caused by GSSException: No valid credentials provided (Mechanism level: Failed to find any Kerberos tgt)]