CODEWITHSUNDEEP
snowflake-cloud-data-platform
mo_jl
mo_jl

Reputation: 101

Is there a documented list of Snowflake query types?

I am working with the view SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. It would be extremely helpful to have an exhaustive list of query types that might appear in the column QUERY_TYPE, with the type of commands that generate them. For example, does a PUT command generate a PUT query type? Or is it something like "LOAD"?

If anyone knows where such a list can be found, please post a link. Snowflake's documentation of the view does not provide any list.

Upvotes: 3

Views: 2126

Answers (4)

Seub
Seub

Reputation: 3192

Here are some additional ones:

ALTER_AUTO_RECLUSTER
ALTER_SET_TAG
ALTER_TABLE_MODIFY_CONSTRAINT
ALTER_UNSET_TAG
CALL
DROP_SESSION_POLICY
RECLUSTER

Upvotes: 1

mo_jl
mo_jl

Reputation: 101

Thanks all who have answered so far. Since the consensus is that no such list exists, here is a merge of the entries provided so far with the values found in my own database. Please keep posting additional answers if your DB contains entries not found below. This way, sooner or later, we will have a fairly complete list:

QUERY_TYPE
CREATE_USER
REVOKE
DROP_CONSTRAINT
RENAME_SCHEMA
UPDATE
CREATE_VIEW
CREATE_TASK
RENAME_TABLE
INSERT
ALTER_TABLE_ADD_COLUMN
RENAME_COLUMN
MERGE
BEGIN_TRANSACTION
ALTER_VIEW_MODIFY_SECURITY
GRANT
ALTER_SESSION
DELETE
DROP_ROLE
DESCRIBE
UNKNOWN
TRUNCATE_TABLE
DROP
SHOW
ALTER_WAREHOUSE_SUSPEND
GET_FILES
UNLOAD
CREATE_NETWORK_POLICY
ALTER_TABLE_DROP_COLUMN
CREATE
REMOVE_FILES
ALTER
ALTER_USER
PUT_FILES
COPY
ALTER_ACCOUNT
DROP_TASK
CREATE_CONSTRAINT
DESCRIBE_QUERY
SELECT
RENAME_USER
COMMIT
RENAME_VIEW
USE
CREATE_TABLE
ALTER_NETWORK_POLICY
CREATE_ROLE
ALTER_TABLE_MODIFY_COLUMN
SET
ALTER_USER_ABORT_ALL_JOBS
ROLLBACK
LIST_FILES
UNSET
CREATE_TABLE_AS_SELECT
DROP_USER
ALTER_WAREHOUSE_RESUME
QUERY_TYPE
ALTER_PIPE
ALTER_ROLE
ALTER_TABLE
ALTER_TABLE_DROP_CLUSTERING_KEY
ALTER_USER_RESET_PASSWORD
CREATE_EXTERNAL_TABLE
CREATE_MASKING_POLICY
CREATE_SEQUENCE
CREATE_STREAM
DROP_STREAM
RENAME_DATABASE
RENAME_FILE_FORMAT
RENAME_ROLE
RENAME_WAREHOUSE
RESTORE

Upvotes: 6

Adrian White
Adrian White

Reputation: 1804

Added ours ... 16 extra's ... pass it on :-)

QUERY_TYPE
ALTER
ALTER_ACCOUNT
ALTER_PIPE
ALTER_ROLE
ALTER_SESSION
ALTER_TABLE
ALTER_TABLE_ADD_COLUMN
ALTER_TABLE_DROP_CLUSTERING_KEY
ALTER_TABLE_DROP_COLUMN
ALTER_TABLE_MODIFY_COLUMN
ALTER_USER
ALTER_USER_ABORT_ALL_JOBS
ALTER_USER_RESET_PASSWORD
ALTER_WAREHOUSE_RESUME
ALTER_WAREHOUSE_SUSPEND
BEGIN_TRANSACTION
COMMIT
COPY
CREATE
CREATE_CONSTRAINT
CREATE_EXTERNAL_TABLE
CREATE_MASKING_POLICY
CREATE_NETWORK_POLICY
CREATE_ROLE
CREATE_SEQUENCE
CREATE_STREAM
CREATE_TABLE
CREATE_TABLE_AS_SELECT
CREATE_TASK
CREATE_USER
CREATE_VIEW
DELETE
DESCRIBE
DESCRIBE_QUERY
DROP
DROP_CONSTRAINT
DROP_ROLE
DROP_STREAM
DROP_TASK
DROP_USER
GET_FILES
GRANT
INSERT
LIST_FILES
MERGE
PUT_FILES
REMOVE_FILES
RENAME_COLUMN
RENAME_DATABASE
RENAME_FILE_FORMAT
RENAME_ROLE
RENAME_SCHEMA
RENAME_TABLE
RENAME_USER
RENAME_VIEW
RENAME_WAREHOUSE
RESTORE
REVOKE
ROLLBACK
SELECT
SET
SHOW
TRUNCATE_TABLE
UNKNOWN
UNLOAD
UNSET
UPDATE
USE

Upvotes: 4

Simon D
Simon D

Reputation: 6269

By the looks of it there is no complete list of query types that show up in this table. Best I can do is give you a list from my own database, which still doesn't contain things like alter role etc. To answer your other question a PUT command is actually PUT_FILES by the looks of it:

select distinct query_type from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;

+-------------------------+
|QUERY_TYPE               |
+-------------------------+
|ALTER                    |
|ALTER_SESSION            |
|ALTER_TABLE_ADD_COLUMN   |
|ALTER_TABLE_DROP_COLUMN  |
|ALTER_TABLE_MODIFY_COLUMN|
|ALTER_USER               |
|ALTER_WAREHOUSE_RESUME   |
|ALTER_WAREHOUSE_SUSPEND  |
|BEGIN_TRANSACTION        |
|COMMIT                   |
|COPY                     |
|CREATE                   |
|CREATE_CONSTRAINT        |
|CREATE_EXTERNAL_TABLE    |
|CREATE_MASKING_POLICY    |
|CREATE_ROLE              |
|CREATE_SEQUENCE          |
|CREATE_STREAM            |
|CREATE_TABLE             |
|CREATE_TABLE_AS_SELECT   |
|CREATE_USER              |
|CREATE_VIEW              |
|DELETE                   |
|DESCRIBE                 |
|DESCRIBE_QUERY           |
|DROP                     |
|DROP_CONSTRAINT          |
|DROP_STREAM              |
|DROP_USER                |
|GET_FILES                |
|GRANT                    |
|INSERT                   |
|LIST_FILES               |
|MERGE                    |
|PUT_FILES                |
|REMOVE_FILES             |
|RENAME_COLUMN            |
|RENAME_DATABASE          |
|RENAME_TABLE             |
|RESTORE                  |
|REVOKE                   |
|ROLLBACK                 |
|SELECT                   |
|SET                      |
|SHOW                     |
|TRUNCATE_TABLE           |
|UNKNOWN                  |
|UNLOAD                   |
|UPDATE                   |
|USE                      |
+-------------------------+

Upvotes: 4

Related Questions