In this article
...
| Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
| Input | Document |
|
| The database, schema, table names in which the query must be executed. You can also pass this information using an upstream ELT Snap. Additionally, you can also pass such information for several tables to the Snap using a separate input view for each table. |
| Output | Document |
|
| A SELECT SQL query for the table specified in the Snap. If you use the SQL editor, then an SQL query built based on your inputs in the editor. Optionally, the output also includes a preview of the query's output if the Get preview data check box checkbox is selected. |
Snap 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 Select | Fetch Sales Data | |||||||||
| Get preview data | Check boxCheckbox |
| 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 for which you want to build the SQL queries. In case it is not defined, then the Snap retrieves all table names associated with the specified schema when you click
If your target database is Databricks Lakehouse Platform (DLP), you can, alternatively, mention the table path in this field. For example,
| N/A | PUBLIC.EMPLOYEES `/mnt/elt/emp` | |||||||||
| Enable SQL query editor | Check boxCheckbox | 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" |
...