...
If the last Snap in the Pipeline takes 2 to 5 seconds to update the runtime, the ELT Pipeline statistics are not displayed even after the Pipeline is completed. The UI does not auto-refresh to display the statistics after the runtime.
Workaround: Close the Pipeline statistics window and reopen it to see the ELT Pipeline statistics.When you return to the Snap Statistics tab from the Extra Details tab in the Pipeline Execution Statistics pane, it contains the status bar (Pipeline execution status) instead of the Download Query Details hyperlink and the individual counts of Records Added, Records Updated, and Records Deleted.
- When your Databricks Lakehouse Platform instance uses Databricks Runtime Version 8.4 or lower, ELT operations involving large amounts of data might fail due to the smaller memory capacity of 536870912 bytes (512MB) allocated by default. This issue does not occur if you are using Databricks Runtime Version 9.0.
- ELT Pipelines created prior to 4.24 GA release using one or more of the ELT Insert-Select, ELT Merge Into, ELT Load, and ELT Execute Snaps may fail to show expected preview data due to a common change made across the Snap Pack for the 4.26 GA release. In such a scenario, replace the Snap in your Pipeline with the same Snap from the Asset Palette and configure the Snap's Settings again.
- In case you are writing into a Snowflake target table, this Snap attempts to create the target table even when it exists in the database.
- Suggestions displayed for the Schema Name field in this Snap are from all databases that the Snap account user can access, instead of the specific database selected in the Snap's account or Settings.
...
Info | ||
---|---|---|
| ||
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 | Checkbox |
| 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 or view 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" | |||||||||||
Advanced Options | Checkbox | Select this checkbox to define the mapping of your source and target table columns scenario with or without the Insert Expressions list. When selected, it activates the Operation Types field. | Deselected | Selected | |||||||||||
Operation Types | Dropdown list | Choose one of the following options that best describes your source data and the INSERT preference:
| Source Columns Order | Some Source and Target Column names are identical | |||||||||||
Table Option List | This field set enables you to specify the table options you want to use on the target table. These options are populated based on the Snap Account (target CDW) selected. You must specify each table option in a separate row. Click to add rows. This field set contains one field:
| ||||||||||||||
Table Option | String/Suggestion | Click the Suggestions icon ( ) to view and select the table option you want to apply for loading data into the target table. | N/A | DISTRIBUTION = HASH ( cust_name ) | |||||||||||
Insert Expression List | 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 is disabled if you select All Source and Target Column names are identical in the Operation Types field. 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 | Checkbox | Select to overwrite the data in the target table. If not selected, the incoming data is appended. | Not selected | Selected |
...
# 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.
Pipeline Execution Statistics
As a Pipeline executes, the Snap shows the following statistics updating periodically. You can monitor the progress of the Pipeline as each Snap performs executes.
Records Added
Records Updated
Records Deleted
You can view more information when clicking the Download Query Details link.
Note |
---|
Note: The statistics are also available in the output view of the child ELT Pipeline. |
Troubleshooting
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 (' (Target CDW: Databricks Lakehouse Platform) | The specified location contains one of the following:
So, the Snap/Pipeline cannot overwrite this table with the target table as needed. | Ensure that you take appropriate action (mentioned below) on the existing table before running your Pipeline again (to create another Delta table at this location). Move or drop the existing table from the schema manually using one of the following commands: Access the DBFS through a terminal and run:
OR Use a Python notebook and run:
|
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" . |
...