MaxCompute allows you to set session-level system variables. 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 Operation platform
SET Sets MaxCompute system variables for the current session. Users who have operation permissions on projects You can execute the statements described in this topic on the following platforms:
show flags Displays the properties that you configured by using the SET statement.

SET

Sets MaxCompute system variables for the current session. MaxCompute also allows you to set project-level properties. For more information, see Configure project properties.

  • Syntax
    set <KEY>=<VALUE>
  • Parameters
    • KEY: the name of the property.
    • VALUE: the value of the property.
      The following table describes common properties at the session level.
      Property Description Valid value
      console.sql.result.instancetunnel Specifies whether to enable InstanceTunnel. For more information, see Tunnel command usage.
      • 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 specific MaxCompute job. This priority 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 avoid 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 types, 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 for storing a column that contains complex data types when MaxCompute writes data into 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 batch row count that corresponds to this column.
      • If you estimate that the size of 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. Based on the preceding calculation method, a large value of this parameter may cause out of memory (OOM).
      Unit: bytes. Default value: 67108864.
  • Example
    -- Set the size of data read by each Mapper to 256 MB.
    set odps.stage.mapper.split.size=256;

show flags

Displays the properties that you configured by using the SET statement.

Syntax:
show flags;