参数管理¶
Snowflake 提供了三种可以为账户设置的参数：
影响整个账户的账户参数。
用户及其会话的默认会话参数。
对象（仓库、数据库、架构和表）的默认对象参数。
所有参数都有默认值，并且可以在账户级别替换这些值。要在账户级别替换默认值，您必须是账户管理员（即获授予 ACCOUNTADMIN 角色的用户）。
此外，可以在参数层次结构的每个级别替换会话和对象参数的默认值。
本主题内容：
查看账户的参数¶
要查看账户的参数、其当前值和默认值的列表，请按照以下语法使用 SHOW PARAMETERS 命令：
SHOW PARAMETERS [ LIKE '<pattern>' ] IN ACCOUNT
例如，要查看所有账户级参数的完整列表，请使用以下命令：
SHOW PARAMETERS IN ACCOUNT; +-------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | key | value | default | level | description | |-------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | ABORT_DETACHED_QUERY | false | false | | If true, Snowflake will automatically abort queries when it detects that the client has disappeared. | | AUTOCOMMIT | true | true | | The autocommit property determines whether is statement should to be implicitly | | | | | | wrapped within a transaction or not. If autocommit is set to true, then a | | | | | | statement that requires a transaction is executed within a transaction | | | | | | implicitly. If autocommit is off then an explicit commit or rollback is required | | | | | | to close a transaction. The default autocommit value is true. | | AUTOCOMMIT_API_SUPPORTED | true | true | | Whether autocommit feature is enabled for this client. This parameter is for | | | | | | Snowflake use only. | | BINARY_INPUT_FORMAT | HEX | HEX | | input format for binary | | BINARY_OUTPUT_FORMAT | HEX | HEX | | display format for binary | | CLIENT_ENCRYPTION_KEY_SIZE | 128 | 128 | | Client-side encryption key size in bits. Either 128 or 256. | | CLIENT_SESSION_KEEP_ALIVE | false | false | | If true, client session will not expire automatically | | DATA_RETENTION_TIME_IN_DAYS | 1 | 1 | | number of days to retain the old version of deleted/updated data | | DATE_INPUT_FORMAT | AUTO | AUTO | | input format for date | | DATE_OUTPUT_FORMAT | YYYY-MM-DD | YYYY-MM-DD | | display format for date | | ERROR_ON_NONDETERMINISTIC_MERGE | true | true | | raise an error when attempting to merge-update a row that joins many rows | | ERROR_ON_NONDETERMINISTIC_UPDATE | false | false | | raise an error when attempting to update a row that joins many rows | | LOCK_TIMEOUT | 43200 | 43200 | | Number of seconds to wait while trying to lock a resource, before timing out | | | | | | and aborting the statement. A value of 0 turns off lock waiting i.e. the | | | | | | statement must acquire the lock immediately or abort. If multiple resources | | | | | | need to be locked by the statement, the timeout applies separately to each | | | | | | lock attempt. | | MAX_CONCURRENCY_LEVEL | 8 | 8 | | Concurrency level for SQL statements (i.e. queries and DML) executed by a warehouse cluster (used to determine when statements are queued or additional clusters are started). | | NETWORK_POLICY | | | | Network policy assigned for the given target. | | PERIODIC_DATA_REKEYING | false | false | | If true, Snowflake will re-encrypt data that was encrypted more than a year ago. | | QUERY_TAG | | | | String (up to 2000 characters) used to tag statements executed by the session | | QUOTED_IDENTIFIERS_IGNORE_CASE | false | false | | If true, the case of quoted identifiers is ignored | | ROWS_PER_RESULTSET | 0 | 0 | | maxium number of rows in a result set | | SAML_IDENTITY_PROVIDER | | | | Authentication attributes for the SAML Identity provider | | SSO_LOGIN_PAGE | true | false | ACCOUNT | Enable federated authentication for console login and redirects preview page to console login | | STATEMENT_QUEUED_TIMEOUT_IN_SECONDS | 0 | 0 | | Timeout in seconds for queued statements: statements will automatically be canceled if they are queued on a warehouse for longer than this amount of time; disabled if set to zero. | | STATEMENT_TIMEOUT_IN_SECONDS | 0 | 0 | | Timeout in seconds for statements: statements will automatically be canceled if they run for longer than this amount of time; disabled if set to zero. | | TIMESTAMP_DAY_IS_ALWAYS_24H | false | true | SYSTEM | If set, arithmetic on days always uses 24 hours per day, | | | | | | possibly not preserving the time (due to DST changes) | | TIMESTAMP_INPUT_FORMAT | AUTO | AUTO | | input format for timestamp | | TIMESTAMP_LTZ_OUTPUT_FORMAT | | | | Display format for TIMESTAMP_LTZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used. | | TIMESTAMP_NTZ_OUTPUT_FORMAT | YYYY-MM-DD HH24:MI:SS.FF3 | YYYY-MM-DD HH24:MI:SS.FF3 | SYSTEM | Display format for TIMESTAMP_NTZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used. | | TIMESTAMP_OUTPUT_FORMAT | YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM | YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM | SYSTEM | Default display format for all timestamp types. | | TIMESTAMP_TYPE_MAPPING | TIMESTAMP_NTZ | TIMESTAMP_NTZ | SYSTEM | If TIMESTAMP type is used, what specific TIMESTAMP* type it should map to: | | | | | | TIMESTAMP_LTZ (default), TIMESTAMP_NTZ or TIMESTAMP_TZ | | TIMESTAMP_TZ_OUTPUT_FORMAT | | | | Display format for TIMESTAMP_TZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used. | | TIMEZONE | America/Los_Angeles | America/Los_Angeles | | time zone | | TIME_INPUT_FORMAT | AUTO | AUTO | | input format for time | | TIME_OUTPUT_FORMAT | HH24:MI:SS | HH24:MI:SS | | display format for time | | TRANSACTION_ABORT_ON_ERROR | false | false | | If this parameter is true, and a statement issued within a non-autocommit | | | | | | transaction returns with an error, then the non-autocommit transaction is | | | | | | aborted. All statements issued inside that transaction will fail until an | | | | | | commit or rollback statement is executed to close that transaction. | | TRANSACTION_DEFAULT_ISOLATION_LEVEL | READ COMMITTED | READ COMMITTED | | The default isolation level when starting a starting a transaction, when no | | | | | | isolation level was specified | | TWO_DIGIT_CENTURY_START | 1970 | 1970 | | For 2-digit dates, defines a century-start year. | | | | | | For example, when set to 1980: | | | | | | - parsing a string '79' will produce 2079 | | | | | | - parsing a string '80' will produce 1980 | | UNSUPPORTED_DDL_ACTION | ignore | ignore | | The action to take upon encountering an unsupported ddl statement | | USE_CACHED_RESULT | true | true | | If enabled, query results can be reused between successive invocations of the same query as long as the original result has not expired | +-------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
在输出中，注意 value 和 level 列：
value指定每个参数的当前值。level指定当前值的来源。如果参数的level列为空，则表示没有显式设置该参数，并且当前值为默认值。
更改账户的参数¶
要更改账户的参数，请以账户管理员身份登录 Snowflake，并按照以下语法使用 ALTER ACCOUNT 命令：
ALTER ACCOUNT SET <param> = <value>
例如，要设置 DATE_OUTPUT_FORMAT 会话参数，请使用以下命令：
ALTER ACCOUNT SET DATE_OUTPUT_FORMAT = 'DD/MM/YYYY'; SHOW PARAMETERS LIKE 'DATE_OUTPUT%' IN ACCOUNT; +--------------------+------------+------------+---------+-------------------------+ | key | value | default | level | description | |--------------------+------------+------------+---------+-------------------------| | DATE_OUTPUT_FORMAT | DD/MM/YYYY | YYYY-MM-DD | ACCOUNT | display format for date | +--------------------+------------+------------+---------+-------------------------+请注意，输出中的
level列显示当前在账户级别设置的参数的值。
以上命令指定会话中所有日期的默认显示格式为 DD/MM/YYYY 而不是 YYYY-MM-DD（例如，23/04/2016 而不是 2016-04-23）。请注意，此日期显示格式仅为默认格式。可以为任何单个用户或会话替换此格式。
重置参数¶
要将账户的参数重置回默认值，请按照以下语法使用 ALTER ACCOUNT：
ALTER ACCOUNT UNSET <param>
例如，要将 DATE_OUTPUT_FORMAT 会话参数设置回其默认值，请使用以下命令：
ALTER ACCOUNT UNSET DATE_OUTPUT_FORMAT;