为 Redshift Spectrum 创建外部表 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

为 Redshift Spectrum 创建外部表

在外部 schema 中创建一个外部表。要创建外部表,您必须是外部 schema 的所有者或是超级用户。要移交外部 schema 的所有权,请使用 ALTER SCHEMA 更改所有者。以下示例将 spectrum_schema schema 的所有者更改为 newowner

alter schema spectrum_schema owner to newowner;

要运行 Redshift Spectrum 查询,您需要以下权限:

  • schema 的使用权限

  • 在当前数据库中创建临时表的权限

以下示例将 schema spectrum_schema 的使用权限授予 spectrumusers 用户组。

grant usage on schema spectrum_schema to group spectrumusers;

以下示例将数据库 spectrumdb 的临时权限授予 spectrumusers 用户组。

grant temp on database spectrumdb to group spectrumusers;

您可在 Amazon Redshift、Amazon Glue、Amazon Athena 或 Apache Hive 元存储中创建外部表。有关更多信息,请参阅《Amazon Glue 开发人员指南》中的开始使用 Amazon Glue、《Amazon Athena 用户指南》中的入门或《Amazon EMR 开发人员指南》中的 Apache Hive

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

要允许 Amazon Redshift 在 Amazon Glue Data Catalog 中查看表,添加 glue:GetTable 到 Amazon Redshift IAM 角色。否则,您可能会遇到类似以下内容的错误。

RedshiftIamRoleSession is not authorized to perform: glue:GetTable on resource: *;

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

select count(*) from athena_schema.lineitem_athena;

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

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

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://redshift-downloads/tickit/spectrum/sales/' table properties ('numRows'='172000');

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

Pseudocolumns

原定设置情况下,Amazon Redshift 使用伪列 $path$size$spectrum_oid 创建外部表。选择 $path 列可针对查询返回的每行查看 Amazon S3 上数据文件的路径,而选择 $size 列可以查看每行的数据文件的大小。$spectrum_oid 列提供了使用 Redshift Spectrum 执行相关查询的功能。有关示例,请参阅示例:在 Redshift Spectrum 中执行相关的子查询。必须用双引号分隔 $path$size$spectrum_oid 列名称。SELECT * 子句不返回伪列。如下例所示,必须在查询中显式包含 $path$size$spectrum_oid 列名称。

select "$path", "$size", "$spectrum_oid" from spectrum.sales_part where saledate = '2008-12-01';

您可以通过将 spectrum_enable_pseudo_columns 配置参数设置为 false 来禁用为会话创建伪列的功能。有关更多信息,请参阅spectrum_enable_pseudo_columns。还可以通过将 enable_spectrum_oid 设置为 false 来仅禁用 $spectrum_oid 伪列。有关更多信息,请参阅enable_spectrum_oid。然而,禁用 $spectrum_oid 伪列也会禁用使用 Redshift Spectrum 进行相关查询的支持。

重要

选择 $size$path$spectrum_oid 将产生费用,因为 Redshift Spectrum 会扫描 Amazon S3 中的数据文件来确定结果集的大小。有关更多信息,请参阅 Amazon Redshift 定价

Pseudocolumns 示例

以下示例将为外部表返回相关数据文件的总大小。

select distinct "$path", "$size" from spectrum.sales_part; $path | $size --------------------------------------------------------------------------+------- s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01/ | 1616 s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-02/ | 1444 s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03/ | 1644

对 Redshift Spectrum 外部表进行分区

当您对数据进行分区时,可通过按分区键进行筛选来限制 Redshift Spectrum 扫描的数据量。您可按任何键对数据进行分区。

常见做法是根据时间对数据进行分区。例如,您可以选择按年、月、日和小时进行分区。如果您的数据来自多个源,您可以按数据源标识符和日期进行分区。

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

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

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

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

    分区键不能是表列的名称。数据类型可以为 SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、BOOLEAN、CHAR、VARCHAR、DATE 或 TIMESTAMP 数据类型。

  3. 添加分区。

    通过使用 ALTER TABLE … ADD PARTITION,添加每个分区,以指定分区列和键值以及分区文件夹在 Amazon S3 中的位置。您可以使用单个 ALTER TABLE … ADD 语句添加多个分区。以下示例为 '2008-01''2008-03' 添加分区。

    alter table spectrum.sales_part add partition(saledate='2008-01-01') location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01/' partition(saledate='2008-03-01') location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03/';
    注意

    如果使用 Amazon Glue 目录,则可以使用单个 ALTER TABLE 语句最多添加 100 个分区。

分区数据示例

在此示例中,您将创建一个由单个分区键进行分区的外部表和一个由两个分区键进行分区的外部表。

本示例的示例数据位于为所有经过身份验证的 Amazon 用户提供读取访问权限的 Amazon S3 桶中。您的集群和外部数据文件必须位于同一 Amazon Web Services 区域中。示例数据桶位于美国东部(弗吉尼亚州北部)区域 (us-east-1) 中。要使用 Redshift Spectrum 访问数据,您的集群必须同样位于 us-east-1 中。要列出 Amazon S3 中的文件夹,请运行以下命令。

aws s3 ls s3://redshift-downloads/tickit/spectrum/sales_partition/
PRE saledate=2008-01/
PRE saledate=2008-03/
PRE saledate=2008-04/
PRE saledate=2008-05/
PRE saledate=2008-06/
PRE saledate=2008-12/

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

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

示例 1:使用单个分区键进行分区

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

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

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 char(10)) row format delimited fields terminated by '|' stored as textfile location 's3://redshift-downloads/tickit/spectrum/sales_partition/' table properties ('numRows'='172000');

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

alter table spectrum.sales_part add partition(saledate='2008-01') location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01/' partition(saledate='2008-03') location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03/' partition(saledate='2008-04') location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04/';

要从分区表中选择数据,请运行以下查询。

select top 5 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-01' group by spectrum.sales_part.eventid order by 2 desc;
eventid | sum     
--------+---------
   4124 | 21179.00
   1924 | 20569.00
   2294 | 18830.00
   2260 | 17669.00
   6032 | 17265.00

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

select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
schemaname | tablename  | values      | location                                                                
-----------+------------+-------------+-------------------------------------------------------------------------
spectrum   | sales_part | ["2008-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01
spectrum   | sales_part | ["2008-03"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03
spectrum   | sales_part | ["2008-04"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04

示例 2:使用多个分区键进行分区

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

create external table spectrum.sales_event( 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 (salesmonth char(10), event integer) row format delimited fields terminated by '|' stored as textfile location 's3://redshift-downloads/tickit/spectrum/salesevent/' table properties ('numRows'='172000');

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

alter table spectrum.sales_event add partition(salesmonth='2008-01', event='101') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-01/event=101/' partition(salesmonth='2008-01', event='102') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-01/event=102/' partition(salesmonth='2008-01', event='103') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-01/event=103/' partition(salesmonth='2008-02', event='101') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-02/event=101/' partition(salesmonth='2008-02', event='102') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-02/event=102/' partition(salesmonth='2008-02', event='103') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-02/event=103/' partition(salesmonth='2008-03', event='101') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-03/event=101/' partition(salesmonth='2008-03', event='102') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-03/event=102/' partition(salesmonth='2008-03', event='103') location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-03/event=103/';

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

select spectrum.sales_event.salesmonth, event.eventname, sum(spectrum.sales_event.pricepaid) from spectrum.sales_event, event where spectrum.sales_event.eventid = event.eventid and salesmonth = '2008-02' and (event = '101' or event = '102' or event = '103') group by event.eventname, spectrum.sales_event.salesmonth order by 3 desc;
salesmonth | eventname       | sum    
-----------+-----------------+--------
2008-02    | The Magic Flute | 5062.00
2008-02    | La Sonnambula   | 3498.00
2008-02    | Die Walkure     |  534.00

将外部表列映射到 ORC 列

可以使用 Amazon Redshift Spectrum 外部表从 ORC 格式的文件查询数据。优化的行列式 (ORC) 格式是一种支持嵌套数据结构的列式存储文件格式。有关查询嵌套数据的更多信息,请参阅使用 Amazon Redshift Spectrum 查询嵌套数据

创建引用 ORC 文件中的数据的外部表时,将外部表中的每个列映射到 ORC 数据中的列。为此,请使用以下方法之一:

按列名映射是默认设置。

按位置映射

使用位置映射,外部表中定义的第一列映射到 ORC 数据文件中的第一列,第二列映射到第二列,依此类推。按位置映射时,要求外部表和 ORC 文件中的列顺序匹配。如果列的顺序不匹配,可以按名称映射列。

重要

在早期版本中,Redshift Spectrum 默认使用位置映射。如果需要继续对现有表使用位置映射,请将表属性 orc.schema.resolution 设置为 position,如以下示例所示。

alter table spectrum.orc_example set table properties('orc.schema.resolution'='position');

例如,表 SPECTRUM.ORC_EXAMPLE 定义如下。

create external table spectrum.orc_example( int_col int, float_col float, nested_col struct< "int_col" : int, "map_col" : map<int, array<float >> > ) stored as orc location 's3://example/orc/files/';

表结构可以按以下方式抽象化。

• 'int_col' : int
• 'float_col' : float
• 'nested_col' : struct
   o 'int_col' : int
   o 'map_col' : map
      - key : int
      - value : array
         - value : float

底层 ORC 文件具有以下文件结构。

• ORC file root(id = 0)
   o 'int_col' : int (id = 1)
   o 'float_col' : float (id = 2)
   o 'nested_col' : struct (id = 3)
      - 'int_col' : int (id = 4)
      - 'map_col' : map (id = 5)
         - key : int (id = 6)
         - value : array (id = 7)
            - value : float (id = 8)

在此示例中,您可以严格按位置将外部表中的每个列映射到 ORC 文件中的列。下面显示了映射。

外部表列名称 ORC 列 ID ORC 列名称
int_col 1 int_col
float_col 2 float_col
nested_col 3 nested_col
nested_col.int_col 4 int_col
nested_col.map_col 5 map_col
nested_col.map_col.key 6 NA
nested_col.map_col.value 7 NA
nested_col.map_col.value.item 8 NA

按列名映射

使用名称映射,可以将外部表中的列使用相同的名称映射到同一级别的 ORC 文件中的指定列。

例如,假设您要将上一个示例 SPECTRUM.ORC_EXAMPLE 中的表映射到使用以下文件结构的 ORC 文件。

• ORC file root(id = 0)
   o 'nested_col' : struct (id = 1)
      - 'map_col' : map (id = 2)
         - key : int (id = 3)
         - value : array (id = 4)
            - value : float (id = 5)
      - 'int_col' : int (id = 6)
   o 'int_col' : int (id = 7)
   o 'float_col' : float (id = 8)

使用位置映射,Redshift Spectrum 会尝试进行以下映射。

外部表列名称 ORC 列 ID ORC 列名称
int_col 1 struct
float_col 7 int_col
nested_col 8 float_col

使用前面的位置映射查询表时,SELECT 命令在类型验证时会失败,因为结构不同。

您可以使用列名映射将同一外部表映射到前面示例中显示的两个文件结构。表列 int_colfloat_colnested_col 按列名映射到 ORC 文件中具有相同名称的列。外部表中名为 nested_col 的列是 struct 列,其子列名为 map_colint_col。子列也按列名正确映射到 ORC 文件中的相应列。

为 Apache Hudi 中管理的数据创建外部表

要查询 Apache Hudi 写入时复制 (CoW) 格式的数据,您可以使用 Amazon Redshift Spectrum 外部表。Hudi 写入时复制表是存储在 Amazon S3 中的 Apache Parquet 文件的集合。您可以在 Apache Hudi 版本 0.5.2、0.6.0、0.7.0、0.8.0、0.9.0、0.10.0、0.10.1、0.11.0 和 0.11.1 中读取写入时复制 (CoW) 表,这些表通过 Insert、Delete 和 Upsert Write 操作创建和修改。例如,不支持引导表。有关更多信息,请参阅开源 Apache Hudi 文档中的写入时复制表

创建引用 Hudi CoW 格式的数据的外部表时,将外部表中的每个列映射到 Hudi 数据中的列。映射按列完成。

分区和未分区 Hudi 表的数据定义语言 (DDL) 语句与其他 Apache Parquet 文件格式的语句类似。对于 Hudi 表,您可以将 INPUTFORMAT 定义为 org.apache.hudi.hadoop.HoodieParquetInputFormatLOCATION 参数必须指向包含 .hoodie 文件夹的 Hudi 表基本文件夹,建立 Hudi 提交时间线时需要此文件夹。在某些情况下,Hudi 表上的 SELECT 操作可能会失败,并显示消息未找到有效的 Hudi 提交时间线。如果是这样,请检查 .hoodie 文件夹是否位于正确的位置,并且包含有效的 Hudi 提交时间线。

注意

Apache Hudi 格式仅在您使用 Amazon Glue Data Catalog 时受到支持。当您使用 Apache Hive 元数据仓作为外部目录时,它不受支持。

用于定义未分区表的 DDL 采用以下格式。

CREATE EXTERNAL TABLE tbl_name (columns) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hudi.hadoop.HoodieParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://s3-bucket/prefix'

用于定义分区表的 DDL 采用以下格式。

CREATE EXTERNAL TABLE tbl_name (columns) PARTITIONED BY(pcolumn1 pcolumn1-type[,...]) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hudi.hadoop.HoodieParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://s3-bucket/prefix'

若要将分区添加到分区的 Hudi 表,请运行 ALTER TABLE ADD PARTITION 命令,其中 LOCATION 参数指向具有属于该分区的文件的 Amazon S3 子文件夹。

用于添加分区的 DDL 采用以下格式。

ALTER TABLE tbl_name ADD IF NOT EXISTS PARTITION(pcolumn1=pvalue1[,...]) LOCATION 's3://s3-bucket/prefix/partition-path'

为 Delta Lake 中托管的数据创建外部表

要查询 Delta Lake 表中的数据,您可以使用 Amazon Redshift Spectrum 外部表。

要从 Redshift Spectrum 访问 Delta Lake 表,请在查询之前生成清单。Delta Lake 清单包含构成 Delta Lake 表的一致快照的文件列表。在分区表中,每个分区都有一个清单。Delta Lake 表是存储在 Amazon S3 中的 Apache Parquet 文件的集合。有关更多信息,请参阅开源 Delta Lake 文档中的 Delta Lake

创建引用 Delta Lake 表中的数据的外部表时,将外部表中的每个列映射到 Delta Lake 数据中的列。映射按列名称完成。

分区和未分区的 Delta Lake 表的 DDL 与其他 Apache Parquet 文件格式的 DDL 类似。对于 Delta Lake 表,您可以将 INPUTFORMAT 定义为 org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat,并将 OUTPUTFORMAT 定义为 org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormatLOCATION 参数必须指向表基本文件夹中的清单文件夹。如果 Delta Lake 表上的 SELECT 操作失败,对于可能的原因,请参阅Delta Lake 表的限制和故障排除

用于定义未分区表的 DDL 采用以下格式。

CREATE EXTERNAL TABLE tbl_name (columns) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://s3-bucket/prefix/_symlink_format_manifest'

用于定义分区表的 DDL 采用以下格式。

CREATE EXTERNAL TABLE tbl_name (columns) PARTITIONED BY(pcolumn1 pcolumn1-type[,...]) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://s3-bucket>/prefix/_symlink_format_manifest'

若要将分区添加到已分区的 Delta Lake 表,请运行 ALTER TABLE ADD PARTITION 命令,其中 LOCATION 参数指向包含分区清单的 Amazon S3 子文件夹。

用于添加分区的 DDL 采用以下格式。

ALTER TABLE tbl_name ADD IF NOT EXISTS PARTITION(pcolumn1=pvalue1[,...]) LOCATION 's3://s3-bucket/prefix/_symlink_format_manifest/partition-path'

或者运行直接指向 Delta Lake 清单文件的 DDL。

ALTER TABLE tbl_name ADD IF NOT EXISTS PARTITION(pcolumn1=pvalue1[,...]) LOCATION 's3://s3-bucket/prefix/_symlink_format_manifest/partition-path/manifest'

Delta Lake 表的限制和故障排除

从 Redshift Spectrum 查询 Delta Lake 表时,请考虑以下事项:

  • 如果清单指向不再存在的快照或分区,则查询将失败,直到生成新的有效清单。例如,这可能因基础表上的 VACUUM 操作所导致,

  • Delta Lake 清单仅提供分区级的一致性。

下表说明了查询 Delta Lake 表时出现某些错误的一些潜在原因。

错误消息 可能的原因

s3-bucket-1 中的 Delta Lake 清单不能包含桶 s3-bucket-2 中的条目。

清单条目指向与指定桶不同的 Amazon S3 桶中的文件。

Delta Lake 文件应位于同一文件夹中。

清单条目指向具有与指定的 Amazon S3 前缀不同的文件。

未找到 Delta Lake 清单 manifest-path 中所列的文件 filename

在 Amazon S3 中找不到清单中列出的文件。

获取 Delta Lake 清单时出错。

在 Amazon S3 中找不到清单。

S3 路径无效。

清单文件中的条目不是有效的 Amazon S3 路径,或者清单文件已损坏。