2
0
Snowflake Parameters: Snowflake provides three types of parameters that can be set for your account. For Instance,
- Account : Firstly, Account parameters that affect your entire account.
- Session: Secondly, Session parameters that default to users and their sessions.
- Object: Thirdly, Object parameters that default to objects (warehouses, databases, schemas, and tables).
In conclusion, All parameters have default values provided by Snowflake, which can be overridden at the account level by ACCOUNTADMIN role.
Account Parameters:
Account Parameters:
- Firstly, These parameters are set at the account level and can’t be overridden at a lower level of the hierarchy.
- Above all, Once you set an account parameter, it will be visible to all users and their sessions, which cannot be changed by any other users.
For Instance, to see a complete list of all account-level parameters.
show parameters in account;
However,Above command will display all the parameters belong to the different category at Account level:
[ accountParams ] [ objectParams ] [ sessionParams ]
accountParams ::=
ALLOW_ID_TOKEN = TRUE | FALSE
CLIENT_ENCRYPTION_KEY_SIZE = <integer>
INITIAL_REPLICATION_SIZE_LIMIT_IN_TB = <num>
NETWORK_POLICY = <string>
PERIODIC_DATA_REKEYING = TRUE | FALSE
PREVENT_UNLOAD_TO_INLINE_URL = TRUE | FALSE
PREVENT_UNLOAD_TO_INTERNAL_STAGES = TRUE | FALSE
REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION = TRUE | FALSE
REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION = TRUE | FALSE
SAML_IDENTITY_PROVIDER = <json_object>
SSO_LOGIN_PAGE = TRUE | FALSE
Session Parameters:
Session Parameters:
- These parameters mainly relate to users and their sessions. Moreover, they can be set at the account level and reflected to the Users and sessions.
- However, These defaults can be overridden at any time by the Users ,which will be applicable to all the sessions opened by user.
- In addition, Defaults can be overridden at the individual session also for a particular user without impacting the other session belongs to same user.
Hence we have following hierarchy:
Therefore, Session type parameters can be useful to meet different user requirements.
For example, users connecting from the US may want to see dates displayed in “mm-dd-yyyy” format, and users from Asia may want to see dates listed as “dd/mm/yyyy”. In other words, The account-level value for this parameter may be the default “yyyy-mm-dd”.
Therefore, Setting the value at user-level ensures different users are seeing dates the way they want to see it.
sessionParams ::=
ABORT_DETACHED_QUERY = TRUE | FALSE
AUTOCOMMIT = TRUE | FALSE
BINARY_INPUT_FORMAT =
BINARY_OUTPUT_FORMAT =
DATE_INPUT_FORMAT =
DATE_OUTPUT_FORMAT =
ERROR_ON_NONDETERMINISTIC_MERGE = TRUE | FALSE
ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE | FALSE
JSON_INDENT =
LOCK_TIMEOUT =
QUERY_TAG =
ROWS_PER_RESULTSET =
However, To get complete list of all session parameters, with their descriptions, as well as account and object parameters, see Parameters.
Show Parameters:
Shows parameters defined at the account level:
SHOW PARAMETERS IN ACCOUNT;—- shows parameters defined at the user level
SHOW PARAMETERS IN USER;– shows parameters defined at the session level
SHOW PARAMETERS IN SESSION;– shows parameters defined at the session level
SHOW PARAMETERS
SHOW PARAMETERS like ‘%DATE_INPUT_FORMAT%’ IN ACCOUNT
Set Parameters at Account level:
Override the default DATE_OUTPUT_FORMAT with new date setting:
- alter account set DATE_OUTPUT_FORMAT = ‘DD/MM/YYYY’;
- select current_date()
Create a new User: REP1 and login to the Snowflake: Alter the Date format setting for USER. This will override the Account level parameter setting.
- alter USER set DATE_output_FORMAT = ‘MM/DD/YYYY’;
- select current_date()
Alter the DATE_output_FORMAT with new Date format in Session1: This will override the setting at USER level.
- Alter SESSION set DATE_output_FORMAT = ‘YYYY/MM/DD’;
- select current_date()
No Date format is set for Session2, This will inherit the format defined at User level:
Object Parameters:
Object Parameters:
- These parameters are applicable to Snowflake objects, like warehouses and databases.
- Warehouses don’t have any hierarchy, so warehouse-specific parameters can be set at account-level and then changed for individual warehouses
- Database-specific parameters can be set at the account level and then for each database
- Within a database, a schema can override the parameters set at account or database level,
- Within the schema, a table can override the parameters set at account, database or schema level.
- ALTER SCHEMA DEMO_DB.PUBLIC SET DATA_RETENTION_TIME_IN_DAYS=0;
- SHOW PARAMETERS like ‘%DATA_RETENTION_TIME_IN_DAYS%’ IN SCHEMA DEMO_DB.PUBLIC;
- ALTER TABLE DEMO_DB.PUBLIC.INVOICE SET DATA_RETENTION_TIME_IN_DAYS=2;
- SHOW PARAMETERS IN TABLE DEMO_DB.PUBLIC.INVOICE;
In next post we will discuss some parameters in details.
