CREATE EXTERNAL TABLE - Amazon Redshift

CREATE EXTERNAL TABLE

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

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

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

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

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

要使用外部表创建视图,请在 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' [, ...] ) ]

以下是 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 }
         

参数

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

对于 DATE,您可以使用以下所示的格式。对于使用数字表示的月份值,支持以下格式:

  • mm-dd-yyyy - 例如:05-01-2017这是默认模式。

  • yyyy-mm-dd,其中年份由 2 位以上的数字表示。例如:2017-05-01

对于使用三个字母缩写表示的月份值,则支持以下格式:

  • mmm-dd-yyyy - 例如:may-01-2017这是默认模式。

  • dd-mmm-yyyy,其中年份由 2 位以上的数字表示。例如:01-may-2017

  • yyyy-mmm-dd,其中年份由 2 位以上的数字表示。例如:2017-may-01

对于始终小于 100 的年份值,请按以下方式计算年份:

  • 如果年份值小于 70,则在计算年份时加上 2000。例如,以 mm-dd-yyyy 格式表示的日期 05-01-17 将被转换为 05-01-2017

  • 如果年份值小于 100 但大于 69,则在计算年份时加上 1900。例如,以 mm-dd-yyyy 格式表示的日期 05-01-89 将被转换为 05-01-1989

  • 对于以两位数表示的年份值,请添加前导零,以 4 位数表示年份。

文本文件中的时间戳值的格式必须为 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 语句更改表,以将新分区注册到外部目录。在添加分区时,您应定义包含分区数据的子文件夹在 Simple Storage Service(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 还会根据表中定义的一个或多个分区键自动将相应的数据写入 Simple Storage Service(Amazon S3)中的分区。

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

注意

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

ROW FORMAT DELIMITED rowformat

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

  • LINES TERMINATED BY 'delimiter'

  • FIELDS TERMINATED BY 'delimiter'

为 'delimiter' 指定一个 ASCII 字符。您可以指定八进制格式的非打印 ASCII 字符,具体格式为 '\ddd',其中 d 是一个八进制数(0–7),最大为“\177”。以下示例使用八进制形式指定 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

    此参数支持 OpenCSVSerde 的以下 SerDe 属性:

    'wholeFile' = 'true'

    wholeFile 属性设置为 true,以正确解析 OpenCSV 请求的引号字符串中的新行字符(\n)。

  • org.openx.data.jsonserde.JsonSerDe

    • JSON SERDE 还支持 Ion 文件。

    • JSON 必须格式正确。

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

    • 该参数支持 JsonSerDe 的以下 SerDe 属性:

      'strip.outer.array'='true'

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

  • com.amazon.ionhiveserde.IonHiveSerDe

    除数据类型外,Amazon ION 格式还提供文本和二进制格式。对于引用 ION 格式数据的外部表,将外部表中的每个列映射到 ION 格式数据中的对应元素。有关更多信息,请参阅 Amazon Ion。此外,您还需要指定输入和输出格式。

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'}

包含数据文件的 Simple Storage Service(Amazon S3)存储桶或文件夹的路径或包含 Simple Storage Service(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 格式的文本文件,其中列出了要从 Simple Storage Service(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 } } ] }

您可以强制包含特定文件。为此,请在清单中的文件级别包含一个 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 参数创建的清单。该清单文件与 从 Simple Storage Service(Amazon S3)执行 COPY 操作 的清单文件兼容,但使用不同的密钥。不使用的密钥会被忽略。

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

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

注意

表属性区分大小写。

'compression_type'='value'

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

  • bzip2

  • gzip

  • snappy

'data_cleansing_enabled'='true / false’

该属性设置该表的数据处理是否已启用。当“data_cleansing_enabled”设置为 true 时,表的数据处理将启用。当“data_cleansing_enabled”设置为 false 时,表的数据处理将关闭。以下是由此属性控制的表级别的数据处理属性列表:

  • invalid_char_handling

  • replacement_char

  • surplus_char_handling

  • numeric_overflow_handling

有关示例,请参阅 数据处理示例

'invalid_char_handling'='value'

指定当查询结果包含无效的 UTF-8 字符值时要执行的操作。您可以指定以下操作:

已禁用

不执行无效字符处理。

FAIL

取消返回包含无效 UTF-8 值的数据的查询。

SET_TO_NULL

将无效 UTF-8 值替换为 null。

DROP_ROW

将行中的每个值替换为 null。

REPLACE

使用 replacement_char,将无效字符替换为您指定的替换字符。

'replacement_char'='character

当您将 invalid_char_handling 设置为 REPLACE 时,请指定要使用的替换字符。

'numeric_overflow_handling'='value’

指定 ORC 数据包含大于列定义(例如,SMALLINT 或 int16)的整数(例如,BIGINT 或 int64)时要执行的操作。您可以指定以下操作:

已禁用

关闭无效字符处理。

FAIL

当数据包含无效字符时取消查询。

SET_TO_NULL

将无效字符设置为 null。

DROP_ROW

将行中的每个值设置为 null。

'surplus_char_handling'='value'

指定如何处理加载的数据,其长度超过包含 VARCHAR、CHAR 或字符串数据列所定义的数据类型长度。默认情况下,对于超出列宽度的数据,Redshift Spectrum 会将该值设置为 null。

当查询返回超过列宽的数据时,您可以指定以下要执行的操作:

SET_TO_NULL

将超过列宽度的数据替换为 null。

已禁用

不执行超额字符处理。

FAIL

取消返回超过列宽度的数据的查询。

DROP_ROW

将行中的每个值替换为 null。

TRUNCATE

移除超出列定义的最大字符数的字符。

'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 数据格式的表的列映射类型。其他数据格式将忽略此属性。

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

  • 名称

  • 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 以串行方式将一个或多个数据文件写入到Simple Storage Service(Amazon S3)。该表属性还适用于指向同一外部表的所有后续 INSERT 语句。

‘write.maxfilesize.mb’=‘size’

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

‘write.kms.key.id’=‘value

您可以指定一个 AWS Key Management Service 密钥,为 Simple Storage Service(Amazon S3)对象启用服务器端加密(SSE),其中 value 为以下值之一:

  • auto,使用存储在 Simple Storage Service(Amazon S3)存储桶中的默认 AWS KMS 密钥。

  • kms-key,用于指定加密数据。

select_statement

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