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 Name | Data Type | Description | Default Value | Example | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
Label | String |
| ELT Sample | Sample Order Data | ||||||||
Get preview data | Check box |
| Not selected | Selected | ||||||||
Sampling Type | Drop-down list | Choose the sampling type to query your data. Available options are:
| Number of Records | Percentage of Records | ||||||||
Number of Records | String/Expression | Activates when you select Number of Records for Sampling Type. Enter the number of records to output.
| NA | 10 | ||||||||
Percentage of Records | String/Expression | Activates when you select 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 | 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.
...