Table Metadata To Grid

Table Metadata To Grid

This component will take the metadata from a table and use this data to populate a grid variable with data. The component can take the following metadata from a table:

  • Name: Column Name.
  • Type: Column Type (as used in Matillion ETL).
  • Database Type: Column Type (as reported by the database).
  • Size: Allowable size (in characters) of the data in the column.
  • Precision: The precision of the data in the column. Will be 0 (zero) for non-applicable types.
  • Encoding: The name of the encoding type used on this column.
  • Name: Column Name.
  • Type: Column Type (as used in Matillion ETL).
  • Database Type: Column Type (as reported by the database).
  • Size: Allowable size (in characters) of the data in the column.
  • Precision: The precision of the data in the column. Will be 0 (zero) for non-applicable types.
  • Default Value: The default value in this column.
  • Not Null: Whether or not the column allows Null values (Yes/No).
  • Unique:Whether or not the column is a unique key (Yes/No).
  • Name: Column Name.
  • Type: Column Type (as used in Matillion ETL).
  • Mode: Field mode. Can be NULLABLE, REQUIRED or REPEATED (Columns in BigQuery are NULLABLE by default)

Properties

Property Setting Description
Name Text The descriptive name for the component.
Database Select Select the database in which to find the table to take metadata from.
Schema Select Select the schema in which to find the table to take metadata from.
Project Select Select the project in which to find the table to take metadata from.
Dataset Select Select the dataset in which to find the table to take metadata from.
Table Name Select The name of the table from which metadata will be taken
Grid Variable Select The name of the grid variable to be filled with data. To learn more about grid variables, see the documentation.
Grid Variable Mapping Multiple selection Map each column in the grid to a particular type of metadata; Name, Type, Database Type, Size, Precision or Encoding

Example

In this example we have a large table filled with data for last year's flight data. We want to make a new table for this year - it would be empty of data but contain the same metadata as the previous year's table. To accomplish this, we can use the Table Metadata To Grid component. The job is set up as below.

First we must create a grid variable for this job. By clicking on the job in the explorer panel and selecting 'Manage Grid Variables' we can create a new grid variable belonging to this job. We give this grid some columns with names pertaining to the metadata we want to take.

Now we move to the Table Metadata To Grid component and set it up as below. The target table is the one we will be pulling our metadata from. The Grid Variable is the grid variable we just created for this job.

By editing the Grid Variable Mapping property, each of the grid columns is assigned its corresponding metadata from the table. Note that we do not assign one for 'Database Type' as we have no need for it - when setting up a new table in Matillion ETL, we only need know the types Matillion ETL is going to use.

Next we set up the Create/Replace Table component. We name it 'flights_newyear' and this will become our new table. All we need to do is edit the Table Metadata property and select the 'Use Grid Variables' checkbox. Doing so will allow us to select our grid variable and map its columns into the various metadata options presented in the dialog, as below. This job can now be run.

Finally, we can load this table in a Transformation job using the Table Input component. Selecting the Metadata tab for this component will help confirm that our new table has been created as desired.