ELT Create View
In this article
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 |
---|---|---|---|---|
Input | Document |
|
| Details of the required view such as view name, names of the columns that are to be included in the view |
Output | Document |
|
| 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:
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 |
---|---|---|---|
Label*
Default Value: ELT Create View | 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 Value: Not selected | 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:
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 Value: None | 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 Value: N/A | 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 Value: N/A | 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 Value: N/A | Dropdown | N/A | Select the action to perform on the target view. Options available are:
|
View Column Names
Default Value: Error if the view already exists | 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 Value: N/A | String | None | Enter the column name to be used for a view. |
Troubleshooting
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.