...
...
...
...
In this article
| Table of Contents | ||||
|---|---|---|---|---|
|
| Multiexcerpt include | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Overview
...
An UNPIVOT operation converts columns into rows. You can use this Snap to add the UNPIVOT operation to an incoming SQL query for Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform (DLP), and BigQuery databases.
...
Azure Synapse and Databricks Lakehouse Platform (DLP) do not support Pivot/Unpivot operations natively, but the Snap supports them internally through a series of rewrite mechanisms.
Prerequisites
- Valid client ID
...
None.
Support for Ultra Pipelines
Capture if Ultra Mode is supported. If the Snap requires certain configurations to support Ultra mention those as well.
- Works in Ultra Pipelines.
- Works in Ultra Pipelines if ....
- Does not support Ultra Pipelines.
See Snap Support for Ultra Pipelines.
Limitations and Known Issues
Limitation
| Multiexcerpt include macro | ||||
|---|---|---|---|---|
|
Known Issues
| Multiexcerpt include macro | ||||
|---|---|---|---|---|
|
Snap Input and Output
| Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
| Input |
...
Document
...
...
Document
Binary
...
Document |
|
...
- Mapper Snap
- Copy Snap
- ..
...
| The SQL query in which to add the UNPIVOT function. Typically, it is a SELECT query reading the source table. | |
| Output | Document |
|
...
- ..
- ..
| The incoming SQL query with the UNPIVOT function. |
Snap Settings
...
- <State what the field contains>. Available options are: <bullet list of LoVs>
The Salesforce API to be used. Available options are:... - Select the <category> that you want to use. Available options are...
* Option 1<italicized>: <third person singular form of the verb>
* Option 2<italicized>: <third person singular form of the verb>
Select the API that you want to use. Available options are:
Bulk API: Sends the Snap execution request details as a bulk API call.
REST API: ... - Indicates.... Available options are:
Indicates how the Snap must be executed. Available options are:
* Validate & Execute: Performs limited execution of the Snap (up to 50 records) during Pipeline validation; performs full execution of the Snap (unlimited records) during Pipeline execution.
* Execute only: Performs full execution of the Snap during Pipeline execution; does not execute the Snap during Pipeline validation.
* Disabled: Disables the Snap and, by extension, its downstream Snaps.
Check boxes:
- If selected, <Snap behavior>.
If selected, an empty file is written when the incoming document has no data. - If selected, <behavior>. If not selected/Otherwise, <behavior>
Use "If not selected" if the first sentence is long.
If selected, the Snap uses the file path value as is. Otherwise, the Snap uses the file path value in the URL.
If selected, an empty file is written when the incoming document has empty data. If there is no incoming document at the input view of the Snap, no file is written regardless of the value of the property.
- Select to <action>
Use this if the behavior is binary. Either this or that, where the converse behavior is apparent/obvious.
Select to execute the Pipeline during validation.
Text Fields
- Describe what the field represents/contains. Additional details, as applicable, in a separate sentence. Include caveats such as the field being conditionally mandatory, limitations, etc.
The name of the account.
The account ID that you want to use to log in to the endpoint.
Required if IAM Role is selected.
Do not use this field if you are using batch processing.
Numeric Text Fields
- Describe what the field represents/contains. Additional details, as applicable, in a separate sentence. Include caveats such as the field being conditionally mandatory, limitations, etc. Include special values that impact the field's behavior as a bullet list.
The number of records in a batch.
The number of seconds for which you want the Snap to wait between retries.
The number of seconds for which the Snap waits between retries.
Use the following special values:
* 0: Disables batching.
* 1: Includes all documents in a single request.
...
Field Sets
Specify advanced parameters that you want to include in the request.
This fieldset consists of the following fields:
- Field 1
- Field 2
- Field 3
Troubleshooting
...
Batch execution failed
...
The Pipeline ended before the batch could complete execution due to a connection error.
Verify that the Batch size field is configured to handle the inputs properly. If you are not sure when the input data is available, configure this field as zero to keep the connection always open.
Examples
Excluding Fields from the Input Data Stream
You can exclude the fields that you do not require from the input data stream by omitting them in the Input schema field set.
<screenshot of Pipeline/Snap>
...
| Info | |
|---|---|
|
...
**Delete Before Publishing**
Choose from the following sentences to document specific field types.
...
| |
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 Unpivot | Sales Data | ||||||||
| Get preview data | Checkbox |
| Not selected | Selected | ||||||||
| Value Column | String | Required. Enter the name for the generated column that shows the values corresponding to the columns specified in the Column List field set. | N/A | Sales | ||||||||
| Name Column | String | Required. Enter the name for the generated column that shows the names corresponding to the columns specified in the Column List field set. | N/A | Month | ||||||||
Column List | Required. Use this field set to specify the columns in the source table to convert into rows. You must add each column in a separate row. Click This field set contains the Column field. | |||||||||||
| Column | String | Required. Enter the names of the columns to convert into rows. | N/A | Jan Feb Mar | ||||||||
.png?version=1&modificationDate=1605177116214&cacheVersion=1&api=v2&width=20){kind=small}
Troubleshooting
None.
Example
Consolidating Quarterly Sales of an Employee
We want to consolidate the quarterly sales of an employee. The source table that captures the sales data is organized by month, meaning that the employee's sales are recorded in a single row, where the months are organized as columns. We need to first convert the column data into row data and then write this modified data into another table. This Pipeline demonstrates how we can use the ELT Unpivot Snap to accomplish this task.
First, we build a SELECT query to read the target table. To do so, we use the ELT Select Snap. Sales data is maintained in the table SALES_BY_3_MONTHS. We want to retrieve the sales data for the employee with ID 1. We configure the ELT Select Snap to read this table. Additionally, we also configure the Snap to show a preview of the SELECT query's execution:
A preview of the output from the ELT Select Snap is shown below:
As seen from the above screenshot, the sales data is organized by months. Each month is a separate column. Therefore, we use the ELT Unpivot Snap and configure it to change the columns into rows as shown below:
A preview of the output from the ELT Unpivot Snap is shown below:
Based on the ELT Unpivot Snap's configuration, the month-based columns are turned into rows under a new column MONTH. Similarly, the sales amounts for each of these months are placed under the AMOUNT column.
We use an ELT Insert-Select Snap to write the above output into another table as shown below:
We can now use the data in the new table SALES_BY_3_MONTHS_UNPIVOT_OUT to conduct further analysis and processing.
Downloads
| Note | ||
|---|---|---|
| ||
|
| Attachments | ||
|---|---|---|
|
| Insert excerpt |
|---|
...
|
...
|
See Also
...
...