Versions Compared

Old Version 2

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 3

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

Table of Contents
minLevel1
maxLevel2
absoluteUrltrue

...

Field Name

Field Type

Description

Label*

Default Value: AlloyDB Execute
Example: AlloyDB Execute

String

Specify a unique name for the Snap.

SQL Statement

Default Value: 
Example:

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

  • emp=$emp

String/Expression

Specify the SQL statement to execute on the server. 

Scenarios to successfully execute your SQL statements

You must understand the following scenarios to successfully execute your SQL statements:

Scenario 1: Executing SQL statements without expressions
If the expression toggle of the SQL statement field is not selected: 

  • The SQL statement must not be within quotes. 

  • The $<variable_name> parts of the SQL statement are expressions. In the below example, $id and $book.

  • Additionally, the JSON path (e.g. $myName) is allowed only in the WHERE clause.

    If the SQL statement starts with SELECT (case-insensitive), the Snap regards it as a select-type query and executes once per input document. If not, it regards it as write-type query and executes in batch mode.
    INSERT into SnapLogic.book (id, book) VALUES ($id,$book)

Scenario 2: Executing SQL queries with expressions 
Enable the expression toggle of the SQL statement field is selected: 

  • The SQL statement must be within quotes. 

  • The + $<variable_name> + parts of the SQL statement are expressions, and must not be within quotes. In the below example, $tablename.

  • The $<variable_name> parts of the SQL statement is bind parameter and must be within quotes. In the below example, $id and $book.

Note: Table name and column names must not be provided as bind parameters. Only values can be provided as bind parameters.

Pass through


Default Value: Selected

Checkbox

Select this checkbox to pass the input document to the output view under the key original.

This field applies only for queries with the SELECT statement.

Ignore empty result


Default Value: Deselected

Checkbox

Select this checkbox to not write any document to the output view when a SELECT operation does not produce any result.
If this checkbox is not selected and the Pass-through checkbox is selected, the input document is passed through to the output view.

Number of Retries


Default Value: 0
Example: 3

Integer/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

Integer/Expression

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

Auto Commit


Default Value: Use account setting
Example: True

Dropdown list

Choose one of the following options to override the state of the Auto commit 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 Auto commit property value inherited by the Account used by the Snap.

Auto commit may be enabled for certain use cases if AlloyDB JDBC driver is used in either Redshift, AlloyDB or generic JDBC Snap. But the JDBC driver may cause out of memory issues when Select statements are executed. In such cases, Auto Commit in Snap should be set to ‘False’ and the Fetch size in the Account settings can be increased for optimal performance.

Behavior of DML Queries in Database Execute Snap when auto-commit is false

  • DDL queries used in the Database Execute Snap will be committed by the Database itself, regardless of the Auto-commit setting.

  • When Auto commit is set to false for the DML queries, the commit is called at the end of the Snap's execution.

  • The Auto commit must be true in a scenario where the downstream Snap depends on the data processed on an Upstream Database Execute Snap containing a DML query.

  • When the Auto commit is set to Use account setting on the Snap, the account level commit needs to be enabled.

Snap Execution

Default Value: Execute only
Example: Validate & Execute

Dropdown list

Select one of the following three modes in which the Snap executes:

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

Troubleshooting

...

Error

...

Reason

...

Resolution

...

Account validation failed.

...

The Pipeline ended before the batch could complete execution due to a connection error.

...

Verify that the Refresh token field is configured to handle the inputs properly. If you are not sure when the input data is available, configure this field as zero to keep the connection always open.

Examples

Excluding Fields from the Input Data Stream

We can exclude the unrequired fields from the input data stream by omitting them in the Input schema field set. This example demonstrates how we can use the <Snap Name> to achieve this result:

<screenshot of Pipeline/Snap and description>

...

  • .

...

Downloads

Info

  1. Download and import the Pipeline into SnapLogic.

  2. Configure Snap accounts, as applicable.

  3. Provide Pipeline parameters, as applicable.

Attachments
patterns*.slp, *.zip
sortByname

Snap Pack History

Insert excerpt

...

Google Alloy DB Snap Pack

...

Google Alloy DB Snap Pack

...