Overview

Use this Snap to build SELECT SQL queries to fetch records from the specified table. Executed in its default state after providing database, schema, and table name, the Snap builds a standard SELECT * FROM query. However, you can use the inbuilt SQL query editor to build complex queries to perform complex operations spanning multiple tables such as JOIN, AGGREGATE, LIMIT, and DISTINCT. Additionally, this Snap can also connect to multiple upstream Snaps. This enables you to build a query to perform operations on multiple tables together or separately at the same time. 

Prerequisites

A valid SnapLogic account to connect to the database in which you want to execute the query if you also want to preview the data.

Limitation

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

• Suggestions displayed for the Schema Name field in this Snap is from all databases that the Snap account user can access, instead of the specific database selected in the Snap's account or Settings.

• While you specify an SQL statement in the SQL Query Editor of the Snap as an expression, the dynamic validation for the expression displays inline errors when there is more than one incoming document and without the '__sql__' key to the current Snap, when you select Get Preview Data checkbox in the previous Snap, and when Preview Document Count in your user settings is set to a value more than 1.

  • To prevent this error and similar ones, do not select the Get Preview Data checkbox in the previous Snap, set the Preview Document Count in your user settings to 1, or append a condition where 1 = 0 to the SQL statement with the Get Preview Data checkbox selected.

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

Table Path Management for DLP

^# We recommend that you specify a table path that resolves to a valid data file. Create the required target file, if need be, before running your Pipeline.

Preventing SQL Injection

You can pass Pipeline parameters as values in an SQL query; however, if you do not phrase the query properly it can lead to the parameter's name being bound as a value in the database. This potentially incorrect information being inserted into the database is known as SQL injection. It is thus necessary to take precautions when including Pipeline parameters in your SQL query to prevent SQL injection. Based upon the intended use of the Pipeline parameter, use one or both the following methods to prevent accidental SQL injection:

Method-1: Simple Substitutions

You can reference the Pipeline parameter directly with a JSON-path without enabling expressions.

For example, consider a Pipeline parameter name which contains the value of a column. You want to write a SELECT query to fetch records from the table mytable where the column's name matches the value in the Pipeline parameter. 

SELECT * FROM mytable WHERE colname = _name

Method-2: Dynamic Substitutions

You must enable expressions when referencing table names using Pipeline parameters. Format the query, except the Pipeline parameter's reference, as a string. 

For example, if you you want to write a SELECT query to fetch records from a table and you are passing the table's name in the Pipeline parameter table:

"SELECT * FROM" + _table

In the example above, you must still use JSON-paths if you want to use another Pipeline parameter for substituting values in the SQL:

"SELECT * FROM " + _table + " WHERE colname = _name"

The Snap evaluates the expression and also carries out path substitutions.

Here is how it works

The Snap pre-processes the query to extract any JSON-Paths and converts them to bound parameters. For example, consider the following query:

SELECT * FROM mytable WHERE name = _name

The Snap converts this query into the following before turning it into a prepared statement for the database:

SELECT * FROM mytable WHERE name = ?

The Snap evaluates the JSON-Path to get the value to bind the Pipeline parameter in the prepared statement. 

Troubleshooting