매개 변수 관리¶

계정 매개 변수 보기¶

계정의 매개 변수 목록, 현재 값과 기본값을 살펴보려면 다음 구문으로 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                                             |
+-------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Copy

출력에서 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 |
+--------------------+------------+------------+---------+-------------------------+

Copy

출력의 level 열에는 현재 계정 수준에서 설정된 매개 변수의 값이 표시됩니다.

이는 세션의 모든 날짜에 대한 기본 표시 형식이 YYYY-MM-DD가 아닌 DD/MM/YYYY(예: 2016-04-23이 아닌 23/04/2016)임을 지정합니다. 이러한 날짜 표시 형식은 기본값이며 개별 사용자 또는 세션에 대하여 재정의할 수 있음에 유의하십시오.

ALTER ACCOUNT UNSET <param>