Wie erstelle und frage ich eine externe Tabelle in Amazon Redshift Spectrum ab?

Lösung

Um Daten von Amazon Simple Storage Service (Amazon S3) abzufragen, musst du keine Daten in Amazon Redshift-Tabellen laden. Wenn Redshift Spectrum Abfragen verarbeitet, verbleiben die Daten im S3-Bucket.

Wichtig: Stelle sicher, dass sich der Amazon Redshift-Cluster und der S3-Bucket in derselben AWS-Region befinden.

Gehe wie folgt vor, um eine externe Tabelle mit Redshift Spectrum zu erstellen:

1. Erstelle eine AWS Identity and Access Management (IAM, Identitäts- und Zugriffsmanagement)-Rolle für Amazon Redshift.

2. Füge die folgenden IAM-Richtlinien an, um Amazon Redshift die erforderlichen Berechtigungen für den Zugriff auf deinen Datenkatalog zu gewähren: 
   Wenn du den AWS-Glue-Datenkatalog verwendest, füge der Rolle die IAM-Richtlinien AmazonS3ReadOnlyAccess und AWSGlueConsoleFullAccess an.
   Wenn du den Amazon Athena-Datenkatalog verwendest, füge der Rolle die IAM-Richtlinie AmazonAthenaFullAccess an.

3. Ordne die IAM-Rolle dem Amazon Redshift-Cluster zu.

4. Erstelle das externe Schema.

5. Lade die Datendateien herunter und lade sie in einen S3-Bucket in deiner Region hoch. Das folgende Beispiel verwendet die TICKIT-Beispieldatendateien für S3:

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

   Hinweis: Ersetze bucket name durch den Namen deines S3-Buckets.

6. Erstelle eine externe Tabelle. Das folgende Beispiel erstellt eine externe Tabelle für die EVENT-Daten:

   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/';

   Hinweis: Ersetze bucket name durch den Namen deines S3-Buckets. Um mithilfe von AWS Glue eine externe Tabelle zu erstellen, musst du dem Datenkatalog unbedingt Tabellendefinitionen hinzufügen. 
   Um Amazon Athena zum Erstellen einer externen Tabelle zu verwenden, füge Tabellendefinitionen hinzu.
   Beispiel für Tabellendefinitionen mit 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. Um alle externen Tabellen anzuzeigen, auf die das externe Schema verweist, führe die folgende Abfrage für SVV_EXTERNAL_TABLES aus:

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

   Hinweis: Ersetze bucket name durch den Namen deines S3-Buckets und file location durch deinen Dateispeicherort.

8. Frage die externen Tabellen als externe Redshift Spectrum-Tabellen ab. Verwende die SELECT-Anweisung:

   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

   Die vorherige Beispielabfrage verknüpft die externe SALES-Tabelle mit einer externen EVENT-Tabelle.

Ähnliche Informationen

Amazon Redshift Spectrum

Problembehandlung bei Abfragen in Amazon Redshift Spectrum