Azure Synapse SQL - Select

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

Known Issues

None.

Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

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

Field Name

Field Type

Description

Label*

Default Value: Azure Synapse SQL - Select
Example: Azure_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 Value: Not 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 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
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 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
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.

Retry Interval (seconds)

Default Value: 1
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 Value: Disabled
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