In this article
...
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 |
...
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.
...