Read

This Snap provides functionality to lookup records in the target Redshift table and return a selected set of fields for every matched record. The Snap executes one request per multiple input documents to avoid making a request for every input record.

JSON Path can be used in the Snap properties and will have values from an incoming document substituted into the properties. However, documents missing values for a given JSON path will be written to the Snap's error view. After a query is executed, the query's results are merged into the incoming document.

Queries produced by the Snap have the format:

The Snap ignores any duplicated lookup condition in the input document stream since it maintains a cache for lookup conditions internally.

ETL Transformations & Data Flow

The Snap extracts records from a Redshift table based on the condition configured using input document stream/parameters. 

Input & Output

• Input: Each document in the input view should contain a Map data of key-value entries. Input data may contain values needed to evaluate expressions in the Object type, Output fields and Conditions properties. If the Pass-through on no lookup match property is unchecked, please make sure input data types match column data types in the database table. Otherwise, you may encounter an error message "Cannot find an input data which is related to the output record .....". If the error view is open, all input data in the batch are routed to the error view with the same error information.

• Output: Each document in the output view contains a Map data of key-value entries, where keys are the Output fields' property values. The input data that has produced the corresponding output data is also included in the output data under the "original" key.

Expected Upstream Snaps: Any Snap which produces documents in the output view, for example CSV Parser, JSON Parser, Structure, Data, and so on.

Expected Downstream Snaps: Any Snap which receives documents in the input view, for example JSON Formatter, Structure, Data, etc. CSV Formatter will cause an error since the output data is not a flattened Map data.

[None]

Works in Ultra Tasks.

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Redshift Account for information on setting up this type of account.

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

The database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema.

Required. Enter or select the name of the table to execute the lookup query on

Example: people

Default value: [None]

Required. Enter or select output filed names for SQL SELECT statement. If this property is empty, the Snap selects all fields by executing the statement "SELECT * FROM ...".

Example: email, address, first, last

Default value: [None]

Required. The lookup conditions are created by using the lookup column name and the lookup column value. Each row will build a condition, such aslookupColumn1 = $inputField. Each additional row will be concatenated using a logical AND. All rows together build the lookup condition being used to lookup records in the lookup table.

Default value: Not selected

Required. Enter or select the JSON path of the lookup column value. The value will be provided by the input data field.JSON path of the lookup column value. The value will be provided by the input data field.

Example: $email, $first, $last

Default value: [None]

Required. Enter or select lookup column name.

Example: email, first, last, etc.

Default value: [None]

When there is no lookup matching an input document, the input document will pass through to the output view if this property is checked. Otherwise, it will be written to the error view as an error condition.

Default value: False

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

Example: 3

Default value: 0

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

Example:  10

Default value: 1

Select an option to specify how the Snap must be executed. Available options are:

• Validate & Execute: Performs limited execution of the Snap (up to 50 records) during Pipeline validation; performs full execution of the Snap (unlimited records) during Pipeline execution.

• Execute only: Performs full execution of the Snap during Pipeline execution; does not execute the Snap during Pipeline validation.

• Disabled: Disables the Snap and, by extension, its downstream Snaps.

type "e" does not exist

This issue occurs due to incompatibilities with the recent upgrade in the Postgres JDBC drivers.

Download the latest 4.1 Amazon Redshift driver here and use this driver in your Redshift Account configuration and retry running the Pipeline.