如何在 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