Skip to content

Create External Schema

An external schema allows you to reference data stored outside your chosen cloud data warehouse. Read Create External Table for details, or Creating external tables for Redshift Spectrum in the Redshift documentation.

Note

External schemas are available on Matillion ETL for Amazon Redshift only.

External schemas must be defined within the Matillion ETL Environment and are only available to the environment where they have been defined.

Create an external schema

  1. Open the Environments panel in the bottom left of Matillion ETL.
  2. You can add an external schema to any of the listed environments. Right-click the environment, and then click Create External Schema.

  3. In the Create External Schema dialog, complete the following fields:

    • External Schema: A name for the new schema that will be visible to the Redshift cluster. This shouldn't be the same as any existing internal or external Redshift schema name.
    • Data Catalog: The name of an Athena Catalog used to represent tables inside Amazon Redshift Spectrum. If this catalog already exists, your external schema will make any tables defined in it available to Redshift queries. Otherwise, a new, empty catalog will be created. Tables can then be defined from Redshift using the external schema or via Athena.

    • Role ARN: The full ARN of a role attached to your Redshift cluster. This must have the following format: arn:aws:iam::<aws-account-id>:role/<role-name>. This role will require permissions to access both Athena and S3. Read IAM policies for Amazon Redshift Spectrum in the Redshift documentation for details.

      Note

      If your Amazon Redshift Spectrum cluster isn't in the same region as your Matillion ETL EC2 instance, the Role ARN won't be automatically found and must be entered manually.

  4. Click OK to create the schema.

Manage external schemas

All external schemas are listed when you expand an environment in the Environments panel. From here, you can right-click a schema and perform any of the following actions:

  • Expand the schema to show tables and views.
  • Click Select Schema to make the selected schema the active schema for this environment. The schema icon displays an arrow to indicate that it has been selected.
  • Click Delete Schema to delete the selected schema. Deleting a schema is irreversible, so be very sure that you want to proceed with this action. To confirm, type the name of the schema and click Yes.
  • Click Refresh to refresh the list of tables and views under the schema.