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 create an external database in Amazon Redshift. You can read data from a single external table, multiple external table, or from a standard Amazon Redshift table that is joined to the 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 create an external table, you must specify the Amazon S3 location from where you want to read the data. You can create the external tables by defining the structure of the Amazon S3 data files and registering the external tables in the external data catalog. Then, you can run queries or join the external tables.

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

spectrum_schemaname

format in the

Select Source Object

dialog box.

When you create an external table using Athena or Glue data catalogs, ensure that you create the external tables using the data types that Amazon Redshift V2 Connector supports.

The following lists the data types that Amazon Redshift V2 Connector supports when you create an external table:

Bigint (INT8)
Boolean (BOOL)
Char (CHARACTER)
Date
Applicable when you create an external table for the ORC, Parquet, and Textfile file formats.
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 the Amazon Redshift Spectrum external table. You cannot insert or update data in the 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.
You cannot use pre-SQL and post-SQL commands to perform target operations on an external table.

For more information on how to create an external table, see the AWS documentation.