ELT Cast Function

In this article

Overview

You can use this Snap to convert a data type of a column in the input SQL string into other supported data types.

 

Snap Type

ELT Cast Function Snap is a Transform-type Snap that transforms the column data type.

Prerequisites

  • Valid accounts and access permissions to connect to the following targets:

    • Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery

Support for Ultra Pipelines

 

Works in Ultra Pipelines.

Limitation

ELT Snap Pack does not support the Legacy SQL dialect of Google BigQuery. We recommend that you use only the BigQuery's Standard SQL dialect in this Snap.

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 Intersect

The SQL query which you want to transform.

Output

Document

  • Min: 1

  • Max: 1

  • ELT Insert-Select

  • ELT Unique

  • ELT Limit

The outgoing SQL query with the specified transformation. 

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 ValueELT Cast Function
ExampleCastFunctions_Testing

Get preview data

Checkbox

Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation.

In case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline.

The number of records displayed in the preview (upon validation) is the smaller of the following:

  • Number of records available upon execution of the SQL query generated by the Snap.

  • The value set in the Preview Data Count setting (default is 50 records).

Rendering Complex Data Types in Databricks Lakehouse Platform

Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type.

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.

  • If pass through is checked and no rows in the column to data t ype Mapping table, then the SQL would be
    SELECT * FROM (SELECT * FROM <inputSqlString>) Data_Type_<random_table_name>

  • If pass through is checked and rows are specified in the Column to Data Type Mapping Table, then the SQL would be
    SELECT *, CAST (<column name1> AS <data type1>) AS <alias_name1>,
    CAST (<column name2> AS <data type2>) AS <alias_name2>,
    .... FROM (SELECT * FROM <inputSqlString>) Data_Type_<random_table_name>

  • If pass through is not checked and rows are specified in the Column to Data Type Mapping Table, then the SQL would be
    SELECT *, CAST (<column name1> AS <data type1>) AS <alias_name1>,
    CAST (<column name2> AS <data type2>)AS <alias_name2>,
    .... FROM (SELECT * FROM <inputSqlString>) Data_Type_<random_table_name>

  • If pass through is not checked and rows are not specified in the Column to Data Type Mapping Table, then it would result in an error.

Default ValueNot selected
ExampleSelected

Cast functions

Specify your SQL expressions, data type and the relevant alias using this fieldset. Click to add a new row. Ensure that you specify each expression in a separate row.

Function*

String/Expression

Specify your function/operation to be performed in the specified columns. Ensure that you specify each function in a separate row.

Data Type*

String/Expression

Specify the data type for the selected expression/function. Clickto retrieve the list of the supported aggregate functions. This is typically a column name in your source table. Each of the function needs only one value.
Default Value: None.
ExampleORDER_ID

Alias*

String

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

Default Value: None.
Example: A_ORDIDS

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 Cast Function snap

ELT Cast Function Snap cannot be used at the start of a Pipeline.

Move the ELT Cast Function Snap to either the middle or the end of the Pipeline.

CAST (DEPT AS BINARY) is not a natural function for Snowflake. It displays the following error message. net.snowflake.client.jdbc.SnowflakeSQLException: The following string is not a legal hex-encoded value: 'operat'
(CDW: Snowflake)

In the SQL query, SELECT CAST(DEPT AS BINARY) AS dept_binary FROM BIGDATAQA.TEST_DATA.org10, CAST (DEPT AS BINARY) is not a natural function for Snowflake.

Instead of (DEPT AS BINARY), you can use:

  • to_binary

  • as_binary

Examples:

  1. insert into TEST_DATA.demo_binary_hex (b) select to_binary(hex_encode('LOGO'), 'HEX');

  2. select as_binary(binary1) as "Binary" from TEST_DATA.type_as_binary;


Examples

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