DESCRIBE <statement>

Syntax

DESCRIBE statement;

Description

This command provides details about the objects used by a CREATE_RELATION, INSERT INTO, or CREATE AS SELECT statement.

The statement is described only if the current role has the USAGE privilege on the Database, Schema, Store, and Relation used in the statement.

Arguments

statement

Any of the executable statements including CREATE STREAM, CREATE CHANGELOG, CREATE STREAM AS SELECT, CREATE CHANGELOG AS SELECT, CREATE MATERIALIZED VIEW AS, CREATE TABLE AS SELECT.

Response Columns

• type: Provides the type of the statement being described.

• info: A key-value list of objects used within the statement and specific information about them:

  • ddl/sink: A new relation the statement creates to write to — or, respectively, an existing relation to which the statement writes new records.

    • Provides fqn, type, db_name, schema_name, name, and store_name. A fqn is in the format of org.db.schema.relation.

  • Sources: List of relations the statement uses for reading records.

    • Provides fqn, type, db_name, schema_name, name, and store_name.

Examples

Describe a stream DDL statement

In this example, a CREATE STREAM statement returns a ddl field for the pageviews stream that would be created if the statement was executed:

demodb.public/demostore# DESCRIBE CREATE STREAM pageviews (viewtime BIGINT, userid VARCHAR, pageid VARCHAR) WITH ('value.format'='json', 'topic.partitions'='5', 'topic.replicas'='5');
+---------------+------------------------------------------+
|  Type         |  Info                                    |
+===============+==========================================+
| CREATE_STREAM | {"ddl":{"fqn":"d39511ce-c918-4637-9524-1 |
|               | 63183452274.demodb.public.pageviews","ty |
|               | pe":"STREAM","db_name":"demodb","schema_ |
|               | name":"public","name":"pageviews","store |
|               | _name":"demostore"}}                     |
+---------------+------------------------------------------+

Describe a CCAS filter statement

In this example, pageviewscl Changelog is filtered and writes its result into pageviewscl_6 changelog:

demodb.public/demostore# DESCRIBE CREATE CHANGELOG PAGEVIEWSCL_6 AS SELECT userid, pageid FROM PAGEVIEWSCL WHERE userid = 'User_6';
+---------------------+------------------------------------------+
|  Type               |  Info                                    |
+=====================+==========================================+
| CREATE_CHANGELOG_AS | {"ddl":{"fqn":"d39511ce-c918-4637-9524-1 |
|                     | 63183452274.demodb.public.pageviewscl_6" |
|                     | ,"type":"CHANGELOG","db_name":"demodb"," |
|                     | schema_name":"public","name":"pageviewsc |
|                     | l_6","store_name":"demostore"},"sources" |
|                     | :[{"fqn":"d39511ce-c918-4637-9524-163183 |
|                     | 452274.demodb.public.pageviewscl","type" |
|                     | :"CHANGELOG","db_name":"demodb","schema_ |
|                     | name":"public","name":"pageviewscl","sto |
|                     | re_name":"demostore"}]}                  |
+---------------------+------------------------------------------+

Describe a CSAS JOIN statement

In this example, the described statement joins the pageviews Stream and users1Changelog changelog before filtering and writing its results into the temporaljoin stream:

demodb.public/demostore# DESCRIBE CREATE STREAM temporaljoin AS SELECT p.userid AS pvuid, u.userid, u.gender, p.pageid, u.interests[1] AS top_interest FROM pageviews p JOIN "users1Changelog" u ON u.userid = p.userid WHERE p.userid != 'User_5';
+------------------+------------------------------------------+
|  Type            |  Info                                    |
+==================+==========================================+
| CREATE_STREAM_AS | {"ddl":{"fqn":"d39511ce-c918-4637-9524-1 |
|                  | 63183452274.demodb.public.temporaljoin", |
|                  | "type":"STREAM","db_name":"demodb","sche |
|                  | ma_name":"public","name":"temporaljoin", |
|                  | "store_name":"demostore"},"sources":[{"f |
|                  | qn":"d39511ce-c918-4637-9524-16318345227 |
|                  | 4.demodb.public.pageviews","type":"STREA |
|                  | M","db_name":"demodb","schema_name":"pub |
|                  | lic","name":"pageviews","store_name":"de |
|                  | mostore"},{"fqn":"d39511ce-c918-4637-952 |
|                  | 4-163183452274.demodb.public.users1Chang |
|                  | elog","type":"CHANGELOG","db_name":"demo |
|                  | db","schema_name":"public","name":"users |
|                  | 1Changelog","store_name":"demostore"}]}  |
+------------------+------------------------------------------+

Describe an INSERT INTO statement

The example below shows that the statement filters the pageviews stream and writes its results into the existing relation called pageviews_6 of type stream:

demodb.public/demostore# DESCRIBE INSERT INTO PAGEVIEWS_6 SELECT userid, pageid FROM PAGEVIEWS WHERE userid = 'User_6';
+-------------+------------------------------------------+
|  Type       |  Info                                    |
+=============+==========================================+
| INSERT_INTO | {"sink":{"fqn":"d39511ce-c918-4637-9524- |
|             | 163183452274.demodb.public.pageviews_6", |
|             | "type":"STREAM","db_name":"demodb","sche |
|             | ma_name":"public","name":"pageviews_6"," |
|             | store_name":"demostore"},"sources":[{"fq |
|             | n":"d39511ce-c918-4637-9524-163183452274 |
|             | .demodb.public.pageviews","type":"STREAM |
|             | ","db_name":"demodb","schema_name":"publ |
|             | ic","name":"pageviews","store_name":"dem |
|             | ostore"}]}                               |
+-------------+------------------------------------------+