In this article
...
Use this Snap to perform build transformation-based SQL queries on the input tables. Transformations such as renaming columns, selecting a set of columns instead of all the columns, and any row-based SQL expressions. Use the Input Schema and Target Schema lists displayed on both sides of the Mapping Table to drag and drop entities from the schemas into the respective columns in the Mapping Table. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.
Prerequisites
...
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 Transform | Map Data | ||||||||
Get preview data | Checkbox | 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. | Pass through | Check box
The number of records displayed in the preview (upon validation) is the smaller of the following:
| Not selected | Selected | ||||||
Pass through | Checkbox | Select this to include the columns in the upstream Snap's query projection along with the existing columns that are specified in the mapping table. | Not selected | Selected | ||||||||
Input Schema | Use this field set to select or drag and drop fields available in the input schema coming from the upstream Snap into the Mapping Table and define the expression , as needed for the transformation. This field set contains the following fields.
| |||||||||||
Select All | Hyperlink | Click this hyperlink to select (the check boxes checkboxes of) for using all the fields from the input schema to define your transformation criteria in the Mapping table. Drag and drop the selected fields to populate all their field names in the Expression column. | N/A | N/A | ||||||||
Deselect All | Hyperlink | Click this hyperlink to deselect all the selected check boxes checkboxes to restart selecting the fields needed to define your transformation criteria. | N/A | N/A | ||||||||
(Search/Find) | String | Start entering a field name to filter the list by the entered keyword and select the fields needed for defining the transformation. | N/A | N/A | ||||||||
All | Drop-down list | Change the selection in this drop-down list to filter the list of fields displayed in the Input Schema. The available options are:
| All | Selected | ||||||||
Mapping Table | This field set enables you to specify the transformations you want to perform on the columns/records in the source table. Each different transformation must be specified in a separate row. Click + to add a new row. This field set consists of the following fields:
| |||||||||||
Expression | String/Expression | The column and the transformation operation that you want on that column. | N/A | EMPLOYEE ORDER_NUMBER + 1 | ||||||||
Target Path | String | The name to be assigned to the column.
| N/A | EMPLOYEE ORDER_NUMBER_NEW | ||||||||
Output Schema | Use this field set to select or drag and drop fields available in the output schema coming from the downstream Snap into the Mapping Table and define the expression, as needed for the transformation. Output schema fields are populated upon validation of the Pipeline and this depends on the downstream Snap's configuration. This field set contains the following fields.
| |||||||||||
Select All | Hyperlink | Click this hyperlink to select (the check boxes of) for mapping all the fields from the output schema with corresponding input field expressions (your transformation criteria) in the Mapping table. drag and drop the selected fields to populate all their field names in the Target Path column. | N/A | N/A | ||||||||
Deselect All | Hyperlink | Click this hyperlink to deselect all the selected check boxes to restart selecting the output fields needed to define your transformation criteria. | N/A | N/A | ||||||||
(Search/Find) | String | Start entering a field name to filter the list by the entered keyword and select the fields to include in the transformation criteria. | N/A | N/A | ||||||||
All | Drop-down list | Change the selection in this drop-down list to filter the list of entries displayed in the Output Schema. The available options are:
| All | Selected | ||||||||
Input Preview | Display-only | This section of the Snap's Settings displays the preview (partial result) of the incoming data from the previous Snap in the Pipeline. | N/A | N/A | ||||||||
Output Preview | Display-only | This section of the Snap's Settings displays the preview (partial result) of applying the transformation criteria defined in the Mapping table. | N/A | N/A | ||||||||
Get preview data | Check box |
| Not selected | Selected |
...