In this article
| Table of Contents | ||||
|---|---|---|---|---|
|
...
| 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 Select | Fetch Sales Data | |||||||||
| Get preview data | Checkbox |
| Not selected | Selected | |||||||||
| Database Name | String | 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. | N/A | TESTDB DELTA ORC | |||||||||
| Schema Name (Not applicable to Databricks Lakehouse Platform) | String | 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
| N/A | PUBLIC | |||||||||
| Table Name | String | The name of the table or view for which you want to build the SQL queries. In case If it is not defined, then the Snap retrieves all the table names and views associated with the specified schema when you click click
If your target database is Databricks Lakehouse Platform (DLP), you can, alternatively, mention include the table path in this field. For example, `/mnt/elt/mytabletarget`. See Learn more about the Snap’s behavior toward paths in the Table Path Management for DLP section to understand the Snap's behavior towards table paths.
| N/A | PUBLIC.EMPLOYEES `/mnt/elt/emp` | |||||||||
| Enable SQL query editor | Checkbox | Select this to enable the SQL query editor. You can use this editor to build all types of SQL queries that will execute on multiple tables. Enabling the SQL query editor activates the fieldset Input View to Virtual Table Map in the Snap.
| Not selected | Selected | |||||||||
| Input View to Virtual Table Map | Activates when you select the Enable SQL query editor checkbox. This is useful when you enable multiple input views in the ELT Select Snap. Use this field set to map each input view to a dummy table. You will reference the table name specified in each input view with this dummy/virtual table name.
Click + to add rows. Each input view must be specified in a separate row. This field set consists of the following fields:
| ||||||||||||
| Input View | String | Select the input view whose table reference is to be mapped. | N/A | input2 | |||||||||
| Virtual Table Name | String | Specify the dummy/virtual table name that will be assigned to the table reference in the selected input view. | N/A | tbl1 | |||||||||
| SQL Query Editor | Query editor | Enter the SQL queries that you want to build using this Snap. Even though the Snap's name is ELT Select, you can use this SQL query editor to build any SQL query.
| N/A | SELECT DISTINCT FROM MYTABLE | |||||||||
...
Method-2: Dynamic Substitutions
You must enable expressions when referencing table names using Pipeline parameters. Format the query, except the Pipeline parameter's reference, as a string.
For example, if you you want to write a SELECT query to fetch records from a table and you are passing the table's name in the Pipeline parameter table:
| Code Block |
|---|
"SELECT * FROM" + _table |
In the example above, you must still use JSON-paths if you want to use another Pipeline parameter for substituting values in the SQL:
| Code Block |
|---|
"SELECT * FROM " + _table + " WHERE colname = _name" |
...
| Note | ||
|---|---|---|
| ||
When expressions are disabled, use \ as an escape character to treat underscore (_) as a string. For example:
|
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. |
| 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. | |
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
| Multiexcerpt include macro | ||||
|---|---|---|---|---|
|
...