LIST QUERIES
Last updated
Last updated
[ LIST | SHOW ] [ INTERACTIVE ] QUERIES
[ WITH ( list_parameter [, ... ] ) ];This command provides a list with a or queries submitted to the current organization, including any active interactive query.
See the 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
statement.
Note See for recovering from ERRORED queries.
Queries are listed only if the current role has USAGE privileges.
This clause specifies List Parameters.
all
A flag that shows queries in any state, including terminated ones.
Required: No
Type: Flag
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; | | | |
+--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+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 |
+=====+=================+===============+========+========+=============+=============+
+-----+-----------------+---------------+--------+--------+-------------+-------------+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 |
+--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+