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
Parameter Field Name | Data Type | Field Dependency | Description | Default ValueExample | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Label | String | None |
Default Value: ELT Sample Example: Sample Order Data | ||||||||||||||
Get preview data | Check boxCheckbox | None |
| Not Default Value: Not selected | SelectedExample: 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 | Activates when you select Sampling Type is Number of Records for Sampling Type. | Enter the number of records to output.
| NA | Default Value: NA Example: 10 | ||||||||||||
Percentage of Records | String/Expression | Activates when you select Sampling Type is Percentage of Records for Sampling Type. | Enter the percentage number of records to sample your data based on a percentage value of the total records in the table.
| NA | 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.
...