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

2 分钟阅读

我正在尝试在 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，请将 AmazonS3ReadOnlyAccess 和 AWSGlueConsoleFullAccess 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 表联接起来。

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

解决方法

相关信息

相关内容