User Tools

Site Tools


transformations:merge

Merge another table

This transformation appends one or more columns from another table where certain columns in both tables match.

There are three possible merge modes:

  • Lookup Mode – get first matching combination from the other table. If there are other combinations they are ignored.
  • Left Join Mode – get all matching combinations from the other table. It can increase number or rows in the current table.
  • Full Join Mode – include all combinations of keys from both tables. Equivalent to the full outer join in SQL.

EXAMPLE

Table 1: The longest rivers in the world

River Length (km) Continent
Nile 6650 Africa
Amazon 6400 South America
Yangtze 6300 Asia
Mississippi 6275 North America

Table 2: The outflow

River Outflow
Nile Mediterranean
Amazon Atlantic Ocean
Yangtze East China Sea
Mississippi Gulf of Mexico

Objective: Merge the “Outflow” column of Table 2 into Table 1

Transformation:

  • Add columns from table: Table 2
  • Column: Outflow
  • Merge mode: Add more rows to this table and all matching rows from Table 2 (i.e. do left join)

Result:

River Length (km) Continent Outflow
Nile 6650 Africa Mediterranean
Amazon 6400 South America Atlantic Ocean
Yangtze 6300 Asia East China Sea
Mississippi 6275 North America Gulf of Mexico

Limitations

The resulting table after Merge can't have more than 134,217,728 rows.

See also

transformations/merge.txt · Last modified: 2018/04/30 13:44 by dmitry