Rewrite External Table

Rewrite External Table

Write data from an external table to an S3 bucket. Note that this will overwrite any existing data on the target file and it is generally recommended not to perform this action on the same file that the source data is referenced from. It is important that the Matillion ETL instance has access to the chosen bucket and file.

Currently-supported AWS regions for writing external data are: US East (Northern Virginia), US East (Ohio), US West (Oregon), as well as Europe (Ireland) and Asia Pacific (Tokyo).

For full information on working with external tables, see the official documentation here.


Properties

Property Setting Description
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 draw data (references) from.
Location Select The file target location, including S3 bucket path. The Matillion instance must have access to this data (typically, access is granted according to the AWS credentials on the instance or if the bucket is public). A directory named after the Target Table will be created at this location and then populated with files.
Partition Select Multiple (Optional) Select source columns to be partitions when writing data. Chosen columns will be queried for distinct values and partitioned file directories will be created (if they don't exist) for those values.

Example

In this example, we have a regular table that holds the latest project data. After some transformation, we want to write the resultant data to an external table so that it can be occasionally queried without the data being held on Redshift. However, since this is an external table and may already exist, we use the Rewrite External Table component. The job is shown below.

We want our new data to overwrite the old table so we've chosen the old table's name in the Target Table property and the same Location in the S3 bucket.

When run, this component will overwrite the written data on the S3 bucket as well as rewrite the external table. We can check the data by sampling the component.

Finally we can also check the S3 bucket for the files through several methods. Below we use the S3 Object Put component to see files within the bucket and confirm that our data is there.