Expressions in Pipeline and Stage Properties

Some pipeline and stage properties allow you to specify an expression. Use one of the following options to configure an expression, as appropriate:
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, such as for a condition or query properties.
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.
This documentation mentions "Snowflake SQL" when you must use Snowflake SQL in a property.
For information about Snowflake SQL, see the Snowflake documentation. For information on specifying column names in Snowflake SQL expressions, see Referencing Columns in Snowflake SQL Expressions.
StreamSets expression language for one-time evaluation
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 connection details and runtime parameters.
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 are not supported in Transformer for Snowflake.
This documentation mentions "StreamSets function" or "StreamSets expression language" when you can use them in a property.
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.
Java regular expressions for pattern matching
A Java regular expression, known simply as regular expression or regex, is a set of characters that represents a pattern used to search for matching text.
For example, you can sometimes use regular expressions in stage properties to define a set of columns to act upon, instead of a single column.
This documentation mentions "regular expression" when you can use a Java regular expression in a property.
For more information about Java regular expressions, see the Oracle documentation.

Referencing Columns in Snowflake SQL 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:

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.


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.