Table of Contents

Search

  1. Preface
  2. Introduction to PowerExchange for Amazon Redshift
  3. PowerExchange for Amazon Redshift Configuration
  4. Amazon Redshift Connections
  5. PowerExchange for Amazon Redshift Data Objects
  6. Amazon Redshift Mappings
  7. Pushdown Optimization
  8. Amazon Redshift Lookup
  9. Appendix A: Amazon Redshift Datatype Reference
  10. Appendix B: Troubleshooting

PowerExchange for Amazon Redshift User Guide

PowerExchange for Amazon Redshift User Guide

External Schema and External Table

External Schema and External Table

To use Amazon Redshift Spectrum, you can read data from an external table within an external schema that references a database in an external data catalog.
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.

0 COMMENTS

We’d like to hear from you!