SETPROJECT

Updated at:

SETPROJECT views and sets project-level properties in MaxCompute. Use it to inspect all configurable parameters or update project and session settings such as scheduling resources, SQL behavior, security policies, and data types.

Platforms

Run SETPROJECT on the MaxCompute client (odpscmd), Cloud Shell (odpscmd), or the ODPS SQL node of DataWorks.

Usage notes

  • A MaxCompute project is not the same as a DataWorks workspace. To find your project name, Log in to the MaxCompute console and select a region in the upper-left corner. In the left-side navigation pane, choose Manage Configurations > Projects.

  • SETPROJECT cannot create or delete projects. To create a project, see Create a MaxCompute project.

View project properties

Returns all project-level properties and their current values.

For session-level properties, use the SHOW FLAGS command instead. See the "SHOW FLAGS" section in SET operations.

Syntax

setproject;

Properties

The following table lists common project properties.

The Scope column indicates whether a property takes effect for the current session only (Session) or for the entire project (Project). Session-scoped properties apply only for the duration of the current connection and are lost when the session ends. Project-scoped changes apply globally to all sessions.

Category

Scope

Parameter

Description

Valid values

Affected statements

Ecosystem

Session

console.sql.result.instancetunnel

Enables or disables InstanceTunnel. See Usage notes for details on Tunnel commands.

True: enabled. False: disabled.

SELECT

Scheduling

Session

odps.stage.mapper.mem

Memory allocated to each Map worker.

256 MiB–12288 MiB. Default: 1024 MiB.

ALL

Scheduling

Session

odps.stage.reducer.mem

Memory allocated to each Reduce worker.

256 MiB–12288 MiB. Default: 1024 MiB.

ALL

Scheduling

Session

odps.stage.joiner.mem

Memory allocated to each Join worker.

256 MiB–12288 MiB. Default: 1024 MiB.

ALL

Scheduling

Session

odps.stage.mem

Total memory for all workers in a job. Lower priority than odps.stage.mapper.mem, odps.stage.reducer.mem, and odps.stage.joiner.mem.

256 MiB–12288 MiB. No default.

ALL

Scheduling

Session

odps.stage.mapper.split.size

Input data size per Map worker (split size). Controls the number of workers at the Map stage. If the SQL statement uses LIMIT, concurrent workers are capped at 1; avoid using LIMIT when setting this parameter.

Default: 256 MiB.

ALL

Scheduling

Session

odps.sql.split.size

Shard size for table-level parallel processing. Example: {"table1": 1024, "table2": 512}

Unit: MiB.

ALL

Scheduling

Session

odps.sql.split.row.count

Row count per shard for table-level parallel processing. Applies only to internal, non-transactional, non-clustered tables. Example: {"table1": 100, "table2": 500}

Positive integer.

ALL

Scheduling

Session

odps.sql.split.dop

Parallelism level for table-level processing. Applies only to internal, non-transactional, non-clustered tables. Example: {"table1": 1, "table2": 5}

Positive integer.

ALL

Scheduling

Session

odps.stage.reducer.num

Number of workers at each Reduce stage. If the SQL statement uses LIMIT, concurrent workers are capped at 1.

—

ALL

Scheduling

Session

odps.stage.joiner.num

Number of workers at each Join stage. If the SQL statement uses LIMIT, concurrent workers are capped at 1.

—

ALL

Scheduling

Session

odps.stage.num

Total concurrent workers in a job. Lower priority than odps.stage.mapper.split.size, odps.stage.reducer.num, and odps.stage.joiner.num. If the SQL statement uses LIMIT, concurrent workers are capped at 1.

—

ALL

Scheduling

Project

odps.instance.priority.enable

Enables job priority for projects that use subscription computing resources. See Job priority. Only the project owner or a user with the Super_Administrator role can run this command.

True: enabled. False: disabled.

ALL

SQL

Session

odps.sql.reshuffle.dynamicpt

Enables dynamic partitioning to prevent excessive small files. If only a small number of dynamic partitions are generated, set this to False to avoid data skew.

True: enabled. False: disabled.

INSERT INTO, INSERT OVERWRITE

SQL

Session

odps.sql.udf.getjsonobj.new

Controls whether GET_JSON_OBJECT retains the original string in its return value. Projects created on or after January 21, 2021 return original strings by default. Earlier projects return JSON reserved characters as escape characters by default. See String functions.

True: enabled. False: disabled.

UDF

SQL

Session

odps.sql.udf.jvm.memory

Maximum Java Virtual Machine (JVM) heap memory for a user-defined function (UDF). Increase this value if large in-memory sorts cause out-of-memory (OOM) errors, but optimize UDF code to address the root cause.

256 MiB–12288 MiB. Default: 1024 MiB.

UDF

SQL

Session

odps.function.timeout

Timeout period for a UDF.

0s–3600s. Default: 600s.

UDF

SQL

Session

odps.sql.session.resources

Resources referenced by a user-defined type (UDT). Separate multiple resources with commas. See Overview.

Uploaded resources.

UDT

SQL

Session

odps.sql.udt.display.tostring

Enables the java.util.Objects.toString(...) mechanism on the Wrap for all UDT output columns.

True: enabled. False: disabled.

UDT

SQL

Session

odps.sql.session.java.imports

Java packages referenced by UDTs. Separate multiple packages with commas. See Overview.

Uploaded Java packages.

UDT

SQL

Session

odps.sql.skewjoin

Enables SKEWJOIN to prevent long tail issues.

True: enabled. False: disabled.

SELECT, JOIN

SQL

Session

odps.sql.skewinfo

The key and value on which SKEWJOIN operates. See Data skew tuning.

—

SELECT, JOIN

SQL

Session

odps.sql.udf.ppr.deterministic

Enables partition pruning for UDFs. See WHERE clause.

True: enabled. False: disabled.

UDF

SQL

Session

odps.sql.udf.ppr.to.subquery

Ignores errors during result backfilling when performing partition pruning. See SELECT syntax.

True: enabled. False: disabled.

UDF

SQL

Session

odps.optimizer.enable.range.partial.repartitioning

Enables the Shuffle Remove feature for range-clustered tables.

True: enabled. False: disabled.

INSERT OVERWRITE, CREATE TABLE

SQL

Session

odps.optimizer.merge.partitioned.table

When a query uses the same partitioned table multiple times, merges the table reads to minimize IO and improve performance.

True: enabled. False: disabled.

SELECT

SQL

Session

odps.optimizer.skew.join.topk.num

Number of hot key values the optimizer fetches when performing aggregate operations. See SKEWJOIN HINT.

—

SKEWJOIN HINT

SQL

Session

odps.optimizer.stat.collect.auto

Enables the Freeride feature, which automatically collects column statistics. See Optimizer.

True: enabled. False: disabled.

CREATE TABLE, INSERT INTO, INSERT OVERWRITE

SQL

Session

odps.optimizer.stat.collect.plan

A collection plan that specifies which column statistics to gather. See Optimizer.

—

CREATE TABLE, INSERT INTO, INSERT OVERWRITE

SQL

Session

odps.sql.external.net.vpc

Enables virtual private cloud (VPC) access for external tables. See Hologres foreign tables.

True: enabled. False: disabled.

CREATE TABLE

SQL

Session

odps.sql.groupby.position.alias

Allows integer constants in GROUP BY to reference column positions in SELECT.

True: enabled. False: disabled.

SELECT, GROUP BY

SQL

Session

odps.sql.groupby.skewindata

Enables the anti-skew feature for GROUP BY.

True: enabled. False: disabled.

GROUP BY

SQL

Session

odps.sql.orderby.position.alias

Allows integer constants in ORDER BY to reference column positions in SELECT.

True: enabled. False: disabled.

ORDER BY, SELECT

SQL

Session

odps.sql.mapjoin.memory.max

Memory size for small tables loaded into memory during MAPJOIN. The total global limit across all small tables in a task equals std::min(8G, odps.sql.mapjoin.memory.max × number of small tables).

0 MiB–8192 MiB.

JOIN

SQL

Session

odps.sql.distributed.map.join.memory.max

Memory per HashTableBuilder instance during MAPJOIN. Default: 2048 MiB.

0 MiB–8192 MiB.

JOIN

SQL

Session

odps.sql.python.version

Python version used when executing SQL statements.

cp27, cp37

UDTF, UDAF

SQL

Session

odps.sql.select.output.format

Controls whether the MaxCompute client displays column headers in SELECT results. See MaxCompute client (odpscmd).

{"needHeader":false,"fieldDelim":""}: no headers. {"needHeader":true,"fieldDelim":""}: with headers.

ALL

SQL

Session

odps.sql.unstructured.data.oss.use.https

Enables HTTPS when accessing external tables at the underlying layer. See Create an OSS external table.

True: enabled. False: disabled.

ALL

SQL

Session

odps.sql.decimal.tostring.trimzero

Removes trailing zeros after the decimal point for the DECIMAL type. Default: True.

True: trailing zeros removed. False: trailing zeros retained.

CAST

SQL

Session

odps.sql.unstructured.tablestore.put.row

Calls the PutRow operation to write data to a Tablestore external table. See PutRow.

True: enabled. False: disabled.

ALL

SQL

Session

odps.sql.unstructured.external.max.dop

Maximum concurrency of workers accessing an external table.

Positive integer.

External table queries and writes

SQL

Session/Project

odps.sql.unstructured.file.pattern.black.list

Ignores files matching a pattern when reading external tables in Object Storage Service (OSS) or Hadoop Distributed File System (HDFS). Use this to skip _SUCCESS files written by Spark. Example at session level: SET odps.sql.unstructured.file.pattern.black.list=.*_SUCCESS$;

.*_SUCCESS$

External table queries

SQL

Session

odps.meta.exttable.stats.onlinecollect

Enables the optimizer to collect statistics on external tables temporarily during query execution. This helps the optimizer discover small tables, apply Hash Join, optimize join order, and reduce shuffle operations. Default: False.

True: enabled. False (default): disabled.

External table queries

SQL

Project

odps.sql.allow.fullscan

Enables full table scans on the project. Full table scans consume significant resources and reduce processing efficiency. Disable this in production unless required.

True: enabled. False: disabled.

SELECT

SQL

Project

odps.table.lifecycle

Sets the lifecycle requirement for tables created in the project.

Optional: the LIFECYCLE clause is optional. Mandatory: the LIFECYCLE clause is required. Inherit: tables without an explicit lifecycle inherit the value of odps.table.lifecycle.value.

CREATE TABLE

SQL

Project

odps.table.lifecycle.value

Default lifecycle for tables, in days. Applies when odps.table.lifecycle is set to Inherit.

1–37231. Default: 37231.

CREATE TABLE

SQL

Project

READ_TABLE_MAX_ROW

Maximum number of rows returned by a SELECT statement.

1–10000. Default: 10000.

SELECT

SQL

Project

odps.output.field.formatter

Dynamic data masking rules applied to SQL query results. See Dynamic data masking.

Custom masking rules.

SELECT

SQL

Project

odps.sql.acid.table.enable

Enables the atomicity, consistency, isolation, and durability (ACID) mechanism. See ACID semantics.

True: enabled. False: disabled.

ALL

SQL

Session/Project

odps.io.oss.use.vipserver

Routes OSS access through VIPServer instead of Server Load Balancer (SLB). VIPServer supports higher throughput with flow control, but may introduce query failures. Evaluate stability tradeoffs before enabling. Default: False.

True: enabled. False (default): disabled.

External table queries

SQL

Session/Project

odps.sql.executionengine.enable.string.to.date.full.format

Enables conversion of date strings that include hour, minute, and second parts. Default: False.

True: enabled. False: disabled.

CAST

SQL

Session/Project

odps.sql.executionengine.enable.rand.time.seed

Controls the seed used by the RAND function. False (default): uses the instance ID as the seed, ensuring idempotence. True: uses system time, making the result non-idempotent and variable across calls.

True: enabled. False: disabled.

RAND

SQL

Session/Project

odps.sql.type.system.odps2

Enables the MaxCompute V2.0 data type edition. See MaxCompute V2.0 data type edition.

True: enabled. False: disabled.

MaxCompute V2.0 additional functions

SQL

Session/Project

odps.sql.type.json.enable

Enables JSON data types. See Instructions for using JSON data types in MaxCompute.

True: enabled. False: disabled.

MaxCompute JSON data types

SQL

Session/Project

odps.sql.hive.compatible

Enables the Hive-compatible data type edition. Required for Hive syntaxes such as inputRecordReader, outputRecordReader, and Serde. See Hive-compatible data type edition.

True: enabled. False: disabled.

ALL

SQL

Session/Project

odps.sql.metering.value.max

Upper limit on resources consumed by a single SQL statement. See Consumption control.

—

ALL

SQL

Session/Project

odps.sql.timezone

Time zone of the MaxCompute project. See Time zone configuration operations.

—

ALL

SQL

Session/Project

odps.sql.unstructured.oss.commit.mode

Enables OSS multipart upload when writing data to OSS external tables. See Write data to OSS.

True: enabled. False: disabled.

INSERT OVERWRITE

SQL

Session/Project

odps.sql.groupby.orderby.position.alias

Allows integer constants in GROUP BY and ORDER BY to reference column positions in SELECT. Setting this to True for an existing project may break existing data parsing logic. Test thoroughly before enabling at the project level.

True: enabled. False: disabled.

GROUP BY, ORDER BY, SELECT

SQL

Session/Project

odps.ext.oss.orc.native

Uses a native Optimized Row Columnar (ORC) reader to read table data.

True: enabled. False: disabled.

SELECT

SQL

Session

odps.sql.job.max.time.hours

Maximum duration for a single job, in hours.

1–72. Default: 24.

SQL jobs

Billing

Session

odps.task.quota.preference.tag

Quota group assigned to a job. The job is scheduled to the quota group whose tag matches. If no match is found, the project's default quota group is used. Applies to SQL jobs only. Example: set odps.task.quota.preference.tag = payasyougo; The tag name can contain letters, digits, and underscores.

Tag name of a quota group configured in MaxCompute Management. You can configure the tag name only for quota groups in the region where the project of your job belongs. The tag name can contain letters, digits, and underscores (_).

ALL

Billing

Session

odps.task.wlm.quota

Name of the quota used to run jobs in a session.

—

ALL

Security and permissions

Project

odps.forbid.fetch.result.by.bearertoken

Controls whether job results appear on the Result tab of LogView.

True: results are hidden. False: results are displayed.

ALL

Security and permissions

Project

LabelSecurity

Enables label-based access control (LabelSecurity). See Label-based access control.

True: enabled. False: disabled.

ALL

Security and permissions

Project

CheckPermissionUsingACL

Enables ACL-based access control. See MaxCompute permissions.

True: enabled. False: disabled.

ALL

Security and permissions

Project

CheckPermissionUsingPolicy

Enables policy-based access control. See Policy-based access control.

True: enabled. False: disabled.

ALL

Security and permissions

Project

ObjectCreatorHasAccessPermission

Grants object creators access to the objects they create.

True: enabled. False: disabled.

ALL

Security and permissions

Project

ObjectCreatorHasGrantPermission

Grants object creators the ability to authorize other users to access the objects they create.

True: enabled. False: disabled.

ALL

Security and permissions

Project

ProjectProtection

Enables project data protection. See Project data protection.

True: enabled. False: disabled.

ALL

Security and permissions

Project

odps.output.field.formatter

Dynamic data masking rules applied to SQL query results. See Dynamic data masking.

Custom masking rules.

SELECT

Security and permissions

Project

odps.security.ip.whitelist

IP address whitelist for accessing the project over the cloud product interconnection network. Separate IP addresses with commas. See Manage IP address whitelists.

Comma-separated IP addresses.

ALL

Security and permissions

Project

odps.security.vpc.whitelist

IP address whitelist for accessing the project over a specific VPC. See Manage IP address whitelists.

RegionID_VPCID[IP Address]

ALL

Data type

Project

odps.sql.decimal.odps2

Enables DECIMAL(precision,scale) in the MaxCompute V2.0 data type edition. See MaxCompute V2.0 data type edition.

True: enabled. False: disabled.

ALL

Data storage

Project

odps.timemachine.retention.days

Retention period for backup data, in days. See Backup and restoration.

0–30. Default: 1.

ALL

Metadata

Project

odps.schema.evolution.enable

Enables schema evolution. Default: False.

True: enabled. False (default): disabled.

Schema evolution

Set project properties

Sets project-level properties. Changes take effect within 5 minutes; check results after 5 minutes.

To set session-level properties instead, use the SET statement. See the "SET" section in SET operations.

Permissions

Only the project owner or a user assigned the Super_Administrator or Admin project-level role can set project-level properties. See Assign a built-in administrator role to a user.

Syntax

setproject <KEY>=<VALUE>;

Parameters

Parameter

Description

KEY

The property name.

VALUE

The property value. For valid values, see View project properties.

Example

Enable full table scans for a project:

setproject odps.sql.allow.fullscan=true;

What's next