如何在 Amazon Redshift Spectrum 中创建和查询外部表?

上次更新时间:2020 年 10 月 15 日

我正在尝试在 Amazon Redshift Spectrum 中创建和查询外部表。我该如何操作?

解决方法

借助 Amazon Redshift Spectrum,您不需要将数据加载到 Amazon Redshift 表,就可以从 Amazon Simple Storage Service (Amazon S3) 查询数据。Amazon Redshift Spectrum 将处理所有查询,与此同时数据会保留在 Amazon S3 存储桶中。

重要提示:在开始之前,请检查 Amazon Redshift 是否有权访问您的 S3 存储桶以及任何外部数据目录。此外,您的 Amazon Redshift 集群和 S3 存储桶必须位于同一个 AWS 区域。

要在 Amazon Redshift Spectrum 中创建外部表,请执行以下步骤:

1.    为 Amazon Redshift 创建 IAM 角色

2.    附加您的 AWS Identity and Access Management (IAM) 策略:
如果使用的是 AWS Glue 数据目录,请将 AmazonS3ReadOnlyAccessAWSGlueConsoleFullAccess IAM 策略附加到您的角色。
如果使用的是 Amazon Athena 数据目录,请将 AmazonAthenaFullAccess IAM 策略附加到您的角色。

3.    将 IAM 角色与 Amazon Redshift 集群相关联

4.    创建外部 schema。外部 schema 引用外部数据目录中的数据库。外部 schema 还为 IAM 角色提供 Amazon 资源名称 (ARN),用于授权 Amazon Redshift 访问 S3。

在以下示例中,我们使用 S3 (tickitdb.zip) 中的示例数据文件。逐一解压缩文件并将文件加载到您的 AWS 区域中的 S3 存储桶,如下所示:

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

在此示例中,外部数据库在 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;

注意:请使用您创建的 ARN 替换 IAM 角色的 ARN。确保为数据库参数指定外部数据库的名称(如“spectrumdb”)。

5.   创建外部表。例如,您可以为 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/';

有关外部表的更多信息,请参阅为 Amazon Redshift Spectrum 创建外部表

要使用 AWS Glue 创建外部表,请确保将表定义添加到您的 AWS Glue 数据目录中。您可以通过多种方式在 AWS Glue 数据目录中添加表定义。有关添加表定义的更多信息,请参阅在 AWS Glue 数据目录中定义表

要使用 Amazon Athena 创建外部表,请按照以下说明添加表定义:

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.    对 SVV_EXTERNAL_TABLES 运行以下查询,以查看您的外部 schema 引用的所有外部表:

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

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

7.    使用 SELECT 语句查询外部表(作为外部 Amazon Redshift Spectrum 表):

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 表联接起来。</p