English 中文(简体)
Snowflake - Sample Useful Queries
  • 时间:2024-11-03

Snowflake - Sample Useful Queries


Previous Page Next Page  

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 &dollar;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:
TIMESTAMP_LTZ (default), TIMESTAMP_NTZ

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
statement must acquire the lock immediately or abort. If multiple resources need to be locked by the statement, the timeout apppes 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 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:
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 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:
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.

Advertisements