Versions Compared

Old Version 14

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version Current

changes.mady.by.user Anand Vedam

Saved on

Key

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

In this article

Table of Contents
minLevel1
maxLevel2
absoluteUrltrue

Multiexcerpt include macro
nameME_NE_ELT_Snap_Accounts
templateData[]
pageELT Case Expression
addpanelfalse

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 

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

Info

  • 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

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: ELT Cast Function
Example: CastFunctions_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.

Info

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

Note

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.

Note

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.

Info

  • 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 Value: Not selected
Example: Selected

Cast functions

Specify your SQL expressions, data type and the relevant alias using this fieldset. Click (blue star) 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. Click(blue star)to 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.
Example: ORDER_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

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;

Snap Pack History

Release

Snap Pack Version 

Date

Type

Updates

February 2023

main19844

09 Feb 2023 

Stable

Upgraded with the latest SnapLogic Platform release.

December 2022

431patches19240

08 Dec 2022 

Latest

  • SnapLogic upgraded the default JDBC Driver versions used to connect the ELT Snaps with the supported CDWs.

  • The ELT Merge Into Snap displays the individual record counts inserted, updated, and deleted for Snowflake targets in the respective Records Inserted, Records Updated, and Records Deleted parameters of the Snap Statistics tab (on Pipeline execution).

  • With Google deprecating the OAuth out-of-band (OOB) flow, the Refresh Token Accounts defined for connecting your ELT Snaps to the BigQuery instances start failing in a phased manner. We recommend that you immediately modify these Snap account configurations to switch to an Access Token Account or a Service Account from the Refresh Token Account.

November 2022

main18944

10 Nov 2022 

Stable

ELT Insert-Select, ELT Merge Into, and ELT SCD2 Snaps show the following statistics on execution.

  • Records Added

  • Records Updated

  • Records Deleted

September 2022

430patches18196

28 Sep 2022 

Latest

New Snap

The ELT Create View Snap enables you to create a new view when the view does not exist in the target database and/or schema or if the view already exists in the database and/or schema, and you choose to drop the existing view and re-create it.

Enhancements

  • The ELT Insert-Select Snap is more flexible and easier to use, especially if the number of columns in your source data set is very large. You can choose to update values only in a subset of columns in the target table.

  • The ELT Execute Snap can retrieve and execute SQL queries from the upstream Snap's output when referenced in the SQL Statement Editor using the Expression language (with the Expression button enabled).

  • The ELT Load Snap can infer the schema from the source files in Amazon S3, ADLS Gen2, Microsoft Azure Blob Storage, or Google Cloud Storage location and use it to create, overwrite, and append the target table in your Snowflake instance with the source data. The source files can be in the AVRO, CSV, JSON, ORC, or PARQUET format. Learn more at Automatic Schema Inference with ELT Load Snap.

  • Target Table Name in the following Snaps supports retrieving editable views with the table names from the selected target schema:

  • The pivot values in the ELT PivotSnap turns dynamic when you select Enable dynamic pivot values. The following field settings are added as part of this dynamic pivot values feature:

    • Filter Predicate List: A field set to filter the predicate list of the pivot values.

      • Pivot Values Filter: Condition required to filter the pivot values.

      • Boolean Operator: Predicate condition type through AND or OR Boolean operators

    • Sort Order: Sorting order of the pivot values.

  • You can specify the type of Microsoft Azure external storage location (source)—an Azure Data Lake Gen2 or a Blob Storage—to access your source data using the Storage Integration type of authentication and load it to your target Snowflake instance.

August 2022

main17386

11 Aug 2022 

Stable

Upgraded with the latest SnapLogic Platform release.

4.29-Patch

429patches16665

17 Jun 2022 

Latest

  • Enhanced the ELT Snap Pack to support the latest JDBC drivers across CDWs—Azure Synapse, BigQuery, DLP, Redshift, and Snowflake. See Configuring ELT Database Accounts or the respective Account page for the exact versions.

  • Enhanced the ELT Pivot Snap to make the Value List field dynamic.

  • Enhanced the ELT DLP Account to configure S3 Bucket, Azure Storage, and DataLake Storage Gen2 Mounts.

  • Enhanced the ELT Snowflake Account with support for Key Pair Authentication.

  • Enhanced the ELT SCD2 Snap:

    • To include a new option Overwrite existing table in the Target Table action field.

    • To display the final SQL query in its output preview upon Pipeline validation.

  • Enhanced the end Snap SQL query in the ELT Insert Select Snap’s preview output to display the CREATE TABLE... or the DELETE/DROP TABLE statements to be run before the query that inserts/loads data into a new table in the Snowflake target CDW.

  • Fixed an issue with ELT Insert Select and ELT Merge Into Snaps where they cause the Pipeline to fail when the specified target table does not exist. After this fix, the Snaps create a new target table if it does not exist during Pipeline validation.

    The new table that is created will not be dropped in the event of a subsequent/downstream Snap failing during validation.

  • Fixed the issue with ELT Load Snap where the Snap caused an SQL exception—[Simba][SparkJDBCDriver](500051) ERROR processing query/statement when reading from a CSV file in S3 mount point on DBFS in the case of a DLP target instance.

  • The ELT Insert Select, ELT Merge Into, ELT Load, and the ELT SCD2 Snaps now run successfully even when the specified target table does not exist. These Snaps now create a new target table if it does not exist during Pipeline validation.

    The new table thus created will not be dropped in the event of a subsequent/downstream Snap failure during validation.

  • Updated the field names in ELT Aggregate, ELT Cast Function, ELT String Function, and ELT Transform Snaps to maintain consistency.

4.29-Patch

4.29patches16287

28 May 2022 

Latest

Fixed an issue with the ELT SCD2 Snap where the Snap was rounding off decimal values to the nearest integer—the value 57.601000000000 in the source table was written to the target table as 58.000000000.

4.29

main15993

14 May 2022 

Stable

  • Introduced the following new ELT Snaps:

    • ELT Cast Function: Snap to convert a data type of a column in the input SQL string into other supported data types.

    • ELT String Function: Snap to support the various string functions supported by the different databases.

    • ELT Router: Snap to enable routing input SQL queries into multiple output views based on the given conditional expressions.

  • Enhanced the following Snaps to display the final SQL query in their output preview upon Pipeline validation.

  • Enhanced the ELT Database Account to support OAuth2-based authentication on the target Snowflake database.

  • Enhanced the ELT Select, ELT Insert Select, ELT SCD2, ELT Merge Into, and ELT Load Snaps to display suggestions on the Schema Name field based on the Default Database Name provided in the Snap Account configuration when the Database Name is not specified in the respective Snap.

    • Improved usability of the suggestions features for these Snaps by making them case-insensitive. For example, typing default in the Schema Name field displays both default and DEFAULT, if they co-exist. You do not need to type DEFAULT to invoke and select the schema name DEFAULT from the suggestions list.

  • Enhanced the ELT SCD2 Snap to address different feature requests and issues raised by multiple customers. These changes provide more flexibility in configuring your SCD2 operations using this Snap.

  • Removed Check for nulls and duplicates in the source field and added two dropdown lists - Null Value Behavior and Invalid Row Handling.

  • Made the following items in the Meaning field of the Target Table Temporal Fields fieldset mandatory while making the Invalid historical rows flag optional.

    • Current row 

    • Historical row

  • Enhanced the ELT Aggregate and ELT Window Functions Snaps to support the following functions across all supported CDWs:

    • KURTOSIS

    • MODE

    • SKEW

  • Enhanced the ELT Aggregate Snap to support the following GROUP BY features across all supported CDWs:

    • Group by Cube

    • Group by Grouping Sets

    • Group by Rollup

    • Automatic GROUP BY for all input columns.

  • Fixed an issue with ELT Merge Into Snap where the Snap erroneously modified the target table column name when the column name contained the target table name.

  • Fixed an issue in ELT SCD2 Snap where the Snap causes incorrect results with Snowflake targets, when:

    • The Historical Row End Date value is provided.

    • Nulls and Invalid rows are recognized, but one or more start dates in the source are null.

    • Fixed the issue in ELT Transform Snap where the Output Schema of the Snap does not populate all the column names from its Input Schema
    Expand

    See ELT 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