In this article
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.
None.
None.
Type of view: Document/Binary/Both. Get number of views from the Views tab in the Snap. List at least three compatible Snaps in each category. Provide a brief of the input/output required. If the input/output is optional then preface the description with "Optional." For example, "Transaction data complying with the Orderful schema as a JSON document."
| 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. |
| 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 | |||
None.
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:
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.
|
Edit the Excerpt Include macro below to link to the Snap Pack page for this Snap page. Ensure that the heading Snap Pack History is not within the Snap Pack's history Excerpt.
Provide links to the endpoint's official documentation, any relevant or related internal documentation, and any other links you have referred to on this page. Care should be taken when referencing external sites/documentation that it is an official site/documentation. Do not refer to forums such as stackoverflow.