如何在 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 数据目录，请将 AmazonS3ReadOnlyAccess 和 AWSGlueConsoleFullAccess 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

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

解决方法

相关信息

终止对 Internet Explorer 的支持