Hologres:Run read and write tasks with Serverless Computing

Features supported by Serverless Computing

Serverless Computing supports the following task types:

• Since Hologres V2.1.17, major DML tasks are supported, such as INSERT (excluding INSERT OVERWRITE), INSERT ON CONFLICT (UPSERT), DELETE, and UPDATE.

• Since Hologres V2.2.14, Data Query Language (DQL) tasks are supported.

• Since Hologres V3.0.1, COPY tasks in DML are supported.

• Since Hologres V3.0.9, CREATE TABLE AS (CTAS) tasks in DML are supported.

• Since Hologres V2.2.42/V3.0.19, RESHARDING tasks are supported.

• Since Hologres V3.0.26, INSERT OVERWRITE tasks in DML are supported using the stored procedure hg_insert_overwrite. DML and DQL statements in stored procedures are also supported.

• Since Hologres V3.1.0, native INSERT OVERWRITE syntax in DML and REBUILD tasks are supported.

• Since Hologres V3.1.3, encrypted tables are supported for both read and write operations. These include Hologres internal encrypted tables and MaxCompute external encrypted tables. To read from or write to Hologres internal encrypted tables, you must enable the GUC parameter hg_serverless_computing_enable_encrypted_table. For more information, see Data encryption.

• Since Hologres V3.1.11, compaction tasks are supported. For more information, see Run compaction tasks with Serverless Computing.

• Since Hologres V4.0, DLF external tables are supported for both read and write operations. For supported file formats and other considerations, see Accelerate access to OSS data lakes with DLF.

Serverless Computing supports the following function extensions:

• ClickHouse

• Flow Analysis

• PostGIS

• RoaringBitmap

• BSI

• Proxima

Serverless Computing is not supported in the following cases:

• Queries on read-only secondary instances. Primary instances and virtual warehouse instances function as expected.

• Queries that use multi-statement DML transactions, such as when set hg_experimental_enable_transaction = on is enabled.

• SQL statements that use Fixed Plans.

• Cross-database queries or writes.

Run read and write tasks with Serverless Computing resources

Hologres lets you flexibly use Serverless Computing resources. You can direct individual SQL statements to Serverless resources or set rules to run batches of SQL statements on Serverless resources.

Hologres supports the following methods to route SQL statements to Serverless resources before execution. They take effect in descending order of priority:

1. SQL level (session level): Set the hg_computing_resource parameter to 'serverless'. All read and write requests in the current connection are then run on Serverless resources.

2. User level: Set the hg_computing_resource parameter to 'serverless' to execute using serverless resources.

3. Query queue level: Configure all tasks in a query queue to run on Serverless resources.

4. Automatic Serverless Computing: When enabled, the system automatically identifies large tasks and runs them on Serverless resources.

5. If none of the preceding conditions apply, the SQL statement runs on the resources of the current instance.

While an SQL statement is running, you can also configure large queries to automatically rerun on Serverless resources. This reduces the load on production systems, reduces the frequency of out-of-memory (OOM) errors, and improves system stability.

-- Use Serverless Computing resources to run SQL. The default value is 'local', which means the SQL runs on resources of the current instance.
SET hg_computing_resource = 'serverless';

-- Reset the setting after submitting DML statements to prevent unnecessary SQL statements from using Serverless resources.
RESET hg_computing_resource;

-- Data preparation --
    -- Create the source table
CREATE TABLE source_tbl ( id int );
    -- Create the sink table
CREATE TABLE sink_tbl ( id int );
    -- Insert sample data into the source table
INSERT INTO source_tbl
SELECT * FROM generate_series(1, 99999999);

-- Run SQL with Serverless Computing resources --
    -- Enable the setting at the session level
SET hg_computing_resource = 'serverless';
    -- Run the SQL statement
INSERT INTO sink_tbl SELECT * FROM source_tbl;
    -- Reset the setting
RESET hg_computing_resource;

-- Make a user use Serverless Computing resources by default for SQL statements in a specific database
ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_computing_resource = 'serverless';

-- Remove the setting
ALTER USER "<role_name>" IN DATABASE <db_name> RESET hg_computing_resource;

Set priorities for Serverless Computing tasks

Hologres lets you set priorities for SQL statements that run on Serverless Computing resources. By limiting the maximum resource usage per SQL statement and using priority-based queuing, Hologres ensures stable and orderly execution of SQL statements on Serverless Computing resources.

• Run the following command to set the priority for SQL statements on Serverless Computing resources.

  Priorities range from 1 to 5. The default is 3. A higher value indicates a higher priority and earlier resource allocation, regardless of the amount of resources requested.

  -- Configure at the session level
  SET hg_experimental_serverless_computing_query_priority = 5;
  -- Reset the setting
  RESET hg_experimental_serverless_computing_query_priority;
  
  -- Configure at the user level
  ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_experimental_serverless_computing_query_priority = 5;
  -- Reset the setting
  ALTER USER "<role_name>" IN DATABASE <db_name> RESET hg_experimental_serverless_computing_query_priority;

• Example

  -- Run SQL with Serverless Computing resources
  SET hg_computing_resource = 'serverless';
  
  -- Set the priority of this SQL to 5
  SET hg_experimental_serverless_computing_query_priority to 5;
  
  -- Run the SQL statement
  INSERT INTO sink_tbl SELECT * FROM source_tbl;
   
  -- Reset the settings
  RESET hg_computing_resource;
  RESET hg_experimental_serverless_computing_query_priority;

Verify Serverless Computing

Run the following command to view the execution plan for the SQL statement.

-- Use Serverless Computing resources to run SQL
SET hg_computing_resource = 'serverless';

-- Run the SQL statement
EXPLAIN INSERT INTO sink_tbl SELECT * FROM source_tbl;

-- Reset the setting
RESET hg_computing_resource;

The result is similar to the following. If the result contains Computing Resource: Serverless, it indicates that the SQL statement ran on Serverless Computing resources.

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Gather  (cost=0.00..5.18 rows=100 width=4)
   ->  Insert  (cost=0.00..5.18 rows=100 width=4)
         ->  Redistribution  (cost=0.00..5.10 rows=100 width=4)
               ->  Local Gather  (cost=0.00..5.10 rows=100 width=4)
                     ->  Decode  (cost=0.00..5.10 rows=100 width=4)
                           ->  Seq Scan on source_tbl  (cost=0.00..5.00 rows=100 width=4)
 Computing Resource: Serverless
 Optimizer: HQO version 2.1.0
(8 rows)

You can also view active and historical tasks on Serverless resources. For more information, see Serverless Computing monitoring and O&M.