SET operations

SET

You can execute the SET statement 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>

• 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 Usage notes.	True: InstanceTunnel is enabled. False: InstanceTunnel is disabled.
    odps.stage.mapper.mem	Sets the memory size of each Map worker.	Valid values: 256 to 12288. Default value: 1024. Unit: MB.
    odps.stage.reducer.mem	Sets the memory size of each Reduce worker.	Valid values: 256 to 12288. Default value: 1024. Unit: MB.
    odps.stage.joiner.mem	Sets the memory size of each Join worker.	Valid values: 256 to 12288. Default value: 1024. Unit: MB.
    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.	Valid values: 256 to 12288. No default value.
    odps.stage.mapper.split.size	Changes the input data amount 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. Note If the LIMIT keyword is used in SQL statements, the number of concurrent workers is limited to 1. This way, only a single worker is allowed to run at a time. In this case, when you configure this parameter, we recommend that you do not add the LIMIT keyword to SQL statements.	Default value: 256. Unit: MB.
    odps.stage.reducer.num	Changes the number of workers at each Reduce stage. Note If the LIMIT keyword is used in SQL statements, the number of concurrent workers is limited to 1. This way, only a single worker is allowed to run at a time. In this case, when you configure this parameter, we recommend that you do not add the LIMIT keyword to SQL statements.	No default value.
    odps.stage.joiner.num	Changes the number of workers at each Join stage. Note If the LIMIT keyword is used in SQL statements, the number of concurrent workers is limited to 1. This way, only a single worker is allowed to run at a time. In this case, when you configure this parameter, we recommend that you do not add the LIMIT keyword to SQL statements.	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. Note If the LIMIT keyword is used in SQL statements, the number of concurrent workers is limited to 1. This way, only a single worker is allowed to run at a time. In this case, when you configure this parameter, we recommend that you do not add the LIMIT keyword to SQL statements.	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 writing performance. You can set this parameter if the output table contains excessive complex data types or the size of a variable of a specified 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 is 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 specified 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 running 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 an out of memory (OOM) error.	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. As of 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.
    odps.sql.udf.jvm.memory	Specifies the maximum memory size of the Java Virtual Machine (JVM) heap for a user-defined function (UDF). If a large amount of data is calculated and sorted in the memory by using a UDF, an out of memory (OMM) error may occur. In this case, you can increase the value of this parameter. However, you must optimize UDF code based on your business requirements to prevent OMM errors.	Valid values: 256 to 12288. Default value: 1024. Unit: MB.
    odps.sql.udf.timeout	Specifies the timeout period of UDFs.	Valid values: 0 to 3600. Default value: 600. Unit: seconds.
    LabelSecurity	Specifies whether to enable LabelSecurity. For more information about LabelSecurity, see Label-based access control.	True: LabelSecurity is enabled. False: LabelSecurity is disabled.
    odps.sql.session.resources	Specifies resources that are referenced by user-defined types (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 access control. For more information about ACL-based access control, see Permissions.	True: ACL-based access control is enabled. False: ACL-based access control is disabled.
    CheckPermissionUsingPolicy	Specifies whether to enable policy-based access control. For more information about policy-based access control, see Policy-based access control.	True: Policy-based access control is enabled. False: Policy-based access control is disabled.
    ObjectCreatorHasAccessPermission	Specifies whether creators of objects can perform operations on objects that are created by themselves.	True: By default, creators of objects can perform operations on objects that are created by themselves. False: By default, 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: By default, 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	Specifies the key and value on which SKEWJOIN is executed. For more information, see Optimize JOIN long tails	N/A.
    odps.sql.reducer.instances	Specifies the number of hash buckets. For more information about hash buckets, see Table operations.	Valid values: 0 to 4000.
    odps.sql.udf.ppr.deterministic	Specifies whether to enable partition pruning for UDFs. For more information about partition pruning, see WHERE clause (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 clause (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	Specifies the number of hot key values obtained by the optimizer when it performs an Aggregate operation. For more information, see SKEWJOIN HINT.	N/A.
    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.	N/A.
    odps.sql.executionengine.batch.rowcount	Specifies the number of data rows that can be processed by the SQL engine at a time. Default value: 1024. If an OOM error occurs because a large field exists in a row or if a timeout occurs due to poor UDF performance, you must set this parameter to a smaller 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.	Valid values: 1 to 1024.
    odps.sql.external.net.vpc	Specifies whether to enable virtual private cloud (VPC) for external tables. For more information, see Hologres external tables.	True: VPC is enabled for external tables. False: VPC is disabled for external tables.
    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 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 running 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 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.	Valid values: 0 to 8192.
    odps.sql.metering.value.max	Specifies the upper limit on resources consumed by an SQL statement. For more information, see Consumption control.	N/A.
    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.	N/A.
    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 unstructured 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

show flags;