In this article

Overview

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

None.

Limitations

This Snap does not display the list of schema entities in the following scenarios:

• Until the first validation of the Pipeline.
• When the downstream Snaps are not configured to point to valid database table/columns.

Known Issues

• This Snap displays incorrect data types (string instead of the actual data type) for column names populated in its Input schema section.

Snap Input and Output

Snap Settings

Parameter Name	Data Type	Description	Default Value	Example
Label	String		ELT Transform	Map Data
Pass through	Check box	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 Deselect All (Search/Find) box All
Select All	Hyperlink	Click this hyperlink to select (the check boxes 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 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. To view all fields available in the Input Schema. Mapped. To view only the input fields that are selected/mapped in the Mapping table. Unmapped. To view only the input fields that are not selected/mapped in the Mapping table.	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 Target Path The output query contains only those columns that are specified here unless the Pass through check box is also selected.
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. If the Target Path field is empty for a specified expression, the respective column is deleted from the output. See the Example below.	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 Deselect All (Search/Find) box All
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. To view all fields available in the Output Schema. Mapped. To view only the target fields that are selected/mapped in the Mapping table. Unmapped. To view only the target fields that are not selected/mapped in the Mapping table.	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

Troubleshooting

None.

Examples

Retrieving Specified Columns from a Table

We need a SELECT query with only the columns that we want to retrieve. This example shows how we can use the ELT Transform Snap to achieve this result.

First, we use the ELT Select Snap to build a query to retrieve all records from the target table.

Upon execution, this Snap builds the query as shown below:

The table has several columns. But, we want to retrieve only the CUST_CODE and ORD_AMOUNT columns. Therefore, we add the ELT Transform Snap and configure it as shown below:

Based on this configuration, the ELT Transform Snap builds a query as shown below:

We can add also an ELT Insert-Select Snap downstream and write the result of this query into another table.

Download this Pipeline.

Using Empty Target Paths to Omit Rows from the Snap Output

We can use the ELT Transform Snap to retrieve information from specific columns in a database table or the input schema instead of returning data from all the available columns. This example shows how we can use the ELT Transform Snap to achieve this result.

First, we configure the ELT Select Snap to build a query to retrieve all records from the "TEST_DATA"."ORG1" table.

Next we use the ELT Transform Snap and configure the Expressions and Target Path in the Mapping table as shown below. Note that we intentionally leave the Target Path blank for DEPT and LOCAL fields, so as to not retrieve these columns in the query. We ensure to select Pass through to avoid duplicate fields in the query if same fields are present in the Mapping table.

Upon validation, the ELT Transform Snap builds a query as shown below. Note that DEPT and LOCAL fields do not appear in the query.

Download the Pipeline.

Downloads

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. 

See Also

• SnapLogic Product Glossary
• Getting started with SnapLogic