On this Page
Table of Contents | ||||
---|---|---|---|---|
|
Overview
Snap type: | Read | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description: | This Snap looks up records in the target Snowflake SQL Server table and returns 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. 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.
| |||||||||||||
Prerequisites: |
Security Prerequisites: You should have the following permissions in your Snowflake account to execute this Snap:
The following commands enable minimum privileges in the Snowflake Console:
| |||||||||||||
Internal SQL Commands | This Snap uses the LOOKUP command internally. It queries the database and retrieves a set of rows or defines the set of columns returned by a query. | |||||||||||||
Support and limitations: | Works in Ultra Task Pipelines. | |||||||||||||
Account: | This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Snowflake Account for information on setting up this type of account. | |||||||||||||
Views: |
| |||||||||||||
Settings | ||||||||||||||
Label | 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. | |||||||||||||
Schema name | Required. The database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all table names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values.
Default value: [None] | |||||||||||||
Table name | Required. Enter or select the name of the Snowflake SQL table to execute the lookup query.
Default value: [None] | |||||||||||||
Output fields | Required. Enter or select output field names for the Snowflake SQL SELECT statement. Default: [None] | |||||||||||||
Lookup conditions | Required. Enter or select the lookup column name. | |||||||||||||
Value | Required. Enter or select the JSON path of the lookup column value. The value will be provided by the input data field. | |||||||||||||
Lookup column name | 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 look up records in the lookup table. | |||||||||||||
Pass-through on no lookup match | 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: Not Selected | |||||||||||||
Number of retries | Specifies the maximum number of retry attempts when the Snap fails to read. Example: 3 Minimum value: 0 Default value: 0 | |||||||||||||
Retry interval (seconds) | Specifies the minimum number of seconds the Snap must wait before each retry attempt. Example: 3 Minimum value: 1 Default value: 1 | |||||||||||||
Handle Timestamp and Date Time Data | Specifies how the Snap must handle timestamp and date time data:
Default value: Default Date Time format in UTC Time Zone | |||||||||||||
Manage Queue Queries | Select this property to decide whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the pipeline.
Default value: Continue to execute queued queries when pipeline is stopped or if it fails | |||||||||||||
|
|
Example
The following example shows how a Snowflake object record can be looked up using the Snowflake Lookup Snap and record the data using the Snowflake Execute Snap. In this pipeline, the Snowflake Execute Snap writes the data from the Snowflake table ADOBEDATA123 to the target Snowflake table, ADOBEDATA using the Snowflake Lookup Snap.
1. The Execute Snap runs the select query to retrieve the data from the table ADOBEDATA123.
2. The Snowflake Lookup Snap runs the lookup query on, and the lookup conditions specify the column names to be looked up in the look-up table ADOBEDATA123.
Additionally, in the Output fields, select the names of the specific output field name(s) for the output preview to contain only those fields("ACCOUNT_C", "ADOBE_ROLE_C", "ID") to be recorded on the target table, ADOBEDATA.
Successful execution of the Snap gives the following preview:
Note that the output preview has only those column names that were specified in the output fields. The column, original, displays all the column names present in the original table (ADOBEDATA123) as displayed below:
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|