Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

Table of Contents
maxLevel2
absoluteUrltrue

Multiexcerpt include
templateData[]
MultiExcerptNameME_OE_ELT_Snap_Accounts
addpanelfalse
PageWithExcerptELT Aggregate

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
nameME_ELT_GBQ_StandardSQL_UOI
pageELT Copy

Known Issues

Multiexcerpt include macro
nameME_ELT_KI_Underscore_Refs
pageELT Snap Pack

Snap Input and Output

Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
Input 

Document

  • Min: 1
  • Max: 1
  • ELT Select
  • ELT Transform
  • ELT Filter
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

  • Min: 1
  • Max: 1
  • ELT Insert-Select
  • ELT Transform

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
titleSQL Functions and Expressions for ELT

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 NameTypeField DependencyDescription
LabelStringNone

Insert excerpt
File Writer
File Writer
nopaneltrue

Default ValueELT Sample

ExampleSample Order Data

Get preview dataCheckboxNone

Multiexcerpt include macro
namegetpreviewdata
pageELT Intersect

Default Value: Not selected
Example: Selected
Sampling TypeDrop-down list

None

Choose the sampling type to query your data.

Available options are:

  • Number of Records
  • Percentage of Records

Default ValueNumber of Records

ExamplePercentage of Records
Number of RecordsString/ExpressionSampling Type is Number of Records

Enter the number of records to output.

Info

The ELT Sample Snap outputs the entire table if the total number of records in the table is less than the value specified here.

Default Value: NA

Example10
Percentage of RecordsString/ExpressionSampling 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.

Info
  • The number of records in the output is rounded off to the lower value if the percentage leads to a decimal. For example, if you enter 75 and if the table has 10 records, the output yields 7 records since 75% of 10 is 7.5.
  • The number of records in the output is randomized and is up to a maximum of the percentage calculation specified above. Therefore, in the above instance, the output may contain 5, 6, or 7 records.

Default Value: NA

Example25

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.
Image Modified

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:
Image Modified

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:
Image Modified

This Snap builds the following query based on this configuration:
Image Modified

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:
Image Modified

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.
Image Modified

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

Download this Pipeline.

Downloads

Note
titleImportant steps to successfully reuse pipelines
  1. Download and import the Pipeline into SnapLogic.
  2. Configure Snap accounts as applicable.
  3. Provide Pipeline parameters as applicable.

Attachments
patterns*.slp, *.zip


Insert excerpt
ELT Snap Pack
ELT Snap Pack
nopaneltrue


See Also