Hive concurrency auto-scaling for standard BI-type queries in public clouds

Your Hive Virtual Warehouse in Cloudera Data Warehouse (CDW) Public Cloud bases its auto-scaling on the size of data scanned for a query. Queries that scan amounts of data within the hive.query.isolation.scan.size.threshold setting use the concurrency method of auto-scaling.

In CDW Public Cloud service, Hive Virtual Warehouses base auto-scaling on the size of data scanned to run a query. The query planner in HiveServer, which receives all incoming queries, examines the scan size required to execute the queries in a workload. Most standard BI-type queries are not data-intensive and so they have a smaller scan size. The query planner determines that these queries can be managed by the following components:

  • HiveServer: Receives all incoming queries and generates a preliminary query plan that does not include distributing the query across executor nodes. Then it looks for an available query coordinator to send the plan to for distribution to executor nodes.
  • Query coordinators: Receives the serialized plan from HiveServer and generates the final query plan that distributes the query tasks across executor nodes for execution. There are as many query coordinators as executor nodes, but there is not a one-to-one correspondence. Instead, each query coordinator can interact with all executor nodes. However, one query coordinator can only orchestrate the execution of tasks for one query at a time so the number of query coordinators determines your query parallelism limit.
  • Query executor nodes: Can execute up to 12 tasks of one or multiple different queries. Query executor nodes determine the throughput of your system. They are the executors which are the unit of sizing for Virtual Warehouses.
  • Executor groups: A group of executors that can execute queries. Each executor group can run up to twelve queries. The size of executor groups is determined by the size you choose when you first create the Virtual Warehouse (XSMALL, SMALL, MEDIUM, or LARGE). A single query is always contained within a single executor group and never spans multiple executor groups. The throughput for an individual query is determined by the original size of the warehouse.

Concurrency auto-scaling process for standard BI-type queries

Hive Virtual Warehouse concurrency auto-scaling manages resources based on query load:

  1. When users connect to Hive Virtual Warehouses using SQL applications such as Hue, Data Analytics Studio (DAS), or other SQL clients that use JDBC or ODBC, the query is handled by HiveServer. First HiveServer generates a preliminary query execution plan that does not include distributing the query tasks across executor nodes.
  2. Then HiveServer locates an available query coordinator in the Virtual Warehouse to handle the query. The query coordinator generates the final query plan that distributes the query tasks across executor nodes for execution and locates available query executors to handle each query task. Each query coordinator can send query tasks to all query executors in the executor group.
  3. Depending on whether HEADROOM or WAIT TIME has been set to manage auto-scaling, additional query executor groups are added when the auto-scaling threshold has been exceeded:
    • HEADROOM: This sets the number of available coordinators that trigger auto-scaling. For example, if Desired Free Capacity is set to 1 on an XSMALL-sized Virtual Warehouse, which has 2 executor nodes, when there is less than one free coordinator (2 queries are concurrently executing), the warehouse auto-scales up and an additional executor group is added.
    • WAIT TIME: Sets how long queries wait in the queue to execute. A query is queued if it arrives on HiveServer and no coordinator is available. For example, if WaitTime Seconds is set to 10, queries are waiting to execute in the queue for 10 seconds, the warehouse auto-scales up and additional executor group is added.

      When these auto-scaling thresholds are exceeded, the Virtual Warehouse continues to add executor groups until the maximum setting for Nodes: Min: , Max: has been reached. This ensures that your Virtual Warehouse does not exceed your cloud account limits or the limits of your budget.

When no queries are being sent to an executor group, it scales down and the nodes are released. When all executor groups are scaled back to the original executor group created when the warehouse was created and its coordinators and executors are idle, the Virtual Warehouse is suspended according to the value set for AutoSuspend Timeout.

AutoSuspend Timeout

You set the AutoSuspend Timeout when you create a Virtual Warehouse:

This sets the maximum time that the original warehouse executor group idles after all other executor groups have scaled down and released their nodes. The JDBC endpoint is kept up and alive to respond to queries from the result cache or statistics, but expensive executor nodes are no longer running. AutoSuspend Timeout is independent of the auto-scaling process and only applies to the original Virtual Warehouse and not to any additional warehouses that are created as a result of auto-scaling.

If a query is executed on a suspended Virtual Warehouse, then the query coordinator queues the query. When a queued query is detected, an executor group is immediately added that can run the query on the Virtual Warehouse.