...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
In this article
| Table of Contents | ||||
|---|---|---|---|---|
|
| Multiexcerpt include | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Overview
Use this Snap to add the SAMPLE clause to the incoming query. The SAMPLE clause creates a subset of the source table based on the specified number of rows. Alternatively, you can also create a subset of rows as a percentage of the number of rows in the source table. You can use this Snap to reduce the cost and resource overhead on the target database and file storage services such as S3 by testing the SQL query on a subset of the data instead of the whole table.
Prerequisites
None.
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 |
|
| The SQL query in which you want to add the SAMPLE clause. Typically, it would be a SELECT query reading the source table. |
| Output | Document |
|
| The incoming SQL query with the SAMPLE clause and a preview of the query's result if the Get preview data check box 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. |
| Field Name | Type | Field Dependency | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Label | String | None |
Default Value: ELT Sample Example: Sample Order Data | ||||||||||
| Get preview data | Checkbox | None |
Default Value: Not selected Example: Selected | ||||||||||
| Sampling Type | Drop-down list | None | Choose the sampling type to query your data. Available options are:
Default Value: Number of Records Example: Percentage of Records | ||||||||||
| Number of Records | String/Expression | Sampling Type is Number of Records | Enter the number of records to output.
Default Value: NA Example: 10 | ||||||||||
| Percentage of Records | String/Expression | Sampling Type is Percentage of Records | Enter the percentage number of records to sample your data based on a percentage value of the total records in the table.
Default Value: NA Example: 25 | ||||||||||
Troubleshooting
None.
Examples
Creating a Subset Table
We want to create a subset of a table for testing various SQL queries. We need a query with the SAMPLE clause. This Pipeline shows how we can use the ELT Sample Snap to accomplish this task.
First, we build a SELECT query to read the target table, ORDERS3. To do so, we use the ELT Select Snap and configure it as shown below. We also configure the Snap to show a preview of the SELECT query's execution:
Upon execution, the ELT Select Snap builds a SELECT query as shown along with a preview of the query's output below:
This table contains 34 records. We only want a sample of 10 records in the subset table. Therefore, we use the ELT Sample Snap and configure it as shown below:
This Snap builds the following query based on this configuration:
From the output preview, we can see that the subset contains 10 records.
Alternatively, if you want to fetch a percentage of records of the original table, you can configure the Snap as shown below:
| Note |
|---|
The number of records in the output is randomized to a maximum of the percentage specified here. In this example, since the original table has 34 records, specifying 50 in the Percentage of Records field yields a maximum of 17 records. |
Based on the Snap's configuration, the output contains a random sample of 14 records.
We can also add an ELT Insert-Select Snap downstream and write the result of this query into another table.
Downloads
| Note | ||
|---|---|---|
| ||
|
| Attachments | ||
|---|---|---|
|
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|