CREATE EXTERNAL TABLE - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

CREATE EXTERNAL TABLE

在指定 schema 中创建一个新外部表。所有外部表必须在外部 schema 中创建。外部 schema 和外部表不支持搜索路径。有关更多信息,请参阅CREATE EXTERNAL SCHEMA

要创建外部表,您必须是外部 schema 的所有者或是超级用户。要移交外部 schema 的所有权,请使用 ALTER SCHEMA 更改所有者。对外部表的访问由对外部 schema 的访问权限控制。您无法对外部表的权限执行 GRANTREVOKE 操作。但是,您可授予或撤销对外部 schema 的 USAGE 权限。

除了使用 CREATE EXTERNAL TABLE 命令创建的外部表之外,Amazon Redshift 还可引用在 Amazon Glue 或 Amazon Lake Formation 目录或 Apache Hive 元存储中定义的外部表。使用 CREATE EXTERNAL SCHEMA 命令可注册在外部目录中定义的外部数据库并使外部表可在 Amazon Redshift 中使用。如果外部表已存在于 Amazon Glue 或 Amazon Lake Formation 目录或 Hive 元存储中,您无需使用 CREATE EXTERNAL TABLE 创建该表。要查看外部表,请查询 SVV_EXTERNAL_TABLES 系统视图。

通过运行 CREATE EXTERNAL TABLE AS 命令,可以根据查询中的列定义创建外部表,并将该查询的结果写入 Amazon S3 中。结果采用 Apache Parquet 或分隔的文本格式。如果外部表具有一个或多个分区键,Amazon Redshift 会根据这些分区键对新文件进行分区,并自动将新分区注册到外部目录中。有关 CREATE EXTERNAL TABLE AS 的更多信息,请参阅使用说明

您可使用用于其他 Amazon Redshift 表的同一 SELECT 语法查询外部表。您还可以使用 INSERT 语法将新文件写入 Amazon S3 上外部表的位置。有关更多信息,请参阅插入(外部表)

要使用外部表创建视图,请在 CREATE VIEW 语句中包含 WITH NO SCHEMA BINDING 子句。

您无法在事务 (BEGIN … END) 内运行 CREATE EXTERNAL TABLE。有关事务的更多信息,请参阅 可序列化的隔离

Syntax

CREATE EXTERNAL TABLE
external_schema.table_name  
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )] 
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name' 
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]

以下是 CREATE EXTERNAL TABLE AS 的语法。

CREATE EXTERNAL TABLE
external_schema.table_name  
[ PARTITIONED BY (col_name [, … ] ) ] 
[ ROW FORMAT DELIMITED row_format ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]
 AS
 { select_statement }
         

Parameters

external_schema.table_name

要创建的表的名称 (由外部 schema 名称进行限定)。外部表必须在外部 schema 中创建。有关更多信息,请参阅CREATE EXTERNAL SCHEMA

表名称的最大长度为 127 个字节;更长的名称将被截断为 127 个字节。您可以使用 UTF-8 多字节字符,每个字符最多为四个字节。Amazon Redshift 对每个集群强制实施 9900 个表的限制,包括用户定义的临时表以及查询处理或系统维护期间由 Amazon Redshift 创建的临时表。您也可以使用数据库名称限定表名称。在下面的示例中,spectrum_db 是数据库名称,spectrum_schema 是外部 schema 名称,而 test 是表名称。

create external table spectrum_db.spectrum_schema.test (c1 int) stored as parquet location 's3://mybucket/myfolder/';

如果指定数据库或 schema 不存在,则不会创建表,并且语句将返回错误。您无法在系统数据库 template0template1padb_harvest 中创建表或视图。

表名称对于指定 schema 必须是唯一名称。

有关有效名称的更多信息,请参阅名称和标识符

( column_name data_type )

要创建的每个列的名称和数据类型。

列名称的最大长度为 127 个字节;更长的名称将被截断为 127 个字节。您可以使用 UTF-8 多字节字符,每个字符最多为四个字节。不能指定列名称 "$path""$size"。有关有效名称的更多信息,请参阅名称和标识符

预设情况下,Amazon Redshift 使用伪列 $path$size 创建外部表。您可以通过将 spectrum_enable_pseudo_columns 配置参数设置为 false 来禁用为会话创建 pseudocolumns 的功能。有关更多信息,请参阅Pseudocolumns

如果已启用 pseudocolumns,则可在单个表中定义的最大列数为 1598。如果未启用 pseudocolumns,则可在单个表中定义的最大列数为 1600。

如果您创建的是“宽表”,请确保在加载和查询处理期间,不要让列列表超出中间结果的行宽度边界。有关更多信息,请参阅使用说明

对于 CREATE EXTERNAL TABLE AS 命令,不需要列的列表,因为列是从查询派生的。

data_type

支持以下数据类型

  • SMALLINT (INT2)

  • INTEGER (INT, INT4)

  • BIGINT (INT8)

  • DECIMAL (NUMERIC)

  • REAL (FLOAT4)

  • DOUBLE PRECISION (FLOAT8)

  • BOOLEAN (BOOL)

  • CHAR (CHARACTER)

  • VARCHAR (CHARACTER VARYING)

  • DATE (DATE 数据类型只能用于文本、Parquet 或 ORC 数据文件,或者用作分区列)

  • TIMESTAMP

文本文件中的时间戳值的格式必须为 yyyy-MM-dd HH:mm:ss.SSSSSS,如以下时间戳值所示:2017-05-01 11:30:59.000000

VARCHAR 列的长度的定义单位是字节而不是字符。例如,VARCHAR(12) 列可包含 12 个单字节字符或 6 个双字节字符。查询外部表时,将截断结果以适合定义的列大小,而不返回错误。有关更多信息,请参阅存储和范围

为获得最佳性能,我们建议您指定适合您数据的最小列大小。要查找列中值的最大大小(以字节为单位),请使用 OCTET_LENGTH 函数。以下示例返回电子邮件列中值的大小上限。

select max(octet_length(email)) from users; max --- 62
PARTITIONED BY (col_name data_type [, … ] )

用于定义包含一个或多个分区列的已分区表的子句。单独的数据目录用于每个指定的组合,这在某些情况下可提高查询性能。已分区列在表数据本身中不存在。如果您将与表列相同的某个值用于 col_name,则会产生错误。

创建分区表后,使用 ALTER TABLE … ADD PARTITION 语句更改表,以将新分区注册到外部目录。在添加分区时,您应定义包含分区数据的子文件夹在 Amazon S3 上的位置。

例如,如果表 spectrum.lineitem_part 是使用 PARTITIONED BY (l_shipdate date) 定义的,请运行以下 ALTER TABLE 命令来添加分区。

ALTER TABLE spectrum.lineitem_part ADD PARTITION (l_shipdate='1992-01-29') LOCATION 's3://spectrum-public/lineitem_partition/l_shipdate=1992-01-29';

如果您使用 CREATE EXTERNAL TABLE AS,则不需要运行 ALTER TABLE ... ADD PARTITION。Amazon Redshift 会自动在外部目录中注册新分区。Amazon Redshift 还会根据表中定义的一个或多个分区键自动将相应的数据写入 Amazon S3 中的分区。

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

注意

对于 CREATE EXTERNAL TABLE AS 命令,您不需要指定分区列的数据类型,因为此列是从查询派生的。

ROW FORMAT DELIMITED rowformat

用于指定基础数据的格式的子句。rowformat 的可能值如下所示:

  • LINES TERMINATED BY 'delimiter'

  • FIELDS TERMINATED BY 'delimiter'

为 'delimiter' 指定一个 ASCII 字符。您可以使用八进制形式以格式 '\ddd'(其中 d 是八进制数 (0–7),最大为“\177”)指定非打印 ASCII 字符。以下示例使用八进制形式指定 BEL (响铃) 字符。

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\007'

如果省略了 ROW FORMAT,则默认格式为 DELIMITED FIELDS TERMINATED BY '\A'(标题开头)和 LINES TERMINATED BY '\n'(换行符)。

ROW FORMAT SERDE 'serde_name'
[WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

用于为基础数据指定 SERDE 格式的子句。

'serde_name'

SerDe 的名称。支持以下:

  • org.apache.hadoop.hive.serde2.RegexSerDe

  • com.amazonaws.glue.serde.GrokSerDe

  • org.apache.hadoop.hive.serde2.OpenCSVSerde

  • org.openx.data.jsonserde.JsonSerDe

    • JSON SERDE 还支持 Ion 文件。

    • JSON 必须格式正确。

    • Ion 和 JSON 中的时间戳必须使用 ISO8601 格式。

    • JsonSerDe 支持以下 SerDe 属性:

      'strip.outer.array'='true'

      处理 Ion/JSON 文件,其中包含一个包含在方括号 ( [ … ] ) 中的非常大的数组,就像它在数组中包含多个 JSON 记录一样。

WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

(可选) 指定以逗号分隔的属性名称和值。

如果省略了 ROW FORMAT,则默认格式为 DELIMITED FIELDS TERMINATED BY '\A'(标题开头)和 LINES TERMINATED BY '\n'(换行符)。

STORED AS file_format

数据文件的文件格式。

有效格式如下所示:

  • PARQUET

  • RCFILE (仅针对使用 ColumnarSerDe 而不是 LazyBinaryColumnarSerDe 的数据)

  • SEQUENCEFILE

  • TEXTFILE

  • ORC

  • AVRO

  • INPUTFORMAT 'input_format_classname' OUTPUTFORMAT 'output_format_classname'

CREATE EXTERNAL TABLE AS 命令只支持两种文件格式:TEXTFILE 和 PARQUET。

对于 INPUTFORMAT 和 OUTPUTFORMAT,指定类名称,如下例所示。

'org.apache.hadoop.mapred.TextInputFormat'
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file'}

包含数据文件的 Amazon S3 存储桶或文件夹的路径或包含 Amazon S3 对象路径列表的清单文件。存储桶必须与 Amazon Redshift 集群位于同一 Amazon 区域。有关受支持的 Amazon 区域的列表,请参阅Amazon Redshift Spectrum 注意事项

如果路径指定存储桶或文件夹,例如 's3://mybucket/custdata/',Redshift Spectrum 会扫描指定的存储桶或文件夹和任意子文件夹中的文件。Redshift Spectrum 将忽略隐藏文件和以句点或下划线开头的文件。

如果路径指定清单文件,则 's3://bucket/manifest_file' 参数必须显式引用单个文件,例如 's3://mybucket/manifest.txt'。它不能引用键前缀。

清单是 JSON 格式的文本文件,其中列出了要从 Amazon S3 加载的每个文件的 URL 以及文件的大小 (以字节为单位)。URL 包含文件的存储桶名称和完整对象路径。在清单中指定的文件可以位于不同的存储桶中,但所有存储桶都必须位于 Amazon Redshift 集群所在的同一 Amazon 区域。如果某个文件被列出两次,那么该文件也会被加载两次。以下示例显示了加载三个文件的清单的 JSON。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

您可以强制包含特定文件。为此,请在清单中的文件级别包含一个 mandatory 选项。当您查询缺少强制性文件的外部表时,SELECT 语句将失败。确保外部表定义中包含的所有文件都存在。如果它们并非全部存在,则会显示一个错误,显示未找到的第一个强制性文件。以下示例显示 mandatory 选项设置为 true 的清单的 JSON。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "mandatory":true, "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "mandatory":false, "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

要引用使用 UNLOAD 创建的文件,您可以使用通过 UNLOAD 和 MANIFEST 参数创建的清单。该清单文件与 从 Amazon S3 执行 COPY 操作 的清单文件兼容,但使用不同的密钥。不使用的密钥会被忽略。

TABLE PROPERTIES ( 'property_name'='property_value' [, ...] )

用于设置表属性的表定义的子句。

注意

表属性区分大小写。

'compression_type'='value'

一个属性,它在文件名不包含扩展名时设置要使用的压缩类型。如果您设置此属性且存在文件扩展名,则将忽略该扩展名,并使用由此属性设置的值。压缩类型的有效值如下:

  • bzip2

  • gzip

  • snappy

'numRows'='row_count'

用于为表定义设置 numRows 值的属性。若要显式更新外部表的统计数据,请设置 numRows 属性来指示表的大小。Amazon Redshift 不分析外部表来生成表统计数据,查询优化程序会使用这些统计数据来生成查询计划。如果没有为外部表设置表统计数据,则 Amazon Redshift 假设外部表是较大的表,本地表是较小的表,以此来生成查询执行计划。

'skip.header.line.count'='line_count'

用于设置在每个源文件开头要跳过的行数的属性。

'serialization.null.format'=' '

一个属性,指定当存在与某个字段中提供的文本完全匹配的项时,Spectrum 应返回 NULL 值。

'orc.schema.resolution'='mapping_type'

一个属性,用于设置使用 ORC 数据格式的表的列映射类型。其他数据格式将忽略此属性。

列映射类型的有效值如下:

  • name

  • position

如果省略 orc.schema.resolution 属性,默认情况下会按名称映射列。如果将 orc.schema.resolution 设置为“name”“position”之外的任何其他值,则按位置映射列。有关列映射的更多信息,请参阅将外部表列映射到 ORC 列

注意

COPY 命令仅按位置映射到 ORC 数据文件。orc.schema.resolution 表属性对 COPY 命令行为无效。

'write.parallel'='on / off’

一个属性,用于设置是否 CREATE EXTERNAL TABLE AS 应并行写入数据。默认情况下,CREATE EXTERNAL TABLE AS 根据集群中的切片数量将数据并行写入到多个文件。默认选项为打开。当“write.parallel”设置为关闭时,CREATE EXTERNAL TABLE AS 以串行方式将一个或多个数据文件写入到 Amazon S3。该表属性还适用于指向同一外部表的所有后续 INSERT 语句。

‘write.maxfilesize.mb’=‘size’

设置由 CREATE EXTERNAL TABLE AS 写入到 Amazon S3 中的每个文件的最大大小(以 MB 为单位)的属性。大小必须是介于 5 到 6200 之间的有效整数。默认最大文件大小为 6,200 MB。该表属性还适用于指向同一外部表的所有后续 INSERT 语句。

select_statement

通过定义任何查询将一行或多行插入外部表的语句。查询生成的所有行都将根据表定义以文本或 Parquet 格式写入到 Amazon S3。

使用说明

您无法使用用于标准 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

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

如果您使用 Lake Formation 目录,则 IAM 角色必须具有在目录中创建表的权限。在这种情况下,它还必须对目标 Amazon S3 路径具有数据湖位置权限。此 IAM 角色成为新 Amazon 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 定价

Examples

以下示例在名为 spectrum 的 Amazon Redshift 外部 schema 中创建一个名为 SALES 的表。数据位于制表符分隔的文本文件中。TABLE PROPERTIES 子句将 numRows 属性设置为 170000 行。

create external table spectrum.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, saledate date, 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'='170000');

以下示例创建一个使用 JsonSerDe 以 JSON 格式引用数据的表。

create external table spectrum.cloudtrail_json ( event_version int, event_id bigint, event_time timestamp, event_type varchar(10), awsregion varchar(20), event_name varchar(max), event_source varchar(max), requesttime timestamp, useragent varchar(max), recipientaccountid bigint) row format serde 'org.openx.data.jsonserde.JsonSerDe' with serdeproperties ( 'dots.in.keys' = 'true', 'mapping.requesttime' = 'requesttimestamp' ) location 's3://mybucket/json/cloudtrail';

以下 CREATE EXTERNAL TABLE AS 示例创建一个未分区的外部表。然后,它将 SELECT 查询的结果以 Apache Parquet 格式写入到目标 Amazon S3 位置。

CREATE EXTERNAL TABLE spectrum.lineitem STORED AS parquet LOCATION 'S3://mybucket/cetas/lineitem/' AS SELECT * FROM local_lineitem;

以下示例创建分区的外部表,并在 SELECT 查询中包含分区列。

CREATE EXTERNAL TABLE spectrum.partitioned_lineitem PARTITIONED BY (l_shipdate, l_shipmode) STORED AS parquet LOCATION 'S3://mybucket/cetas/partitioned_lineitem/' AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM local_table;

如需外部数据目录中的现有数据库的列表,请查询 SVV_EXTERNAL_DATABASES 系统视图。

select eskind,databasename,esoptions from svv_external_databases order by databasename;
eskind | databasename | esoptions -------+--------------+---------------------------------------------------------------------------------- 1 | default | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} 1 | sampledb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} 1 | spectrumdb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}

要查看外部表的详细信息,请查询 SVV_EXTERNAL_TABLESSVV_EXTERNAL_COLUMNS 系统视图。

以下示例将查询 SVV_EXTERNAL_TABLES 视图。

select schemaname, tablename, location from svv_external_tables;
schemaname | tablename | location -----------+----------------------+-------------------------------------------------------- spectrum | sales | s3://awssampledbuswest2/tickit/spectrum/sales spectrum | sales_part | s3://awssampledbuswest2/tickit/spectrum/sales_partition

以下示例将查询 SVV_EXTERNAL_COLUMNS 视图。

select * from svv_external_columns where schemaname like 'spectrum%' and tablename ='sales';
schemaname | tablename | columnname | external_type | columnnum | part_key -----------+-----------+------------+---------------+-----------+--------- spectrum | sales | salesid | int | 1 | 0 spectrum | sales | listid | int | 2 | 0 spectrum | sales | sellerid | int | 3 | 0 spectrum | sales | buyerid | int | 4 | 0 spectrum | sales | eventid | int | 5 | 0 spectrum | sales | saledate | date | 6 | 0 spectrum | sales | qtysold | smallint | 7 | 0 spectrum | sales | pricepaid | decimal(8,2) | 8 | 0 spectrum | sales | commission | decimal(8,2) | 9 | 0 spectrum | sales | saletime | timestamp | 10 | 0

要查看表分区,请使用以下查询。

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

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

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

分区示例

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

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'='170000');

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

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

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

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 系统视图。

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

行格式示例

下面显示为以 AVRO 格式存储的数据文件指定 ROW FORMAT SERDE 参数的示例。

create external table spectrum.sales(salesid int, listid int, sellerid int, buyerid int, eventid int, dateid int, qtysold int, pricepaid decimal(8,2), comment VARCHAR(255)) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES ('avro.schema.literal'='{\"namespace\": \"dory.sample\",\"name\": \"dory_avro\",\"type\": \"record\", \"fields\": [{\"name\":\"salesid\", \"type\":\"int\"}, {\"name\":\"listid\", \"type\":\"int\"}, {\"name\":\"sellerid\", \"type\":\"int\"}, {\"name\":\"buyerid\", \"type\":\"int\"}, {\"name\":\"eventid\",\"type\":\"int\"}, {\"name\":\"dateid\",\"type\":\"int\"}, {\"name\":\"qtysold\",\"type\":\"int\"}, {\"name\":\"pricepaid\", \"type\": {\"type\": \"bytes\", \"logicalType\": \"decimal\", \"precision\": 8, \"scale\": 2}}, {\"name\":\"comment\",\"type\":\"string\"}]}') STORED AS AVRO location 's3://mybucket/avro/sales' ;

下面显示了使用 RegEx 指定 ROW FORMAT SERDE 参数的示例。

create external table spectrum.types( cbigint bigint, cbigint_null bigint, cint int, cint_null int) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties ('input.regex'='([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)') stored as textfile location 's3://mybucket/regex/types';

下面显示了使用 Grok 指定 ROW FORMAT SERDE 参数的示例。

create external table spectrum.grok_log( timestamp varchar(255), pid varchar(255), loglevel varchar(255), progname varchar(255), message varchar(255)) row format serde 'com.amazonaws.glue.serde.GrokSerDe' with serdeproperties ('input.format'='[DFEWI], \\[%{TIMESTAMP_ISO8601:timestamp} #%{POSINT:pid:int}\\] *(?<loglevel>:DEBUG|FATAL|ERROR|WARN|INFO) -- +%{DATA:progname}: %{GREEDYDATA:message}') stored as textfile location 's3://mybucket/grok/logs';

下面显示了一个有关在 S3 存储桶中定义 Amazon S3 服务器访问日志的示例。您可以使用 Redshift Spectrum 查询 Amazon S3 访问日志。

CREATE EXTERNAL TABLE spectrum.mybucket_s3_logs( bucketowner varchar(255), bucket varchar(255), requestdatetime varchar(2000), remoteip varchar(255), requester varchar(255), requested varchar(255), operation varchar(255), key varchar(255), requesturi_operation varchar(255), requesturi_key varchar(255), requesturi_httpprotoversion varchar(255), httpstatus varchar(255), errorcode varchar(255), bytessent bigint, objectsize bigint, totaltime varchar(255), turnaroundtime varchar(255), referrer varchar(255), useragent varchar(255), versionid varchar(255) ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^ ]*)\\s*([^ ]*)\\s*([^ ]*)\" (- |[^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*).*$') LOCATION 's3://mybucket/s3logs’;