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

Date de la dernière mise à jour : 15/10/2020

J'essaie de créer et d'interroger une table externe dans Amazon Redshift Spectrum. Comment dois-je procéder ?

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éer un rôle IAM pour Amazon Redshift.

2.    Attachez votre stratégie AWS Identity and Access Management (IAM) :
Si vous utilisez AWS Glue Data Catalog, associez les stratégies IAM Amazons3ReadOnlyAccess et AWSglueConsoleFullAccess à votre rôle.
Si vous utilisez le catalogue de données Amazon Athena, associez la stratégie 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/

Dans cet exemple, la base de données externe est créée dans un catalogue de données AWS Glue :

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 delimited
fields 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 Amazon Redshift Spectrum.

Pour créer une table externe à l'aide d'AWS Glue, veillez à 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 tables, reportez-vous à la section Définition de tables dans le catalogue de données AWS Glue.

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 EVENTexterne.</p


Cet article vous a-t-il été utile ?


Besoin d'aide pour une question technique ou de facturation ?