Versions Compared

Old Version 18

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version 19

changes.mady.by.user Anand Vedam

Saved on

Key

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

In this article

...

Use this Snap to add a JOIN clause to join tables in separate queries coming from the upstream Snaps. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.

Note
titleELT Join Snap requires an account configuration

Starting from 4.24 GA, ensure to configure an account for this Snap.

...

Parameter NameData TypeDescriptionDefault ValueExample 
LabelString
Insert excerpt
File Writer
File Writer
nopaneltrue
ELT JoinCombined Dataset
Get preview dataCheck box
Multiexcerpt include macro
namegetpreviewdata
pageELT Intersect
Not selectedSelected
ELT Join TypeString/Drop-down list

Choose the join type to use in the SQL.

Available options are:

  • Inner
  • Left outer
  • Full outer
  • Right outer
  • Natural Full outer
  • Natural Left outer
  • Natural Right outer
  • Natural Inner
  • Cross
  • Left Anti
  • Left Semi

See Snowflake Join Types for details.

See Redshift Join Types for details.

See Azure Synapse Join Types for details. 

See Join Types for Databricks on AWS for details.

Info
titleNatural Joins for Azure Synapse and , Databricks Lakehouse Platform and BigQuery

Natural Joins are not natively supported by Azure Synapse and , Databricks Lakehouse Platform (DLP), and BigQuery databases. But, this Snap uses a series of query rewrite mechanisms to support these Join Types. You can apply these Natural Joins to your data sets in Azure Synapse or , Databricks Lakehouse Platform (DLP), and BigQuery, accordingly.


Info
titleMore Joins for BigQuery

BigQuery does not have the native support for Left Anti and Left Semi join types. But, this Snap uses a series of query rewrite mechanisms to support these Join Types in BigQuery.


InnerLeft outer
ELT Join ConditionString/Expression

Specify the condition to initiate the JOIN operation. If you do not specify any condition here, the Snap uses ON null as the default condition. You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

You can specify any SQL expression that has a boolean output (true or false).

Note

The ELT Join Condition is ignored if the join type is:

  • Natural Inner Join
  • Natural Left Outer Join
  • Natural Right Outer Join
  • Natural Full Outer Join
  • Cross Join


N/ACUST_CODE1 = CUST_CODE
Left Table AliasString/ExpressionSpecify the alias to use for the table in the first input view.
This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names.		N/ATBL1
Right Table AliasString/ExpressionSpecify the alias to use for the table in the second input view.
This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names.		N/ATBL2
Resultant Column Names Prefix TypeDrop-down list

Not applicable if target database is Databricks Lakehouse Platform (DLP).

Choose an option from the list to prefix the resultant columns names with a table alias; this enables the Snap to prevent collision of identical column names in resultant table.

Available options are:

  • None: Select this option if you do not want to add a prefix to any of the column names.
  • All Columns: Select this option to prefix the alias name to all the resultant column names.
  • Only Duplicate Columns: Select this option to prefix the alias name to only identical column names.
Note
titleFor existing Pipelines

For Pipelines created prior to 4.24 GA, if you choose the All Columns or the Only Duplicate Columns option in this field, ensure that you also configure an account for the Snap.


NoneRT.D_DATE_SK

...

Method-2: Dynamic Substitutions

You must enable expressions when using Pipeline parameters for dynamic substitutions. Format the SQL expression, except the Pipeline parameter's reference, as a string. 

For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ELT Join Condition field: 

...

We need a query that contains a JOIN clause. This example demonstrates how we can use the ELT Join Snap to build a query with the JOIN clause.

First, we build SELECT queries to read the target tables. To do so, we can use two ELT Select Snaps, in this example: Read Part A and Read Part B. Each of these Snaps is configured to output a SELECT * query to read the target table in the database. Additionally, these Snaps are also configured to show a preview of the SELECT query's execution as shown:

Read Part A ConfigurationRead Part B Configuration

...