Versions Compared

Old Version 51

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version Current

changes.mady.by.user Gouri Bhagchandani

Saved on

Key

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

On this Page

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Overview

Snap type

Transform

Description

This Snap joins two or more data streams. It supports inner, left outer, and outer joins. If input data streams are sorted (ascending or descending), it is a streaming Snap at highly optimized performance. If the data streams are not sorted, you may use a Sort Snap in front of the Join Snap or select UNSORTED for the Sorted streams property. Please note that all documents in the same input view must have the same set of fields, otherwise, the naming of the fields in the output documents may appear to be inaccurate.

  • Expected upstream Snaps. Sort, Mapper, CSV Parser, JSON Parser, XML Parser, or any Snap with a document output view.
  • Expected downstream Snaps. CSV Formatter, JSON Formatter, XML Formatter, or any Snap with a document input view.
  • Expected input. All documents in the same stream should have the same set of fields regardless if values are null or not.
  • Expected output. Data joined from input document streams. Field names in the left input data are passed to the output data 'as is. For all field names in the right input document streams, if a field name conflicts with a field name in the left input data, it will be prefixed with its input view name. If there is no conflict, the field names in the right input documents are used in the output data without any modification.
Prerequisites

All documents in the same stream should have the same set of fields.

Known Issue, support and limitations

Known Issue: When the upstream Snaps of the Join Snap contains Copy, Router, Aggregate, or similar Snaps, it is likely that the data flow of a branch in a Pipeline gets blocked until another branch completes streaming the document. The Join Snap might hang if its upstream Snaps in a Pipeline has a blocked branch.

Workaround: Set Sorted streams to Unsorted in the Join Snap to effectively buffer all documents in all input views internally—this unblocks the document flow of all the upstream branches. The internal sorters sort the input documents from the input views into the local temporary stage.

Limited support in Ultra

Task Pipelines

Tasks:

Multiexcerpt include macro
nameJoin Snap Ultra Workaround
pageSnap Support for Ultra

Pipelines

Tasks

Account

Accounts are not used with this Snap.

Views


Input

This Snap has two or more document input views.

Note

The input data schema in the upstream Snaps of Join Snap must be consistent for each input view to produce the expected joined output data. Else, the Snap might output unexpected joined data. See examples below for more information.

Workaround:

You can insert a Mapper Snap to add missing fields with null values to fix the inconsistent input schema.


OutputThis Snap has exactly one document output view.
ErrorThis Snap has at most one document error view and produces zero or more documents in the view. The error view behavior of this Snap is different from other Snaps. Error view documents do not contain error, reason, resolution, and stack trace. The left input documents are passed to the error view without any modification if there are no matches in the inner join operation and the Unmatched data to error view property is selected. When the error view is not open (that is, Stop pipeline execution is selected for the error view in the View pane), the input document written to the error view will not stop the pipeline and the pipeline execution will continue.

 

Settings

Label

Required. 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.

Join Type


 

Required.The type of join to execute. The options available include:

  • Inner
  • Left outer
  • Outer
  • Merge

Default value: Inner

Note
If you select Merge, the documents from the input views are merged into one document. You do not have to specify any other join properties when merging documents.


The rows on the left table are merged with the rows on the right table in the merge operation. If the right table has a fewer number of rows than the left table, null is added in the output document for the remaining rows. 

Join paths


JSON paths to use for left and right sides of the join. 

Each row in the table defines a relationship between the left-field and one of the right fields.
If there are N input views, N-1 rows are required to define each join path relationship. So, M*(N-1) rows are required to define all the join path relationships if there are M relationships. For example, if there are 4 input views and 3 join paths, 9 rows ((4-1) x 3) are required to define all the join path relationships. 

To use a partial set of join path relationships, use multiple Join Snaps.

Default value: [None]

Left path

Required. The JSON path to a value in a document of the first input view. One of the suggested field names should be selected. This property does not support expressions.
Example: $customer_id
Default value: [None]

Right input view

Required. Right input view name which is the second or another next input view.
You may use Suggest to select the right input view names.
Example: input1
Default value: [None] 

Right path

Required. The JSON path to a value in a document of the second or another next input view. One of the suggested field names should be selected. This property does not support expressions.
Example: $customer_id
Default value: [None]

Sorted streams

Required. How the data is sorted. Options available are Ascending, Descending, or Unsorted. If an Unsorted data stream is selected, the Snap sorts input data streams before it starts the join operation. 

Default value: Ascending

Null greater


If selected, null values are considered greater than non-null values. In conjunction with Sort streams:
  • If selected and Sort streams are ascending, nulls appear at the end of the list.
  • If selected and Sort streams are descending, nulls appear at the beginning of the list.
  • If not selected and Sort streams are ascending, nulls appear at the beginning of the list.
  • If not selected and Sort streams are descending, nulls appear at the end of the list.

Default value: Not selected

Unmatched data to error view


If selected, unmatched left input documents are passed to the error view only if the Join type is 'Inner'.

Default value: Not selected (false)

Null-safe access


If selected, the Snap will ignore missing data when accessing the join path. For example, a join path is '$id', but the 'id' key does not exist in the input data. In this case, the Snap will assume its value is null and continue. If unselected, the Snap will write an error to the error view for missing data and stop the execution.

Default value: Not selected (false)

Available Memory Threshold (%)

The Snap keeps all the Right input view documents with the same join-path values in memory until the join operation is done for the specific join-path values. When the Right input view has more than 10,000 input documents with the same join-path values, the Snap checks if the available memory is less than the threshold value mentioned in this property. If so, it starts to store input data into local temporary files to prevent the node from out of memory.


Note
  • The Snap may fail if there isn't sufficient free local disk space in the node.
  • The Snap instances that existed before this property was first introduced, execute with a default value of 20% until its property value is updated.

  • When you select Unsorted in theSorted stream field, the Snap sorts, and stages all input data into temporary disk files. The following capabilities in this Snap enable you to monitor the status of system memory and disk space:

    • Pipeline Execution Statistics displays a status message for Free disk space, Available memory and Average document size.

    • The Snap reduces the internal sort buffer size to a minimum of 10MB when the available memory in the node becomes lower than 500MB to help avoid the out-of-memory crash.

    • The Snap restores the internal sort buffer size to its original size when the available memory becomes larger than 2GB.


Minimum memory (MB)

Default value: 500
Example: 750



If the available memory is less than this property value while processing input documents, the Snap stops to fetch the next input document until more memory is available. This feature is disabled if this property value is 0.

Minimum free disk space (MB)

Default value: 500
Example: 750

Minimum free disk space (MB)


If the free disk space is less than this property value, the Snap stops processing input documents until more free disc space is available. This feature is disabled if this property value is 0.

Default value: 500
Example: 750

Out-of-resource timeout (minutes)

Default value: 30
Example: 20


If the Snap pauses longer than this property value while waiting for more memory available, it throws an exception to prevent the system from running out of memory or disk space.

Multiexcerpt include macro
nameSnap Execution
pageAnaplan Read

Multiexcerpt include macro
nameSnap_Execution_Introduced
pageAnaplan Read
Multiexcerpt macro
nameTemporary Files

Temporary Files

During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When larger datasets are processed that exceeds the available compute memory, the Snap writes Pipeline data to local storage as unencrypted to optimize the performance. These temporary files are deleted when the Snap/Pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex's node properties, which can also help avoid Pipeline errors due to the unavailability of space. For more information, see Temporary Folder in Configuration Options. 

Examples

Providing Consistent Input Schema to Get Correct Joined Output

This example Pipeline demonstrates how you can get expected output joined data from two inputs by providing a consistent input schema. We use the Join Snap to accomplish this task.
Image Removed

First, we provide input documents with consistent input schema using JSON Generator Snaps as shown below. 

Left Input SchemaRight Input Schema
Image Removed

Image Removed

Next, we connect the Join Snap to the upstream Snaps and configure it as shown below. 
Image Removed
Upon validation, the Snap displays the following joined output as a result of providing a consistent input schema. The key name of the right view is the same as in the left view; hence

Default value: 30
Example: 20

Snap Execution





Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.

  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default Value: Execute only
Example: Validate & Execute


Multiexcerpt macro
nameTemporary Files

Temporary Files

During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When larger datasets are processed that exceeds the available compute memory, the Snap writes Pipeline data to local storage as unencrypted to optimize the performance. These temporary files are deleted when the Snap/Pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex's node properties, which can also help avoid Pipeline errors due to the unavailability of space. For more information, see Temporary Folder in Configuration Options. 

Examples

Providing Consistent Input Schema to Get Correct Joined Output

This example Pipeline demonstrates how you can get expected output joined data from two inputs by providing a consistent input schema. We use the Join Snap to accomplish this task.
Image Added

First, we provide input documents with consistent input schema using JSON Generator Snaps as shown below. 

Left Input SchemaRight Input Schema
Image Added

Image Added

Next, we connect the Join Snap to the upstream Snaps and configure it as shown below. 
Image Added

Upon validation, the Snap displays the following joined output as a result of providing a consistent input schema. The key name of the right view is the same as in the left view; hence, the Join Snap prefixes it with the right view label in the output data, right_id, right_field1 and right_field2.
Image Added

Download this Pipeline.

Join data based on a matching key or condition

This example pipeline demonstrates how to join two files using the Join Snaps. The pipeline combines records from two sources based on a matching key or condition - in this case, the customer_id.


Image Added

  1. Configure the JSON Generator Snaps to provide input data as shown below. 


Customer Details (JSON Generator)

Item & Cost (JSON Generator)


Image Added


Image Added

Generates JSON data with customer information, including customer IDs, names, email addresses, and mailing addresses.

Generates JSON data with order information, including order IDs, customer IDs, order dates, and item details such as item IDs, names, quantities, and prices.


2. Configure the Join Snap as shown below. This Snap merges data from both JSON Generator Snaps (Item & Cost and Customer Details) into a single output stream, combining order and customer information.

Image Added

3. Upon validation, the Snap displays the joined output, resulting from a consistent input schema. The key name customer_id is the same as in both views, therefore, the Join Snap prefixes it with the right view label in the output data, right_id, right_field1 and right_field2.Image Removed

Image Added

Download this Pipelinepipeline.

Inconsistent Joined Output Data as a Result of Inconsistent Input Schema

This example Pipeline demonstrates how the Join Snap generates inconsistent output joined data by providing inconsistent input schema in your inputs.

First, we provide input documents with inconsistent input schema using JSON Generator Snaps. 
The complete key set of input documents is {“id”, “field1”, “field2”}. Note that field2 entry is missing in the first left input document, the field1 entry is missing in the second left input document, and so on. The missing entries with null values cause unexpected results in the joined output data.

Left Input SchemaRight Input Schema

Next, we connect the Join Snap to the upstream Snaps to join the left and right input documents. To that end, we configure the Snap as shown below.
Image Modified

Upon validation, the Snap displays inconsistent output result, because the input documents contain incomplete key sets. The value right_c appears in the column field1 and the values right_d and right_h appear in the column field2, wherein they should be under right_field1 and right_field2 columns respectively.
Image Modified

Download this Pipeline.

Merging Documents 

You can use the Merge Join type to merge documents.

In this example:

  • $input comes into input0 and contains a value of 42
  • $output comes into input1 and contains a value of 84

  • Join type: Merge
  • Join Path:
    • Left path: (expression toggle on) $input
    • Right input view: input1
    • Right path: (expression toggle on) $output
  • Sorted streams: Ascending

Merge Result:

 Image Modified

If the Join type is changed to Outer, the result is:

 Image Modified

If the Join type is changed to Left Outer, the result is:

Image Modified

If the Join type is changed to Inner, no results are returned because there are no shared records.

If both inputs have a record of $day with a value of today added, set the paths to $day and the Join type to Inner. The result will look like this:


 Image Modified

Insert excerpt
Transform Snap Pack
Transform Snap Pack
nopaneltrue

Downloads

Multiexcerpt include macro
namedownload_instructions
pageOpenAPI

Attachments
patterns.*slp, .*zip


See Also

Video Tutorial: How to Perform Inner Join with the Join Snap