Multi Table Input
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
Name = string
A human-readable name for the component.
Pattern Type = drop-down
- ILike: The available tables are filtered using a case-insensitive SQL syntax pattern. See Snowflake ILike documentation.
- Like: The available tables are filtered using a case-sensitive SQL syntax pattern. See Snowflake Like documentation.
- Regex: The available tables are filtered using a POSIX EXE Regular Expression comparison. See Snowflake Regex documentation.
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 = string
The pattern to match available tables to. The pattern syntax depends upon the chosen Pattern Type (see above).
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. Use the Editor to select which columns to pass along.
Cast Types = drop-down
- 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. Default is False.
Add Source Table Name = drop-down
When set to True, Matillion ETL adds a column, "source_table", containing the input table name that was matched to provide this row.
Name = string
A human-readable name for the component.
Catalog = drop-down
Select a Databricks Unity Catalog. The special value, [Environment Default], will use the catalog specified in the Matillion ETL environment setup. Selecting a catalog will determine which databases are available in the next parameter.
Database = drop-down
Select the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
Pattern = string
The regular expression pattern used to filter out unwanted tables.
Except for * and | characters, the pattern works like a regular expression.
* alone matches 0 or more characters and | is used to separate multiple different regular expressions, any of which can match.
The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.
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. Use the Editor to select which columns to pass along.
Cast Types = drop-down
- 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. Default is False.
Add Source Table Name = drop-down
When set to True, Matillion ETL adds a column, "source_table", containing the input table name that was matched to provide this row. Default is False.
Name = string
A human-readable name for the component.
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.
External schemas are not supported.
Pattern = string
The regular expression pattern used to filter out unwanted tables.
Except for * and | characters, the pattern works like a regular expression.
* alone matches 0 or more characters and | is used to separate multiple different regular expressions, any of which can match.
The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.
Pattern Type = drop-down
- 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 = 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. Use the Editor to select which columns to pass along.
Cast Types = drop-down
- 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. Default is False.
Add Source Table Name = drop-down
When set to True, Matillion ETL adds a column, "source_table", containing the input table name that was matched to provide this row. Default is False.
Trim Columns = drop-down
Wraps the column names in a BTRIM function, which will strip out all the leading and trailing spaces.
See the Redshift documentation for details.
Name = string
A human-readable name for the component.
Schema = drop-down
Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, read the Azure Synapse documentation.
Pattern = string
The pattern to match available tables to. For more information, please refer to the Microsoft Azure documentation.
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. Use the Editor to select which columns to pass along.
Cast Types = drop-down
A CAST command converts an expression of one data type to another. Default is true.
For more information, please refer to the Microsoft Azure documentation.
Add Source Table Name = drop-down
Select whether to add the source table to the load. The default setting is false.
Strategy
Generates a set of select statements, concatenated together using UNION.
Video
| Snowflake | Delta Lake on Databricks | Amazon Redshift | Google BigQuery | Azure Synapse Analytics |
|---|---|---|---|---|
| ✅ | ✅ | ✅ | ❌ | ✅ |