ELT Limit
In this article
An account for the Snap
You must define an account for this Snap to communicate with your target CDW. Click the account specific to your target CDW below for more information:
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
clauseON
condition (ELT Join, ELT Merge Into Snaps)HAVING
clauseQUALIFY
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)
Workaround
As a workaround while using these SQL query constructs, you can:
- Precede this Snap with an ELT Transform Snap to re-map the '_' column references to suitable column names (that do not begin with an _ ) and reference the new column names in the next Snap, as needed.
- 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.
- Write the data in the CSV file to a DLP table beforehand, as in:
- 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
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 LIMIT clause. |
Output | Document |
|
| The incoming SQL query with the LIMIT clause. |
Snap Settings
SQL Functions and Expressions for ELT
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.
Parameter Name | Data Type | Description | Default Value | Example |
---|---|---|---|---|
Label | String | Specify a name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline. | ELT Limit | Top 10 Records |
Get preview data | Check box | Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation. In the case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline. The number of records displayed in the preview (upon validation) is the smaller of the following:
Rendering Complex Data Types in Databricks Lakehouse Platform Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type. | Not selected | Selected |
ELT Limit | Integer | Required. The maximum number of records to be displayed. | N/A | 3 |
ELT Limit Offset (Not valid for Azure Synapse and Databricks Lakehouse Platform) | Integer | The number of rows from the top that you want to skip. If this field is not configured, then the Snap fetches from the first row of records. For example, if you specify 2 here, then the Snap fetches records from the third row up to the number of rows specified in the ELT Limit field. This Snap does not support applying an ELT Limit Offset (the value in this field is ignored) when fetching data from an Azure Synapse or a Databricks Lakehouse Platform database. | N/A | 2 |
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.
Download this Pipeline.Downloads
Important Steps to Successfully Reuse Pipelines
- Download and import the Pipeline into SnapLogic.
- Configure Snap accounts as applicable.
- Provide Pipeline parameters as applicable.