Auto Routing

The AUTO_ROUTING Pre-hook optimizes query performance and cost efficiency by dynamically routing queries to the most suitable warehouse. This hook analyzes historical query data, execution times, and associated costs to make informed decisions.

Key features of the AUTO_ROUTING Pre-hook:

1. Query Analysis: The hook examines incoming queries and compares them against the historical query performance data.

2. Optimal Warehouse Selection: Based on the analysis, queries are directed to an appropriate warehouse size within the user-specified warehouse pool.

3. Performance and Cost Optimization: The hook aims to enhance query performance while minimizing costs.

4. Adaptive Learning: Recommendations are based on query hashes that are seen by this hook at least once within the defined look-back window, as well as additional queries identified by the training label during this period.

5. Default Routing: For new queries without historical data, the system routes them to the default warehouse in the pool.

Sundeck intercepts each incoming query before execution. The hook checks for an existing warehouse recommendation for the query. If a recommendation exists, Sundeck routes the query to the suggested warehouse. Otherwise, it directs the query to the default warehouse in the pool.

AUTO_ROUTING {
  'warehousePoolName' : <warehouse-pool-name>,
  'refreshFrequency'  : <refresh-frequency>,
  'lookbackPeriod'  : <lookback-period>,
  'labelName'  : <training-label-name>,
  'hint'  : <hint>,
}

<warehouse-pool-name>

The hook will direct queries to an appropriate warehouse size in this warehouse pool.

<refresh-frequency>

How often the hook should relearn/update it’s routing table. Valid values are 1 hour, 3 hours, 6 hours, 12 hours, 1 day, 2 days and 7 days. Default is 1 day.

<lookback-period>

The amount of data to review when making routing recommendations 7 days, 1 month, 3 months, 6 months and All time. Default is 1 month.

<training-label-name>

Select a label that identifies queries likely to be used with this hook. This is helpful when trying to accelerate routing decisions for infrequent queries. This is an optional parameter.

<hint>

For use by sundeck support only

CREATE OR REPLACE SUNDECK FLOW "Route queries automatically based on query history"
  PRE HOOK route_tpc_queries_hook
  ALWAYS 
  AUTO_ROUTING {'labelName': 'TPCDI', 'lookbackPeriod': '1 month', 'refreshFrequency': '1 hour', 'warehousePoolName': 'CS_POOL1'}
  ENABLED = TRUE;

When you connect to this flow, the queries will be routed to the optimal warehouse in the warehouse pool CS_POOL1 based on the query history of the last month. The hook will refresh the routing table every hour.

See also:

QUERY_SIGNATURE, LABELS, WAREHOUSE_POOL