Multi Table Input
Multi Table Input reads chosen columns from multiple input tables into a transformation pipeline.
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
Name = string
A human-readable name for the component.
Database = drop-down
The Snowflake database. The special value, [Environment Default], will use the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.
Schema = drop-down
The Snowflake schema. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.
Pattern Type = drop-down
- 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.
Pattern = string
The pattern to match available tables to. The pattern syntax depends upon the chosen Pattern Type.
Cast Types = boolean
- 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.
Add Source Table = boolean
When True, Matillion adds a column, "source_table", containing the input table name that was matched to provide this row.
Columns = dual listbox
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. Move columns from left to right to include in the input.
Name = string
A human-readable name for the component.
Database = drop-down (optional)
Cross-query two databases. Specify a separate database to the one selected in the connection (that uses the environment default database). For more information, read Querying data across databases. There are limitations as to whether an additional database can be queried. For more information, read Considerations and Limitations. These limitations will prevent cross database connections, and any deviation from the environment default database will fail.
Note
- You will need to be connected to either an RA3 or Serverless Redshift cluster type for a cross database connection to work.
- On RA3 or Serverless Redshift clusters, you will also need the necessary permissions to view schemas and tables outside of your environment’s default database.
Schema = drop-down
Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, read Schemas.
Pattern Type = drop-down
- Like: The available tables are filtered using a case-sensitive SQL syntax pattern. Read Redshift Like documentation.
- Regex: The available tables are filtered using a POSIX EXE Regular Expression comparison. Read Redshift Regular Expression documentation.
- Similar To: The available tables are filtered using an SQL Similar To comparison. Read Redshift Similar To documentation.
Pattern = string
The pattern to match available tables to. The pattern syntax depends upon the chosen Pattern Type.
Cast Types = boolean
- 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.
Add Source Table = boolean
When True, Matillion adds a column, "source_table", containing the input table name that was matched to provide this row.
Columns = dual listbox
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. Move columns from left to right to include in the input.
Trim Columns = drop-down
Wraps the column names in a BTRIM function, which will strip out all the leading and trailing spaces.
To learn more, read the Amazon Redshift documentation.
| Snowflake | Databricks | Amazon Redshift |
|---|---|---|
| ✅ | ❌ | ✅ |