Using Phoenix sqlline utility to browse Ambari Metrics Collector Data.

[#1]

- We will need to make sure that we have the phoenix client packages installed in the host where we want to run the sqlline utility. If it is not installed then we can install it as following:

 yum install phoenix -y

- 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 will be "/ams-hbase-unsecure" in the AMS configs.

- Now we will need to use the "sqlline" utility to connect to it. Here "c6403.ambari.apache.org" is the AMS hostname running in embedded mode and the embedded zookeeper port is 61181

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

.

NOTE: For Kerberized Cluster use the value of "zookeeper.znode.parent" may be "/ams-hbase-secure" so we can connect to it as following:

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

Example:

# /usr/hdp/2.5.0.0-1245/phoenix/bin/sqlline.py c6403.ambari.apache.org:61181:/ams-hbase-unsecure
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:c6403.ambari.apache.org:61181:/ams-hbase-unsecure none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:c6403.ambari.apache.org:61181:/ams-hbase-unsecure
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.5.0.0-1245/phoenix/phoenix-4.7.0.2.5.0.0-1245-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.5.0.0-1245/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.
16/12/13 02:18:17 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
16/12/13 02:18:19 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded.
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)...
381/381 (100%) Done
Done
sqlline version 1.1.8
0: jdbc:phoenix:c6403.ambari.apache.org:61181> 

- Once we are able to connect as above then we should be able to run various queries as following:

Listing tables

0: jdbc:phoenix:c6403.ambari.apache.org:61181> !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  | V |
+------------+--------------+--------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+---+
|            | SYSTEM       | CATALOG                  | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | SYSTEM       | FUNCTION                 | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | SYSTEM       | SEQUENCE                 | SYSTEM TABLE  |          |            |                            |                 |              | false           | 2             | false         |                 |   |
|            | SYSTEM       | STATS                    | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            |              | CONTAINER_METRICS        | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | HOSTED_APPS_METADATA     | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            |              | METRICS_METADATA         | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            |              | METRIC_AGGREGATE         | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_AGGREGATE_DAILY   | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_AGGREGATE_HOURLY  | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_AGGREGATE_MINUTE  | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_RECORD            | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_RECORD_DAILY      | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_RECORD_HOURLY     | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            |              | METRIC_RECORD_MINUTE     | TABLE         |          |            |                            |                 |              | true            | null          | false         |                 |   |
|            | ACTIVITY     | ACTIVITY_QUEUE           | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | CONFIG                   | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | COUNTER                  | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | FAILED_ACTIVITY          | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | HDFS_FILES               | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | HDFS_USER_FILE_SUMMARY   | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | JOB                      | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | RECOMMENDATION           | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | SCHEMA_INFO              | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | TASK                     | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
|            | ACTIVITY     | YARN_APPLICATION         | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |   |
+------------+--------------+--------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+---+
0: jdbc:phoenix:c6403.ambari.apache.org:61181> 

.

- Similarly now we can run the following select queries to see the various "METRIC_AGGREGATE" data.

0: jdbc:phoenix:c6403.ambari.apache.org:61181> select * from METRIC_AGGREGATE where METRIC_NAME='regionserver.Server.storeFileCount' OR METRIC_NAME= 'master.Server.numRegionServers' OR METRIC_NAME='master.Server.averageLoad' order by SERVER_TIME limit 3; 
+-------------------------------------+------------+--------------+----------------+--------+-------------+--------------+-------------+-------------+
|             METRIC_NAME             |   APP_ID   | INSTANCE_ID  |  SERVER_TIME   | UNITS  | METRIC_SUM  | HOSTS_COUNT  | METRIC_MAX  | METRIC_MIN  |
+-------------------------------------+------------+--------------+----------------+--------+-------------+--------------+-------------+-------------+
| master.Server.averageLoad           | ams-hbase  |              | 1481454420000  |        | 31.0        | 1            | 31.0        | 31.0        |
| regionserver.Server.storeFileCount  | ams-hbase  |              | 1481454420000  |        | 108.0       | 1            | 108.0       | 108.0       |
| master.Server.numRegionServers      | ams-hbase  |              | 1481454420000  |        | 1.0         | 1            | 1.0         | 1.0         |
+-------------------------------------+------------+--------------+----------------+--------+-------------+--------------+-------------+-------------+

Other queries that we can try as following:

select * from METRIC_AGGREGATE where METRIC_NAME='regionserver.Server.storeFileCount' order by SERVER_TIME limit 1;

select * from METRIC_AGGREGATE where METRIC_NAME= 'master.Server.numRegionServers' order by SERVER_TIME limit 1;

select * from METRIC_AGGREGATE where METRIC_NAME='master.Server.averageLoad' order by SERVER_TIME limit 1;

.

.

[#2]

Hi, I've been trying similar thing on a kerberoized cluster.

Would you mind sharing the steps making this work for a kerberoized clsuter please?

[#3]

@Aakash Singh

Did you find out how to do this on a kerberized cluster?

[#4]

@josh_nicholson NOTE: For Kerberized Cluster use the value of "zookeeper.znode.parent" may be "/ams-hbase-secure" so we can connect to it as following: /usr/hdp/2.5.0.0-1245/phoenix/bin/sqlline.py c6403.ambari.apache.org:61181:/ams-hbase-secure