Create View Component

Create View Component



Create View Component

The Create View component lets users output a view definition to a database. In some circumstances, this action may be preferable to writing the data to a physical table.

If there is an existing view with the same name, it will be replaced. If there is an existing table with the same name, then the step will fail - it will not replace an existing table, only a view.

The views created by this component are somewhat isolated from the other views created as part of a normal operation. It will NOT be dropped when the job is re-validated, but will be recreated at runtime when the job is executed.

Note:This component cannot create views using external schemas.


Redshift Properties

Property Setting Description
Name String Input the descriptive name of the component.
Schema Select Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
View Name String Provide a name for the view to be created.
Late Binding Select Selecting "Yes", creates a late-binding view. Late-binding views do not check underlying database objects, such as tables and other views, until the view is queried. As a result, users can alter or drop the underlying objects without dropping and recreating the view. If the user drops underlying objects, queries to the late-binding view will fail. If the query to the late-binding view references columns in the underlying object that aren't present, the query will fail.
The default setting is "No".
View Type Select Select the view type.
Materialized: A materialized view is a pre-computed data set derived from a query specification and stored for later use. Since the data is pre-computed, querying a materialized view is faster than executing the original query. Materialized views are advised when:
  • Query results contain a small number of rows and/or columns relative to the base table.
  • Query results contain results that require significant processing.
More information can be found here.
Standard: create standard views when:
  • The results of the view change often.
  • The results are not used often (relative to the rate at which the results change).
  • The query is not resource intensive, so it is not costly to re-run it.
More information can be found here.

Snowflake Properties

Property Setting Description
Name String Input the descriptive name of the component.
Database Select Select a database. A database is a logical grouping of schemas. Each database belongs to a single Snowflake account.
Schema Select Select the schema. A schema is a logical grouping of database “objects” (tables, views, etc.). Each schema belongs to a single database. The special value, [Environment Default], will use the schema defined in the environment.
View Name String Provide a name for the view to be created.
Secure View Select When Yes, the view definition and details are only visible to authorised users, i.e. users who are granted the role that owns the view. Default is No.
Snowflake advises that views should be defined as secure when they are specifically designated for data privacy. For more information about secure views, please read the Snowflake documentation.
View Type Select Select the view type.
Materialized: A materialized view is a pre-computed data set derived from a query specification and stored for later use. Since the data is pre-computed, querying a materialized view is faster than executing the original query. Materialized views are advised when:
  • Query results contain a small number of rows and/or columns relative to the base table.
  • Query results contain results that require significant processing.
Standard: (default setting) create standard views when:
  • The results of the view change often.
  • The results are not used often (relative to the rate at which the results change).
  • The query is not resource intensive, so it is not costly to re-run it.

BigQuery Properties

Property Setting Description
Name String Input the descriptive name of the component.
Project Select Select the Google Bigquery project. The special value, [Environment Default], will use the project defined in the environment.
For more information, refer to the BigQuery documentation.
Dataset Select Select the Google Bigquery dataset the source table exists on. The special value, [Environment Default], will use the dataset defined in the environment.
For more information, refer to the BigQuery documentation.
View Name String Provide a name for the view to be created.

Strategy

Generates a permanent, named view, containing the SQL generated by all the input tables up to the Create View component.

Example

This example creates a view over the Accounts table, applying a filter to select New Accounts.

Only the View Name needs to be completed:

Whenever "new_accounts_view" is read, it will reflect the current contents of the Accounts table with the filter applied.