- Examines all tables in the specified database schema and selects those whose names match a user-specified pattern. For example, tables called “Budgets_2012”, “Budget_2013”, and so on, would be selected with a pattern of
Budget%_20%. - Selects user-specified columns from each of the selected tables. The matching tables need to have common column names, so that the same columns can be selected from each table and combined into a single dataset.
- Outputs the dataset for use by the pipeline’s downstream components.
Use case
The Multi Table Input component is used to consolidate data from multiple similar tables. Some common uses for this include:- Consolidating historical data from multiple yearly tables. Combine multiple identical tables that are partitioned by year into a single dataset for analysis and reporting.
- Comparing or joining data across multiple similar tables. Load similar data from multiple tables, allowing further transformations such as aggregations to be performed.
Properties
- Snowflake
- Amazon Redshift
string
required
A human-readable name for the component.
drop-down
required
The Snowflake database. The special value
[Environment Default] uses the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.drop-down
required
The Snowflake schema. The special value
[Environment Default] uses the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.drop-down
required
- ILike: The available tables are filtered using a case-insensitive SQL syntax pattern. Read Snowflake ILike documentation.
- Like: The available tables are filtered using a case-sensitive SQL syntax pattern. Read Snowflake Like documentation.
- Regex: The available tables are filtered using a POSIX EXE Regular Expression comparison. Read Snowflake Rlike documentation.
string
required
The pattern to match available tables to. The pattern syntax depends upon the chosen Pattern Type.
boolean
required
- True: If the same-named column from multiple tables has a different data type, attempt to cast to a common type.
- False: If the same-named column from multiple tables has a different data type, it is reported as an error. This is the default setting.
boolean
required
When True, adds a column, “source_table”, containing the input table name that was matched to provide this row.
object selector
required
Select the columns to include in the input. This list contains all columns from all input tables.The available columns are generated by first scanning the available tables, and then providing all columns from any of the inputs. It is expected that the tables matching the pattern are fairly similar and share many columns.