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 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)

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.
  • 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/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
Input 

Document

  • Min: 1
  • Max: 1
  • ELT Select
  • ELT Intersect
The SQL query in which you want to add the LIMIT clause.
Output

Document

  • Min: 1
  • Max: 1
  • ELT Copy
  • ELT Insert-Select

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 NameData TypeDescriptionDefault ValueExample 
LabelString
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 LimitTop 10 Records
Get preview dataCheck 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:

  • Number of records available upon execution of the SQL query generated by the Snap.

  • The value set in the Preview Data Count setting (default is 50 records).

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 selectedSelected
ELT LimitIntegerRequired. The maximum number of records to be displayed.N/A3
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/A2

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

  1. Download and import the Pipeline into SnapLogic.
  2. Configure Snap accounts as applicable.
  3. Provide Pipeline parameters as applicable.

  File Modified

File ELT_Select_Limit_Example.slp

Aug 20, 2020 by Mohammed Iqbal


Snap Pack History

 Click here to expand...

Release

Snap Pack Version 

Date

Type

Updates

August 2024main28010 Latest

The ELT Insert-Select Snap no longer fails to execute SQL statements that contain multiple multiline comment character pairs (/* and */) and/or multiple quoted substrings. Quoted substrings refer to schema, database, table, or column identifiers, which are delimited to allow special characters.

  • We recommend that you upgrade your main27765 (August 2024 GA release) ELT Snap Pack to this latest version.

August 2024main27765 Stable

Upgraded the jOOQ library for the ELT Snap Pack from v3.9.1 to v3.17.x.

May 2024437patches27372 Latest

Enhanced the pipeline execution statistics of ELT Insert-Select Snap to be displayed in its output view and to allow downloading detailed stats as a JSON file that includes additional statistics (extraStats) on DML statement executions on target Databricks Lakehouse Platform (DLP) tables.

May 2024437patches27246 Latest

Enhanced the ELT Execute Snap to display SQL execution statistics in the pipeline execution statistics and the output view of the Snap for all SQL statements executed. The Snap also allows you to download detailed stats as a JSON file that includes additional statistics (extraStats) on DML statement executions on target Databricks Lakehouse Platform (DLP) tables.

May 2024437patches26846 LatestFixed the issue with the ADLS Gen 2 account connection where some conflicts between internally used Azure libraries prevented the ELT Load Snap from reading files.

Fixed an issue with the ELT Merge Into Snap where the Snap’s SELECT SQL statement could not fetch the target tables information from the PG_TABLE_DEF catalog table of the Amazon Redshift instance.

  • The SELECT SQL statement now uses lowercase for both non-delimited and delimited table names and excludes double quotes from delimited table names to fetch the values from the catalog table.

PG_TABLE_DEF is a Redshift system catalog table that contains information about the tables including table names, column names, data types among their other metadata.

Enhanced the ELT Load Snap to support loading data from nested AVRO, JSONLines, ORC, or Parquet files in Azure storage to the target tables in a Databricks Lakehouse Platform (DLP) instance. Only two Load actions are supported: Drop and create table and Append table.
May 2024main26341 Stable

Fixed an issue where the ELT Merge Into Snap failed to load data into the Google BigQuery target table because of the error: Number of source column expressions in the input SQL is greater than the target table columns (even after altering the target table schema to match the source table columns). The resolution was to modify an SQL statement that was used internally to achieve the merge action.

February 2024436patches25953  Latest

Enhanced the ELT Load Snap’s capabilities to allow loading flat and nested data sets (from canonical and non-canonical formats) from your Parquet files to the target tables in Snowflake. Learn more about the usage of this feature at Load data from Parquet files and in the following example Pipelines:

February 2024main25112 StableUpdated and certified against the current SnapLogic Platform release.
November 2023435patches24461 LatestFixed an issue with the ELT Merge Into Snap that caused inaccurate aliases and table identifiers in its generated SQL statement.
November 2023435patches23671 Latest and Stable
  • Enhanced the ELT Merge Into Snap to support defining the update expressions for updating tables in your target Databricks Lakehouse Platform instance using the WHEN NOT MATCHED BY SOURCE clause, besides other supported clauses and expression lists.

November 2023main23721 Stable
  • Enhanced the ELT Merge Into and ELT SCD2 Snaps to display detailed pipeline execution statistics about the data loaded to target tables on a Databricks Lakehouse Platform instance.