MaxCompute allows you to set system variables for sessions. This topic describes how to set and view the system variables, which can affect MaxCompute operations.

The following table describes the statements that are used for SET operations.
Operation Description Role Platform
set Sets MaxCompute system variables for the current session. Users who have operation permissions on projects You can execute these statements on the following platforms:
show flags Displays the properties that you configured by using the SET statement.

set

The SET statement allows you to set MaxCompute system variables for the current session. MaxCompute also allows you to set properties for projects. For more information, see Set project properties.

  • Syntax
    set <KEY>=<VALUE>
  • Parameter
    • KEY: the name of the property.
    • VALUE: the value of the property.
      The following table describes common properties at the session level.
      Property name Description Valid value
      console.sql.result.instancetunnel Specifies whether to enable InstanceTunnel. For more information, see Usage notes.
      • True: InstanceTunnel is enabled.
      • False: InstanceTunnel is disabled.
      odps.stage.mapper.mem Sets the memory size of each Map worker. Unit: MB. Default value: 1024.
      odps.stage.reducer.mem Sets the memory size of each Reduce worker. Unit: MB. Default value: 1024.
      odps.stage.joiner.mem Sets the memory size of each Join worker. Unit: MB. Default value: 1024.
      odps.stage.mem Sets the total memory size of all workers in a specified MaxCompute job. This property has a lower priority than the odps.stage.mapper.mem, odps.stage.reducer.mem, and odps.stage.joiner.mem properties. Unit: MB. No default value.
      odps.stage.mapper.split.size Changes the input data volume of each Map worker, which is the split size of the input file. You can use this property to indirectly control the number of workers at each Map stage. Unit: MB. Default value: 256.
      odps.stage.reducer.num Changes the number of workers at each Reduce stage. No default value.
      odps.stage.joiner.num Changes the number of workers at each Join stage. No default value.
      odps.stage.num Changes the total number of concurrent workers in a specified MaxCompute job. This property has a lower priority than the odps.stage.mapper.split.size, odps.stage.reducer.mem, and odps.stage.joiner.num properties. No default value.
      odps.sql.reshuffle.dynamicpt Specifies whether to enable dynamic partitioning. Dynamic partitioning prevents the generation of a large number of small files.
      • True: Dynamic partitioning is enabled.
      • False: Dynamic partitioning is disabled.
      Note If a small number of dynamic partitions are generated, we recommend that you set this parameter to False to prevent data skew.
      odps.sql.type.system.odps2 Specifies whether to enable the MaxCompute V2.0 data type edition. For more information about the MaxCompute V2.0 data type edition, see MaxCompute V2.0 data type edition.
      • True: The MaxCompute V2.0 data type edition is enabled.
      • False: The MaxCompute V2.0 data type edition is disabled.
      odps.sql.hive.compatible Specifies whether to enable the Hive-compatible data type edition. MaxCompute supports Hive syntax, such as inputRecordReader, outputRecordReader, and Serde, only after the Hive-compatible data type edition is enabled. For more information about the Hive-compatible data type edition, see Hive-compatible data type edition.
      • True: The Hive-compatible data type edition is enabled.
      • False: The Hive-compatible data type edition is disabled.
      odps.sql.executionengine.coldata.deep.buffer.size.max Sets the cache size requested to store a column that contains complex data types when MaxCompute writes data to a table. This improves the write performance.
      You can set this parameter if the output table contains excessive complex data types or the size of a variable of a specific complex data type in the output table is too large.
      • If the schemas of three columns in the output table are of complex data types, such as STRING, MAP, STRUCT, ARRAY, and BINARY, MaxCompute reserves 192 MB of memory (64 × 3) for table write operations by default. The cache requested for each column is used to store data in the row that are specified by batch row count.
      • If you estimate that the size of the variables of complex data types in the table is small, we recommend that you set this parameter to a small value. For example, if the size of each variable of a specific complex data type does not exceed 1,024 bytes and the default value (1024) of batch row count is used, you can set the value to 1048576 (1024 × 1024).
        set odps.sql.executionengine.coldata.deep.buffer.size.max=1048576;
      • If you estimate that the size of each variable of a complex data type is between 7 MB and 8 MB, and batch row count is set to 32, you can adjust this parameter to a value that equals 256 MB (8 × 32).
      • If the output table contains variables of complex data types or the small table involved in the MAPJOIN operation contains complex data types, the memory used for job execution may be affected when you adjust the value of this parameter. If you use the preceding calculation method, a large value of this parameter may cause out of memory (OOM).
      Unit: bytes. Default value: 67108864.
      odps.sql.udf.getjsonobj.new Specifies whether the GET_JSON_OBJECT function retains the original string when it returns a value.

      For MaxCompute projects created on or after April 20, 2021, the GET_JSON_OBJECT function automatically retains the original strings for the returned results. For MaxCompute projects created before April 20, 2021, the GET_JSON_OBJECT function automatically returns JSON-formatted reserved characters by using escape characters. This prevents the impact on existing jobs.

      For more information about the GET_JSON_OBJECT function, see GET_JSON_OBJECT.

      • True: The original string is retained.
      • False: The original string is not retained.
      LabelSecurity Specifies whether to enable LabelSecurity. For more information about LabelSecurity, see Column-level access control.
      • True: LabelSecurity is enabled.
      • False: LabelSecurity is disabled.
      odps.sql.session.resources Specifies resources that are referenced by user-defined type (UDTs). You can specify multiple resources and separate them with commas (,). For more information about how to reference resources, see Overview. The uploaded resources.
      odps.sql.udt.display.tostring Specifies whether to enable the java.util.Objects.toString(...) mechanism on the Wrap for all the columns whose outputs are UDTs.
      • True: The java.util.Objects.toString(...) mechanism is enabled.
      • False: The java.util.Objects.toString(...) mechanism is disabled.
      odps.sql.session.java.imports Specifies Java packages that are referenced by UDTs. You can specify multiple Java packages and separate them with commas (,). For more information about how to reference Java packages, see Overview. The uploaded Java package.
      CheckPermissionUsingACL Specifies whether to enable ACL-based authorization. For more information about ACL-based authorization, see Authorize users.
      • True: ACL-based authorization is enabled.
      • False: ACL-based authorization is disabled.
      CheckPermissionUsingPolicy Specifies whether to enable policy-based authorization. For more information about policy-based authorization, see Policy-based access control and download control.
      • True: Policy-based authorization is enabled.
      • False: Policy-based authorization is disabled.
      ObjectCreatorHasAccessPermission Specifies whether creators of objects can perform operations on objects created by themselves.
      • True: By default, creators of objects can perform operations on objects created by themselves.
      • False: Creators of objects cannot perform operations on objects created by themselves.
      ObjectCreatorHasGrantPermission Specifies whether creators of objects can authorize other users to perform operations on objects.
      • True: By default, creators of objects can authorize other users to perform operations on objects.
      • False: Creators of objects cannot authorize other users to perform operations on objects.
      ProjectProtection Specifies whether to enable project data protection. For more information about project data protection, see Project data protection.
      • True: Project data protection is enabled.
      • False: Project data protection is disabled.
      odps.sql.skewjoin Specifies whether to enable the SKEWJOIN feature to prevent long tails.
      • True: The SKEWJOIN feature is enabled.
      • False: The SKEWJOIN feature is disabled.
      odps.sql.skewinfo Sets the key and value on which SKEWJOIN is executed. For more information, see Optimize JOIN long tails. None.
      odps.sql.reducer.instances Sets the number of hash buckets. For more information about hash buckets, see Table operations. 0~4000
      odps.sql.udf.ppr.deterministic Specifies whether to enable partition pruning for user-defined functions (UDFs). For more information about partition pruning, see where_condition.
      • True: Partition pruning for UDFs is enabled.
      • False: Partition pruning for UDFs is disabled.
      odps.sql.udf.ppr.to.subquery Specifies whether to ignore the error that occurs during result backfilling when partition pruning is being performed. For more information about partition pruning, see where_condition.
      • True: The error is ignored.
      • False: The error is not ignored.
      odps.optimizer.enable.range.partial.repartitioning Specifies whether to enable the Shuffle Remove feature for range-clustered tables.
      • True: The Shuffle Remove feature is enabled.
      • False: The Shuffle Remove feature is disabled.
      odps.optimizer.skew.join.topk.num Sets the number of hot key values obtained by the optimizer when it performs an Aggregate operation. For more information, see SKEWJOIN HINT. None.
      odps.optimizer.stat.collect.auto Specifies whether to enable the Freeride feature. After the Freeride feature is enabled, the column stats metrics of tables are automatically collected. For more information, see Collect information for the optimizer of MaxCompute.
      • True: The Freeride feature is enabled.
      • False: The Freeride feature is disabled.
      odps.optimizer.stat.collect.plan Sets a collection plan to collect specific column stats metrics of specific columns. For more information, see Collect information for the optimizer of MaxCompute. None.
      odps.sql.executionengine.batch.rowcount Sets the number of data rows that can be processed by the SQL engine at a time. The default value is 1024. If OOM occurs because a large field exists in a row or if timeout occurs due to poor UDF performance, you must change this parameter to a small value. We recommend that you do not set this parameter to a value too small because a small value may affect the performance of the SQL engine. 1~1024
      odps.sql.external.net.vpc Specifies whether to enable virtual private cloud (VPC) for external tables. For more information, see MC-Hologres external tables.
      • True: This property is enabled.
      • False: This property is disabled.
      odps.sql.groupby.position.alias Specifies whether to process integer constants in the GROUP BY clause as column numbers in a SELECT statement.
      • True: Integer constants are processed as column numbers.
      • False: Integer constants are not processed as column numbers.
      odps.sql.groupby.skewindata Specifies whether to enable the anti-skew feature for the GROUP BY clause.
      • True: The anti-skew feature for the GROUP BY clause is enabled.
      • False: The anti-skew feature for the GROUP BY clause is disabled.
      odps.sql.orderby.position.alias Specifies whether to process integer constants in the ORDER BY clause as column numbers in a SELECT statement.
      • True: Integer constants are processed as column numbers.
      • False: Integer constants are not be processed as column numbers.
      odps.sql.groupby.orderby.position.alias Specifies whether to process integer constants in the GROUP BY and ORDER BY clauses as column numbers in a SELECT statement. We recommend that you specify this parameter.
      Note If you set this parameter to true for projects in which tasks are running, the task parsing and execution may be affected. Therefore, you must make sure that existing tasks can still run properly based on the original logic when you set the parameter to true. Otherwise, specify this parameter for sessions.
      • True: Integer constants are processed as column numbers.
      • False: Integer constants are not be processed as column numbers.
      odps.sql.jobconf.odps2 Specifies whether to enable jobConf2 to generate SQL execution plans.
      • True: JobConf2 is enabled.
      • False: JobConf2 is disabled.
      odps.sql.mapjoin.memory.max Specifies the size of small tables that are read to the memory when the MAPJOIN statement is executed. Unit: MB. 0 MB~10000 MB
      odps.sql.metering.value.max Specifies the upper limit on resources consumed by an SQL statement. For more information, see Consumption control. None.
      odps.sql.python.version Specifies the Python version on which SQL statements are executed.
      • cp27
      • cp37
      odps.sql.select.output.format Specifies whether to display the table headers in the returned results of the MaxCompute client. For more information, see MaxCompute client.
      • {"needHeader":false,"fieldDelim":""}: The table headers are not displayed.
      • {{"needHeader":true,"fieldDelim":""}: The table headers are displayed.
      odps.sql.timezone Specifies the time zone of the MaxCompute project. For more information about time zones, see Time zone configuration operations. None.
      odps.sql.unstructured.data.oss.use.https Specifies whether to obtain data by using HTTPS at the underlying layer when you use a built-in extractor to create external tables. For more information, see Access OSS data by using a built-in extractor.
      • True: This property is enabled.
      • False: This property is disabled.
      odps.sql.unstructured.oss.commit.mode Specifies whether to enable the multipart upload feature of Object Storage Service (OSS) to write data to external tables. For more information, see Export data to OSS.
      • True: Multipart upload is enabled.
      • False: Multipart upload is disabled.
  • Example
    -- Set the size of data read by each Mapper to 256 MB. 
    set odps.stage.mapper.split.size=256;

show flags

The SHOW FLAGS statement displays the properties that you configured by using the SET statement. Syntax:
show flags;