Skip to end of banner
Go to start of banner

Azure Synapse SQL Select

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 23 Current »

In this article

Overview

You can use this Snap to fetch data from an Azure Synapse SQL Server database by providing a table name and configuring the connection.

You can use JSON paths in a query and have values from an incoming document substituted into the query. However, documents with missing values for a specific JSON path are written to Snap's error view. After a query is executed, the query's results are merged into the incoming document, overwriting any existing keys' values. The original document is displayed as output if there are no results from the query.

Snap Type

The Azure Synapse SQL Select Snap is a READ-type Snap that reads or fetches data from an Azure Synapse SQL Server database.

Prerequisites

A valid database account with the required permissions.

Support for Ultra Pipelines

Works in Ultra Pipelines

Limitations

When the table name contains single quote characters, the No table found error is displayed. This is because of the JDBC driver limitation.

Known Issues

None.

Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

  • Min: 0

  • Max: 1

  • Mapper

  • Copy

Document that provides values for one or more properties of the Snap or simply for pass through purposes.

Output

Document

  • Min: 1

  • Max: 2

  • JSON Formatter

Document for each record retrieved. Special types such as TIMESTAMP, TIMESTAMPTZ, and TIMESTAMPLTZ are converted into SnapLogic internal date type representations which then can be consumed by downstream Snaps just like any other data type.

The where clause can only use variables, not constants or Pipeline parameters. For example, SALARY =$SALARY (to use the SALARY variable of the input document). 

Queries produced by the Snap have the following format:

SELECT [output fields] FROM [table] WHERE [where clause] ORDER BY [ordering] LIMIT [limit] OFFSET [offset]

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

  • Stop Pipeline Execution: Stops the current Pipeline execution when the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

  • Asterisk (*): Indicates a mandatory field.

  • Suggestion icon ((blue star)): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon ((blue star)): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon ((blue star)): Indicates that you can add fields in the field set.

  • Remove icon ((blue star)): Indicates that you can remove fields from the field set.

Field Name

Field Type

Description

Label*

Default ValueAzure Synapse SQL - Select
ExampleAzure_Synapse_SQL_Select

String

Specify a unique name for the Snap.

Schema Name

Default Value: None
Example: dbo

String/Expression

Specify the database schema name. If it is not defined, then the suggestion for the Table Name retrieves all tables names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values.

Table Name*

Default Value: None
Example: dbo.people

String/Expression

Specify the name of a table on which the select query is to be executed.

Where Clause 

Default Value: None
Examples:

Without using expressions

  • EmpId = 12 

  • email = 'you@example.com'

Using expressions

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"

  • email = 'you@example.com' or email = $email 

  • emp=$emp

  • "emp='" + $emp + "'"

  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"

String/Expression

Specify the where clause of the select statement. This field supports document value substitution (for example, $person.firstname is substituted with the value found in the incoming document at the path).

Do not use expressions that join strings together to create SQL queries or conditions, which has a potential SQL injection risk and therefore unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with the '=' Expression enabled. 

Order By

Use this field set to define the column names for sorting the results. If you do not specify column names, the Snaps uses the default database sort order.

Column Names


Default Value: None
Example: name, email 

String/Expression

Specify the columns by which you want to order the result. The default database sort order is used.

Output Fields

Use this field set to define the output fields for SQL Select statement.

Output Field


Default Value: None
Example: name, email, address, first, last, etc

String/Expression

Specify the output fields for the SQL SELECT statement. To select all fields, leave them blank.

Fetch output fields in schema

Default ValueNot selected
Example: Selected

Checkbox

Select this checkbox to include only the selected fields or columns in the Output Schema (second output view). If you do not provide any Output fields, all the columns are visible in the output.
If you provide output fields, we recommend you to select Fetch output fields in schema checkbox.

Ignore empty result

Default ValueDeselected

Checkbox

Select this checkbox if you do not want to write to the output view when a SELECT operation does not produce any result or is empty. If you do not select this property and with the default Pass through property, the input document is written to the output view.

Auto Commit

Default ValueFalse
Example: True

Dropdown

Select one of the following options to override the state of the Auto Commit property on the account:

  •  True - The Snap executes with auto commit enabled regardless of the value set for Auto Commit in the Account used by the Snap.

  •  False - The Snap executes with auto-commit disabled regardless of the value set for Auto Commit in the Account used by the Snap.

  • Use account setting - The Snap executes with the Auto Commit property value inherited by the Account used by the Snap.

Match data types

Default ValueDeselected

Checkbox

Select this checkbox to match the output data type when the Output Fields property is empty (SELECT * FROM ...). The output preview is in the same format as the one when SELECT * FROM is implied and all the contents of the table are displayed.

This property applies only when the Output Fields property is provided with any field values.

Number of Retries

Default Value0
Example: 3

String/Expression

Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.

If the value is larger than 0, the Snap first downloads the target file into a temporary local file. If any error occurs during the download, the Snap waits for the time specified in the Retry interval and attempts to download the file again from the beginning. When the download is successful, the Snap streams the data from the temporary file to the downstream Pipeline. All temporary local files are deleted when they are no longer needed.

Ensure that the local drive has sufficient free disk space to store the temporary local file.

Retry Interval (seconds)

Default Value1
Example: 10

String/Expression

Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. 

Staging Mode

Default value: In memory
Example: On disk

Dropdown

Select a value from the following options to specify the location to store input documents between retries when the value in the Number of retries field is greater than 0:

  • In memory: The query results are stored in the Snaplex memory. If the query is too large to fit in the memory space, it might cause the Snap to fail, so choose the On disk option.

  • On disk: The query results are stored on the disk in a temporary (tmp) directory that is managed by the SnapLogic platform. This directory is deleted automatically when the Snap terminates.

To disable staging, enter 0 in the Number of retries field.

Snap Execution

Default ValueDisabled
Example: Validate & Execute

Dropdown list

Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.

  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Example

Retrieving Fields from Employee Table with Specific Condition

This example Pipeline demonstrates how to retrieve data from an employee table by specifying a condition to retrieve only those fields where the account type is Savings.

Configure the Azure Synapse Select Snap to retrieve fields based on the specific condition:

On validation, the Snap retrieves the list of columns based on the specific condition:

Download this Pipeline

Downloads

  1. Download and import the Pipeline into SnapLogic.

  2. Configure Snap accounts as applicable.

  3. Provide Pipeline parameters as applicable.

  File Modified
No files shared here yet.

Snap Pack History

 Click here to expand...

Release

Snap Pack Version

Date

Type

Updates

February 2025

main29887

Stable

Updated and certified against the current SnapLogic Platform release.

November 2024

main29029

Stable

Updated and certified against the current SnapLogic Platform release.

August 2024

main27765

Stable

Upgraded the org.json.json library from v20090211 to v20240303, which is fully backward compatible.

May 2024

437patches26597

Latest

Fixed an issue with the Azure Synapse SQL Select Snap that displayed an error and did not retry the connection when the serverless SQL pool was warming up.

May 2024

main26341

Stable

February 2024

436patches25597

-

Upgraded the jOOQ library of the Azure Synapse SQL Snap Pack from v3.9.1 to v3.17.x.

This upgrade is currently NOT available with the latest distribution. However, you can still consume this patch through 436patches25597. This update is scheduled to be included in the latest distribution release on April 10, 2024, and will be a part of the stable distribution with the GA release on May 8. 2024.

Behavior change:

The jOOQ library upgrade for Azure Synapse SQL Snap Pack, done as part of the 436patches25597, resulted in the following behavior change:

Previously, when “null”(string) was passed from the upstream Mapper Snap for the Bigint datatype, the /wiki/spaces/DRWIP/pages/3011674578Snap updated it as null without displaying any error. It displayed the status as 0 and the output message as Success. Now, the Snap displays the following error in this scenario:

Error converting value(expecting a number but you are sending String(“null”))

If you pass null from Mapper Snap with the expression enabled, the Snap updates the null value for the BigInt datatype correctly, as earlier.

February 2024

main25112

Stable

Updated and certified against the current SnapLogic Platform release.

November 2023

main23721

Stable

Updated and certified against the current SnapLogic Platform release.

August 2023

main22460

Stable

The Azure Synapse SQL - Execute Snap now includes a new Query type field. When Auto is selected, the Snap tries to determine the query type automatically.

May 2023

433patches21718

Latest

Fixed an issue with the Azure Synapse SQL Bulkload Snap that caused a 404 error when the External Location was set to ADLS Gen2 and the Azure Folder field was not specified in the account settings.

May 2023

main21015

Stable

Upgraded with the latest SnapLogic Platform release.

February 2023

432patches20389

Latest

The Azure Synapse SQL Account and Azure Synapse SQL Active Directory Account now support the latest JDBC Driver 12.2.0.jre11 by default. You do not have to upload the JAR file in the JDBC Driver field manually.

February 2023

main19844

 

Stable

Upgraded with the latest SnapLogic Platform release.

November 2022

431patches19493

Latest

The Azure Synapse SQL - Bulk Load Snap no longer fails when loading the JDBC driver from SLDB as it now uses the uploaded JAR driver when performing all bulk load operations.

November 2022

431patches19410

Stable

Fixed the Azure SQL - Execute Snap using the Azure SQL Active Directory Account that failed with SQL operation failed errors in environments using federated authentication

November 2022

431patches19410

Latest

Fixed the Azure SQL - Execute Snap using the Azure SQL Active Directory Account that failed with SQL operation failed errors in environments using federated authentication

November 2022

431patches19354

Latest

The Azure Synapse SQL - Bulk Load Snap no longer fails with the error Invalid column value in CSV data file when the data contains the field quote character (").

November 2022

431patches19263

Latest

The Azure Synapse SQL Insert Snap no longer supports the Preserve Case Sensitivity checkbox.

November 2022

main18944

Stable

Introduced the Azure Synapse SQL Snap Pack. The Azure Synapse Analytics is service from Microsoft that helps you query data to create, update, insert, or delete data in the Azure Synapse SQL. This Snap Pack contains the following Snaps:


Related Content

  • No labels