Apache Hive Performance Tuning
Also available as:
loading table of contents...

Chapter 4. Connectivity and Admission Control

Creating and maintaining an environment for performant data analytics applications using a Hive EDW requires orchestrating several software components that reside on your cluster and using compatible client tools. The main pieces that concern the application developer and IT or DevOps staff are the following:

  • HiveServer: A service that connects your client application to the Hive EDW.

  • YARN: A system resource for queuing data queries.

  • Cost-Based Optimizer: An enhanced queuing mechanism of Hive.

  • Apache Tez: An application framework for running high-performance batch and interactive data applications.

  • For interactive and sub-second queries: Hive LLAP daemons. The daemons manage resources across all YARN nodes, rather than relying on separate Tez sessions on each node of the cluster.

HiveServer, YARN, and Tez are components that work together to “intelligently” queue incoming queries on your Hive data set to minimize latency of returned results.

HiveServer is one of several architectural components for admission control. Admission control is designed to minimize query response time while enabling high concurrency. It operates by scaling the Hive processing of concurrent queries to the available system resources while removing the traditional launch time associated with MapReduce or Tez applications by maintaining long-living sessions. Admission control is akin to “connection pooling” in RDBMS databases.

To optimize Hive performance, configuration parameter settings that affect admission control must be optimized in line with your needs and system resources.

This chapter focuses on what you need to know about the components listed above to ensure clients connect to the Hive data warehouse and receive query results with high performance. To achieve optimal results, you also need to tune the data warehouse infrastructure so that it can handle concurrent queries in the way that comes closest to meeting your priorities.


HiveServer is a server interface that enables remote clients to execute queries against Hive and retrieve the results using a JDBC or ODBC connection. For a client, you can use one of various BI tools (for example, Microstrategy, Tableau, and BusinessObjects) or another type of application that can access Hive over a JDBC or ODBC connection. In addition, you can also use a command-line tool, such as Beeline, that uses JDBC to connect to Hive.


Using the Beeline command-line shell is recommended.

An embedded metastore, which is different from the MetastoreDB, also runs in HiveServer. This metastore performs the following tasks:

  • Get statistics and schema from the MetastoreDB

  • Compile queries

  • Generate query execution plans

  • Submit query execution plans

  • Return query results to the client

Sizing HiveServer Heap Memory

The following are general recommendations for sizing heap memory of a HiveServer instance:

  • 1 to 20 concurrent executing queries: Set to 6 GB heap size.

  • 21 to 40 concurrent executing queries: Set to 12 GB heap size.

  • More than 40 concurrent executing queries: Create a new HiveServer instance. See Multiple HiveServer Instances for Different Workloads for how to add a HiveServer instance.

HiveServer Interactive UI


The HiveServer Interactive UI functions only with clusters that have LLAP enabled.

The HiveServer Interactive UI monitors and displays heap, system, and cache metrics of each Hive LLAP node.


The HiveServer Interactive UI enables you to view executing queries in real time, a recent history of queries, and access running LLAP daemons. The Tez View provides diagnostics for debugging queries that executed or attempted to execute in the past.

From the Quick Links menu of Ambari, shown in the following figure, you can open the HiveServer Interactive UI.

Figure 4.1. Quick Links

Multiple HiveServer Instances for Different Workloads

Multiple HiveServer instances can be used for:

  • Load-balancing and high availability using ZooKeeper

  • Running multiple applications with different settings

Because HiveServer uses its own settings file, using one for ETL operations and another for interactive queries is a common practice. All HiveServer instances can share the same MetastoreDB.

Adding a HiveServer Instance to Your Cluster

  1. In Ambari, select the Hosts window

  2. Click the name of the host node where you want to create the HiveServer instance.

  3. On the Summary tab, click the Add button and select HiveServer2.


HiveServer performs standard SQL security checks when a query is submitted, including connection authentication. After the connection authentication check, the server runs authorization checks to make sure that the user who submits the query has permission to access the databases, tables, columns, views, and other resources required by the query. Hortonworks recommends Ranger to implement security. Storage-based access controls, which is suitable for ETL workloads only, is also available.

Workload Management with YARN Capacity Scheduler Queues

YARN allocates Hadoop cluster resources among users and groups with Capacity Scheduler queues. The Hive queries that are submitted from your data analytics applications compose just one set of the competing resource demands from different Hortonworks Data Platform (HDP) components.

You can configure the Capacity Scheduler queues to scale Hive batch and LLAP workloads as needed for your environment. However, the queue configuration in YARN for batch processing is different from the YARN configuration for Hive LLAP.

Queues for Batch Processing

Capacity Scheduler queues can be used to allocate cluster resources among users and groups. These settings can be accessed from Ambari > YARN > Configs > Scheduler or in conf/capacity-scheduler.xml.

The following configuration example demonstrates how to set up Capacity Scheduler queues. This example separates short- and long-running queries into two separate queues:

  • hive1--This queue is used for short-duration queries and is assigned 50% of cluster resources.

  • hive2--This queue is used for longer-duration queries and is assigned 50% of cluster resources.

The following capacity-scheduler.xml settings are used to implement this configuration:


Configure usage limits for these queues and their users with the following settings:


Setting maximum-capacity to 50 restricts queue users to 50% of the queue capacity with a hard limit. If the maximum-capacity is set to more than 50%, the queue can use more than its capacity when there are other idle resources in the cluster. However, any user can use only the configured queue capacity. The default value of "1" for user-limit means that any single user in the queue can at a maximum occupy 1X the queue's configured capacity. These settings prevent users in one queue from monopolizing resources across all queues in a cluster.

Figure 4.2. YARN Capacity Scheduler

This example is a basic introduction to queues.

Setup Using the Ambari Capacity Scheduler View

Queues can be set up using the Ambari Capacity Scheduler View as shown in the following image:

  1. From the Ambari dashboard, click ADMIN > Manage Ambari.

  2. Click Views.

  3. Click the URL for the view named AUTO_CS_INSTANCE, which is the capacity scheduler view.

  4. In the YARN Queue Manager, select the queue you want to use or create a queue.

    To create the following schedule, select the root queue and add hive1 and hive2 at that level.

Figure 4.3. YARN Queue Manager and hive1 queue

Queues in Hive LLAP Sites

If you accept the default llap queue of the Hive LLAP Service in Ambari, then no manual configuration of the YARN Capacity Scheduler is required. But if you prefer to create and customize the workload queue for interactive queries, then you need to complete the following task before enabling and configuring Hive LLAP in Ambari.


If you are an advanced Hive user and want to launch Hive LLAP with a customized queue, complete the following task before enabling Hive LLAP. Do not complete the following task if plan to use the default llap queue that can be deployed automatically by starting the Hive LLAP Service in Ambari.

Setup of YARN for a Non-Default LLAP Queue

  1. Create and configure a queue with the YARN Capacity Scheduler.

  2. Open the YARN Queue Manager View in Ambari:

  3. Select the queue that should be used by Hive LLAP.

  4. In the Resources pane, set the Priority field with a number that is larger than the priority value of the other queues. The default value of the Priority field after a queue is created is zero.