Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

为 Amazon Redshift Spectrum 创建外部表

Amazon Redshift Spectrum 使用外部表查询存储在 Amazon S3 中的数据。您可使用用于其他 Amazon Redshift 表的同一 SELECT 语法查询外部表。外部表为只读。您无法对外部表进行写入。

您可在 Amazon Redshift、Amazon Athena 或 Apache Hive 元存储中创建外部表。有关在 Athena 中创建表的信息,请参阅 Amazon Athena 用户指南 中的入门。有关使用 Hive 元存储的信息,请参阅 Amazon EMR 文档中的 Apache Hive

重要

Redshift Spectrum 不支持嵌套数据类型 (如 STRUCT、ARRAY 和 MAP)。

如果您的外部表是在 Athena 或 Hive 元存储中定义的,您首先应创建引用外部数据库的外部架构。然后,您可通过使用架构名称作为表名称的前缀来在 SELECT 语句中引用外部表,无需在 Amazon Redshift 中创建表。有关更多信息,请参阅 为 Amazon Redshift Spectrum 创建外部架构

例如,假设您有一个在 Athena 外部目录中定义的名为 lineitem_athena 的外部表。在这种情况下,您可以定义一个名为 athena_schema 的外部架构,然后使用以下 SELECT 语句查询表。

Copy
select count(*) from athena_schema.lineitem_athena;

要在 Amazon Redshift 中定义外部表,请使用 CREATE EXTERNAL TABLE 命令。外部表语句定义了表列、您的数据文件的格式和您的数据在 Amazon S3 中的位置。Redshift Spectrum 扫描指定的文件夹和任意子文件夹中的文件。Redshift Spectrum 将忽略隐藏文件以及以句点、下划线或哈希标记 (.、_ 或 #) 开头或以波形符结尾的文件。

以下示例在名为 spectrum 的 Amazon Redshift 外部架构中创建一个名为 SALES 的表。数据位于制表符分隔的文本文件中。

Copy
create external table spectrum.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) row format delimited fields terminated by '\t' stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales/' table properties ('numRows'='172000');

要查看外部表,请查询 SVV_EXTERNAL_TABLES 系统视图。

对 Redshift Spectrum 外部表进行分区

当您对数据进行分区时,可通过按分区键进行筛选来限制 Redshift Spectrum 扫描的数据量。您可按任何键对数据进行分区。常见做法是根据时间对数据进行分区。例如,您可以选择按年、月、日和小时进行分区。如果您的数据来自多个源,您可以按数据源标识符和日期进行分区。

以下过程介绍如何对您的数据进行分区。

对您的数据进行分区

  1. 根据分区键将您的数据存储在 Amazon S3 文件夹中。

    为每个分区值创建一个文件夹并使用分区键和值为该文件夹命名。例如,如果您按日期分区,您可能具有名为 saledate=2017-04-31saledate=2017-04-30 等的文件夹。Redshift Spectrum 会扫描分区文件夹中的文件,但不会扫描子文件夹。Redshift Spectrum 将忽略隐藏文件以及以句点、下划线或哈希标记 (.、_ 或 #) 开头或以波形符结尾的文件。

  2. 创建外部表并在 PARTITIONED BY 子句中指定分区键。

    分区键不能是表列的名称。数据类型可以是 TIMESTAMPTZ 之外的任何标准 Amazon Redshift 数据类型。您可仅对分区列使用 DATE 类型,而不对其他外部表列使用。

  3. 添加分区。

    通过使用 ALTER TABLE … ADD PARTITION,添加每个分区,并指定分区列和键值以及分区文件夹在 Amazon S3 中的位置。您在每个 ALTER TABLE 语句中只能添加一个分区。以下示例为“2008-01-01”和“2008-02-01”添加分区。

    Copy
    alter table spectrum.sales_part add partition(saledate='2008-01-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/'; alter table spectrum.sales_part add partition(saledate='2008-02-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/';

数据分区示例

在本示例中,您将创建按月分区的外部表。

本示例的示例数据位于为所有经过身份验证的 AWS 用户提供读取访问权限的 Amazon S3 存储桶中。您的集群和外部数据文件必须位于同一区域。示例数据存储桶位于美国西部(俄勒冈)区域 (us-west-2) 中。要使用 Redshift Spectrum 访问数据,您的集群必须同样位于 us-west-2 中。要列出 Amazon S3 中的文件夹,请运行以下命令。

Copy
aws s3 ls s3://awssampledbuswest2/tickit/spectrum/sales_partition/
PRE saledate=2008-01/ PRE saledate=2008-02/ PRE saledate=2008-03/ PRE saledate=2008-04/ PRE saledate=2008-05/ PRE saledate=2008-06/ PRE saledate=2008-07/ PRE saledate=2008-08/ PRE saledate=2008-09/ PRE saledate=2008-10/ PRE saledate=2008-11/ PRE saledate=2008-12/

如果您还没有外部架构,请运行以下命令,并用 Amazon 资源名称 (ARN) 替换 AWS Identity and Access Management (IAM) 角色。

Copy
create external schema spectrum from data catalog database 'spectrumdb' iam_role 'arn:aws:iam::123456789012:role/myspectrumrole' create external database if not exists;

要创建按日期分区的外部表,请运行以下命令。

Copy
create external table spectrum.sales_part( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) partitioned by (saledate date) row format delimited fields terminated by '|' stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/' table properties ('numRows'='172000');

要添加分区,请运行以下 ALTER TABLE 命令。

Copy
alter table spectrum.sales_part add partition(saledate='2008-01-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/'; alter table spectrum.sales_part add partition(saledate='2008-02-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/'; alter table spectrum.sales_part add partition(saledate='2008-03-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03/'; alter table spectrum.sales_part add partition(saledate='2008-04-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04/'; alter table spectrum.sales_part add partition(saledate='2008-05-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05/'; alter table spectrum.sales_part add partition(saledate='2008-06-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06/'; alter table spectrum.sales_part add partition(saledate='2008-07-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07/'; alter table spectrum.sales_part add partition(saledate='2008-08-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08/'; alter table spectrum.sales_part add partition(saledate='2008-09-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09/'; alter table spectrum.sales_part add partition(saledate='2008-10-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10/'; alter table spectrum.sales_part add partition(saledate='2008-11-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11/'; alter table spectrum.sales_part add partition(saledate='2008-12-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12/';

运行以下查询可选择已分区表中的数据。

Copy
select top 10 spectrum.sales_part.eventid, sum(spectrum.sales_part.pricepaid) from spectrum.sales_part, event where spectrum.sales_part.eventid = event.eventid and spectrum.sales_part.pricepaid > 30 and saledate = '2008-12-01' group by spectrum.sales_part.eventid order by 2 desc;
eventid | sum --------+--------- 914 | 36173.00 5478 | 27303.00 5061 | 26383.00 4406 | 26252.00 5324 | 24015.00 1829 | 23911.00 3601 | 23616.00 3665 | 23214.00 6069 | 22869.00 5638 | 22551.00

要查看外部表分区,请查询 SVV_EXTERNAL_PARTITIONS 系统视图。

Copy
select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
schemaname | tablename | values | location -----------+------------+----------------+-------------------------------------------------- spectrum | sales_part | ["2008-01-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01 spectrum | sales_part | ["2008-02-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02 spectrum | sales_part | ["2008-03-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03 spectrum | sales_part | ["2008-04-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04 spectrum | sales_part | ["2008-05-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05 spectrum | sales_part | ["2008-06-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06 spectrum | sales_part | ["2008-07-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07 spectrum | sales_part | ["2008-08-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08 spectrum | sales_part | ["2008-09-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09 spectrum | sales_part | ["2008-10-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10 spectrum | sales_part | ["2008-11-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11 spectrum | sales_part | ["2008-12-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12