Configure Query Spotlight: Hive (Parcel)

On This Page

Prerequisites
Task 1: Enable Fetching of Hive Query Data
Task 2: (Optional) Encrypt the Connect String for the Hive Metastore
Task 3: Enable Fetching of Hive Databases and Tables’ Metadata

Prerequisites

Before you begin configuring Query Spotlight for Hive query monitoring, ensure that your system meets the required prerequisites.

On every host where you are configuring Query Spotlight, Pepperdata must be installed (the applicable distro’s procedure in Installing Pepperdata) and Hadoop configured to use Pepperdata (Configure Hadoop to Use Pepperdata).
Your cluster uses a supported combination of Hive version and platform; see the entries for Query Spotlight 8.1.x in the table of Supported Hive-Distro Combinations by Query Spotlight Version.

DDL and Analyze statements are neither collected nor displayed in the Query Spotlight pages.

Task 1: Enable Fetching of Hive Query Data

To enable Pepperdata to fetch Hive query data, add the environment variables for the Hive query JAR, Pepperdata configuration, and the Pepperdata activation script to the Hive configuration on the Hive server hosts.

Procedure

Use Cloudera Manager to add the following snippet to the Hive > Configuration > Client Java Configuration Options template.

Be sure to replace the PLACEHOLDER-FOR-YOUR-HIVE-QUERY-JAR-NAME placeholder with the actual name of the Hive Query JAR that you’re using.
- Choose the JAR where the first set of numbers corresponds to the version of Hive that you’re using.
  
  For all Hive 3.x versions, use the PepperdataHiveQuery-3.1.2.jar unified JAR. It supports all Hive 3.x-based distros.
  
  For example, if you are using CDP 7.1.3, which is built with Hive 3.1.3, use the PepperdataHiveQuery-3.1.2.jar JAR.
- For Hive 2.x, the distro version numbers refer to the earliest version number that is supported. If your version is later than any of the distro’s designations in the JAR filenames, use the latest-named one.
  
  For example, if you are using Hive 2.1.1 with CDH 6.3.4, use the PepperdataHiveQuery-2.1.1-cdh6.3.3.jar JAR.
- Choose from the following JARs:
  - PepperdataHiveQuery-3.1.2.jar
  - PepperdataHiveQuery-2.3.6.jar
  - PepperdataHiveQuery-2.1.1-cdh6.3.3.jar
  - PepperdataHiveQuery-2.1.1-cdh6.2.1.jar
```
-Dpepperdata.class.patching.enabled=true -javaagent:/opt/cloudera/parcels/PEPPERDATA_SUPERVISOR/supervisor/lib/internal-jars/query/hive/PLACEHOLDER-FOR-YOUR-HIVE-QUERY-JAR-NAME -Dpepperdata.class.transformer=com.pepperdata.hive.classpatcherlib.ClassTransformerForHive
```

Use Cloudera Manager to add the same snippet to the Hive > Configuration > Java Configuration Options for HiveServer2 template.

Be sure to replace the PLACEHOLDER-FOR-YOUR-HIVE-QUERY-JAR-NAME placeholder with the actual name of the Hive Query JAR that you’re using.

-Dpepperdata.class.patching.enabled=true -javaagent:/opt/cloudera/parcels/PEPPERDATA_SUPERVISOR/supervisor/lib/internal-jars/query/hive/PLACEHOLDER-FOR-YOUR-HIVE-QUERY-JAR-NAME -Dpepperdata.class.transformer=com.pepperdata.hive.classpatcherlib.ClassTransformerForHive

Deploy the configuration to all clients.
Restart all the Hive servers.
- In Cloudera Manager, for every Hive server, select the Restart action for the Hive service.

Task 2: (Optional) Encrypt the Connect String for the Hive Metastore

If you want to encrypt the connect string for the Hive metastore, regardless of whether you’ll store it in the Pepperdata site file or an external file, use the Pepperdata password encryption script.

At a minimum, the unencrypted connect string must include the jdbc:hive2://YOUR-HOSTNAME:YOUR-PORTNUM/ string. You can add as many connection properties/parameters as you need for your environment, separating them with a semicolon, ;.

Example Connect Strings

Without properties/parameters: jdbc:hive2://localhost:10000/
Add properties for authenticated environments: jdbc:hive2://localhost:10000/;user=YOUR-USERNAME;password=YOUR-PASSWORD
Multiple properties/parameters: jdbc:hive2://<zookeeper quorum>/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=<hiveserver2_namespace>

Procedure

Run the Pepperdata encryption script.

/opt/cloudera/parcels/PEPPERDATA_SUPERVISOR/supervisor/encrypt_password.sh
At the Enter the password to encrypt: prompt, enter your connect string.
Copy (or make note of) the resulting encrypted connect string.

For example, in the following output from the script, the encrypted connect string is the string W+ONY3ZcR6QLP5sqoRqcpA=2.

Encrypted password is W+ONY3ZcR6QLP5sqoRqcpA=2

Use this encrypted result as the value for pepperdata.jdbcfetch.hive.connect.string.encrypted (which you’ll configure later), or store it in the external file specified by the pepperdata.jdbcfetch.hive.connect.string.encrypted.file property.

Task 3: Enable Fetching of Hive Databases and Tables’ Metadata

To enable Pepperdata to fetch data from the Hive metastore, add the required variables to the Pepperdata configuration.

Procedure

Use Cloudera Manager to configure the hostname by adding the following snippet to the PepAgent Advanced Configuration Snippet (Safety Valve) for conf/pepperdata-site.xml template, as an XML block.
- Select a host that is configured to be a Hive client (and from which you launch Hive queries).
- Be sure to substitute the fully-qualified, canonical hostname of the selected host for the YOUR.CANONICAL.HOSTNAME placeholder in the following code snippet.
```
<property>
  <name>pepperdata.jdbcfetch.hive.pepagent.host</name>
  <value>YOUR.CANONICAL.HOSTNAME</value>
  <description>Host where the fetching should be enabled.</description>
</property>
```
Configure the connect string.

Add one of the following properties, depending on your environment and security requirements.

Be sure to substitute your information for the YOUR... placeholders.
- Plain text connect string stored in the Pepperdata site file.
  
  At a minimum, the connect string must include the jdbc:hive2://YOUR-HOSTNAME:YOUR-PORTNUM/ string. You can add as many connection properties/parameters as you need for your environment, separating them with a semicolon, ;.
  
  Example Connect Strings
  - Without properties/parameters: jdbc:hive2://localhost:10000/
  - Add properties for authenticated environments: jdbc:hive2://localhost:10000/;user=YOUR-USERNAME;password=YOUR-PASSWORD
  - Multiple properties/parameters: jdbc:hive2://<zookeeper quorum>/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=<hiveserver2_namespace>
```
<property>
  <name>pepperdata.jdbcfetch.hive.connect.string</name>
  <value>jdbc:hive2://YOUR-HOSTNAME:YOUR-PORTNUM/${;OPTIONAL-ADDITIONAL-PROPERTY}</value>
  <description>JDBC Connect string to be used.</description>
</property>
```
- Plain text connect string stored in an external file:
```
<property>
  <name>pepperdata.jdbcfetch.hive.connect.string.file</name>
  <value>YOUR-PATH-TO-JDBCSTRING-FILE</value>
  <description>Path to file containing JDBC Connect string.</description>
</property>
```
- Encrypted connect string—the result from encrypting the string earlier in the configuration procedure—stored in the Pepperdata site file:
```
<property>
  <name>pepperdata.jdbcfetch.hive.connect.string.encrypted</name>
  <value>YOUR-ENCRYPTED-TEXT</value>
  <description>Encrypted JDBC Connect string to be used.</description>
</property>
```
- Encrypted connect string—the result from encrypting the string earlier in the configuration procedure—stored in an external file:
```
<property>
  <name>pepperdata.jdbcfetch.hive.connect.string.encrypted.file</name>
  <value>YOUR-PATH-TO-JDBCSTRING-FILE</value>
  <description>Path to file containing encrypted JDBC Connect string.</description>
</property>
```
(Kerberized Clusters) If the hiveserver2 service is Kerberized, add the properties for the Kerberos principal and keytab to the Pepperdata site file.
1. Enable fetching from a Kerberized Hiveserver2.
```
<property>
  <name>pepperdata.jdbcfetch.hive.kerberos.enabled</name>
  <value>true</value>
  <description>Should kerberos be used when connecting to Hive?</description>
</property>
```
2. (CDH and CDP Private Cloud Base) Configure the principal and keytab.
  
  • If you already configured the cluster for Kerberos during the installation process (that is, you you selected Enable Access to Kerberized Cluster Components in Task 2: Add Pepperdata Service to Cloudera Manager), you do not need to manually configure the principal and keytab, and you should skip this substep.
  
  • For installations on CDP Public Cloud, Pepperdata automatically configures this for Kerberized clusters; therefore you should skip this substep.
  
  Be sure to substitute your information for the YOUR... placeholders.
```
<property>
  <name>pepperdata.jdbcfetch.hive.kerberos.principal</name>
  <value>YOUR_PRINICPAL/HOST@DOMAIN.COM</value>
  <description>The Kerberos principal to use to authenticate with the Hive client.</description>
</property>

<property>
  <name>pepperdata.jdbcfetch.hive.kerberos.keytab.location</name>
  <value>YOUR-PATH-TO-KEYTAB-FILE</value>
  <description>Path to the keytab file for the specified principal.</description>
</property>
```
Add the hive-jdbc-*standalone.jar JAR file to the PepAgent’s classpath on the host that you selected in step 1.
1. Find the fully-qualified name of the JAR, which depends on the cluster’s distro.
  - The filename pattern is hive-jdbc-*standalone.jar.
  - For Parcel installations on Cloudera CDH/CDP Runtime, the JAR file is located in /opt/cloudera/parcels/CDH/jars/.
  - You can use the find command to locate all available JAR files, and output their names to the console; for example:
    find /opt/cloudera/parcels/CDH/jars/ /usr/lib/hive/lib/ /usr/lib/hive/jdbc/ -name "hive-jdbc-*standalone.jar" 2>/dev/null /usr/lib/hive/lib/hive-jdbc-standalone.jar
  Make a note of the JAR file to use. You’ll need this information in the next substep, as the value for the YOUR-HIVE-JDBC-JAR placeholder.
2. Use Cloudera Manage to add the following snippet to the PepAgent Environment Advanced Configuration Snippet (Safety Valve) template.
  
  Be sure to substitute the actual path and filename for the YOUR-HIVE-JDBC-JAR placeholder.
```
PD_EXTRA_CLASSPATH_ITEMS=YOUR-HIVE-JDBC-JAR
```
In Cloudera Manager, select the Restart action for the PepAgent service.