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

2 分钟阅读

我想在 Amazon Redshift Spectrum 中创建和查询外部表。

解决方法

要查询 Amazon Simple Storage Service (Amazon S3) 中的数据，无需将数据加载到 Amazon Redshift 表中。当 Redshift Spectrum 处理查询时，数据会保留在 S3 存储桶中。

**重要事项：**确保您的 Amazon Redshift 集群和 S3 存储桶位于同一 AWS 区域中。

要在 Redshift Spectrum 中创建外部表，请完成以下步骤：

为 Amazon Redshift 创建一个 AWS Identity and Access Management (IAM) 角色。
附加以下 IAM 策略，以向 Amazon Redshift 授予访问您的数据目录所需的权限：
如果您使用 AWS Glue Data Catalog，请将 AmazonS3ReadOnlyAccess 和 AWSGlueConsoleFullAccess IAM 策略附加到您的角色中。
如果您使用 Amazon Athena Data Catalog，请将 AmazonAthenaFullAccess IAM 策略附加到您的角色中。
将 IAM 角色与 Amazon Redshift 集群相关联。
创建外部架构。

下载数据文件并将其上传到您所在区域中的 S3 存储桶。以下示例使用了适用于 S3 的 TICKIT 示例数据文件：

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

**注意：**请将 bucket name 替换为您的 S3 存储桶的名称。

创建外部表。以下示例为 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/';

**注意：**请将 bucket name 替换为您的 S3 存储桶的名称。要使用 AWS Glue 创建外部表，请务必将表定义添加到 Data Catalog 中。
要使用 Amazon Athena 创建外部表，请添加表定义。
使用 Athena 的表定义示例：

CREATE EXTERNAL TABLE spectrum.event (
      eventid int,
      venueid smallint,
      catid smallint,
      dateid smallint,
      eventname varchar(max),
      starttime timestamp)
    ROW FORMAT DELIMITED
      FIELDS TERMINATED BY '|'
    STORED AS INPUTFORMAT
      'org.apache.hadoop.mapred.TextInputFormat'
    OUTPUTFORMAT

要查看外部架构引用的所有外部表，请对 SVV_EXTERNAL_TABLES 运行以下查询：

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

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

**注意：**请将 bucket name 替换为您的 S3 存储桶的名称，将 file location 替换为您的文件位置。

将这些外部表作为外部 Redshift Spectrum 表进行查询。使用 SELECT 语句：

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 中创建和查询外部表？

解决方法

相关信息

相关内容