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 |
|
| Document that provides values for one or more properties of the Snap or simply for pass through purposes. |
Output | Document |
|
| 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:
|
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. The available options are:
Learn more about Error handling in Pipelines. | |||
Snap Settings
Asterisk (*): Indicates a mandatory field.
Suggestion icon (
): Indicates a list that is dynamically populated based on the configuration.Expression icon (
): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.Add icon (
): Indicates that you can add fields in the field set.Remove icon (
): Indicates that you can remove fields from the field set.
Field Name | Field Type | Description | |
|---|---|---|---|
Label* Default Value: Azure Synapse SQL - Select | String | Specify a unique name for the Snap. | |
Schema Name Default Value: None | 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 | String/Expression | Specify the name of a table on which the select query is to be executed. | |
Where Clause Default Value: None Without using expressions
Using expressions
| String/Expression | Specify the where clause of the select statement. This field supports document value substitution (for example, 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 '=' 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
| 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
| String/Expression | Specify the output fields for the SQL SELECT statement. To select all fields, leave them blank. | |
Fetch output fields in schema Default Value: Not 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. | |
Ignore empty result Default Value: Deselected | 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 Value: False | Dropdown | Select one of the following options to override the state of the Auto Commit property on the account:
| |
Match data types Default Value: Deselected | 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 Value: 0 | 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. | |
Retry Interval (seconds) Default Value: 1 | 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 | 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:
To disable staging, enter 0 in the Number of retries field. | |
Snap Execution Default Value: Disabled | Dropdown list | Select one of the three modes in which the Snap executes. Available options are:
| |
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.
Downloads
Download and import the Pipeline into SnapLogic.
Configure Snap accounts as applicable.
Provide Pipeline parameters as applicable.
Snap Pack History
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 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 The following Azure Synapse SQL Active Directory Account has been renamed because Microsoft has rebranded Azure Active Directory to Microsoft Entra ID. Azure Synapse SQL Active Directory Account → Azure Synapse SQL Entra Account Updated the Delete Condition (Truncates a Table if empty) field in the Azure Synapse SQL Delete Snap to Delete condition (deletes all records from a table if left blank) to indicate that all entries will be deleted from the table when this field is blank, but no truncate operation is performed. 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 Behavior change: The jOOQ library upgrade for Azure Synapse SQL Snap Pack, done as part of the Previously, when If you pass 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 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 November 2022 431patches19410 Latest Fixed the Azure SQL - Execute Snap using the Azure SQL Active Directory Account that failed with November 2022 431patches19354 Latest The Azure Synapse SQL - Bulk Load Snap no longer fails with the error 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:org.json.json library from v20090211 to v20240303, which is fully backward compatible.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.436patches25597, resulted in the following behavior change:“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”)) null from Mapper Snap with the expression enabled, the Snap updates the null value for the BigInt datatype correctly, as earlier.404 error when the External Location was set to ADLS Gen2 and the Azure Folder field was not specified in the account settings.SQL operation failed errors in environments using federated authenticationSQL operation failed errors in environments using federated authentication Invalid column value in CSV data file when the data contains the field quote character (").