Expressions in Pipeline and Stage Properties

Some pipeline and stage properties allow you to specify an expression. When configuring an expression, use one of the following languages:
Snowflake SQL for data manipulation
Snowflake SQL is the relational query language used with Snowflake. Because processing for Snowflake pipelines occurs in Snowflake, you must use Snowflake SQL for all expressions that manipulate pipeline data.
For example, when using the Filter processor to remove data from the pipeline, you define the filter condition using any Snowflake SQL syntax that can be used in the WHERE clause of a query.
For information about Snowflake SQL, see the Snowflake documentation. For information on specifying column names in Snowflake SQL expressions, see Referencing Columns in Expressions.
StreamSets expression language
The StreamSets expression language is based on the JSP 2.0 expression language. If you already use StreamSets, you are probably familiar with the StreamSets expression language.
You can use the StreamSets expression language in pipeline or stage properties that are evaluated only once, before pipeline processing begins. This includes properties such as connection details and runtime parameters.
Important: You do not use the StreamSets expression language in properties that evaluate pipeline data. As a result, some functions you might be accustomed to using in other StreamSets products, such as the row or column functions, are not supported in Transformer for Snowflake.
For details about the StreamSets expression language, see the Transformer documentation. Note that not all functionality described in the documentation is valid in Transformer for Snowflake at this time.

Referencing Columns in Expressions

To reference a column that contains a single level of data in a Snowflake SQL expression, you simply specify the column name. Column names are not case-sensitive.

For example, to deduplicate data based on an ID column, you configure a Deduplicate processor to deduplicate based on columns. Then, you can specify ID, Id, iD, or id as the column to use.

When a column contains hierarchical data, you reference a specific element within that column differently, based on the following Snowflake data types:

Object
To reference an element within an Object column, use dot notation (.) to specify the path to the element, as follows:
<top level>.<next level>.<next level>.<element to use>

For example, customer.transactions.2019.

Array

To reference an element within an Array column, use bracket notation ([#]) to indicate the position in a list. Use 0 to indicate the first element in the list, 1 to indicate the second, and so on.

For example, to reference the second element in an appt_date Array column, enter appt_date[1].

Tip: After running preview for a pipeline, you can also copy a column path from the preview results.