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.

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).

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.