Skip to main content

CREATE TEMPORARY VIEW (DLT)

Use the CREATE TEMPORARY VIEW statement to create temporary views in your DLT pipelines.

note

CREATE LIVE VIEW and CREATE TEMPORARY LIVE VIEW are older syntax for the same command. CREATE TEMPORARY VIEW is the preferred syntax.

The only exception is CREATE LIVE VIEW supports expectations in the form of CONSTRAINT clauses. If you need to include expectations, use the CREATE LIVE VIEW syntax.

Syntax​

The following describes the syntax for declaring views with SQL:

CREATE TEMPORARY VIEW view_name
  [(
    [ col_name [ COMMENT col_comment ] [, ...] ]
  )]
  [ COMMENT view_comment ]
  [ TBLPROPERTIES ]
  AS query

Parameters​

  • view_name

    The name for the view.

  • col_name

    Optionally, you can specify columns for the resulting view. col_name is a name for the column.

  • col_comment

    When specifying columns, you can optionally specify a description for the column.

  • view_comment

    An optional description for the view.

  • query

    This clause populates the table using the data from query. When you specify a query and a list of columns together, the column list must contain all the columns returned by the query, otherwise you get an error. Any columns specified but not returned by query return null values when queried.

Limitations​

  • Temporary views are only persisted across the lifetime of the pipeline.
  • They are private to the defining pipeline.
  • They are not added to the catalog, and can have the same name as a view in the catalog. Within the pipeline, if a temporary view and a view in the catalog have the same name, references to the name will resolve to the temporary view.

Examples​

SQL
-- Create a temporary view, and use it
CREATE TEMPORARY VIEW my_view (sales_day, total_sales, sales_rep)
  AS SELECT date(sales_date) AS sale_day, SUM(sales) AS total_sales, FIRST(sales_rep) FROM sales GROUP BY date(sales_date), sales_rep;

CREATE OR REFRESH STREAMING TABLE sales_by_date
  AS SELECT * FROM STREAM my_view;
Last updated on