CREATE TEMPORARY VIEW (Lakeflow Declarative Pipelines)

Use the CREATE TEMPORARY VIEW statement to create temporary views in your Lakeflow Declarative Pipelines.

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.

• TBLPROPERTIES

  An optional list of table properties for the table.

• query

  This clause populates the table using the data from a query. When you specify a query and a list of columns together, the column list must contain all the columns returned by the query, or you will 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 or table in the catalog have the same name, references to the name will resolve to the temporary view.

Examples

-- 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 MATERIALIZED VIEW sales_by_date
  AS SELECT * FROM my_view;