Hologres:Query Queue

Create a query queue

• Syntax

  • General-purpose instance

    CALL hg_create_query_queue (query_queue_name, max_concurrency, max_queue_size);

  • Compute Group-based instance

    CALL hg_create_query_queue (warehouse_name, query_queue_name, max_concurrency, max_queue_size);

• Parameter description

  • warehouse_name: Optional. The name of the compute group. If you do not specify this parameter, the query queue is created in the compute group of the current connection by default.

    Note

    This parameter is required only for Compute Group-based instances.

  • query_queue_name: Required. The name of the query queue. The name must be unique within the current instance or compute group.

  • max_concurrency: Optional. The maximum concurrency. The default value is -1, which indicates that there is no concurrency limit. The value must be in the range of [-1, 2147483647).

  • max_queue_size: Optional. The maximum queue size. This parameter specifies the maximum number of SQL statements that can be queued. The default value is -1, which indicates that the queue size is unlimited. The value must be in the range of [-1, 2147483647).

  Note

  When you create a query queue, you can configure only the max_concurrency and max_queue_size properties. For information about other properties, see Configure query queue properties.

• Examples

  • General-purpose instance

    -- Create a query queue named insert_queue and set the maximum concurrency to 10. Note that the concurrency parameter does not require single quotation marks.
    CALL hg_create_query_queue ('insert_queue', 10);

  • Compute Group-based instances

    -- In the init_warehouse compute group, create a query queue named insert_queue and set the maximum concurrency to 10.
    CALL hg_create_query_queue ('init_warehouse', 'insert_queue', 10);

Create a classifier

• Syntax

  • General-Purpose Instance

    CALL hg_create_classifier (query_queue_name, classifier_name, priority);

  • Compute Group instance

    CALL hg_create_classifier (warehouse_name, query_queue_name, classifier_name, priority);

• Parameter description

  • warehouse_name: Optional. The name of the compute group. If you do not specify this parameter, the compute group of the current connection is used by default.

    Note

    This parameter applies only to Compute Group-based instances.

  • query_queue_name: Required. The name of the query queue for which you want to create a classifier.

  • classifier_name: Required. The name of the classifier. The name must be unique within the current instance or compute group.

  • priority: Optional. The matching priority of the classifier. A larger value indicates a higher priority. The default value is 50. The value must be in the range of [1, 100]. If you do not specify this parameter when you create the classifier, you can configure it later. For more information, see Configure classifier properties.

    Note

    • Classifiers with a higher priority are matched earlier.

    • If multiple classifiers have the same priority, they are matched based on the lexicographic order of their query queue and classifier names. The classifier with the name that comes first in lexicographic order is matched first. For example, queue_a(classifier_1) has a higher priority than queue_b(classifier_1).

• Examples

  • General-purpose instances

    -- In the insert_queue query queue, create a classifier named classifier_insert with a matching priority of 20. Note that the priority parameter does not require single quotation marks.
    CALL hg_create_classifier ('insert_queue', 'classifier_insert', 20);

  • Compute Group instance

    -- In the insert_queue query queue of the init_warehouse compute group, create a classifier named classifier_insert with a matching priority of 20.
    CALL hg_create_classifier ('init_warehouse', 'insert_queue', 'classifier_insert', 20);

Configure classifier matching rules

In a query queue, you can configure matching rules for a classifier to match SQL statements. This process determines which query queue an SQL statement enters.

• Syntax

  • General-Purpose instance

    CALL hg_set_classifier_rule_condition_value (query_queue_name, classifier_name, condition_name, condition_value);

  • Compute group-based instance

    CALL hg_set_classifier_rule_condition_value (warehouse_name, query_queue_name, classifier_name, condition_name, condition_value);

• Parameter description

  Name	Description
  warehouse_name	Optional. The name of the compute group. If you do not configure this parameter, the compute group of the current connection is used by default. Note You must configure this parameter only for Compute Group-based instances.
  query_queue_name	Required. The name of the query queue.
  classifier_name	Required. The name of the classifier for which you want to add a matching rule.
  condition_name and condition_value	Required. The supported condition properties are: user_name: The UID of the current account. command_tag: The request type. Valid values: INSERT, SELECT, UPDATE, and DELETE. To improve the execution efficiency of COPY operations, Hologres implements COPY operations using INSERT. Therefore, when the concurrency of INSERT operations is limited, the write concurrency of COPY operations is also affected. db_name: The database name. engine_type: The engine used by the request. Valid values: HQE, PQE, SQE, and HiveQE. We recommend that you use this property starting from Hologres V3.1.18. digest: The SQL fingerprint. For more information about SQL fingerprints, see SQL fingerprints. application_name: The application that initiated the query. This property is supported only in Hologres V3.0.9 and later. storage_mode: The storage mode. Valid values: hot and cold. We recommend that you use this property starting from Hologres V3.1.18 or V3.1.8. write_table: The table to which the query writes data. The format is <db_name>.<schema_name>.<table_name>. This property is supported only in Hologres V3.1 and later. read_table: The table from which the query reads data. This property is supported only in Hologres V3.1 and later.

  You can set multiple properties in a classifier. Each property can have multiple matching rules. Starting from Hologres V3.1.18, the relationships between properties and rules are as follows:

  • If you set matching rules for multiple properties within a single classifier:

    • An AND relationship exists among different properties. For example, if a classifier contains matching rules for both the user_name and command_tag properties, a query must satisfy both rules to be assigned to this classifier.

    • If you require an OR relationship among multiple properties, where a query is matched if it meets the requirements of any property, you must create multiple classifiers and assign them to the same query queue.

  • There are three types of relationships for matching rules set for the same property within a single classifier:

    • user_name, command_tag, db_name, digest, or application_name:

      • For a specific property, such as user_name, the set of property values configured in the matching rule is set_a.

      • The corresponding property set for a query is set_b, which contains only one value.

      • The query matches the rule if the set operation set_b ⊆ set_a is true.

    • storage_mode:

      • For the storage_mode property, the set of property values configured in the matching rule is set_a.

      • The corresponding property set for a query is set_b, which can have one of three possible values: "hot", "cold", or "hot, cold".

      • The query matches the rule only if the set operation set_a == set_b is true.

    • engine_type, write_table, or read_table:

      • For a specific property, such as read_table, the set of property values configured in the matching rule is set_a.

      • The corresponding property set for a query is set_b, which can have any number of values, such as multiple tables.

      • The query matches the rule if the set operation (set_a ∩ set_b) != ∅ is true.

  Note

  • You can configure only one condition property at a time. If the condition value is case-sensitive, enclose it in double quotation marks ("").

  • If a condition property needs to match multiple values, you must set the property multiple times. For example, if a classifier needs to match requests where command_tag is either SELECT or INSERT, you must execute two SQL statements to set the rules.

• Examples

  The following examples use a compute group-based instance. For a general-purpose instance, you can remove the first input parameter. Create a query queue named test_queue for the default compute group init_warehouse:

  CALL hg_create_query_queue ('init_warehouse', 'test_queue');

  • Example 1: Assign a query to the test_queue query queue if the query is submitted by user p4_123 or p4_456, or if its SQL fingerprint is xxx or yyy.

    -- Because an OR relationship is required between the user and SQL fingerprint properties, you must configure two classifiers.
    
    -- Create a classifier named classifier_user and attach it to the test_queue query queue.
    CALL hg_create_classifier ('init_warehouse', 'test_queue', 'classifier_user');
    -- Set matching rules based on the user property. Queries from user "p4_123" or "p4_456" will hit this classifier.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_user', 'user_name', 'p4_123');
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_user', 'user_name', 'p4_456');
    
    -- Create a classifier named classifier_digest and attach it to the test_queue query queue.
    CALL hg_create_classifier ('init_warehouse', 'test_queue', 'classifier_digest');
    -- Set matching rules based on the SQL fingerprint property. Queries with SQL fingerprint "xxx" or "yyy" will hit this classifier.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_user', 'digest', 'xxx');
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_user', 'digest', 'yyy');

  • Example 2: Assign a query to the test_queue query queue if the query is initiated by the xx_bi application and accesses both cold and hot storage.

    -- Because an AND relationship is required between the application and storage class properties, define both properties within the same classifier.
    
    -- Create a classifier named classifier_3 and attach it to the test_queue query queue.
    CALL hg_create_classifier ('init_warehouse', 'test_queue', 'classifier_3');
    -- Set a matching rule based on the application property.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_3', 'application_name', 'xx_bi');
    -- Set matching rules based on the storage class. Set both "hot" and "cold" rules. A query must access both cold and hot storage to match.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_3', 'storage_mode', 'hot');
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_3', 'storage_mode', 'cold');

  • Example 3: Assign all requests that access cold storage data to the test_queue query queue.

    -- Because the storage class value requires an exact match, requests that "access any cold storage data" must be split into the following two classifiers. The request only needs to match one of them.
    
    -- Create a classifier named classifier_cold_1 and attach it to the test_queue query queue.
    CALL hg_create_classifier ('init_warehouse', 'test_queue', 'classifier_cold_1');
    -- Queries where storage_mode is only 'cold' enter this queue.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_cold_1', 'storage_mode', 'cold');
    
    -- Create a classifier named classifier_cold_2 and attach it to the test_queue query queue.
    CALL hg_create_classifier ('init_warehouse', 'test_queue', 'classifier_cold_2');
    -- Queries where storage_mode includes both 'hot' and 'cold' enter this queue.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_cold_2', 'storage_mode', 'hot');
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_cold_2', 'storage_mode', 'cold');

  • Example 4: Assign all requests that read from table a and write to table b to the test_queue query queue.

    -- Because an AND relationship is required between the read table and write table properties, define both properties within the same classifier.
    
    -- Create a classifier named classifier_table and attach it to the test_queue query queue.
    CALL hg_create_classifier ('init_warehouse', 'test_queue', 'classifier_table');
    -- Set a matching rule based on the read table. A request matches this rule if it reads data from table a, regardless of whether it reads from other tables.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_3', 'read_table', 'db_name.schema_name.a');
    -- Set a matching rule based on the write table. A request that writes to table b matches this rule.
    CALL hg_set_classifier_rule_condition_value ('init_warehouse','test_queue', 'classifier_3', 'write_table', 'db_name.schema_name.b');

More operations

Use Serverless Computing resources to execute queries in a query queue

Starting from Hologres V3.0.10, you can specify that all queries in a specific query queue are executed by Serverless Computing resources. After this is configured, queries in the queue request Serverless resources for execution based on the request order and configured priority. They are no longer affected by the query queue's concurrency configuration or queuing mechanism. For more information, see Serverless Computing User Guide.

• General-purpose instance

  • Syntax

    -- Set all queries in the target queue to run on Serverless resources.
    CALL hg_set_query_queue_property('<query_queue_name>', 'computing_resource', 'serverless');
    
    -- (Optional) Set the priority for queries in the target queue when using Serverless resources. Supported values are 1-5. The default is 3.
    CALL hg_set_query_queue_property('<query_queue_name>', 'query_priority_when_using_serverless_computing', '<priority>');

  • Parameter description

    • query_queue_name: Required. The name of the query queue.

    • priority: The priority. The default value is 3. The value must be in the range of [1, 5].

  • Example

    -- Set all queries in the target queue to run on Serverless resources.
    CALL hg_set_query_queue_property('insert_queue', 'computing_resource', 'serverless');
    
    -- Set the priority to 2 for queries in the target queue when using Serverless resources.
    CALL hg_set_query_queue_property('insert_queue', 'query_priority_when_using_serverless_computing', '2');

• Compute group-based instance

  • Syntax

    -- Set all queries in the target queue to run on Serverless resources.
    CALL hg_set_query_queue_property('<warehouse_name>', '<query_queue_name>', 'computing_resource', 'serverless');
    
    -- (Optional) Set the priority for queries in the target queue when using Serverless resources. Supported values are 1-5. The default is 3.
    CALL hg_set_query_queue_property('<warehouse_name>', '<query_queue_name>', 'query_priority_when_using_serverless_computing', '<priority>');

  • Parameter description

    • warehouse_name: Required. The name of the compute group.

    • query_queue_name: Required. The name of the query queue.

    • priority: Required. The priority. The default value is 3. The value must be in the range of [1, 5].

  • Example

    -- Set all queries in the target queue to run on Serverless resources.
    CALL hg_set_query_queue_property('init_warehouse', 'insert_queue', 'computing_resource', 'serverless');
    
    -- Set the priority to 2 for queries in the target queue when using Serverless resources.
    CALL hg_set_query_queue_property('init_warehouse', 'insert_queue', 'query_priority_when_using_serverless_computing', '2');

View the query queue used by a specific SQL statement

You can use EXPLAIN to view the query queue used by a specific SQL statement. The Query Queue field shows the query queue used. The following code provides an example.

-- Create a query queue with a concurrency of 10 and a maximum queue size of 20.
CALL hg_create_query_queue ('select_queue', 10, 20);
-- Create a classifier and attach the command_tag property.
CALL hg_create_classifier ('select_queue', 'classifier_1');
CALL hg_set_classifier_rule_condition_value ('select_queue', 'classifier_1', 'command_tag', 'select');

-- Use Explain Analyze to view the classifier and query queue matched by the query.
EXPLAIN ANALYZE SELECT * FROM hg_stat_activity;

The following result is returned.

QUERY PLAN
Gather  (cost=0.00..14.96 rows=1000 width=408)
[4:1 id=100003 dop=1 time=16/16/16ms rows=142(142/142/142) mem=43/43/43KB open=0/0/0ms get_next=16/16/16ms]
  ->  Forward  (cost=0.00..12.19 rows=1000 width=408)
      [0:4 id=100002 dop=4 time=16/8/5ms rows=142(39/35/33) mem=6/6/6KB open=16/8/5ms get_next=0/0/0ms scan_rows=142(39/35/33)]
        ->  ExecuteExternalSQL on PQE  (cost=0.00..10.04 rows=0 width=408)
"              External SQL: SELECT "datid" AS c_d2adb610_0, "datname" AS c_d2adb760_1, "pid" AS c_d2adb8a0_2, "usesysid" AS c_d2adba10_3, "usename" AS c_d2adbb60_4, "application_name" AS c_d2adbd10_5, "client_addr" AS c_d2adbe80_6, "client_hostname" AS c_d2df1020_7, "client_port" AS c_d2df1190_8, "backend_start" AS c_d2df1300_9, "xact_start" AS c_d2df1470_10, "query_start" AS c_d2df15e0_11, "state_change" AS c_d2df1750_12, "wait_event_type" AS c_d2df18c0_13, "wait_event" AS c_d2df1a30_14, "state" AS c_d2df1b80_15, "backend_xid" AS c_d2df1cf0_16, "backend_xmin" AS c_d2df1e60_17, "query" AS c_d2df1fb0_18, "backend_type" AS c_d2df2120_19, "query_id" AS c_d2df2290_20, "transaction_id" AS c_d2df2400_21, "extend_info" AS c_d2df2570_22, "running_info" AS c_d2df26e0_23 FROM pg_catalog."hg_stat_activity""
Query id:[1001002491453065719]
Query Queue: init_warehouse.select_queue.classifier_1

View the query queue used by SQL statements in active queries

You can execute the following SQL statement to view information, such as the query queue name, current status, and queue time, for SQL statements in active queries.

SELECT
    running_info::json -> 'current_stage' ->> 'stage_name' AS stage_name,
    running_info::json -> 'current_stage' ->> 'queue_time_ms' AS queue_time_ms,
    running_info::json ->> 'query_queue' AS query_queue,
    *
FROM
    hg_stat_activity;

View the query queue used by SQL statements in the Query Log

You can execute the following SQL statement to view the query queue, current status, and queue time for SQL statements in the Query Log. The query_detail field records the query queue used by the SQL statement. For more information about the hologres.hg_query_log system table, see View the query_log table.

SELECT * FROM hologres.hg_query_log WHERE query_detail like '%query_queue = <warehouse_name>.<queue_name>%';-- You must configure the warehouse_name parameter only for Compute group-based instances.

Create query queues with different matching rules

• Example 1: Create a query queue with a matching rule for the INSERT request type. The following code provides an example.

  After the query queue is created, all SQL requests of the INSERT type are matched by the classifier_1 classifier and assigned to the insert_queue query queue.

  -- Create a query queue with a concurrency of 10 and a maximum queue size of 20.
  CALL hg_create_query_queue ('insert_queue', 10, 20);
  
  -- Create a classifier and attach the command_tag property.
  CALL hg_create_classifier ('insert_queue', 'classifier_1');
  CALL hg_set_classifier_rule_condition_value ('insert_queue', 'classifier_1', 'command_tag', 'INSERT');

• Create a query queue with a matching rule for users p4_123 and p4_345. The following code provides an example.

  After creation, SQL requests submitted by users p4_123 and p4_345 are matched by classifier_2 and assigned to the user_queue query queue.

  -- Create a query queue with a concurrency of 3 and an unlimited maximum queue size.
  CALL hg_create_query_queue ('user_queue', 3);
  CALL hg_set_query_queue_property('user_queue','max_queue_size', -1);
  -- Create a classifier and set a user_name matching rule.
  CALL hg_create_classifier ('user_queue', 'classifier_2');
  CALL hg_set_classifier_rule_condition_value ('user_queue', 'classifier_2', 'user_name', 'p4_123');
  CALL hg_set_classifier_rule_condition_value ('user_queue', 'classifier_2', 'user_name', 'p4_345');

  Note

  If you specify a custom user account, you must enclose its name in double quotation marks (""). For example, CALL hg_set_classifier_rule_condition_value ('user_queue', 'classifier_2', 'user_name', '"BASIC$xxx"');.

• Create a query queue with a matching rule for the test and postgres databases. The following code provides an example.

  After the query queue is created, SQL requests related to the test and postgres databases are matched by the classifier_3 classifier and assigned to the db_queue query queue.

  -- Create a query queue with a concurrency of 5.
  CALL hg_create_query_queue ('db_queue', 5);
  
  -- Set the maximum queue time to 600000 ms. If a query waits longer than this time, an error is reported.
  CALL hg_set_query_queue_property ('db_queue', 'queue_timeout_ms', '600000');
  
  -- Create a classifier and attach the db_name property.
  CALL hg_create_classifier ('db_queue', 'classifier_3');
  CALL hg_set_classifier_rule_condition_value ('db_queue', 'classifier_3', 'db_name', 'test');
  CALL hg_set_classifier_rule_condition_value ('db_queue', 'classifier_3', 'db_name', 'postgres');

• Create a query queue with a matching rule for the HQE engine type. The following code provides an example.

  After the query queue is created, SQL requests related to the HQE engine are matched by the classifier_4 classifier and assigned to the hqe_queue query queue.

  -- Create a query queue with a concurrency of 10.
  CALL hg_create_query_queue ('hqe_queue', 10);
  
  -- Create a classifier and attach the engine_type property.
  CALL hg_create_classifier ('hqe_queue', 'classifier_4');
  CALL hg_set_classifier_rule_condition_value ('hqe_queue', 'classifier_4', 'engine_type', 'HQE');

Block all tasks (extreme scenarios)

Set the concurrency and queue size of the insert_queue query queue to 0. The following code provides an example.

CALL hg_set_query_queue_property ('insert_queue', 'max_concurrency', '0');
CALL hg_set_query_queue_property ('insert_queue', 'max_queue_size', '0');