Amazon Redshift
数据库开发人员指南
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

CREATE EXTERNAL TABLE

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

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

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

您可使用用于其他 Amazon Redshift 表的同一 SELECT 语法查询外部表。外部表为只读。您无法对外部表进行写入。

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

您无法在事务 (BEGIN … END) 内执行 CREATE EXTERNAL TABLE。

语法

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' [, ...] ) ]

参数

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 textfile location 's3://mybucket/myfolder/';

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

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

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

( column_name data_type )

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

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

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

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

注意

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

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 上的位置。您在每个 ALTER TABLE 语句中只能添加一个分区。

例如,如果表 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';

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

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'

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

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

包含数据文件的 Amazon S3 存储桶或文件夹的路径或包含 Amazon S3 对象路径列表的清单文件。存储桶必须与 Amazon Redshift 集群位于同一 AWS 区域。有关受支持的 AWS 区域的列表,请参阅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 集群所在的同一 AWS 区域。如果某个文件被列出两次,那么该文件也会被加载两次。以下示例显示了加载三个文件的清单的 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 } } ] }

要引用使用 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 命令行为无效。

使用说明

您无法查看所用的资源与您用于标准 Amazon Redshift 表(如 PG_TABLE_DEFSTV_TBL_PERM、PG_CLASS 或 information_schema)的资源相同的 Amazon Redshift Spectrum 表的详细信息。如果您的商业智能或分析工具无法识别 Redshift Spectrum 外部表,请将您的应用程序为配置查询 SVV_EXTERNAL_TABLESSVV_EXTERNAL_COLUMNS

创建和查询外部表的权限

要创建外部表,您必须是外部 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;

Pseudocolumns

默认情况下,Amazon Redshift 使用 pseudocolumns $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 定价

示例

以下示例在名为 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';

如需外部数据目录中的现有数据库的列表,请查询 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 IF NOT EXISTS 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’;