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 from which to LIST QUERIES. When queries are terminated/STOPPED and cleaned up, DeltaStream removes them from the list of active queries.

Failed queries remain in the ERRORED state until they are explicitly terminated using the TERMINATE QUERY statement.

Note See RESTART QUERY for recovering from ERRORED queries.

Queries are listed only if the current role has USAGE privileges.

Arguments

WITH ( list_parameter [, ... ] )

This clause specifies List Parameters.

List Parameters

Parameter Name	Description
all	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 the user owns:

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 |
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+

Only active queries display in the list of queries. If the above interactive query is stopped, it is not 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

When a query is terminated and cleaned up, you can access it 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 |
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+