Vertica - Multi Execute

Overview

The Vertica - Multi Execute Snap allows you to execute multiple SQL statements for each document.

Each set of statements is executed as one transaction (per document). The Snap will substitute values from an incoming document into the SQL statement if it finds a JSONPath substring in the statement. If a substitution value is missing in the incoming document then the document will be written to an error view.
vertica-multiexecute
Note:
  • Multiple documents cannot be executed as part of one transaction; the relationship is one transaction per document.
  • The account being used must have auto commit set to false, otherwise the Snap will fail with a configuration exception. You can drop your database with it, so be careful.

Supported Accounts

Snap views

Type Description Examples of upstream and downstream Snaps
Input

This Snap has exactly one document input view.
Output

This Snap has at most one document output view.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Description
Label

String

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: Vertica Multi Execute

Example: Vertica Multi Execute
Executions

List

 Required. Specify a list of statements that are executed as one transaction.
  • Path - A JSONPath that returns a list or object from the input document. Example: jsonPath($, "$ADJUSTMENTS[*]")
  • SQL Statement - A SQL statement that uses the result of the path as part of the statement substitution. Example: Insert into ADJUSTMENTS VALUES ($BUS_AREA_CD, $CR_FG, $FUNC_AREA_CD, $LGCL_DEL_FG, $MAIN_MCC_FG, $PR_EFF_DT, $SO_ADJ_AM, $SO_ADJ_CD)

Examples:

Without using expressions:

Using expressions:

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"
  • email = $email
  • emp=$emp
  • "emp='" + $emp + "'"
  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
Warning: Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled.
Warning:

Single quotes in values must be escaped

Any relational database (RDBMS) treats single quotes (') as special symbols. So, single quotes in the data or values passed through a DML query may cause the Snap to fail when the query is executed. Ensure that you pass two consecutive single quotes in place of one single quote within the values to escape the single quote.

For example:

  • If string has no single quotes: pass 'Schaum Series'
  • If string contains single quotes: pass 'O''Reilly''s Publication' for the value O'Reilly's Publication

Default value: N/A
Number of retries

Integer/Expression

 Specify the maximum number of times that the process must be retried before displaying an error.

Default value: 0

Example: 2
Retry interval (seconds)

Integer/Expression

 Specify the maximum resting time in seconds between subsequent retries.

Default value: 1

Example: 2
Snap Execution

Dropdown list

Choose one of the three modes in which the Snap executes. The 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.

Default value: Execute only

Example: Validate & Execute