External Table Output
External Table Output
Writes new external table data with a column mapping of the user's choice. This will append existing external tables. To output a new external table rather than appending, use the Rewrite External Table component.
External tables are part of Amazon Redshift Spectrum and may not be available in all regions. For a list of supported regions see the Amazon documentation.
For full information on working with external tables, see the official documentation here.
|Name||Text||The descriptive name for the component.
This is automatically determined from the table name when the Table Name property is first set.
|Schema||Select||Select the table schema. Note that an external schema must be used. For more information on using multiple schemas, see Schema Support.|
|Target Table||Text||The name of the table to append data to.|
|Column Mapping||Source Column||All input fields are available, and can be added multiple times to create copies of a field.|
|Target Column||The output field name. By default, this is the same as the input. Just overwrite your preferred names.|
|S3 Object Prefix||Select||The file name to write data to. These files are stored at the S3 bucket location given when creating an external table. If this field is left blank, an automatically-generated UUID will be used as the prefix.|
In this example we have some data in an external table that requires some minor changes to the metadata. After the reformatting we want to save the new table and since it is external, we use the External Table Output component. The job canvas is shown below.
The external table data is read into Matillion ETL using a Table Input component and its metadata (shown below) must be changed by a Convert Type component. The table metadata is shown below.
The Convert Type component alters the metadata before being linked to our External Table Output and will automatically pass its data by doing so. The External Table Output component is that configured to write the external table data to Redshift as shown below. Note that this component only accepts external schemas and must have a target table that already exists as an external table (see Create External Table).
Since we do not want to change column names, we have opted to map each input column to one of the same name. We have also opted to drop some columns that we no longer need by not including them in the mapping.
Now we can check the metadata on the External Table Output component to ensure we have the columns we desire.
We can also sample the data to ensure everything remains in tact. When this job is run, it will alter the external table data and then write the new data into our target table.