Merging or Joining Spreadsheet or Database Data

Introduction

Merging or joining data in FME is accomplished by using a transformer. These transformers allow the user to define a relationship based on a join. This article covers which transformers work best for merging data that comes from a spreadsheet or database. If you are interested in merging spatial data, see the Merging or Joining Spatial Data article.

Merging or joining data together is different from connecting data streams in FME. When the data is merged, the attributes from each dataset are combined using a common identifier, and the resulting dataset is output as a single fused dataset. When the data streams are connected by drawing two connections to an input port of a transformer that isn’t a merging transformer, the data will be in a single stream, but the attributes will remain separate. For more information on combining data streams, see the Combining Multiple Streams of Data (Append vs. Merge or Join) article.

Transformers

There are two main categories for merging or joining transformers, they are: SQL and no-SQL. The transformers that are in the SQL category require knowledge of SQL; these are the InlineQuerier, SQLCreator, and SQLExecutor. The no-SQL transformers have the same functionality as the SQL ones but can be set up easily without any database knowledge; these are the FeatureJoiner, FeatureMerger, FeatureReader, and DatabaseJoiner. This isn’t an exhaustive list; there are other transformers that allow data to be joined but are used for a specific task. For more information on these other transformers, see the documentation.

Now, when trying to decide which transformer is the correct one to use, follow the flowchart below to help. When trying to decide, inspect your data and ask the following questions:

1) Is all of my data already inside the workspace?

• If yes: Follow the INTERNAL path
• If no: Follow the EXTERNAL path

2) Does the FeatureJoiner for internal or DatabaseJoiner for external work for my data?

• If yes: Great! End here
• If no: Continue to question 3

3) Do I know or want to use SQL?

• If yes: See the transformers in the green SQL box
• If no: See the transformers in the blue No SQL box

4) Did any of those transformers work?

• If yes: Great!
• If no: Read the documentation to ensure that it was set up correctly and suits your needs
• If unsure, re-read the documentation; if still unsure, ask a question in our community.

Tutorials and Documentation

The tutorial "How to Merge and Join Tabular Data" includes a simple set of exercises that covers most of the transformers listed above. More specific tutorials on each transformer are below. 

Transformers listed in alphabetical order:

DatabaseJoiner

The DatabaseJoiner, previously known as the Joiner transformer, enables joining an existing dataset in the workspace with a database or other external dataset.

The DatabaseJoiner Transformer tutorial

Join CSV File to a Database Table with the DatabaseJoiner

DatabaseJoiner Documentation

FeatureJoiner

The FeatureJoiner transformer is a simpler version of the FeatureMerger that uses SQL-based joins, offering better performance. This transformer can be used with ease without prior SQL knowledge. 

The FeatureJoiner Transformer tutorial

FeatureJoiner Documentation

FeatureMerger

The FeatureMerger performs a simple, in-memory join. The join is performed across two or more input streams of data that have already been read using a reader or a transformer, such as the FeatureReader.

The FeatureMerger Transformer tutorial

Combining Multiple Streams of Data (Append vs. Merge or Join)

FeatureMerger Documentation

FeatureReader

The FeatureReader reads features from any FME-supported format. This can be done midstream, which helps add and join data quickly.

The FeatureReader Transformer tutorial

FeatureReader Documentation

InlineQuerier

The InlineQuerier leverages SQL. It can be used to build data linkages more directly by using a temporary database consisting of tables created from incoming features, and returning the results (generated from SQL statements) as new features.

The InlineQuerier Transformer tutorial

Using the InlineQuerier as a replacement for multiple FeatureMergers

InlineQuerier Documentation

SQLExecutor and SQLCreator

The SQLExecutor and SQLCreator transformers are very similar. They are both used to execute SQL against a database. The only difference is that the SQLExecutor requires an incoming feature to trigger the SQL statement, and the SQLCreator does not.

The SQLExecutor and SQLCreator Transformers tutorial

Using the SQLExecutor to do a SQL Join

Performing native spatial queries on database tables using the SQLExecutor

SQLExecutor Documentation

SQLCreator Documentation

Looking for spatial joining transformers? See the Merging or Joining Spatial Data article.