Quiero crear y consultar una tabla externa con 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:
-
Crea un rol de AWS Identity and Access Management (IAM) para Amazon Redshift.
-
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.
-
Asocia el rol de IAM a tu clúster de Amazon Redshift.
-
Crea el esquema externo.
-
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.
-
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
-
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.
-
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