Hive query isolation for scan-heavy, data-intensive queries on 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
that exceed the
hive.query.isolation.scan.size.threshold setting use the query
isolation method of auto-scaling.
In CDW Public Cloud service, Hive Virtual Warehouses base auto-scaling on
total scan size of the query. HiveServer, which receives all incoming queries, has a query
planner component. When the HiveServer query planner receives queries, it examines the total
scan size of each query. That is, it looks at the number of bytes read from
the file system required to execute the query. If the Query Isolation
feature has been enabled for a Virtual Warehouse and a query scans more than the threshold
set in the
hive.query.isolation.scan.size.threshold parameter, the planner
runs the query in isolation. This means that an isolated standalone executor group is spawned to
run the data-intensive query.
The default setting for this parameter is the data cache size (200GB) times the T-shirt size set for the Virtual Warehouse. T-shirt size is the XSMALL, SMALL, MEDIUM or LARGE sized executor group you configure for the Virtual Warehouse.
Concurrency auto-scaling, auto-suspending, and auto-resume features of the core executor groups are not impacted by any isolated queries that are running.
Scan-heavy, data-intensive queries are not scheduled in the XSMALL, SMALL, MEDIUM, or LARGE-sized executor groups you configure for your Virtual Warehouse. Instead the Hive Virtual Warehouse spawns a new dedicated executor group with the right number of executors that are required to limit query execution time to acceptable targets. This isolated executor group is only used for the single scan-heavy, data-intensive query, and it terminates after the query completes, ensuring there are no wasted resources.
If you have enabled Query Isolation on a MEDIUM-sized Virtual Warehouse which creates a 20-executor core executor group capable of running 20 queries concurrently, the default scan size threshold is the T-shirt size times the data cache size, or:
20 x 200GB = 4TB
In this case, if any query scans more than 4TB of data, the query planner decides to run the
query in isolation, estimating the number of executors needed to run the query. The number
of executors spawned to run the query does not exceed the default setting for the
hive.query.isolation.max.nodes.per.query parameter, which default to 2 times
the T-shirt size. For this MEDIUM-sized Virtual Warehouse, which has a 20-executor core executor
group, 40 executors (2 x 20) are spawned to run the isolated query.
When the isolated query is running, if HiveServer receives another query, which scans less than 4TB of data, the planner runs that query in the core executor group and does not spawn an isolated executor group to run it. If there is capacity in the core executor group, the query runs immediately or concurrency auto-scaling provisions more capacity in the core executor group.
Assume the original data-intensive "scan-heavy" isolated query is still running. If
HiveServer receives an additional "scan-heavy" query, which scans more than 4TB of data,
another isolated executor group is spawned to run the additional query. However, if two
additional "scan-heavy" queries are received, unless the default value (2) is changed for the
hive.query.isolation.max.queries parameter, one scan-heavy query runs, while
the other is queued until one of the other isolated queries has finished executing.
Query isolation process for data-intensive queries
Optimize your Virtual Warehouse using the controls described in Hive concurrency auto-scaling-for standard BI-type
queries, enable Query Isolation, and set a value for the
hive.query.isolation.scan.size.threshold configuration parameter. When
the query planner encounters a scan-heavy, data-intensive query whose scan size exceeds the
value set for the threshold, it launches the query isolation feature. Query isolation
automatically spawns an “on-demand” executor group with the estimated correct number of
executors for the scan-heavy query :
- 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 executors. The query planner also determines the data scan size of the query.
For standard BI-type queries with smaller scan sizes: HiveServer locates an available query coordinator in the Virtual Warehouse to handle the query. The full process for these queries is described in Hive auto-scaling-for standard BI-type queries.
For data-intensive queries with larger scan sizes: You can enable the query isolation feature and specify a scan size threshold when you are setting up your Virtual Warehouse. Then the query isolation feature spawns an on-demand executor group with the estimated correct number of executors to handle the single query. This executor group is limited in size by the values you set for the controls in the Data Warehouse UI:
Use the following settings to control resource usage and costs:
- Max Concurrent Isolated Queries sets the maximum number of queries that can spawn dedicated executor groups at one time. For example, if Max Concurrent Isolated Queries is set to 3 and a dedicated executor group is spawned for each data-intensive query, only 3 dedicated executor groups can be running at one time. If another data-intensive query is received, it must wait in a queue to run.
- Max Nodes Per Isolated Query sets the maximum number of executors that can be created for each data-intensive query.
The scan size threshold for triggering the query isolation feature can be set with the
- In the Data Warehouse service UI, select Virtual Warehouses in the left menu to see a table containing all of your Virtual Warehouses, and locate the warehouse you want to set the scan size threshold for.
- In the detail page for the specific Virtual Warehouse, select the Hiveserver2 tab.
- On the Hiveserver2 tab, search on the word "isolation" in the Search
Settings text box to locate the
- Set the value. Specify a data size in storage units. For example: "500GB" or "2TB"
- Click Apply.
After setting a value for this parameter, when the threshold is reached, the CDW service spawns dedicated executor groups to isolate scan-heavy, data-intensive queries and terminates them as soon as the query finishes executing.