CREATE TEMPORARY VIEW (pipelines)

Use the CREATE TEMPORARY VIEW statement to create temporary views in a pipeline.

Syntax

The following describes the syntax for declaring views with SQL:

CREATE TEMPORARY VIEW view_name
  [(
    [ col_name [ COMMENT col_comment ] [, ...] ]
    [ column_constraint ] [, ...]
  )]
  [ 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.

  • column_constraint

    • CONSTRAINT expectation_name EXPECT (expectation_expr) [ ON VIOLATION { FAIL UPDATE | DROP ROW } ]

      Adds data quality expectations to the view. These data quality expectations can be tracked over time and accessed through the pipeline's event log. A FAIL UPDATE expectation causes the processing to fail when both creating the view as well as refreshing the pipeline. A DROP ROW expectation causes the entire row to be dropped if the expectation is not met. See Manage data quality with pipeline expectations.

      expectation_expr may be composed of literals, column identifiers within the view, and deterministic, built-in SQL functions or operators except:

      Also expr must not contain any subquery.

  • view_comment

    An optional description for the view.

  • TBLPROPERTIES

    An optional list of table properties for the view.

  • query

    This clause populates the view 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 an error occurs. 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 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;

-- Create a temporary view with a data quality expectation
CREATE TEMPORARY VIEW valid_sales (
  CONSTRAINT valid_total_sales EXPECT (total_sales > 0) ON VIOLATION DROP ROW
)
  AS SELECT date(sales_date) AS sales_day, SUM(sales) AS total_sales FROM sales GROUP BY date(sales_date);
  • Last updated on