Snowflake - Sample Useful Queries

In this chapter, we will some sample useful queries in Snowflake and their outputs.

Use the following query to bring limited data in Select statement −

"SELECT * from <table_name>" Limit 10

This query will display only the first 10 rows.

Use the following query to display the usage of last 10 days.

SELECT * FROM TABLE (INFORMATION_SCHEMA.DATABASE_STORAGE_USAGE_HISTORY
   (DATEADD('days', -10, CURRENT_DATE()), CURRENT_DATE()))

Use the following query to check stages and file format created in Snowflake −

SHOW STAGES
SHOW FILE FORMATS

To check variables, run following queries in sequence −

SELECT * FROM snowflake_sample_data.tpch_sf1.region
JOIN snowflake_sample_data.tpch_sf1.nation
ON r_regionkey = n_regionkey;

select * from table(result_scan(last_query_id()));

SELECT * FROM snowflake_sample_data.tpch_sf1.region
JOIN snowflake_sample_data.tpch_sf1.nation
ON r_regionkey = n_regionkey;

SET q1 = LAST_QUERY_ID();

select $q1;

SELECT * FROM TABLE(result_scan($q1)) ;

SHOW VARIABLES;

Use the following query to find the login history of a database −

select * from table(test_db.information_schema.login_history());

Results provide timestamp, username, how login has done either using password or SSO, errors during login etc.

Use the following command to see all the columns −

SHOW COLUMNS 
SHOW COLUMNS in table <table_name>

Use the following command to show all the parameters provided by snowflake −

SHOW PARAMETERS;

Following are the few details which can be viewed by just running the query "SHOW PARAMETERS;"

Sr.No	Keys & Description
1	ABORT_DETACHED_QUERY If true, Snowflake will automatically abort queries when it detects that the client has disappeared.
2	AUTOCOMMIT The auto-commit property determines whether statement should be implicitly wrapped within a transaction or not. If auto-commit is set to true, then a statement that requires a transaction is executed within a transaction implicitly. If auto-commit is false, then an explicit commit or rollback is required to close a transaction. The default auto-commit value is true.
3	AUTOCOMMIT_API_SUPPORTED Whether auto-commit feature is enabled for this client. This parameter is for Snowflake use only.
4	BINARY_INPUT_FORMAT input format for binary
5	BINARY_OUTPUT_FORMAT display format for binary
6	CLIENT_ENABLE_CONSERVATIVE_MEMORY_USAGE Enables conservative memory usage for JDBC
7	CLIENT_ENABLE_DEFAULT_OVERWRITE_IN_PUT Set default value of overwrite option to true for put command, if overwrite option is not specified in the sql command.
8	CLIENT_ENABLE_LOG_INFO_STATEMENT_PARAMETERS Enable info-level logging for Prepared Statement binding parameters
9	CLIENT_MEMORY_LIMIT Limit the amount of memory used by the client in MB
10	CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX For client metadata request(getTables()), use session catalog and schema if set to true
11	CLIENT_METADATA_USE_SESSION_DATABASE For client metadata request(getTables()), use session catalog but multiple schemas if set to true (in conjunction with CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX
12	CLIENT_PREFETCH_THREADS Customer parameter to control threads, 0=auto
13	CLIENT_RESULT_CHUNK_SIZE Set the max result chunk size in MB on the client side
14	CLIENT_RESULT_COLUMN_CASE_INSENSITIVE Column name search is case insensitive in clients
15	CLIENT_SESSION_CLONE If true, client clones a new session from the previously used tokens for the account and user.
16	CLIENT_SESSION_KEEP_ALIVE If true, client session will not expire automatically
17	CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY Heartbeat frequency in seconds for CLIENT_SESSION_KEEP_ALIVE.
18	CLIENT_TIMESTAMP_TYPE_MAPPING If a variable is bound to the TIMESTAMP data type using the bind API determines which TIMESTAMP* type it should map to: TIMESTAMP_LTZ (default), TIMESTAMP_NTZ
19	C_API_QUERY_RESULT_FORMAT Format used to serialize query result to send back to C API
20	DATE_INPUT_FORMAT input format for date
21	DATE_OUTPUT_FORMAT display format for date
22	ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION Enable physical-type optimizations used in Snowflake to affect the Parquet output
23	ERROR_ON_NONDETERMINISTIC_MERGE raise an error when attempting to merge-update a row that joins many rows
24	ERROR_ON_NONDETERMINISTIC_UPDATE raise an error when attempting to update a row that joins many rows
25	GEOGRAPHY_OUTPUT_FORMAT GEOGRAPHY display format: GeoJSON, WKT, or WKB(case-insensitive)
26	GO_QUERY_RESULT_FORMAT Format used to serialize query result to send back to golang driver
27	JDBC_FORMAT_DATE_WITH_TIMEZONE When true, ResultSet#getDate(int columnIndex, Calendar cal) and getDate(String columnName, Calendar cal) will display the date using the Calendar's output.
28	JDBC_QUERY_RESULT_FORMAT Format used to serialize query result to send back to jdbc
29	JDBC_TREAT_DECIMAL_AS_INT When scale is 0, whether to treat Decimal as Int in JDBC
30	JDBC_TREAT_TIMESTAMP_NTZ_AS_UTC When true, Timestamp_NTZ values are always stored in UTC timezone
31	JDBC_USE_SESSION_TIMEZONE When true, JDBC driver will not display timezone offset between JVM and session.
32	JSON_INDENT Width of indentation in JSON output (0 for compact)
33	JS_TREAT_INTEGER_AS_BIGINT If true, the nodejs client will convert all integer columns to bigint type
34	LANGUAGE The selected language that will be used by UI, GS, Query Coordination and XP. The input languages should be in BCP-47 format. AKA. dash format. See LocaleUtil.java for the details.
35	LOCK_TIMEOUT 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.
36	MULTI_STATEMENT_COUNT Number of statement included in submitted query text. This parameter is submitted by user to avoid sql injection. Value 1 means one statement, value > 1 means N statements can be executed, if not equal to the value will raise the exception. Value 0 means any number of statements can be executed
37	ODBC_QUERY_RESULT_FORMAT Format used to serialize query result to send back to ODBC
38	ODBC_SCHEMA_CACHING When true, enables schema caching in ODBC. This can speed up SQL Columns API calls.
39	ODBC_USE_CUSTOM_SQL_DATA_TYPES ODBC return snowflake specific sql data types in result set metadata
40	PYTHON_CONNECTOR_QUERY_RESULT_FORMAT Format used to serialize query result to send back to python connector
41	QA_TEST_NAME Test name if running in QA mode. Used as a diversifier for shared pools
42	QUERY_RESULT_FORMAT Format used to serialize query result to send back to client
43	QUERY_TAG String (up to 2000 characters) used to tag statements executed by the session
44	QUOTED_IDENTIFIERS_IGNORE_CASE If true, the case of quoted identifiers is ignored
45	ROWS_PER_RESULTSET maximum number of rows in a result set
46	SEARCH_PATH Search path for unqualified object references.
47	SHOW_EXTERNAL_TABLE_KIND_AS_TABLE Change the way external table KIND info is displayed by SHOW TABLES and SHOW OBJECTS. The KIND column of external tables is displayed as TABLE if true, EXTERNAL_TABLE otherwise.
48	SIMULATED_DATA_SHARING_CONSUMER Data sharing views will return rows as if executed in the specified consumer account.
49	SNOWPARK_LAZY_ANALYSIS Enable lazy result schema analysis for Snowpark
50	STATEMENT_QUEUED_TIMEOUT_IN_SECONDS 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.
51	STATEMENT_TIMEOUT_IN_SECONDS Timeout in seconds for statements: statements are automatically canceled if they run for longer; if set to zero, max value (604800) is enforced.
52	STRICT_JSON_OUTPUT JSON output is strictly compliant to the specification
53	TIMESTAMP_DAY_IS_ALWAYS_24H If set, arithmetic on days always uses 24 hours per day, possibly not preserving the time (due to DST changes)
54	TIMESTAMP_INPUT_FORMAT input format for timestamp
55	TIMESTAMP_LTZ_OUTPUT_FORMAT Display format for TIMESTAMP_LTZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used.
56	TIMESTAMP_NTZ_OUTPUT_FORMAT Display format for TIMESTAMP_NTZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used.
57	TIMESTAMP_OUTPUT_FORMAT Default display format for all timestamp types.
58	TIMESTAMP_TYPE_MAPPING If TIMESTAMP type is used, what specific TIMESTAMP* type it should map to: TIMESTAMP_LTZ, TIMESTAMP_NTZ (default) or TIMESTAMP_TZ
59	TIMESTAMP_TZ_OUTPUT_FORMAT Display format for TIMESTAMP_TZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used.
60	TIMEZONE time zone
61	TIME_INPUT_FORMAT input format for time
62	TIME_OUTPUT_FORMAT display format for time
63	TRANSACTION_ABORT_ON_ERROR If this parameter is true, and a statement issued within a non-auto-commit transaction returns with an error, then the non-auto-commit transaction is aborted. All statements issued inside that transaction will fail until an commit or rollback statement is executed to close that transaction.
64	TRANSACTION_DEFAULT_ISOLATION_LEVEL The default isolation level when starting a starting a transaction, when no isolation level was specified
65	TWO_DIGIT_CENTURY_START 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
66	UI_QUERY_RESULT_FORMAT Format used to serialize query result to send back to python connector
67	UNSUPPORTED_DDL_ACTION The action to take upon encountering an unsupported DDL statement
68	USE_CACHED_RESULT If enabled, query results can be reused between successive invocations of the same query as long as the original result has not expired
69	WEEK_OF_YEAR_POLICY Defines the policy of assigning weeks to years: 0: the week needs to have 4 days in a given year; 1: a week with January 1st always belongs to a given year.
70	WEEK_START Defines the first day of the week: 0: legacy Snowflake behavior; 1: Monday .. 7: Sunday.