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 |
---|---|---|---|---|
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:
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 the | |
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: