{ LIST | SHOW } [INTERACTIVE] QUERIES [WITH('all')];
Arguments
WITH('all')
Optionally, this lists stopped and running queries.
Description
This command provides a list with a Streaming or Continuous Query or queries submitted to the current Organization, including any active interactive query.
See the USE command to change the Organization to LIST QUERIES from. Once queries are terminated/STOPPED and cleaned up, they are removed from the list of active queries. Failed queries remain in the ERRORED state until they are explicitly terminated using the
TERMINATE QUERY statement.
See RESTART QUERY for recovering from ERRORED queries.
Queries will only be listed if the current role has USAGE privileges.
Examples
List all active queries in the current Organization
The following shows a list of queries that are active in the MY org Organization:
demodb.public/demostore#LISTORGANIZATIONS; Current | ID | Name | Owner | Created at | Updated at
----------+--------------------------------------+------------+----------+----------------------+-----------------------
✓ | 1e184c11-4540-44d3-a56e-34db1fbe767e | MY org | orgadmin | 2023-01-10T19:29:33Z | 2023-01-10T19:29:33Z
| b55cd202-9858-42c2-a684-447dc32130a0 | Shared org | orgadmin | 2023-02-21T23:00:21Z | 2023-02-21T23:00:21Z
demodb.public/demostore#LISTQUERIES; ID | State | DSQL | Owner | Created at | Updated at
---------------------------------------+---------+--------------------------------------------------------------+----------+----------------------+-----------------------
b2041101-c37c-4807-93d4-2de876b7bd39 | RUNNING | CREATE CHANGELOG users2468_log | sysadmin | 2023-01-12T20:46:00Z | 2023-01-12T20:46:00Z
| | AS SELECT * FROM users_log | | |
| | WHERE userid = 'User_2' OR | | |
| | userid = 'User_4' OR userid = | | |
| | 'User_6' OR userid = 'User_8'; | | |
c81fc632-a043-472d-a1fe-aa81bc9078df | RUNNING | CREATE STREAM | sysadmin | 2023-01-12T20:46:27Z | 2023-01-12T20:46:27Z
| | pv_user2468_interest WITH | | |
| | ('store'='kinesis_main') | | |
| | AS SELECT p.userid AS pvid, | | |
| | u.userid AS uid, u.gender, | | |
| | p.pageid, u.interests[1] AS | | |
| | top_interest FROM pageviews | | |
| | p JOIN users_log u ON | | |
| | u.userid = p.userid; | | |
1d282874-6691-408b-8a54-56de367ce925 | RUNNING | CREATE CHANGELOG | sysadmin | 2023-01-12T20:47:28Z | 2023-01-12T20:47:28Z
| | interest_count WITH | | |
| | ('store'='kinesis_main') | | |
| | AS SELECT COUNT(uid) AS | | |
| | u_count, top_interest FROM | | |
| | pv_user2468_interest GROUP | | |
| | BY top_interest; | | |
40949c95-1062-432e-a8e6-fc8eb720ef1f | RUNNING | CREATE MATERIALIZED VIEW | sysadmin | 2023-01-18T04:02:05Z | 2023-01-18T04:02:05Z
| | interest_count_view AS SELECT | | |
| | * FROM interest_count; | | |
List all queries in a specific Organization
The following lists queries in a different Organization, Shared org, using its unique identifier from the list of Organizations owned by the user:
demodb.public/demostore#LISTORGANIZATIONS; Current | ID | Name | Owner | Created at | Updated at
----------+--------------------------------------+------------+----------+----------------------+-----------------------
✓ | 1e184c11-4540-44d3-a56e-34db1fbe767e | MY org | orgadmin | 2023-01-10T19:29:33Z | 2023-01-10T19:29:33Z
| b55cd202-9858-42c2-a684-447dc32130a0 | Shared org | orgadmin | 2023-02-21T23:00:21Z | 2023-02-21T23:00:21Z
demodb.public/demostore#USEORGANIZATIONb55cd202-9858-42c2-a684-447dc32130a0;bzdb.public/sharedstore#LISTQUERIES; ID | State | DSQL | Owner | Created at | Updated at
---------------------------------------+----------------+-------------------------------------------------------+----------+----------------------+-----------------------
fa86821e-a3dc-431c-bbaa-3f330bae2a5f | NEW -> RUNNING | SELECT * FROM interest_count; | sysadmin | 2023-02-21T22:15:51Z | 2023-02-21T22:15:54Z
Since only active queries are showed in the list of queries, if the above interactive query is stopped, it won’t be returned in the list after it’s cleaned up:
bzdb.public/sharedstore#STOPSANDBOX;bzdb.public/sharedstore#LISTQUERIES; ID | State | DSQL | Owner | Created at | Updated at
---------------------------------------+--------------------+-------------------------------------------------------+----------+----------------------+-----------------------
fa86821e-a3dc-431c-bbaa-3f330bae2a5f | STOPPED -> RUNNING | SELECT * FROM interest_count; | sysadmin | 2023-02-21T22:15:51Z | 2023-02-21T22:15:54Z
bzdb.public/sharedstore#LISTQUERIES;ID|State|DSQL|Owner|Createdat|Updatedat-----+-------+--------------------------------------------------------------+-------+------------+-------------
