¿Cómo creo y consulto una tabla externa en Amazon Redshift Spectrum?

Resolución

Para consultar datos de Amazon Simple Storage Service (Amazon S3), no es necesario cargar los datos en las tablas de Amazon Redshift. Cuando Redshift Spectrum procesa las consultas, los datos permanecen en el bucket de S3.

Importante: Asegúrate de que el clúster de Amazon Redshift y el bucket de S3 estén en la misma región de AWS.

Para crear una tabla externa con Redshift Spectrum, sigue estos pasos:

1. Crea un rol de AWS Identity and Access Management (IAM) para Amazon Redshift.

2. Adjunta las siguientes políticas de IAM para conceder a Amazon Redshift los permisos necesarios para acceder a tu catálogo de datos: 
   Si utilizas el catálogo de datos de AWS Glue, adjunta las políticas de IAM AmazonS3ReadOnlyAccess y AWSGlueConsoleFullAccess a tu rol.
   Si utilizas el catálogo de datos de Amazon Athena, adjunta la política de IAM AmazonAthenaFullAccess a tu rol.

3. Asocia el rol de IAM a tu clúster de Amazon Redshift.

4. Crea el esquema externo.

5. Descarga los archivos de datos y cárgalos en un bucket de S3 de tu región. En el siguiente ejemplo se utilizan los archivos de datos de muestra de TICKIT para S3:

   s3://<bucket_name>/tickit/spectrum/event/' and 's3://bucket_name/tickit/spectrum/sales/

   Nota: Sustituye bucket-name por el nombre del bucket de S3.

6. Crea una tabla externa. En el siguiente ejemplo se crea una tabla externa para los datos de EVENT:

   create external table spectrum.event(
       eventid integer,
       venueid smallint,
       catid smallint,
       dateid smallint,
       eventname varchar(200),
       starttime timestamp
   )
   row format delimited
   fields terminated by '|'
   stored as textfile
   location 's3://bucket_name/tickit/spectrum/event/';

   Nota: Sustituye bucket-name por el nombre del bucket de S3. Para usar AWS Glue para crear una tabla externa, asegúrate de agregar definiciones de tablas al catálogo de datos. 
   Para usar Amazon Athena para crear una tabla externa, agrega definiciones de tablas.
   Ejemplos de definiciones de tablas con Athena:

   CREATE EXTERNAL TABLE spectrum.event (
         eventid int,
         venueid smallint,
         catid smallint,
         dateid smallint,
         eventname varchar(max),
         starttime timestamp)
       ROW FORMAT DELIMITED
         FIELDS TERMINATED BY '|'
       STORED AS INPUTFORMAT
         'org.apache.hadoop.mapred.TextInputFormat'
       OUTPUTFORMAT

7. Para ver todas las tablas externas a las que hace referencia tu esquema externo, ejecuta la siguiente consulta para SVV_EXTERNAL_TABLES:

   select schemaname , tablename , location from svv_external_tables where schemaname = 'spectrum';
   
   schemaname | tablename | location
   ----------------+---------------------------------+-----------------------------------------------------------------------
   spectrum | event | s3://bucket-name/file-location

   Nota: Sustituye bucket name por el nombre del bucket de S3 y file location por la ubicación del archivo.

8. Consulta las tablas externas como tablas externas de Redshift Spectrum. Usa la instrucción SELECT:

   select top 3 spectrum.sales.eventid, sum(spectrum.sales.pricepaid) from spectrum.sales, spectrum.event
   where spectrum.sales.eventid = spectrum.event.eventid
   and spectrum.sales.pricepaid > 30
   group by spectrum.sales.eventid
   order by 2 desc;
   
    eventid |   sum
   ---------+----------
        289 | 51846.00
       7895 | 51049.00
       1602 | 50301.00

   La consulta del ejemplo anterior une la tabla SALES externa con una tabla EVENT externa.

Información relacionada

Amazon Redshift Spectrum

Solución de problemas de consultas en Amazon Redshift Spectrum