Comment créer et interroger une table externe dans Amazon Redshift Spectrum ?

Résolution

Pour interroger des données depuis Amazon Simple Storage Service (Amazon S3), il n'est pas nécessaire de charger des données dans les tables Amazon Redshift. Lorsque Redshift Spectrum traite des requêtes, les données demeurent dans votre compartiment S3.

Important : Assurez-vous que votre cluster Amazon Redshift et votre compartiment S3 se trouvent dans la même région AWS.

Pour créer une table externe avec Redshift Spectrum, procédez comme suit :

1. Créez un rôle AWS Identity and Access Management (IAM) pour Amazon Redshift.

2. Associez les politiques IAM suivantes pour accorder à Amazon Redshift les autorisations requises pour accéder à votre catalogue de données : 
   Si vous utilisez le Catalogue de données AWS Glue, associez les politiques IAM AmazonS3ReadOnlyAccess et AWSGlueConsoleFullAccess à votre rôle.
   Si vous utilisez Amazon Athena Data Catalog, associez la politique IAM AmazonAthenaFullAccess à votre rôle.

3. Associez le rôle IAM à votre cluster Amazon Redshift.

4. Créez le schéma externe.

5. Téléchargez les fichiers de données et chargez-les dans un compartiment S3 de votre région. L'exemple suivant utilise les exemples de fichiers de données TICKIT pour S3 :

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

   Remarque : Remplacez bucket name par le nom de votre compartiment S3.

6. Créez une table externe. L'exemple suivant crée une table externe pour vos données 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/';

   Remarque : Remplacez bucket name par le nom de votre compartiment S3. Pour utiliser AWS Glue afin de créer une table externe, veillez à ajouter des définitions de tables à votre catalogue de données. 
   Pour utiliser Amazon Athena afin de créer une table externe, ajoutez des définitions de tables.
   Exemples de définitions de tables avec 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. Pour afficher toutes les tables externes auxquelles votre schéma externe fait référence, exécutez la requête suivante pour SVV_EXTERNAL_TABLES :

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

   Remarque : Remplacez bucket name par le nom de votre compartiment S3 et file location par votre emplacement de fichier.

8. Interrogez les tables externes en tant que tables Redshift Spectrum externes. Utilisez l'instruction 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

   L'exemple de requête précédent joint la table SALES externe à une table EVENT externe.

Informations connexes

Amazon Redshift Spectrum

Résolution des problèmes liés aux requêtes dans Amazon Redshift Spectrum