The metadata of the external database and external table are stored in the external data catalog. You must provide Amazon Redshift authorization to access the data catalog and the data files in Amazon S3.

You can read data from a single external table, multiple external tables, or from a standard Amazon Redshift table that is joined to an external table.

Multiple Amazon Redshift clusters can contain multiple external tables. You can run a query for the same data on Amazon S3 from any Amazon Redshift cluster in the same region. When you update the data in Amazon S3, the data is immediately available in all the Amazon Redshift clusters.

When you add an external table as a source in a mapping, the external table name is displayed in the

spectrum_schemaname

format in the

Select Source Object

dialog box.

You can use the following data types when you read from an external table:

Bigint (INT8)
Boolean (BOOL)
Char (CHARACTER)
Date
Decimal (NUMERIC)
Double Precision (FLOAT8)
Integer (INT, INT4)
Real (FLOAT4)
Smallint (INT2)
Timestamp
Varchar (CHARACTER VARYING)

Rules and guidelines for external tables

Consider the following rules and guidelines for external tables:

You can only read data from an Amazon Redshift Spectrum external table.
The Secure Agent does not remove the external table names from the list of target objects available in the Target transformation.