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

上次更新日期:2022 年 9 月 30 日

我正在尝试在 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 创建 AWS Identity and Access Management (IAM) 角色

2.    附加您的 IAM policy:
如果使用的是 AWS Glue Data Catalog,请将 AmazonS3ReadOnlyAccessAWSGlueConsoleFullAccess IAM policy 附加到您的角色。
如果使用的是 Amazon Athena Data Catalog,请将 AmazonAthenaFullAccess IAM policy 附加到您的角色。

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/

您可以使用类似于以下命令的命令创建外部架构:

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 delimitedfields terminated by '|'stored as textfile location 's3://<bucket_name>/tickit/spectrum/event/';

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

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

要使用 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 运行以下查询,以查看您的外部外部架构引用的所有外部表:

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