Using Table Metadata to Grid
Overview
The Table Metadata To Grid component in Matillion ETL takes metadata from a table and uses this metadata to populate a grid variable. This page offers an example of how and why the component might be used.
In this example we have a large table filled with data for last year's flight data, and we want to make a new table for this year. The new table will be empty of data, but should use the same metadata as last year's table. To accomplish this, we will use the Table Metadata To Grid component to write the original table's metadata to a grid variable, and then use that grid variable to create the metadata in the new table.
The job to accomplish this task is set up as follows:
Once this job is set up, it can be used in each subsequent year to configure the next year's table with minimal effort.
Create a grid variable
Before the job can run, we must create a grid variable to hold the metadata that we will be pulling from the source table. The process for creating a grid variable is fully described in Grid Variables.
Create columns in the grid variable that match the metadata that you will copy from the source table. For this example we have created the following columns:
Note
The metadata required to define a table depends on the database being used. For this example, we are using Snowflake. To determine what metadata will be required by the database you use, see the Table Metadata To Grid component documentation, where the metadata will be listed in the Grid Variable Mapping row of the Properties table.
Configure the Table Metadata To Grid component
Next, we will set up the Table Metadata To Grid component to pull metadata from the original table and write it to the grid variable we have created. The component is configured as follows:
See the Table Metadata To Grid component documentation for details of how to configure this component, but note the following:
- Table - is the name of the table we will be pulling metadata from, in this example flights.
- Grid Variable - is the name of the grid variable we have just created, in this example Metadata.
- Grid Variable Mapping - we use this property to assign each of the grid variable columns to its corresponding metadata from the table.
Configure the Create Table component
Next, we will set up the Create Table component to create a new empty table defined by the metadata we have stored in the grid variable. The component is configured as follows:
See the Create Table component documentation for details of how to configure this component, but note the following:
- New Table Name - is the name of the table we are creating, in this example flights-newyear.
- Columns - we use this property to specify that the new table will use the column definitions we currently have stored in the grid variable. Open the Columns dialog and click Use Grid Variable, and then select the grid variable you are using, in this example Metadata
This job can now be run to create the new table.
Finally, we can load this table in a transformation job using the Table Input component. Selecting the Metadata tab for this component will allow us to confirm that our new table has been created as desired.
