LIST QUERIES
Syntax
[ LIST | SHOW ] [ INTERACTIVE ] QUERIES
[ WITH ( list_parameter [, ... ] ) ];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.
Arguments
WITH ( list_parameter [, ... ] )
This clause specifies List Parameters.
List Parameters
| Parameter Name | Description |
|---|---|
| A flag that shows queries in any state, including terminated ones. Required: No Type: Flag |
Examples
List active Queries in the current Organization
The following shows a list of Queries that are active in the myorg Organization:
demodb.public/demostore# LIST ORGANIZATIONS;
+--------------------------------------+----------+-------------+-------------+--------------+--------------------+-------------------------------+
| ID | Name | Is Current | Is Default | Description | Profile Image Uri | Created At |
+======================================+==========+=============+=============+==============+====================+===============================+
| caab10ef-5c55-4e6b-906b-29a9dae9b74d | myorg | true | true | <null> | <null> | 2024-07-02 21:16:22 +0000 UTC |
+--------------------------------------+----------+-------------+-------------+--------------+--------------------+-------------------------------+
| 1fc8845b-8a35-452d-961a-91814e0ba3c0 | mr_prod | false | false | <null> | <null> | 2023-09-19 16:17:03 +0000 UTC |
+--------------------------------------+----------+-------------+-------------+--------------+--------------------+-------------------------------+
demodb.public/demostore# LIST QUERIES;
+--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+
| ID | Name | Version | Intended State | Actual State | Query | Owner | Created At | Updated At |
+======================================+-----------+----------+=================+===============+==========================================+==========+===============================+===============================+
| e4353d8a-5187-4bc7-a2a0-684a4dbe51b1 | | 1 | running | running | CREATE MATERIALIZED VIEW mviewstream AS | sysadmin | 2024-06-25 22:08:25 +0000 UTC | 2024-06-25 22:24:05 +0000 UTC |
| | | | | | SELECT upper(pageid) as pageidupper, | | | |
| | | | | | pageid, count(userid) as cnt FROM pv | | | |
| | | | | | GROUP BY pageid; | | | |
+--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+
| c7c813cd-e5d7-4270-bd33-0a7d939d58da | | 1 | running | running | BEGIN APPLICATION some_app CREATE | sysadmin | 2024-03-19 03:29:27 +0000 UTC | 2024-05-01 17:46:19 +0000 UTC |
| | | | | | VIRTUAL STREAM virtual.public.vx_mr AS | | | |
| | | | | | SELECT * FROM pv WHERE userid = | | | |
| | | | | | 'User_1'; INSERT INTO | | | |
| | | | | | virtual.public.vx_mr SELECT * FROM pv | | | |
| | | | | | WHERE userid <> 'User_1'; CREATE STREAM | | | |
| | | | | | pouria_app_sink AS SELECT * FROM | | | |
| | | | | | virtual.public.vx_mr; END APPLICATION; | | | |
+--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+
| 3cd847cd-82bf-4d8a-80fd-772d5c6a0aa2 | | 1 | running | running | CREATE TABLE pv_agg WITH ( 'store' = | sysadmin | 2024-01-11 22:23:30 +0000 UTC | 2024-01-17 23:09:59 +0000 UTC |
| | | | | | 'databricks_us_west_2', | | | |
| | | | | | 'databricks.catalog.name' = | | | |
| | | | | | 'deltastream_databricks1', | | | |
| | | | | | 'databricks.schema.name' = '"default"', | | | |
| | | | | | 'databricks.table.name' = 'pv_agg', | | | |
| | | | | | 'table.data.file.location' = | | | |
| | | | | | 's3://deltastream-mr-databricks-bucket2/ | | | |
| | | | | | test/0/pv_agg' ) AS SELECT userid, | | | |
| | | | | | count(pageid) AS pagecount FROM | | | |
| | | | | | pageviews GROUP BY userid; | | | |
+--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+
| c97ff2a2-2a95-4f44-82b4-ef48c0456d1e | | 1 | running | running | CREATE TABLE "TOTAL_PV" WITH ( | sysadmin | 2023-09-19 20:49:20 +0000 UTC | 2023-10-02 19:06:27 +0000 UTC |
| | | | | | 'snowflake.db.name' = | | | |
| | | | | | 'DELTA_STREAMING', | | | |
| | | | | | 'snowflake.schema.name' = 'PUBLIC') AS | | | |
| | | | | | SELECT userid AS "USERID", COUNT(*) AS | | | |
| | | | | | "VIEWS" FROM pageviews GROUP BY userid; | | | |
+--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+List active interactive Queries in a specific Organization
The following lists interactive Queries in a different Organization, Shared org, using its unique identifier from the list of Organizations owned by the user:
demodb.public/demostore# USE ORGANIZATION "Shared org";
+--------------+------------+------------+-------------------------------+
| Type | Name | Command | Summary |
+==============+============+============+===============================+
| organization | Shared org | USE | using organization Shared org |
+--------------+------------+------------+-------------------------------+
bzdb.public/sharedstore# LIST INTERACTIVE QUERIES;
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+
| ID | Intended State | Actual State | Query | Owner | Created At | Updated At |
+======================================+=================+===============+===============================+==========+===============================+===============================+
| fa86821e-a3dc-431c-bbaa-3f330bae2a5f | running | new | SELECT * FROM interest_count; | sysadmin | 2023-02-21 22:08:25 +0000 UTC | 2023-02-21 22:08:25 +0000 UTC |
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+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# STOP SANDBOX;
+---------+---------+----------+------------------------------------------+
| Type | Name | Command | Summary |
+=========+=========+==========+==========================================+
| sandbox | sandbox | DROP | sandbox was successfully marked for stop |
+---------+---------+----------+------------------------------------------+
bzdb.public/sharedstore# LIST INTERACTIVE QUERIES;
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+
| ID | Intended State | Actual State | Query | Owner | Created At | Updated At |
+======================================+=================+===============+===============================+==========+===============================+===============================+
| fa86821e-a3dc-431c-bbaa-3f330bae2a5f | stopped | running | SELECT * FROM interest_count; | sysadmin | 2023-02-21 22:08:25 +0000 UTC | 2023-02-21 22:15:54 +0000 UTC |
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+
bzdb.public/sharedstore# LIST INTERACTIVE QUERIES;
+-----+-----------------+---------------+--------+--------+-------------+-------------+
| ID | Intended State | Actual State | Query | Owner | Created At | Updated At |
+=====+=================+===============+========+========+=============+=============+
+-----+-----------------+---------------+--------+--------+-------------+-------------+List all interactive Queries in the current Organization
Once any Query is terminated and cleaned up, it'll be accessible using the all parameter:
bzdb.public/sharedstore# LIST INTERACTIVE QUERIES ('all');
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+
| ID | Intended State | Actual State | Query | Owner | Created At | Updated At |
+======================================+=================+===============+===============================+==========+===============================+===============================+
| fa86821e-a3dc-431c-bbaa-3f330bae2a5f | stopped | stopped | SELECT * FROM interest_count; | sysadmin | 2023-02-21 22:08:25 +0000 UTC | 2023-02-21 22:15:59 +0000 UTC |
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+Last updated