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

Résolution

Avec Amazon Redshift Spectrum, vous pouvez interroger des données d'Amazon Simple Storage Service (Amazon S3) sans avoir à charger des données dans des tables Amazon Redshift. Amazon Redshift Spectrum traite toutes les requêtes pendant que les données restent dans votre compartiment Amazon S3.

Important : avant de commencer, vérifiez si Amazon Redshift est autorisé à accéder à votre compartiment S3 et à tous les catalogues de données externes. En outre, votre cluster Amazon Redshift et votre compartiment S3 doivent se trouver dans la même région AWS.

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

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

2.    Associez votre politique IAM :
Si vous utilisez le catalogue de données AWS Glue, associez les politiques IAM AmazonS3ReadOnlyAccess et AWSGlueConsoleFullAccess à votre rôle.
Si vous utilisez le catalogue de données Amazon Athena, associez la politique IAM AmazonAthenaFullAccess à votre rôle.

3.    Associer le rôle IAM au cluster Amazon Redshift.

4.    Créez le schéma externe. Le schéma externe fait référence à une base de données dans le catalogue de données externe. Le schéma externe fournit également au rôle IAM un Amazon Resource Name (ARN) qui autorise l'accès Amazon Redshift à S3.

Dans l'exemple suivant, nous utilisons des exemples de fichiers de données de S3 (tickitdb.zip). Décompressez et chargez les fichiers individuels dans un compartiment S3 de votre région AWS comme suit :

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

Vous pouvez créer un schéma externe à l'aide d'une commande similaire à celle ci-dessous :

create external schema spectrum
from data catalog   
database 'spectrumdb'
iam_role 'arn:aws:iam::123456789012:role/mySpectrumRole'
create external database if not exists;

Remarque : remplacez l'ARN du rôle IAM par l'ARN que vous avez créé. Assurez-vous de spécifier le nom de la base de données externe (par exemple « spectrumdb ») pour le paramètre de base de données.

5.    Créez une table externe. Par exemple, vous pouvez créer une table externe pour vos données EVENT comme ceci :

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

Pour plus d'informations sur les tables externes, consultez Création de tables externes pour Redshift Spectrum.

Pour créer une table externe à l'aide d'AWS Glue, assurez-vous d'ajouter des définitions de table à votre catalogue de données AWS Glue. Vous pouvez ajouter des définitions de table dans votre catalogue de données AWS Glue de plusieurs manières. Pour plus d'informations sur l'ajout de définitions de table, consultez Utilisation de tableaux sur la AWS Glueconsole.

Pour créer une table externe à l'aide d'Amazon Athena, ajoutez des définitions de table comme ceci :

CREATE EXTERNAL TABLE `spectrumdb.event`(  `eventid` int, 
      `venueid` smallint, 
      `catid` smallint, 
      `dateid` smallint, 
      `eventname` string, 
      `starttime` timestamp)
    ROW FORMAT DELIMITED 
      FIELDS TERMINATED BY '|' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION  's3://<bucket_name>/tickit/spectrum/event';

6.    Exécutez la requête suivante pour SVV_EXTERNAL_TABLES pour afficher toutes les tables externes référencées par votre schéma externe :

select schemaname , tablename , location from svv_external_tables where schemaname = 'spectrum';

schemaname | tablename | location
----------------+---------------------------------+-----------------------------------------------------------------------
spectrum | event | s3://<bucket-name>/<file-location>

7.    Interrogez les tables externes (en tant que tables externes Amazon Redshift Spectrum) à l'aide d'une 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

Cet exemple de requête joint la table SALES externe à une table EVENT externe.

---

Informations connexes

Interrogation de données externes avec Amazon Redshift Spectrum

Dépannage des requêtes dans Amazon Redshift Spectrum