In this article
Use this Snap to perform the INSERT INTO SELECT operation on the specified table. The Snap creates a table and inserts the data if the target table does not exist. After successfully running a Pipeline that contains this Snap, you can check for the data updates made to the target table in one of the following ways:
Type of view: Document/Binary/Both. Get number of views from the Views tab in the Snap. List at least three compatible Snaps in each category. Provide a brief of the input/output required. If the input/output is optional then preface the description with "Optional." For example, "Transaction data complying with the Orderful schema as a JSON document."
Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| The data to be inserted into the target table. Ensure that the data corresponds to the target table's schema. |
Output | Document |
|
| A document containing the SQL SELECT query executed on the target database. |
You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports. |
Parameter Name | Data Type | Description | Default Value | Example | |||
---|---|---|---|---|---|---|---|
Label | String | ELT Insert-Select | Insert Employee Records | ||||
Get preview data | Check box | Not selected | Selected | ||||
Database Name | String | Required. Enter the name of the database in which the target table is 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. | N/A | TESTDB | |||
Schema Name (Not applicable to Databricks Lakehouse Platform) | String | Required. Enter the name of the database schema. In case it is not defined, then the suggestion for the schema name retrieves all schema names in the specified database when you click .
| N/A | "TEST_DATA" | |||
Target Table Name | String | Required. The name of the table into which you want to insert the data. If your target database is Databricks Lakehouse Platform (DLP), you can, alternatively, mention the target table path in this field. Enclose the DBFS table path between two
| N/A | "TEST_DATA"."DIRECT" EMPLOYEE_DATA EMPLOYEE_123_DATA REVENUE"-"OUTLET "net_revenue" | |||
Target Table Hash Distribution Column (Azure Synapse Only) | String/Expression | Specify the Hash distribution column name for the target table (in Azure Synapse), if the Snap creates a target table during the execution of the Snap. If the target table is created outside the Snap, you need not specify the target table column name.
| N/A | var table | |||
Insert Expression | This field set enables you to specify the values for a subset of the columns in the target table. The remaining columns are assigned null values automatically. You must specify each column in a separate row. Click to add rows. This field set consists of the following fields:
| ||||||
Insert Column | String | Enter the name of the column in the target table to assign values. | N/A | ORD_AMOUNT | |||
Insert Value | String | Enter the value to assign in the specified column. Repeat the column name if you want to use the values in the source table. You can also use expressions to transform the values. | N/A | ORD_AMOUNT ORD_AMOUNT+20 | |||
Overwrite | Check box | Select to overwrite the data in the target table. If not selected, the incoming data is appended. | Not selected | Selected |
A table path in Databricks Lakehouse Platform is the folder in the DBFS where the files corresponding to the target table are stored. You need to enclose the DBFS table path between two `
(backtick/backquote) characters.
# | File Format Type | Table Path exists?# | All other requirements are valid? | Snap Operation Result |
---|---|---|---|---|
1 | DELTA | Yes | Yes | Success |
2 | DELTA | No | Yes | Failure. Snap displays error message. |
3 | DELTA | Yes | No | Failure. Snap displays error message. |
4 | AVRO/CSV/JSON/ORC/other | Yes | Yes | Success. Snap creates a DELTA table. |
# We recommend that you specify a target table path that resolves to a valid data file. Create the required target file, if need be, before running your Pipeline.
Error | Reason | Resolution |
---|---|---|
Invalid placement of ELT Insert Select Snap | You cannot use the ELT Insert Select Snap at the beginning of a Pipeline. | Move the ELT Insert Select Snap to the middle or to the end of the Pipeline. |
Snap configuration invalid | The specified target table does not exist in the database for the Snap to insert the provided subset values. | Ensure that the target table exists as specified for the ELT Insert Select Snap to insert the provided subset values. |
Database encountered an error during Insert Select processing. | ||
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. |
Column names in Snowflake tables are case-sensitive. It stores all columns in uppercase unless they are surrounded by quotes during the time of creation in which case, the exact case is preserved. See, Identifier Requirements — Snowflake Documentation. | Ensure that you follow the same casing for the column table names across the Pipeline. | |
[Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0 Cannot create table (' | A non-Delta table that currently exists is corrupted and needs to be dropped from the schema before creating a Delta-formatted table. However, this corrupted table can only be dropped manually—by accessing the DBFS through a terminal. The Pipeline cannot perform this operation. | Drop the corrupted table and then try creating the new table in Delta format (using the Pipeline). To drop the corrupted table, from the terminal, access the DBFS and run the following command:
|
Syntax error when database/schema/table name contains a hyphen (-) such as in (CDW: Azure Synapse) | Azure Synapse expects any object name containing hyphens to be enclosed between double quotes as in "<object-name>" . | Ensure that you use double quotes for every object name that contains a hyphen when your target database is Azure Synapse. For example: default."schema-1"."order-details" . |
Merging Two Tables and Creating a New TableWe need a query with the UNION clause to merge two tables. To write these merged records into a new table, we need to perform the INSERT INTO SELECT operation. This example demonstrates how we can do both of these tasks. First, we build SELECT queries to read the target tables. To do so, we can use two ELT Select Snaps, in this example: Read Part A and Read Part B. Each of these Snaps is configured to output a SELECT * query to read the target table in the database. Additionally, these Snaps are also configured to show a preview of the SELECT query's execution as shown:
A preview of the outputs from the ELT Select Snaps is shown below:
Then, we connect the ELT Union Snap to the output view of the ELT Select Snaps. The SELECT * queries in both of these Snaps form the inputs for the ELT Union Snap. The ELT Union Snap is also configured to eliminate duplicates, so it adds a UNION DISTINCT clause. Upon execution, the ELT Union Snap combines both incoming SELECT * queries and adds the UNION DISTINCT clause. To perform the INSERT INTO SELECT operation, add the ELT Insert-Select Snap. We can perform this operation on an existing table. Alternatively, we can also use this Snap to write the records into a new table. To do so, we configure the Target Table Name field with the name of the new table. The result is a table with the specified table name in the database after executing this Pipeline. |
|
Edit the Excerpt Include macro below to link to the Snap Pack page for this Snap page. Ensure that the heading Snap Pack History is not within the Snap Pack's history Excerpt.