- Snowflake - Discussion
- Snowflake - Useful Resources
- Snowflake - Quick Guide
- External Data Unloading (Into AWS S3)
- External Data Loading (from AWS S3)
- Unload Data from Snowflake to Local
- Snowflake - Cache
- Snowflake - Monitor Usage and Storage
- Snowflake - Sample Useful Queries
- Snowflake - Load Data From Files
- Snowflake - Table & Columns
- Snowflake - Schema
- Snowflake - Database
- Snowflake - Warehouse
- Snowflake - Login
- Snowflake - Table and View Types
- Snowflake - Objects
- Snowflake - Pricing Model
- Snowflake - Editions
- Snowflake - How to Access
- Snowflake - Functional Architecture
- Snowflake - Data Architecture
- Snowflake - Introduction
- Snowflake - Home
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Snowflake - Sample Useful Queries
In this chapter, we will some sample useful queries in Snowflake and their outputs.
Use the following query to bring pmited 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 cpent has disappeared. |
2 | AUTOCOMMIT The auto-commit property determines whether statement should be imppcitly 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 imppcitly. If auto-commit is false, then an exppcit 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 cpent. 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 cpent in MB |
10 | CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX For cpent metadata request(getTables()), use session catalog and schema if set to true |
11 | CLIENT_METADATA_USE_SESSION_DATABASE For cpent 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 cpent side |
14 | CLIENT_RESULT_COLUMN_CASE_INSENSITIVE Column name search is case insensitive in cpents |
15 | CLIENT_SESSION_CLONE If true, cpent clones a new session from the previously used tokens for the account and user. |
16 | CLIENT_SESSION_KEEP_ALIVE If true, cpent 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: |
19 | C_API_QUERY_RESULT_FORMAT Format used to seriapze 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 seriapze 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 seriapze 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 cpent 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 |
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 seriapze 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 seriapze query result to send back to python connector |
41 | QA_TEST_NAME Test name if running in QA mode. Used as a spanersifier for shared pools |
42 | QUERY_RESULT_FORMAT Format used to seriapze query result to send back to cpent |
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 unquapfied 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 comppant 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: |
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. |
66 | UI_QUERY_RESULT_FORMAT Format used to seriapze 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 popcy of assigning weeks to years: |
70 | WEEK_START Defines the first day of the week: |