ELT Create View

Overview

You can use this Snap to create a new view when the view does not exist in the given database and/or schema or if the view already exists in the given database and/or schema, but you choose to drop the existing view and recreate it.

Snap Type

ELT Create Snap is a WRITE-type Snap that writes the output of the query in the selected view.

Prerequisites

Valid accounts and access permissions to connect to the following targets: Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery

Support for Ultra Pipelines

Does not support Ultra Pipelines

Limitations

None.

Known Issues

None.

Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

  • Min: 1

  • Max: 1

  • ELT Transform

  • ELT Copy

Details of the required view such as view name, names of the columns that are to be included in the view

Output

Document

  • Min: 1

  • Max: 1

  • ELT Copy

  • ELT Intersect

A SQL query for the table in the view specified in the Snap.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter while running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

  • Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

  • Asterisk ( * ): Indicates a mandatory field.

  • Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon (): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon ( ): Indicates that you can add fields in the fieldset.

  • Remove icon ( ): Indicates that you can remove fields from the fieldset.

Field Name

Field Type

Field Dependency

Description

Field Name

Field Type

Field Dependency

Description

Label*

 

Default ValueELT Create View
ExampleELT view query

String

None

The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline.

 

Get preview data

Default ValueNot selected
ExampleSelected

Checkbox

None

Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation.

In case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline.

The number of records displayed in the preview (upon validation) is the smaller of the following:

  • Number of records available upon execution of the SQL query generated by the Snap.

  • The value set in the Preview Data Count setting (default is 50 records).

Rendering Complex Data Types in Databricks Lakehouse Platform

Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type.

Database Name

 

Default ValueNone
ExampleRedshift

String/Expression

N/A

The name of the database in which the target/source tables are located. Leave it blank to use the database name specified in the account settings.

If your target database is Databricks Lakehouse Platform (DLP), you can, alternatively, mention the file format type for your table path in this field. For example, DELTA, CSV, JSON, ORC, AVRO. See Table Path Management for DLP section below to understand the Snap's behavior towards table paths.

Schema Name (Not applicable to Databricks Lakehouse Platform)*

 

Default ValueN/A
ExamplePUBLIC

String/Expression

N/A

The name of the database schema. In case it is not defined, then the suggestion for the table name retrieves all tables names of all schema in the specified database (except for DLP) when you click .

Target View Name*

 

Default ValueN/A
ExamplePUBLIC.EMPLOYEES `/mnt/elt/emp`

String/Expression

N/A

The name of the view in which you want to build the SQL queries. In case it is not defined, then the Snap retrieves all view names associated with the specified schema from the suggestions.

Target View Action*

 

Default ValueN/A
ExamplePUBLIC.EMPLOYEES `/mnt/elt/emp`

Dropdown

N/A

Select the action to perform on the target view. Options available are:

  • Error if the view already exists: The Snap immediately errors out if the view already exists. Otherwise it will proceed forward in creating the view

  • Drop and Create view: The Snap attempts to drop the view that already exists. If there is no such view, it will move forward and create the view

  • Do not error if the view already exists: The Snap does not create the view if it already exists, otherwise goes ahead and creates the view

View Column Names

 

Default ValueError if the view already exists
Example Drop and Create a view

This field set enables you to specify the names of the column to be used. Specify each value in a separate row. Click + to add a new row. You must specify all the columns.

View Column Name

 

Default ValueN/A
ExampleEMP_ID_NEW

String

None

Enter the column name to be used for a view.

Troubleshooting

Error

Reason

Resolution

Error

Reason

Resolution

Database cannot be blank.

(when seeking the suggested list for Schema Name field)

Suggestions in the Schema Name and Target Table Name fields do not work when you have not specified a valid value for the Database Name field in this Snap.

Specify the target Database Name in this Snap to view and choose from a suggested list in the Schema Name and Target Table Name fields respectively.

Database encountered an error during preview processing

(Target CDW: BigQuery)

This can happen when the target database cannot recognize/interpret the table name.

Ensure that the table name is valid and the table with provided name exists. Using a wildcard in the SQL editor mode, ensure that the table name is enclosed between backticks (`). For example: `scd*`, `mode*`.

Examples

In the following example, ELT Select Snap has the target table with the details of the employees. It is connected to the ELT Create View Snap to create a new view by giving the details of the column names.

First, we use the ELT Select Snap to build a query to retrieve all records from the target table.

Upon execution, this Snap builds the query as shown below:

Then, we add the ELT Create View Snap and configure it as needed. In this example, we want to create a new view for the columns mentioned in the View Column Names field. So, we configure the ELT Create View Snap as shown below:

Based on this configuration, the ELT Create View Snap retrieves the following records to create a new view.

Downloads