使用说明 - Amazon Redshift

使用说明

本主题包含 CREATE EXTERNAL TABLE 的用法说明。您无法使用用于标准 Amazon Redshift 表(如 PG_TABLE_DEFSTV_TBL_PERM、PG_CLASS 或 information_schema)的同一资源查看 Amazon Redshift Spectrum 表的详细信息。如果您的商业智能或分析工具无法识别 Redshift Spectrum 外部表,请将您的应用程序为配置查询 SVV_EXTERNAL_TABLESSVV_EXTERNAL_COLUMNS

CREATE EXTERNAL TABLE AS

在某些情况下,您可能会对 AWS Glue Data Catalog、AWS Lake Formation 外部目录或 Apache Hive 元存储运行 CREATE EXTERNAL TABLE AS 命令。在这种情况下,您使用 AWS Identity and Access Management (IAM) 角色创建外部架构。此 IAM 角色必须同时具有在 Amazon S3 上读取和写入的权限。

如果您使用 Lake Formation 目录,则 IAM 角色必须具有在目录中创建表的权限。在这种情况下,它还必须对目标 Amazon S3 路径具有数据湖位置权限。此 IAM 角色成为新 AWS Lake Formation 表的所有者。

为确保文件名是唯一的,Amazon Redshift 预设情况下对上载到 Amazon S3 的每个文件的名称使用以下格式。

<date>_<time>_<microseconds>_<query_id>_<slice-number>_part_<part-number>.<format>.

示例是 20200303_004509_810669_1007_0001_part_00.parquet

运行 CREATE EXTERNAL TABLE AS 命令时,请考虑以下事项:

  • Amazon S3 位置必须为空。

  • Amazon Redshift 仅在使用 STORED AS 子句时才支持 PARQUET 和 TEXTFILE 格式。

  • 您不需要定义列定义列表。新外部表的列名和列数据类型直接从 SELECT 查询获得。

  • 您无需在 PARTITIONED BY 子句中定义分区列的数据类型。如果指定分区键,则此列的名称必须存在于 SELECT 查询结果中。当有多个分区列时,它们在 SELECT 查询中的顺序并不重要。Amazon Redshift 使用在 PARTITIONED BY 子句中定义的顺序来创建外部表。

  • Amazon Redshift 根据分区键值自动将输出文件分区到分区文件夹中。预设情况下,Amazon Redshift 从输出文件中删除分区列。

  • 不支持 LINES TERMINATED BY 'delimiter' 子句。

  • 不支持 ROW FORMAT SERDE 'serde_name' 子句。

  • 不支持使用清单文件。因此,您无法在 Amazon S3 上的清单文件中定义 LOCATION 子句。

  • Amazon Redshift 自动在命令末尾更新“numRows”表属性。

  • 'compression_type' 表属性对于 PARQUET 文件格式仅接受 'none' 或 'snappy'。

  • Amazon Redshift 不允许在外部 SELECT 查询中使用 LIMIT 子句。相反,您可以使用嵌套的 LIMIT 子句。

  • 您可以使用 STL_UNLOAD_LOG 跟踪由每个 CREATE EXTERNAL TABLE AS 操作写入到 Amazon S3 的文件。

创建和查询外部表的权限

要创建外部表,请确保您是外部架构的所有者或超级用户。要移交外部 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;

Pseudocolumns

预设情况下,Amazon Redshift 使用伪列 $path$size 创建外部表。选择这些列可针对查询返回的每行查看 Amazon S3 上数据文件的路径以及数据文件的大小。$path$size 列名称必须用双引号分隔。SELECT * 子句不返回 pseudocolumns。如下例所示,必须在查询中显式包含 $path$size 列名称。

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

您可以通过将 spectrum_enable_pseudo_columns 配置参数设置为 false 来禁用为会话创建 pseudocolumns 的功能。

重要

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

设置数据处理选项

您可以设置表参数以指定外部表中查询的数据的输入处理,其中包括:

  • 包含 VARCHAR、CHAR 和字符串数据的列中的多余字符。有关更多信息,请参阅外部表属性 surplus_char_handling

  • 包含 VARCHAR、CHAR 和字符串数据的列中的无效字符。有关更多信息,请参阅外部表属性 invalid_char_handling

  • 为外部表属性 invalid_char_handling 指定 REPLACE 时要使用的替换字符。

  • 包含整数和十进制数据的列的转换溢出处理。有关更多信息,请参阅外部表属性 numeric_overflow_handling

  • Surplus_bytes_handling 为包含 varbyte 数据的列中的超额字节指定输入处理。有关更多信息,请参阅外部表属性 surplus_bytes_handling