Multi Table Input Component

Multi Table Input Component

Read chosen columns from an input table into the job.

The difference between Table Input and Multi Table Input is that Multi Table Input reads data from many input tables based on filtering all available input tables matching a pattern.

The matching tables are expected to be very similar, e.g. Budgets_2012, Budget_2013 and so on, with a common set of columns.

Properties

Property Setting Description
Name Text The descriptive name for the component.
Pattern Text The pattern to match available tables to. The pattern syntax depends upon the chosen Pattern Type (see below).
Pattern Type Select Like – The available tables are filtered using an SQL Like comparison. See Redshift Like documentation.
Similar To – The available tables are filtered using an SQL Similar To comparison. See Redshift Similar To documentation.
Regex – The available tables are filtered using a POSIX Regular Expression comparison. See Redshift Regular Expression documentation.
Column Names Select Many The available column names are generated by first scanning the available tables, and then providing all column names from any of the inputs. It is expected that the tables matching the pattern are fairly similar and share many columns.
Cast Types Yes / No Yes –If the same-named column from multiple tables has a different data type, attempt to cast to a common type.
No –If the same-named column from multiple tables has a different data type, it is reported as an error.
Add source table name Yes / No When set to Yes, add an additional column "source_table" containing the input table name that was matched to provide this row.
Trim Columns Select Wraps the column names in a BTRIM function, which will strip out all the leading and trailing spaces. See the Redshift documentation for details.

Strategy

Generates a set of select statements, concatenated together using UNION.

Example

This job reads data from all tables that start with the prefix 'flight' using an SQL Like pattern.

The Pattern uses the % wildcard, as that is the multi-character wildcard used in a Like filter.

All available columns are chosen.

Video