LIST RELATIONS
Syntax
[ LIST | SHOW ] [ RELATIONS | STREAMS | CHANGELOGS | MATERIALIZED VIEWS ]
[ IN SCHEMA schema_name ];Description
This command provides a list of Relation(s) defined in a Database’s Schema. This command can also be used to further filter based on a Relation types.
Relations will only be listed if the current role has USAGE privileges on the Database and Schema and SELECT or INSERT privilege on the Relation.
Arguments
IN SCHEMA schema_name
Optionally, this specifies the schema name for which the Relations should be listed. A fully qualified Schema name of the form <database_name>.<schema_name> can be provided. The current Database and Schema are used as the default for this command.
Examples
List all Relations in the default Schema
The following provides a list of all Relations that are in the scope of a user’s account and default Schema(demodb.public):
demodb.public/demostore# LIST RELATIONS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| keyed_pv | stream | sysadmin | created | { "value.format": "json", | 2024-07-12 20:43:54 +0000 UTC | 2024-07-12 20:43:54 +0000 UTC |
| | | | | "topic.partitions": 1, "key.type": | | |
| | | | | "STRUCT\u003cuserid VARCHAR\u003e", | | |
| | | | | "topic": "ds_pageviews", "store": | | |
| | | | | "pub_demo_msk", "key.format": "json", | | |
| | | | | "topic.replicas": 3 } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| mviewstream | materiali | sysadmin | created | { "key.type": "STRUCT\u003c\"pageid\" | 2024-06-25 22:08:25 +0000 UTC | 2024-06-25 22:08:27 +0000 UTC |
| | zed_view | | | VARCHAR\u003e", "primary.key": "pageid" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| pv | stream | sysadmin | created | { "value.format": "json", "topic": | 2024-03-19 03:29:03 +0000 UTC | 2024-03-19 03:29:04 +0000 UTC |
| | | | | "ds_pageviews", "store": "pub_demo_msk" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| pv_avro | stream | sysadmin | created | { "value.format": "avro", "key.type": | 2024-06-11 19:47:20 +0000 UTC | 2024-06-11 19:47:21 +0000 UTC |
| | | | | "STRUCT\u003cuserid VARCHAR\u003e", | | |
| | | | | "topic": "pageviews_avro", "store": | | |
| | | | | "kafka_with_sr", "key.format": "avro" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| users_log | changelog | sysadmin | created | { "value.format": "json", "key.type": | 2024-05-01 20:01:44 +0000 UTC | 2024-05-01 20:01:45 +0000 UTC |
| | | | | "STRUCT\u003cuserid VARCHAR\u003e", | | |
| | | | | "topic": "ds_users", "store": | | |
| | | | | "pub_demo_msk", "primary.key": | | |
| | | | | "userid", "key.format": "json" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| dbx_pv | table | sysadmin | errored | { "table.data.file.location": | 2024-07-01 22:41:56 +0000 UTC | 2024-07-01 22:42:12 +0000 UTC |
| | | | | "s3://deltastream-ctan-databricks-bucket | | |
| | | | | /new_catalog/new_schema/pageviews", | | |
| | | | | "databricks.catalog.name": | | |
| | | | | "new_catalog", "databricks.table.name": | | |
| | | | | "pageviews", "databricks.schema.name": | | |
| | | | | "new_schema", "store": | | |
| | | | | "test_databricks_store" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+List all Relations in a specific Schema
The following provides a list of Relations within the testdb.analytics Schema regardless of the default Schema:
demodb.public/demostore# LIST RELATIONS IN SCHEMA testdb.analytics;
main_db.public/pub_demo_msk# LIST RELATIONS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| total_views | changelog | sysadmin | created | { "key.type": "STRUCT\u003c\"pageid\" | 2023-02-10 21:48:25 +0000 UTC | 2023-02-25 22:08:27 +0000 UTC |
| | | | | VARCHAR\u003e", "primary.key": "pageid" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+List all Streams in the default Schema
The following provides a list of all Streams that are in the scope of a user’s account and default Schema:
demodb.public/demostore# LIST STREAMS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| keyed_pv | stream | sysadmin | created | { "value.format": "json", | 2024-07-12 20:43:54 +0000 UTC | 2024-07-12 20:43:54 +0000 UTC |
| | | | | "topic.partitions": 1, "key.type": | | |
| | | | | "STRUCT\u003cuserid VARCHAR\u003e", | | |
| | | | | "topic": "ds_pageviews", "store": | | |
| | | | | "pub_demo_msk", "key.format": "json", | | |
| | | | | "topic.replicas": 3 } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| pv | stream | sysadmin | created | { "value.format": "json", "topic": | 2024-03-19 03:29:03 +0000 UTC | 2024-03-19 03:29:04 +0000 UTC |
| | | | | "ds_pageviews", "store": "pub_demo_msk" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| pv_avro | stream | sysadmin | created | { "value.format": "avro", "key.type": | 2024-06-11 19:47:20 +0000 UTC | 2024-06-11 19:47:21 +0000 UTC |
| | | | | "STRUCT\u003cuserid VARCHAR\u003e", | | |
| | | | | "topic": "pageviews_avro", "store": | | |
| | | | | "kafka_with_sr", "key.format": "avro" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+List all Changelogs in the default Schema
The following provides a list of all Changelogs that are in the scope of a user’s account and default Schema:
demodb.public/demostore# LIST CHANGELOGS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| users_log | changelog | sysadmin | created | { "value.format": "json", "key.type": | 2024-05-01 20:01:44 +0000 UTC | 2024-05-01 20:01:45 +0000 UTC |
| | | | | "STRUCT\u003cuserid VARCHAR\u003e", | | |
| | | | | "topic": "ds_users", "store": | | |
| | | | | "pub_demo_msk", "primary.key": | | |
| | | | | "userid", "key.format": "json" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+List all Materialized Views in the default Schema
The following provides a list of all Materialized Views that are in the scope of a user’s account and default Schema:
demodb.public/demostore# LIST MATERIALIZED VIEWS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| mviewstream | materiali | sysadmin | created | { "key.type": "STRUCT\u003c\"pageid\" | 2024-06-25 22:08:25 +0000 UTC | 2024-06-25 22:08:27 +0000 UTC |
| | zed_view | | | VARCHAR\u003e", "primary.key": "pageid" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+List all Tables in the default Schema
The following provides a list of all Tables that are in the scope of a user’s account and default Schema:
demodb.public/demostore# LIST TABLES;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| dbx_pv | table | sysadmin | errored | { "table.data.file.location": | 2024-07-01 22:41:56 +0000 UTC | 2024-07-01 22:42:12 +0000 UTC |
| | | | | "s3://deltastream-ctan-databricks-bucket | | |
| | | | | /new_catalog/new_schema/pageviews", | | |
| | | | | "databricks.catalog.name": | | |
| | | | | "new_catalog", "databricks.table.name": | | |
| | | | | "pageviews", "databricks.schema.name": | | |
| | | | | "new_schema", "store": | | |
| | | | | "test_databricks_store" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+Last updated