Table Metadata To Grid

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)


Redshift Properties

Property Setting Description
Name String Input the descriptive name of the component.
Schema Select Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
Table Name Select Select the table from which metadata will be taken.
Grid Variable Select Select the grid variable to be loaded with data. To learn more about grid variables, read our Grid Variables documentation.
Grid Variable Mapping Multiple Selection Map each column in the grid to a particular "type" of metadata by selecting from the available dropdown menus. Columns include: name, type, precision, size, encoding.
Schema Select Select the schema in which to find the table to take metadata from. A schema is a logical grouping of database “objects” (tables, views, etc.). Each schema belongs to a single database. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.

Snowflake Properties

Property Setting Description
Name String Input the descriptive name of the component.
Database Select Select the database in which to find the table to take metadata from. A database is a logical grouping of schemas. Each database belongs to a single Snowflake account.
Schema Select Select the schema in which to find the table to take metadata from. A schema is a logical grouping of database “objects” (tables, views, etc.). Each schema belongs to a single database. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
Target Table Select Select the table from which metadata will be taken.
Grid Variable Select Select the grid variable to be loaded with data. To learn more about grid variables, read our Grid Variables documentation.
Grid Variable Mapping Multiple Selection Map each column in the grid to a particular "type" of metadata by selecting from the available dropdown menus. Columns include: Column Name, Data Type, Size, Precision, Default Value, Not Null, Unique.

BigQuery Properties

Property Setting Description
Name String Input the descriptive name of the component.
Project Select Select the Google Bigquery project. The special value, [Environment Default], will use the project defined in the environment.
For more information, refer to the BigQuery documentation.
Dataset Select Select the Google Bigquery dataset the source table exists on. The special value, [Environment Default], will use the dataset defined in the environment.
For more information, refer to the BigQuery documentation.
Table Select Select the table from which metadata will be taken.
Grid Variable Select Select the grid variable to be loaded with data. To learn more about grid variables, read our Grid Variables documentation.
Grid Variable Mapping Multiple Selection Map each column in the grid to a particular "type" of metadata by selecting from the available dropdown menus. Columns include: Column Name, Data Type, Mode.

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.