Versions Compared

Old Version 35

changes.mady.by.user Shilpa

Saved on

compared with

New Version 36

changes.mady.by.user Shilpa

Saved on

Key

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

On this Page

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

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.
The Snap ignores any duplicated lookup condition in the input document stream since it maintains a cache for lookup conditions internally.


Queries produced by the Snap have the format:

Code Block
SELECT [Output fields] FROM [Table name] WHERE
[C1 = V11 AND C2 = V21 AND...[Cn = Vn1] OR
[C1 = V12 AND C2 = V22 AND...[Cn = Vn2] OR 
......................................  OR
[Cn = V1n AND Cm = V2m AND...[Cn = Vnm]


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


Expected upstream Snaps:  Any Snap which produces documents in the output view, such as CSV Parser, JSON Parser, Structure, and Mapper.
 
Expected downstream Snaps:  Any Snap which receives documents in the input view, such as JSON Formatter, Structure, and Mapper. The CSV Formatter will cause an error since the output data is not a flattened Map data.
 

Prerequisites:

Insert excerpt
Snowflake - Bulk Load
Snowflake - Bulk Load
nopaneltrue

Security Prerequisites: You should have the following permissions in your Snowflake account to execute this Snap: 

  • Usage (DB and Schema): Privilege to use database, role, and schema.

The following commands enable minimum privileges in the Snowflake Console:

Code Block
grant usage on database <database_name> to role <role_name>;
grant usage on schema <database_name>.<schema_name>;


For more information on Snowflake privileges, refer to Access Control Privileges.
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:


InputThis Snap allows exactly one input view and expects documents in the view. 
OutputThis Snap has at most one document output view.
Error

This Snap has at most one error view and produces zero or more documents in the view.



Settings

Label*


Specify 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*



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

Note

The values can be passed using the pipeline parameters but not the upstream parameter.

Default value:  [None]


Table name*

Enter or select the name of the Snowflake SQL table to execute the lookup query.

Note

The values can be passed using the pipeline parameters but not the upstream parameter.

Default value: [None]
 

Output fields*

Enter or select output field names for the Snowflake SQL SELECT statement.

Default: [None]
 

Lookup conditions*


Enter or select the lookup column name.


Value*

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


Lookup column name*


Specify 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

Specify the maximum number of retry attempts when the Snap fails to read.

Minimum value: 0

Default value: 0
Example: 3

Retry Interval (seconds)

Specifies the minimum number of seconds the Snap must wait before each retry attempt.

Minimum value: 1

Default value: 1
Example: 3

Input Date Format
The property has the following two options:
  • Select Continue to execute the snap with the given input Date format if you want the Snap to continue with the current date format. This option is selected by default. 
  • Select Auto Convert the format to Snowflake default format if you want the Snap to convert the provided date format to the default Snowflake date format. To know about the date formats supported by Snowflake, see Snowflake date formats
Handle Timestamp and Date Time Data

Specify how the Snap must handle timestamp and date-time data. The available options are:

  • Default Date Time format in UTC Time Zone: The Snowflake date time data are represented in UTC Time Zone.

  • SnapLogic Date Time format in Regional Time Zone: The Snowflake date time data are represented in the same regional time zone value, as provided in the Snowflake account.

Default value: Default Date Time format in UTC Time Zone

Multiexcerpt include macro
nameTimestamp_recommendation
pageSnowflake - Execute

Manage Queue Queries

Select this checkbox to determine whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the Pipeline.

Note

If you select Cancel queued queries when the pipeline is stopped or if it fails, then the read queries under execution are canceled, whereas the write queries under execution are not canceled. Snowflake internally determines which queries are safe to be canceled and cancels those queries.

Default value: Continue to execute queued queries when pipeline is stopped or if it fails

Multiexcerpt include macro
nameSnap Execution
pageAnaplan Read

Multiexcerpt include macro
nameSnap_Execution_Introduced
pageAnaplan Read


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
Snowflake Snap Pack
Snowflake Snap Pack
nopaneltrue