Overview

Use this Snap to add the LIMIT clause to the incoming SQL query. The LIMIT clause sets an upper limit on the number of records returned by the SQL query. Additionally, you can also specify an offset value so that the Snap displays the records from the specified offset value up to the number of records based on the specified limit. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.

Prerequisites

None.

Limitations

• This Snap does not support applying an ELT Limit Offset (the value in this field is ignored) when fetching data from an Azure Synapse database.

ELT Snap Pack  does not support Legacy SQL dialect of Google BigQuery. We recommend that you use only the BigQuery's Standard SQL dialect in this Snap.

Known Issue

In any of the supported target databases, this Snap does not appropriately identify nor render column references beginning with an _ (underscore) inside SQL queries/statements that use the following constructs and contexts (the Snap works as expected in all other scenarios):

• WHERE clause (ELT Filter Snap)
• WHEN clause
• ON condition (ELT Join, ELT Merge Into Snaps)
• HAVING clause
• QUALIFY clause
• Insert expressions (column names and values in ELT Insert Select, ELT Load, and ELT Merge Into Snaps)
• Update expressions list (column names and values in ELT Merge Into Snap)
• Secondary AND condition

• Inside SQL query editor (ELT Select and ELT Execute Snaps)

• In case of Databricks Lakehouse Platform where CSV files do not have a header (column names), a simple query like SELECT * FROM CSV.`/mnt/csv1.csv` returns default names such as _c0, _c1, _c2 for the columns which this Snap cannot interpret. To avoid this scenario, you can:
  • Write the data in the CSV file to a DLP table beforehand, as in: CREATE TABLE csvdatatable (a1 int, b1 int,…) USING CSV `/mnt/csv1.csv` where a1, b1, and so on are the new column names.
  • Then, read the data from this new table (with column names a1, b1, and so on) using a simple SELECT statement.
• In case of Databricks Lakehouse Platform, all ELT Snaps' preview data (during validation) contains a value with precision higher than that of the actual floating point value (float data type) stored in the Delta. For example, 24.123404659344 instead of 24.1234. However, the Snap reflects the exact values during Pipeline executions.

Snap Input and Output

Snap Settings

Troubleshooting

None.

Examples

Retrieving a Fixed Number of Records from a Table

In a typical scenario, we use the SELECT command to retrieve records from a table. We can control how many records are retrieved by specifying a WHERE condition. However, if we want to limit the number of records retrieved from the table without any conditions, or if we want to retrieve a fixed number of records starting from a specific row, we must use the SELECT command with the LIMIT clause. This example shows how we can use the ELT Limit Snap to achieve this result.

First, we use the ELT Select Snap to build a query to retrieve all records from the target table.

Upon execution, this Snap builds the query as shown below:

Then, we add the ELT Limit Snap and configure it as needed. In this example, we want to retrieve the next 5 records after the first record. So, we configure the ELT Limit Snap as shown below:

Based on this configuration, the ELT Limit Snap retrieves 5 (ELT Limit field's value) records starting from the second record (ELT Limit Offset field's value)

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

Downloads

Snap Pack History