Come posso creare ed eseguire query su una tabella esterna in Amazon Redshift Spectrum?

Risoluzione

Per eseguire query sui dati di Amazon Simple Storage Service (Amazon S3), non serve caricarli nelle tabelle di Amazon Redshift. Quando Redshift Spectrum elabora le query, i dati rimangono nel bucket S3.

Importante: assicurati che il cluster Amazon Redshift e il bucket S3 si trovino nella stessa Regione AWS.

Per creare una tabella esterna con Redshift Spectrum, completa i seguenti passaggi:

1. Crea un ruolo AWS Identity and Access Management (AWS IAM) per Amazon Redshift.

2. Collega le seguenti policy IAM per concedere ad Amazon Redshift le autorizzazioni necessarie per accedere al catalogo dati: 
   Se utilizzi il Catalogo dati AWS Glue, collega al ruolo le policy IAM AmazonS3ReadOnlyAccess e AWSGlueConsoleFullAccess.
   Se utilizzi il Catalogo dati Amazon Athena, collega al ruolo la policy IAM AmazonAthenaFullAccess.

3. Associa il ruolo IAM al cluster Amazon Redshift.

4. Crea lo schema esterno.

5. Scarica i file di dati e caricali in un bucket S3 nella Regione utilizzata. L'esempio seguente utilizza i file di dati campione TICKIT per S3:

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

   Nota: sostituiscibucket name con il nome del tuo bucket S3.

6. Crea una tabella esterna. L'esempio seguente crea una tabella esterna per i dati 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: sostituiscibucket name con il nome del tuo bucket S3.Per creare una tabella esterna utilizzando AWS Glue, assicurati di aggiungere le definizioni delle tabelle al catalogo dati. 
   Per creare una tabella esterna utilizzando Amazon Athena, aggiungi le definizioni delle tabelle.
   Esempi di definizioni delle tabelle 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. Per visualizzare tutte le tabelle esterne a cui fa riferimento il tuo schema esterno, esegui la seguente query per 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: sostituisci bucket name con il nome del tuo bucket S3 e file location con la posizione del tuo file.

8. Esegui query sulle tabelle esterne come tabelle Redshift Spectrum esterne. Utilizza l'istruzione 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

   Il precedente esempio di query unisce la tabella SALES esterna a una tabella EVENT esterna.

Informazioni correlate

Amazon Redshift Spectrum

Risoluzione dei problemi relativi alle query in Amazon Redshift Spectrum