Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Amazon Redshift connectors
  3. Part 2: Data Integration with Amazon Redshift V2 Connector
  4. Part 3: Data Integration with Amazon Redshift Connector

Amazon Redshift Connectors

Amazon Redshift Connectors

External schema and external table

External schema and external table

To use Amazon Redshift Spectrum, you must create an external table within an external schema that references a database in an external data catalog. You can create the external table for Avro, ORC, Parquet, RCFile, SequenceFIile, and Textfile file formats.
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)
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.
For more information on how to create an external table, see the AWS documentation.