ELT String Function

Overview

You can use this Snap to support the various string functions supported by the different databases such as Snowflake, Redshift, Azure Synapse, and Google BigQuery. Every database supports string functions in one name or another and some may not support string functions that are present in other databases and vice versa may be true.

 

Snap Type

ELT String Function Snap is a transform-type Snap that transforms SQL.

Prerequisites

Valid accounts and access permissions to connect to the following targets: Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery

Limitations and Known Issues

None.

Snap Input and Output

Input/Output

Type of View

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input/Output

Type of View

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

  • Min: 1

  • Max: 1

  • ELT Select

  • ELT Copy

The SQL query which you want to support the string functions

Output

Document

  • Min: 1

  • Max: 1

  • ELT Insert-Select

  • ELT Union

The modified SQL query for which the string functions are supported.

Error

Error handling is to have a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that might arise while running the Pipeline by choosing one of the options from the When errors occur section under the Views tab.

The options available are:

  • Stop Pipeline Execution: Stops the current pipeline execution when an error arises.

  • Discard Error Data and Continue: Ignores an error completely, discard that record, and continue with the rest of the records.

  • Route Error Data to Error View: Routes the data to an error view of the Snap and then decides what to do with the error.

See Error View in Pipeline in examples to understand the Snap error view settings and error output. For more information on the error view and handling error output in a Pipeline, see Error handling in Pipelines.

Snap Settings

  • Click the = (Expression) button in the Snap's configuration, if available, to define the corresponding field value using expression language and Pipeline parameters. 

  • Field names marked with an asterisk ( * )  in the table below are mandatory. 

Field Name

Type

Description

Field Name

Type

Description

Label*

String

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.

Default Value: N/A
Example: ELT String functions Account

Pass through

Checkbox

Select this checkbox to specify that the Snap must include the incoming document (SQL query) in its output document.

If this checkbox is selected and there are no conditional functions defined in the fieldsets below, Subquery Pushdown Optimization logically ignores this Snap—sends the input SQL query coming from the immediately upstream Snap as its output SQL query.

String Unary Functions

Specify your arithmetic string function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Function

  • Argument

  • Alias

Function*

String/Suggestion

Select an arithmetic unary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.

Default Value: None.
Example: PARSE_URL

Argument*

String/Expression

Each of the arithmetic unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
Example: https://www.snaplogic.com

Alias Name*

String

Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: PRL

String Binary Functions

Specify your arithmetic binary string function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Function

  • Argument 1

  • Argument 2

  • Alias

Function*

String/Suggestion

Select an binary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.

Default Value: None.
Example: SPLIT

Argument 1*

String/Expression

Each of the binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
Example: LAT

Argument 2*

String/Expression

Each of the binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. You can skip this argument by entering the quotes if you want to leave it empty.
Default Value: None.
Example: LAT

Alias Name*

String

Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: SPL

String Ternary Functions

Specify your arithmetic ternary string function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row.

This fieldset consists of the following fields:

  • Function

  • Argument 1

  • Argument 2

  • Argument 3

  • Alias

Function*

String/Expression

Select a ternary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.

Default Value: None.
Example: POSITION

Argument 1*

String/Expression

Each of the ternary functions needs three values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
Example: e

Argument 2*

String/Expression

Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. You can skip this argument by entering the quotes if you want to leave it empty.
Default Value: None.
Example: DPT

Argument 3*

String/Expression

Specify the third argument in this field for the selected expression/function. This is typically a column name in your source table. You can skip this argument by entering the quotes if you want to leave it empty.
Default Value: None.
Example: 1

Alias Name*

String

Specify the column in which to display the result of the ternary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: PST

Function*

String/Expression

Select a quaternary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.

Default Value: None.
Example: INSERT

Argument 1*

String/Expression

Each of the quaternary functions needs four values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table.
Default Value: None.
Example: DPT

Argument 2*

String/Expression

Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. You can skip this argument by entering the quotes if you want to leave it empty.
Default Value: None.
Example: 2

Argument 3*

String/Expression

Specify the third argument in this field for the selected expression/function. This is typically a column name in your source table. You can skip this argument by entering the quotes if you want to leave it empty.
Default Value: None.
Example: 1

Argument 4*

String/Expression

Specify the fourth argument in this field for the selected expression/function. This is typically a column name in your source table. You can skip this argument by entering the quotes if you want to leave it empty.
Default Value: None.
Example: e

Alias Name*

String

Specify the column in which to display the result of the quaternary function. You can also reference this name in downstream Snaps to process the data further. 

Default Value: None.
Example: INS

Troubleshooting

 

Error

Reason

Resolution

Error

Reason

Resolution

Missing property value

 

 

You have not specified a value for the mandatory field where this message is displayed.

Ensure that you specify appropriate values for all mandatory fields in the Snap configuration.

Invalid placement of ELT String Function Snap

You cannot use the ELT String Function Snap at the beginning of a Pipeline.

Move the ELT String Functions Snap to the middle of the Pipeline.

The Snap throws an error cannot resolve 'sha(indigo_sealion.col1)' due to data type mismatch: argument 1 requires binary type, however, 'indigo_sealion.col1' is of string type as in SELECT SHA(col1) AS sh FROM (SELECT * FROM default.tsha2);

The issue is applicable to the following next functions:

  • BASE64()

  • CRC32()

  • MD5()

  • SHA()

  • SHA2()

Enter the datatype accepted in the each Argument of the String function. For example, Argument in the Binary String Function should have only binary values.

If the datatype does not match, then use ELT Cast Snap or ELT String Function Snap to cast string value from the table into Binary datatype in the field Argument of the Snap.

For example, CAST(DPT AS BINARY) where DPT is a column name.

Snap Pack History


See Also

https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439233/Glossary

https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438341/Getting+Started

https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439233/Glossary