如何在 Amazon Redshift Spectrum 中建立和查詢外部資料表？

解決方法

若要從 Amazon Simple Storage Service (Amazon S3) 查詢資料，您無需將資料載入至 Amazon Redshift 資料表中。當 Redshift Spectrum 處理查詢時，資料會保留在您的 S3 儲存貯體中。

**重要：**請確定您的 Amazon Redshift 叢集和 S3 儲存貯體位於相同的 AWS 區域。

若要使用 Redshift Spectrum 建立外部資料表，請完成下列步驟：

1. 為 Amazon Redshift 建立 AWS Identity and Access Management (IAM) 角色。

2. 附上下列 IAM 政策，以授與 Amazon Redshift 存取資料目錄所需的權限： 
   如果您使用 AWS Glue Data Catalog，請將 AmazonS3ReadOnlyAccess 和 AWSGlueConsoleFullAccess IAM 政策附加到您的角色。
   如果您使用 Amazon Athena Data Catalog，請將 AmazonAthenaFullAccess IAM 政策附加到您的角色。

3. 將 IAM 角色與您的 Amazon Redshift 叢集關聯。

4. 建立外部結構描述。

5. 下載資料檔案，並將其上傳到您所在區域的 S3 儲存貯體。以下範例使用 S3 的 TICKIT 範例資料檔案：

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

   注意： 將 bucket name (儲存貯體名稱) 替換為您 S3 儲存貯體的名稱。

6. 建立外部資料表。以下範例會為您的 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/';

   注意： 將 bucket name (儲存貯體名稱) 替換為您 S3 儲存貯體的名稱。 若要使用 AWS Glue 建立外部資料表，請務必將資料表定義新增至您的 Data Catalog。 
   若要使用 Amazon Athena 建立外部資料表，請新增資料表定義。
   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. 若要查看外部結構描述參照的所有外部資料表，請對 SVV_EXTERNAL_TABLES 執行下列查詢：

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

   **注意：**將 bucket name (儲存貯體名稱) 替換為您 S3 儲存貯體的名稱，將 file location (檔案位置) 替換為您的檔案位置。

8. 將外部資料表作為外部 Redshift Spectrum 資料表進行查詢。使用 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

   上述查詢範例將外部 SALES 資料表與外部 EVENT 資料表聯結。

相關資訊

Amazon Redshift Spectrum

Amazon Redshift Spectrum 中的查詢疑難排解