AnalyticDB:Config and hint configuration parameters

REPLICATION_SWITCH_TIME_RANGE

Specifies the switchover time window during a cluster scale-out. During switchover, the old cluster serves read-only traffic for 5–10 minutes. Configure this parameter on the old cluster after connecting to it.

If you do not set a time window, the clusters switch over automatically after all incremental data from the old cluster is synchronized to the new cluster.

Syntax:

SET ADB_CONFIG REPLICATION_SWITCH_TIME_RANGE=`23:00, 23:30`;

See also: Scale a Data Warehouse Edition cluster

MAX_IN_ITEMS_COUNT

Sets the maximum number of values allowed in an IN condition. Must be a positive integer.

Syntax:

SET ADB_CONFIG MAX_IN_ITEMS_COUNT=4000;

See also: Writes and queries

QUERY_TIMEOUT

Sets the query timeout for all queries at the cluster level. Must be a positive integer. Unit: milliseconds (ms).

Syntax:

SET ADB_CONFIG QUERY_TIMEOUT=1000;

INSERT_SELECT_TIMEOUT

Sets the maximum execution time for INSERT, UPDATE, and DELETE statements at the cluster level.

• Default: 24×3600000 ms

• Must be a positive integer. Unit: milliseconds (ms).

Syntax:

SET ADB_CONFIG INSERT_SELECT_TIMEOUT=3600000;

See also: Limits

FILTER_NOT_PUSHDOWN_COLUMNS / NO_INDEX_COLUMNS

Disables predicate pushdown for specific columns at the cluster level. The parameter name depends on your cluster version:

Syntax:

-- Cluster version 3.1.4 and later
SET ADB_CONFIG FILTER_NOT_PUSHDOWN_COLUMNS=[Schema1.tableName1:colName1|colName2;Schema2.tableName2:colName1|colName2];

-- Earlier than 3.1.4
SET ADB_CONFIG NO_INDEX_COLUMNS=[tableName1.colName1;colName2,tableName2.colName1];

See also: Filter conditions without pushdown

QUERY_TYPE

Switches the query execution mode for the cluster.

• Valid values: interactive, batch

This parameter is not available for Data Warehouse Edition clusters in reserved mode, or for Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters.

Syntax:

SET ADB_CONFIG QUERY_TYPE=interactive;

See also: Query execution mode

QUERY_PRIORITY

Sets the scheduling priority for a BSP job.

• Valid values: HIGH, NORMAL, LOW, LOWEST

• Default: NORMAL

Syntax:

SET ADB_CONFIG QUERY_PRIORITY=HIGH;

See also: Configuration item list

ELASTIC_JOB_MAX_ACU

Sets the maximum number of ACUs (AnalyticDB Compute Units) a single BSP job can use.

• Default: 9

• Minimum: 3

• Valid range: [3, maximum ACUs of the Job-type resource group]

Syntax:

SET ADB_CONFIG ELASTIC_JOB_MAX_ACU=20;

BATCH_QUERY_TIMEOUT

Sets the timeout for BSP jobs. Must be a positive integer. Unit: milliseconds (ms).

• Default: 7200000 ms

Syntax:

SET ADB_CONFIG BATCH_QUERY_TIMEOUT=3600000;

HIVE_SPLIT_ASYNC_GENERATION_ENABLED

Controls whether Hive submits split tasks to the queue asynchronously when generating an execution plan.

• Valid values: true, false

• Default: false

Requires cluster version 3.1.10.1 or later.

Syntax:

SET ADB_CONFIG HIVE_SPLIT_ASYNC_GENERATION_ENABLED=true;

SQL_OUTPUT_BATCH_SIZE

Sets the number of rows per batch when importing data from a MaxCompute external table. Must be a positive integer.

Syntax:

SET ADB_CONFIG SQL_OUTPUT_BATCH_SIZE=6000;

See also: Import and export

ENABLE_ODPS_MULTI_PARTITION_PART_MATCH

Controls whether the system pre-traverses MaxCompute partitions to get the record count for each partition before query execution.

• Valid values: true, false

Syntax:

SET ADB_CONFIG ENABLE_ODPS_MULTI_PARTITION_PART_MATCH=false;

ASYNC_GET_SPLIT

Controls whether MaxCompute submits split-loading tasks to the queue asynchronously when generating an execution plan.

• Valid values: true, false

• Default: false

Requires cluster version 3.1.10.1 or later.

Syntax:

SET ADB_CONFIG ASYNC_GET_SPLIT=true;

RC_CSTORE_BUILD_SCHEDULE_PERIOD

Limits the time range during which BUILD tasks can be scheduled (not executed). Must be an integer between 0 and 24.

Syntax:

SET ADB_CONFIG RC_CSTORE_BUILD_SCHEDULE_PERIOD=`6,8`;

See also: Configure automatic BUILD scheduling times

RC_BUILD_TASK_PRIORITY_LIST

Sets the scheduling priority for BUILD jobs on one or more tables. The priority stays in effect until you set a new one.

• The priority is controlled by the task_priority parameter. Must be an integer.

• Default: 0

Syntax:

SET ADB_CONFIG RC_BUILD_TASK_PRIORITY_LIST=`<db1_name>.<table1_name>.<task_priority>;<db2_name>.<table2_name>.<task_priority>`;

See also: BUILD

RC_ELASTIC_JOB_SCHEDULER_ENABLE

Manually enables the elastic import feature.

Syntax:

SET ADB_CONFIG RC_ELASTIC_JOB_SCHEDULER_ENABLE=true;

See also:

• Use external tables to import data from OSS

• Elastic import of MaxCompute data through external tables

SPLIT_FLOW_CONTROL_ENABLED

Enables or disables scan concurrency control at the cluster level.

• Valid values: true, false

• Default: true

Syntax:

SET ADB_CONFIG SPLIT_FLOW_CONTROL_ENABLED=true;

See also: Scan concurrency control

NODE_LEVEL_SPLIT_FLOW_CONTROL_ENABLED

Enables dynamic adjustment of per-task scan concurrency based on the node's overall scan concurrency quota.

• Valid values: true, false

• Default: false

Syntax:

SET ADB_CONFIG NODE_LEVEL_SPLIT_FLOW_CONTROL_ENABLED=true;

Scan concurrency quotas

Syntax:

SET ADB_CONFIG MIN_RUNNING_SPLITS_LIMIT_PER_TASK=24;
SET ADB_CONFIG TARGET_RUNNING_SPLITS_LIMIT_PER_TASK=32;
SET ADB_CONFIG MAX_RUNNING_SPLITS_LIMIT_PER_TASK=128;

VIEW_OUTPUT_NAME_CASE_SENSITIVE

Controls whether column name matching for logical views is case-sensitive.

• Valid values: true (case-sensitive), false (case-insensitive)

• Default: false

Syntax:

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

See also: CREATE VIEW

ALLOW_MULTI_QUERIES

Enables executing multiple SQL statements in a single request using the Multi-Statement feature. Must be enabled manually before use.

• Valid values: true, false

• Default: false

Syntax:

SET ADB_CONFIG ALLOW_MULTI_QUERIES=true;

See also: Multi-Statement

BINLOG_ENABLE

Enables binary logging at the cluster level.

• For clusters running a version earlier than 3.2.0.0: manually enable this parameter before enabling binary logging for individual tables.

• For clusters running version 3.2.0.0 or later: binary logging is enabled by default. This parameter is not required.

Syntax:

SET ADB_CONFIG BINLOG_ENABLE=true;

See also: Refresh materialized view

RC_DDL_ENGINE_REWRITE_XUANWUV2

Sets the default table engine for newly created tables at the cluster level.

Syntax:

SET ADB_CONFIG RC_DDL_ENGINE_REWRITE_XUANWUV2=true;

See also: Specify a table engine

QUERY_TIMEOUT

Sets the query timeout for a single query. Must be a positive integer. Unit: milliseconds (ms).

Syntax:

/*+ QUERY_TIMEOUT=1000 */ SELECT COUNT(*) FROM t;

See also: Writes and queries

INSERT_SELECT_TIMEOUT

Sets the maximum execution time for INSERT, UPDATE, and DELETE statements for a single query.

• Default: 24×3600000 ms

• Must be a positive integer. Unit: milliseconds (ms).

Syntax:

/*+ INSERT_SELECT_TIMEOUT=3600000 */ UPDATE customer SET customer_name='adb' WHERE customer_id='2369';

See also: Limits

MAX_SELECT_ITEMS_COUNT

Sets the maximum number of columns allowed in a SELECT clause.

• Default: 1024

• Must be a positive integer.

Syntax:

/*+ MAX_SELECT_ITEMS_COUNT=2048*/

MAX_IN_ITEMS_COUNT

Sets the maximum number of values allowed in an IN condition for a single query.

• Default: 4000

• Must be a positive integer.

Syntax:

/*+ MAX_IN_ITEMS_COUNT=1000*/

MAX_WHERE_ITEMS_COUNT

Sets the maximum number of conditions allowed in a WHERE clause.

• Default: 256

• Must be a positive integer.

Syntax:

/*+ MAX_WHERE_ITEMS_COUNT=512*/

QUERY_MAX_SHUFFLE_DATA_SIZE_MB

Sets the maximum shuffle data volume for a query. If the query exceeds this threshold, the system terminates it with an error. By default, no limit is enforced. Must be a positive integer. Unit: MB.

Syntax:

/*+ QUERY_MAX_SHUFFLE_DATA_SIZE_MB=1024*/

FILTER_NOT_PUSHDOWN_COLUMNS / NO_INDEX_COLUMNS

Disables predicate pushdown for specific columns at the query level. The parameter name depends on your cluster version:

Syntax:

-- Cluster version 3.1.4 and later
/*+ FILTER_NOT_PUSHDOWN_COLUMNS=[Schema1.table1:colName1|colName2;Schema2.table2:colName1|colName2] */

-- Earlier than 3.1.4
/*+ NO_INDEX_COLUMNS=[tableName1.colName1;colName2,tableName2.colName1] */

See also: Filter conditions without pushdown

BUILD_TASK_PRIORITY

Sets the scheduling priority for a BUILD task on a single table. A higher value means higher priority.

• Default: 0

• Must be an integer >= 0.

This hint can only set the priority for one table at a time. To set priorities for multiple tables, use the RC_BUILD_TASK_PRIORITY_LIST config parameter instead.

Syntax:

/*+ BUILD_TASK_PRIORITY=2 */ BUILD TABLE db.table;

See also: Configure BUILD scheduling priority

ELASTIC_LOAD and ELASTIC_LOAD_CONFIGS

Enables elastic import and specifies the Job resource group for executing the import task.

Syntax:

/*+ ELASTIC_LOAD=true, ELASTIC_LOAD_CONFIGS=[adb.load.resource.group.name=resource_group]*/
SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

See also:

• Use external tables to import data from OSS

• Use external tables to import data from MaxCompute

O_CBO_RULE_SWAP_OUTER_JOIN / LEFT_TO_RIGHT_ENABLED

Instructs the optimizer to convert a Left Join to a Right Join when the right table is large, reducing memory consumption. The parameter name depends on your cluster version:

• Valid values: true, false

• Default: false

Syntax:

/*+ O_CBO_RULE_SWAP_OUTER_JOIN=true*/
/*+ LEFT_TO_RIGHT_ENABLED=true*/

See also: Change LEFT JOIN to RIGHT JOIN

REORDER_JOINS

Controls automatic join order adjustment. When disabled, queries execute in the join order as written in the SQL statement.

• Valid values: true, false

• Default: true

Syntax:

/*+ REORDER_JOINS=false*/

See also: Manually adjust join order

AGGREGATION_PATH_TYPE

Controls whether the optimizer skips partial aggregation and goes directly to final aggregation. Useful when the number of groups in a groupby query is large and partial aggregation consumes excessive resources.

• Valid values: single_agg (skip partial aggregation), auto (optimizer decides)

• Default: auto

Syntax:

/*+ AGGREGATION_PATH_TYPE=single_agg*/

See also: Grouping and aggregation query optimization

HASH_PARTITION_COUNT

Sets the number of concurrent tasks for compute operations. Must be a positive integer.

• Default: 32

Syntax:

/*+ HASH_PARTITION_COUNT=32*/

TASK_WRITER_COUNT

Sets the write concurrency for INSERT INTO SELECT import tasks. Reduce this value if worker load is too high. Must be a positive integer.

• Default: 16

Syntax:

/*+ TASK_WRITER_COUNT=8*/

CTE_EXECUTION_MODE

Controls whether Common Table Expression (CTE) subqueries that are referenced multiple times execute only once (shared mode) or each time they are referenced (inline mode).

• Valid values: shared (execute once), inline (execute each time)

• Default: inline

Syntax:

/*+ CTE_EXECUTION_MODE=shared*/

See also: WITH

QUERY_PRIORITY

Sets the scheduling priority for a BSP job at the query level.

• Valid values: HIGH, NORMAL, LOW, LOWEST

• Default: NORMAL

Syntax:

/*+ QUERY_PRIORITY=HIGH*/

See also: Configuration item list

ELASTIC_JOB_MAX_ACU

Sets the maximum number of ACUs a single BSP job can use at the query level.

• Default: 9

• Minimum: 3

• Valid range: [3, maximum ACUs of the Job-type resource group]

Syntax:

/*+ ELASTIC_JOB_MAX_ACU=20*/

BATCH_QUERY_TIMEOUT

Sets the timeout for a BSP job at the query level. Must be a positive integer. Unit: milliseconds (ms).

• Default: 7200000 ms

Syntax:

/*+ BATCH_QUERY_TIMEOUT=3600000*/

OUTPUT_FILENAME

Customizes the file name when exporting data to an OSS external table. Add this hint before the export statement.

Syntax:

/*+ OUTPUT_FILENAME=output.csv*/

See also: Export to OSS

SQL_OUTPUT_OSS_FILE_HEAD_ENABLE

Controls whether exported OSS files include column names. By default, exported files contain data only.

• Valid values: true, false

• Default: false

Syntax:

/*+ SQL_OUTPUT_OSS_FILE_HEAD_ENABLE=true*/

HIVE_SPLIT_ASYNC_GENERATION_ENABLED

Controls whether Hive submits split tasks asynchronously when generating an execution plan.

• Valid values: true, false

• Default: false

Requires cluster version 3.1.10.1 or later.

Syntax:

/*+ HIVE_SPLIT_ASYNC_GENERATION_ENABLED=true*/

See also: Import OSS data using external tables

FAST_PARQUET_READER_ENABLE

Falls back to the native Parquet reader when statistics in Parquet files are incorrect.

• Valid values: true, false

• Default: true

Syntax:

/*+ FAST_PARQUET_READER_ENABLE=true*/

OSS_VECTORIZED_PARQUET_READER_ENABLE

Enables vectorized Parquet reading for data that contains no NULL values.

• Valid values: true, false

• Default: false

Syntax:

/*+ OSS_VECTORIZED_PARQUET_READER_ENABLE=true*/

OSS_VECTORIZED_PARQUET_READER_NULL_SCENE_ENABLE

Enables vectorized Parquet reading for data that contains NULL values.

• Valid values: true, false

• Default: false

Syntax:

/*+ OSS_VECTORIZED_PARQUET_READER_NULL_SCENE_ENABLE=true*/

HUDI_METADATA_TABLE_ENABLED

Controls whether Hudi metadata is loaded when querying Hudi tables.

• Valid values: true, false

• Default: false

Syntax:

/*+ HUDI_METADATA_TABLE_ENABLED=true*/

HUDI_QUERY_TYPE

Sets the query method for Hudi tables.

Syntax:

/*+ HUDI_QUERY_TYPE=snapshot*/

HUDI_REALTIME_SKIP_MERGE

In snapshot query mode, controls whether to skip merging records from base files and log files.

• Valid values: true, false

• Default: false

Syntax:

/*+ HUDI_REALTIME_SKIP_MERGE=true*/

Hudi split and performance settings

Syntax:

/*+ HUDI_MAX_SPLIT_SIZE=128*/
/*+ HUDI_SPLIT_PARALLEL_GENERATION_ENABLED=true*/
/*+ HUDI_DATA_SKIPPING_ENABLED=true*/
/*+ HUDI_SPLIT_ASYNC_GENERATION_ENABLED=true*/

JDBC_SCAN_SPLITS

Sets the number of splits during a Java Database Connectivity (JDBC)-based table scan.

• Default: 1

• Valid range: 1–100

Syntax:

/*+ JDBC_SCAN_SPLITS=1*/

See also: Use external tables to import data from RDS for MySQL

JDBC_SPLIT_COLUMN

Specifies the column used to split data when reading an ApsaraDB RDS for MySQL external table.

Syntax:

/*+ JDBC_SPLIT_COLUMN=column1*/

JDBC_INSERT_DIRECTLY

Writes data directly to the target table instead of staging it in a temporary table first. Direct writes are faster but may leave dirty data in the target table if an error occurs.

• Valid values: true, false

• Default: false

Syntax:

/*+ JDBC_INSERT_DIRECTLY=true*/

OTS-INSERT-AS-UPDATE

Uses INSERT statements instead of UPDATE statements when writing to a Tablestore external table.

• Valid values: true, false

• Default: false

Syntax:

/*+ OTS-INSERT-AS-UPDATE=true*/

See also: Query and import Tablestore data

ODPS_SPLIT_LIMIT

Sets the maximum number of splits when reading MaxCompute data via Tunnel.

• Default: 1000

• Valid range: 1–1000

Syntax:

/*+ ODPS_SPLIT_LIMIT=1000*/

See also: Import MaxCompute data using external tables

IGNORE_PARTITION_CACHE

Queries partition metadata directly from the MaxCompute table instead of using cached metadata. Useful when partition data has changed since the cache was populated.

• Valid values: true, false

• Default: false

Syntax:

/*+ IGNORE_PARTITION_CACHE=true*/

ROWS_PER_SPLIT

Sets the maximum number of rows a single split can read via Tunnel.

• Default: 500000

• Valid range: 10000–500000

Syntax:

/*+ ROWS_PER_SPLIT=500000*/

STORAGE_API_ENABLED

Uses the MaxCompute Storage API instead of Tunnel to read MaxCompute data. The Storage API significantly improves read performance.

• Valid values: true, false

• Default: false

Syntax:

/*+ STORAGE_API_ENABLED=true*/

MaxCompute Storage API split settings

All parameters below apply when reading data via the MaxCompute Storage API.

Syntax:

/*+ APLIT_BYTE_SIZE=256*/
/*+ MAX_BATCH_ROW_COUNT=1024*/
/*+ PAGE_BATCH_SIZE=1024*/
/*+ MAX_ALLOCATION_PER_SPLIT=300*/
/*+ ASYNC_GET_SPLIT=true*/

SPLIT_FLOW_CONTROL_ENABLED

Enables or disables scan concurrency control at the query level.

• Valid values: true (enable), false (disable)

• Default: true

Syntax:

/*+ SPLIT_FLOW_CONTROL_ENABLED=true*/ SELECT * FROM table;

See also: Scan concurrency control

Scan concurrency quotas (query-level)

PAGING_ID

Enables paging cache and manually specifies a paging_id to group related paged queries (queries with the same pattern that differ only in LIMIT and OFFSET values).

Syntax:

/*PAGING_ID=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

See also: Paging cache

PAGING_CACHE_ENABLED

Enables paging cache and automatically generates a paging_id based on the SQL pattern.

• Valid values: true, false

• Default: false

Syntax:

/*PAGING_CACHE_ENABLED=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

PAGING_CACHE_VALIDITY_INTERVAL

Sets the cache validity period in seconds. After this period, the cache expires. If the cache is not accessed within one hour of expiration, the system deletes the corresponding temporary table. Must be a positive integer. Use with PAGING_CACHE_ENABLED.

Syntax:

/*PAGING_CACHE_ENABLED=true, PAGING_CACHE_VALIDITY_INTERVAL=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

INVALIDATE_PAGING_CACHE

Clears the paging cache for a specified query pattern. Use with PAGING_ID or PAGING_CACHE_ENABLED.

• Valid values: true, false

• Default: false

Syntax:

/*PAGING_CACHE_ENABLED=true, INVALIDATE_PAGING_CACHE=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

RC_DDL_ENGINE_REWRITE_XUANWUV2

Specifies the table engine for the table created by the current SQL statement.

Syntax:

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=true */

See also: Specify table engine

RESOURCE_GROUP

Specifies the resource group for a query. If not set, the resource group bound to the database account is used. If the account has no bound resource group, the default resource group is used.

Syntax:

/*+ RESOURCE_GROUP=my_group_name*/

DML_EXCEPTION_TO_NULL_ENABLED

Controls how invalid values in INSERT SELECT statements are handled.

• Valid values: true (write NULL instead of throwing an error), false (throw an error)

• Default: false

Syntax:

/*+ DML_EXCEPTION_TO_NULL_ENABLED=true*/

DISPLAY_HINT

Tags a query with a label for identification, such as marking queries from different business teams.

Syntax:

/*+ DISPLAY_HINT=from_user_123*/