CREATE TABLE AS - Amazon Athena

CREATE TABLE AS

创建新表,使用 SELECT 查询的结果填充该表。要创建空表,使用 CREATE TABLECREATE TABLE ASCREATE TABLE DDL 语句与 SELECT DML 语句结合在一起,因此严格地说同时包含 DDL 和 DML。请注意,尽管 CREATE TABLE AS 在这里与其他 DDL 语句分组在一起,但出于服务限额目的,Athena 中的 CTAS 查询仍被视为 DML。有关 Athena 服务限额的信息,请参阅 服务限额

注意

对于 CTAS 语句,预期存储桶拥有者设置不适用于 Amazon S3 中的目标表位置。预期存储桶拥有者设置仅适用于您为 Athena 查询结果指定的 Amazon S3 输出位置。有关更多信息,请参阅 使用 Athena 控制台指定查询结果位置

有关超出此参考主题范围的 CREATE TABLE AS 其他信息,请参阅 从查询结果创建表(CTAS)

摘要

CREATE TABLE table_name [ WITH ( property_name = expression [, ...] ) ] AS query [ WITH [ NO ] DATA ]

其中:

WITH ( property_name = expression [, ...] )

可选 CTAS 表属性的列表,其中一些属性特定于数据存储格式。请参阅 CTAS 表属性

查询

用于创建新表的 SELECT 查询。

重要

如果您计划创建包含分区的查询,请指定 SELECT 语句的列列表中最后一个分区列的名称。

[ WITH [ NO ] DATA ]

如果使用 WITH NO DATA,则将创建与原始表具有相同架构的新空表。

注意

若要在查询结果输出中包含列标题,可以使用简单的 SELECT 查询而不是 CTAS 查询。您可以从查询结果位置检索结果,也可以使用 Athena 控制台直接下载结果。有关更多信息,请参阅 使用查询结果、最近查询和输出文件

CTAS 表属性

Athena 中的每个 CTAS 表具有可选的 CTAS 表属性列表,您可以使用 WITH (property_name = expression [, ...] ) 指定。有关使用这些参数的信息,请参阅 CTAS 查询的示例

WITH (property_name = expression [, ...], )
table_type = ['HIVE', 'ICEBERG']

可选。默认为 HIVE。指定结果表的表类型

例如:

WITH (table_type ='ICEBERG')
external_location = [location]
注意

由于 Iceberg 表不是外部表,因此该属性不适用于 Iceberg 表。要在 CTAS 语句中定义 Iceberg 表的根位置,请使用本节后面所述的 location 属性。

可选。Athena 在 Amazon S3 中保存 CTAS 查询的位置。

例如:

WITH (external_location ='s3://DOC-EXAMPLE-BUCKET/tables/parquet_table/')

Athena 不会对查询结果使用同一路径两次。如果您手动指定了位置,请确保您指定的 Amazon S3 位置没有任何数据。Athena 从不尝试删除数据。如果您要再次使用相同的位置,请手动删除数据,否则 CTAS 查询将失败。

如果您运行一个 CTAS 查询,且该查询在强制执行查询结果位置的工作组中指定 external_location,则查询会失败并出现错误消息。要查看为工作组指定的查询结果位置, 请参阅工作组的详细信息

如果您的工作组覆盖查询结果位置的客户端设置,则 Athena 会在以下位置创建表:

s3://workgroup-query-results-location/tables/query-id/

如果您未使用 external_location 属性指定位置,并且工作组未覆盖客户端设置,则 Athena 将使用查询结果位置的客户端设置在以下位置创建表:

s3://query-results-location-setting/Unsaved-or-query-name/year/month/date/tables/query-id/
is_external = [boolean]

可选。表示此表是否为外部表。默认值为 true。对于 Iceberg 表,必须将其设置为 false。

例如:

WITH (is_external = false)
location = [location]

对于 Iceberg 表是必需的。指定要根据查询结果创建的 Iceberg 表的根位置。

例如:

WITH (location ='s3://DOC-EXAMPLE-BUCKET/tables/iceberg_table/')
field_delimiter = [delimiter]

(可选)特定于基于文本的数据存储格式。CSV、TSV 和文本文件中文件的单字符字段分隔符。例如,WITH (field_delimiter = ',')。目前,CTAS 查询不支持多字符字段分隔符。如果您未指定字段分隔符,则默认使用 \001

format = [storage_format]

CTAS 查询结果的存储格式,例如 ORCPARQUETAVROJSONIONTEXTFILE。对于 Iceberg 表,允许使用 ORCPARQUETAVRO 格式。如果忽略,则默认使用 PARQUET。此参数的名称 format 必须以小写列出,否则 CTAS 查询将失败。

例如:

WITH (format = 'PARQUET')
bucketed_by = ARRAY[ column_name[,…], bucket_count = [int] ]
注意

该属性不适用于 Iceberg 表。对于 Iceberg 表,使用带存储桶转换的分区。

用于分桶存储数据的存储桶数组列表。如果省略,则 Athena 在此查询中不会分桶存储数据。

bucket_count = [int]
注意

该属性不适用于 Iceberg 表。对于 Iceberg 表,使用带存储桶转换的分区。

用于分桶存储数据的存储桶编号。如果省略,Athena 不分桶存储数据。例如:

CREATE TABLE bucketed_table WITH ( bucketed_by = ARRAY[column_name], bucket_count = 30, format = 'PARQUET', external_location ='s3://DOC-EXAMPLE-BUCKET/tables/parquet_table/' ) AS SELECT * FROM table_name
partitioned_by = ARRAY[ col_name[,…] ]
注意

该属性不适用于 Iceberg 表。要将分区转换用于 Iceberg 表,请使用本节后面所述的 partitioning 属性。

可选。CTAS 表进行分区所依据的列的数组列表。确认分区列的名称在 SELECT 语句的列列表中最后列出。

partitioning = ARRAY[partition_transform, ...]

可选。指定要创建的 Iceberg 表的分区。Iceberg 支持多种分区转换和分区演化。下表总结了分区转换。

转换 描述
year(ts) 为每年创建一个分区。分区值是 1970 年 1 月 1 日与 ts 之间以年为单位的整数差。
month(ts) 为每年的每个月创建一个分区。分区值是 1970 年 1 月 1 日与 ts 之间以月为单位的整数差。
day(ts) 为每年的每一天创建一个分区。分区值是 1970 年 1 月 1 日与 ts 之间以天为单位的整数差。
hour(ts) 为每年的每个小时创建一个分区。分区值是一个时间戳,分和秒设置为零。
bucket(x, nbuckets) 将数据哈希处理到指定数量的存储桶中。分区值是 x 的整数哈希值,介于 0 到 nbuckets - 1(含)之间。
truncate(s, nchars) 将分区值设为 s 的第一个 nchars 字符。

例如:

WITH (partitioning = ARRAY['month(order_date)', 'bucket(account_number, 10)', 'country']))
optimize_rewrite_min_data_file_size_bytes = [long]

可选。数据优化的特定配置。包含小于指定值的文件以进行优化。默认值为 write_target_data_file_size_bytes 的 0.75 倍。该属性仅适用于 Iceberg 表。有关更多信息,请参阅 优化 Iceberg 表

例如:

WITH (optimize_rewrite_min_data_file_size_bytes = 402653184)
optimize_rewrite_max_data_file_size_bytes = [long]

可选。数据优化的特定配置。包含大于指定值的文件以进行优化。默认值为 write_target_data_file_size_bytes 的 1.8 倍。该属性仅适用于 Iceberg 表。有关更多信息,请参阅 优化 Iceberg 表

例如:

WITH (optimize_rewrite_max_data_file_size_bytes = 966367641)
optimize_rewrite_data_file_threshold = [int]

可选。数据优化的特定配置。若需要优化的数据文件少于给定阈值,则不会重写这些文件。这将允许积累更多的数据文件以生成更接近目标大小的文件,并跳过不必要的计算以节省成本。默认值为 5。该属性仅适用于 Iceberg 表。有关更多信息,请参阅 优化 Iceberg 表

例如:

WITH (optimize_rewrite_data_file_threshold = 5)
optimize_rewrite_delete_file_threshold = [int]

可选。数据优化的特定配置。如果与数据文件关联的删除文件少于阈值,则不会重写该数据文件。这将允许为每个数据文件累积更多的删除文件,以节省成本。默认值为 2。该属性仅适用于 Iceberg 表。有关更多信息,请参阅 优化 Iceberg 表

例如:

WITH (optimize_rewrite_delete_file_threshold = 2)
vacuum_min_snapshots_to_keep = [int]

可选。对特定配置执行 vacuum 操作。要保留的最新快照的最小数量。默认 为 1。该属性仅适用于 Iceberg 表。有关更多信息,请参阅 VACUUM

注意

vacuum_min_snapshots_to_keep 属性需要 Athena 引擎版本 3。

例如:

WITH (vacuum_min_snapshots_to_keep = 1)
vacuum_max_snapshot_age_seconds = [long]

可选。对特定配置执行 vacuum 操作。以秒为单位的时间段,表示要保留的快照期限。默认值为 432000(5 天)。该属性仅适用于 Iceberg 表。有关更多信息,请参阅 VACUUM

注意

vacuum_max_snapshot_age_seconds 属性需要 Athena 引擎版本 3。

例如:

WITH (vacuum_max_snapshot_age_seconds = 432000)
write_compression = [compression_format]

用于允许指定压缩的任何存储格式的压缩类型。compression_format 值指定将数据写入表时要使用的压缩。您可以为 TEXTFILEJSONPARQUETORC 文件格式指定压缩。

例如,如果 format 属性指定 PARQUET 作为存储格式,则 write_compression 的值指定 Parquet 的压缩格式。在这种情况下,为 write_compression 指定一个值等同于为 parquet_compression 指定一个值。

同样,如果 format 属性指定 ORC 作为存储格式,则 write_compression 的值指定 ORC 的压缩格式。在这种情况下,为 write_compression 指定一个值等同于为 orc_compression 指定一个值。

不能在同一 CTAS 查询中指定多个压缩格式表属性。例如,您无法在同一个查询中同时指定 write_compressionparquet_compression。这同样适用于 write_compressionorc_compression。有关每种文件格式支持的压缩类型的信息,请参阅 Athena 压缩支持

orc_compression = [compression_format]

ORC 数据写入表时用于 ORC 文件格式的压缩类型。例如,WITH (orc_compression = 'ZLIB')ORC 文件内的数据块(ORC Postscript 除外)使用您指定的压缩进行压缩。如果省略,则默认情况下对 ORC 使用 ZLIB 压缩。

注意

为保持一致性,我们建议您使用 write_compression 属性而不是 orc_compression。使用 format 属性将存储格式指定为 ORC,然后使用 write_compression 属性指定 ORC 将使用的压缩格式。

parquet_compression = [compression_format]

将 Parquet 数据写入表时用于 Parquet 文件格式的压缩类型。例如,WITH (parquet_compression = 'SNAPPY')。此压缩应用于 Parquet 文件中的列块。如果省略,则默认情况下对 Parquet 使用 GZIP 压缩。

注意

为保持一致性,我们建议您使用 write_compression 属性而不是 parquet_compression。使用 format 属性将存储格式指定为 PARQUET,然后使用 write_compression 属性指定 PARQUET 将使用的压缩格式。

compression_level = [compression_level]

要使用的压缩级别。此属性仅适用于 ZSTD 压缩。可能的值介于 1 和 22 之间。默认值为 3。有关更多信息,请参阅 在 Athena 中使用 ZSTD 压缩级别

示例

有关 CTAS 查询的示例,请参阅以下资源。