# 从查询结果创建表（CTAS）
<a name="ctas"></a>

`CREATE TABLE AS SELECT` (CTAS) 查询根据另一个查询的 `SELECT` 语句的结果在 Athena 中创建一个新表。Athena 将由 CTAS 语句创建的数据文件存储在 Amazon S3 中的指定位置。有关语法，请参阅 [CREATE TABLE AS](create-table-as.md)。

`CREATE TABLE AS` 将 `CREATE TABLE` DDL 语句与 `SELECT` DML 语句结合在一起，因此严格地说同时包含 DDL 和 DML。但是，请注意，出于服务限额目的，Athena 中的 CTAS 查询被视为 DML。有关 Athena 服务限额的信息，请参阅 [服务配额](service-limits.md)。

使用 CTAS 查询可以：
+ 在一个步骤中从查询结果创建表，无需反复查询原始数据集。这样可以更轻松地处理原始数据集。
+ 转换查询结果并将表迁移到其他表格式，例如 Apache Iceberg。这可以在 Athena 中提高性能并降低查询成本。有关信息，请参阅[创建 Iceberg 表](querying-iceberg-creating-tables.md)。
+ 将查询结果转换为其他存储格式，例如 Parquet 和 ORC。这可以在 Athena 中提高性能并降低查询成本。有关信息，请参阅[使用列式存储格式](columnar-storage.md)。
+ 创建仅包含所需数据的现有表的副本。

**Topics**
+ [CTAS 查询的注意事项和限制](ctas-considerations-limitations.md)
+ [创建 CTAS 查询](ctas-console.md)
+ [CTAS 示例](ctas-examples.md)
+ [将 CTAS 和 INSERT INTO 用于 ETL](ctas-insert-into-etl.md)
+ [绕过 100 分区限制](ctas-insert-into.md)

# CTAS 查询的注意事项和限制
<a name="ctas-considerations-limitations"></a>

以下章节说明了在 Athena 中使用 `CREATE TABLE AS SELECT`（CTAS）查询时需注意的考虑因素和限制。

## 学习 CTAS 查询语法
<a name="ctas-considerations-limitations-query-syntax"></a>

CTAS 查询语法不同于用于创建表的 `CREATE [EXTERNAL] TABLE` 语法。请参阅[CREATE TABLE AS](create-table-as.md)。

## 视图与 CTAS 查询之间的区别
<a name="ctas-considerations-limitations-queries-vs-views"></a>

CTAS 查询将新数据写入 Amazon S3 中的指定位置。而视图不会写入任何数据。

## 为 CTAS 查询结果指定位置
<a name="ctas-considerations-limitations-location-of-query-results"></a>

如果您的工作组为查询结果位置[覆盖客户端设置](workgroups-settings-override.md)，则 Athena 会在位置 `s3://amzn-s3-demo-bucket/tables/<query-id>/` 创建表。要查看为工作组指定的查询结果位置，请[查看工作组的详细信息](viewing-details-workgroups.md)。

如果您的工作组不覆盖查询结果位置，则可在 CTAS 查询中使用语法 `WITH (external_location ='s3://amzn-s3-demo-bucket/')` 来指定 CTAS 查询结果的存储位置。

**注意**  
`external_location` 属性必须指定一个空位置。CTAS 查询检查存储桶中的路径位置（前缀）是否为空，如果该位置中已有数据，永远不会覆盖这些数据。要再次使用相同的位置，请删除存储桶中键前缀位置的数据。

如果省略 `external_location` 语法并且未使用工作组设置，则 Athena 会使用查询结果位置的[客户端设置](query-results-specify-location-console.md)，并在位置 `s3://amzn-s3-demo-bucket/<Unsaved-or-query-name>/<year>/<month/<date>/tables/<query-id>/` 创建表。

## 查找孤立文件
<a name="ctas-considerations-limitations-locating-orphaned-files"></a>

如果 `CTAS` 或 `INSERT INTO` 语句失败，则失败或取消的查询可能会在目标数据位置留下孤立数据。由于 Athena 在某些情况下不会从目标存储桶中删除查询的数据，因此部分数据可能会包含在后续查询中。

若要查找孤立文件以执行检查或删除操作，您可以使用 Athena 提供的数据清单文件跟踪要写入的文件列表。在极少数情况下，Athena 查询突然失败时，清单文件可能不存在。您可以手动检查目标 S3 位置来查找孤立文件。有关更多信息，请参阅[识别查询输出文件](querying-finding-output-files.md#querying-identifying-output-files)和 [DataManifestLocation](https://docs.aws.amazon.com/athena/latest/APIReference/API_QueryExecutionStatistics.html#athena-Type-QueryExecutionStatistics-DataManifestLocation)。

我们强烈建议使用 Apache Iceberg 来实现表的原子事务。有关更多信息，请参阅 [查询 Apache Iceberg 表](querying-iceberg.md)。

## 请记住，ORDER BY 子句被忽略
<a name="ctas-considerations-limitations-order-by-ignored"></a>

在 CTAS 查询中，Athena 会忽略查询的 `SELECT` 部分中的 `ORDER BY` 子句。

根据 SQL 规范（ISO 9075 第 2 部分），只有直接包含 `ORDER BY` 子句的查询表达式才能保证由查询表达式指定的表的行顺序。在任何情况下，SQL 中的表本质上都是无序的，在子查询子句中实施 `ORDER BY` 会导致查询性能不佳，同时不会生成输出有序。因此，在 Athena CTAS 查询中，无法保证在写入数据时保留 `ORDER BY` 子句指定的顺序。

## 选择一种格式来存储您的查询结果
<a name="ctas-considerations-limitations-formats-for-query-results"></a>

您可以使用 `PARQUET`、`ORC`、`AVRO`、`JSON` 和 `TEXTFILE` 格式存储 CTAS 结果。CTAS `TEXTFILE` 格式不支持多字符分隔符。如果您未指定数据存储格式，默认情况下，CTAS 查询结果以 Parquet 格式存储。

CTAS 查询不需要指定 SerDe 来解释格式转换。请参阅[Example: Writing query results to a different format](ctas-examples.md#ctas-example-format)。

## 考虑压缩格式
<a name="ctas-considerations-limitations-compression-formats"></a>

`GZIP` 压缩用于 JSON 和 TEXTFILE 格式的 CTAS 查询结果。对于 Parquet，您可以使用 `GZIP` 或者 `SNAPPY`，默认为 `GZIP`。对于 ORC，您可以使用 `LZ4`、`SNAPPY`、`ZLIB`，或者 `ZSTD`，默认为 `ZLIB`。有关指定压缩的 CTAS 示例，请参阅 [Example: Specifying data storage and compression formats](ctas-examples.md#ctas-example-compression)。有关 Athena 中压缩的更多信息，请参阅 [在 Athena 中使用压缩](compression-formats.md)。

## 对结果进行分区和分桶
<a name="ctas-considerations-limitations-partition-and-bucket-limits"></a>

您可以对 CTAS 查询的结果数据进行分区和分桶。要指定目标表的属性，在 `WITH` 子句末尾包括分区和分桶谓词。有关更多信息，请参阅[使用分区和分桶](ctas-partitioning-and-bucketing.md)和[Example: Creating bucketed and partitioned tables](ctas-examples.md#ctas-example-bucketed)。

使用 CTAS 创建分区表时，Athena 的写入限制为 100 个分区。有关绕过 100 个分区的限制的信息，请参阅[使用 CTAS 和 INSERT INTO 绕过 100 分区限制](ctas-insert-into.md)。

## 加密您的结果
<a name="ctas-considerations-limitations-encryption"></a>

您可以加密 Amazon S3 中的 CTAS 查询结果，其方法类似于在 Athena 中加密其他查询结果。有关更多信息，请参阅 [加密在 Amazon S3 中存储的 Athena 查询结果](encrypting-query-results-stored-in-s3.md)。

## 预期存储桶所有者设置不适用于 CTAS
<a name="ctas-considerations-limitations-expected-bucket-owner"></a>

对于 CTAS 语句，预期存储桶拥有者设置不适用于 Amazon S3 中的目标表位置。预期存储桶拥有者设置仅适用于您为 Athena 查询结果指定的 Amazon S3 输出位置。有关更多信息，请参阅 [使用 Athena 控制台指定查询结果位置](query-results-specify-location-console.md)。

## 已保留列数据类型
<a name="ctas-considerations-limitations-data-types"></a>

CTAS 查询的列数据类型与为原始查询指定的类型相同。

# 在 Athena 控制台中创建 CTAS 查询
<a name="ctas-console"></a>

在 Athena 控制台中，您可以通过另一个查询创建 CTAS 查询。<a name="ctas-create-from-query"></a>

**通过另一个查询创建 CTAS 查询**

1. 在 Athena 控制台查询编辑器中运行查询。

1. 在查询编辑器底部，选择 **Create**（创建）选项，然后选择 **Table from query**（根据查询创建表）。

1. 在 **Create table as select**（根据选择创建表）表单中，填写字段，如下所示：

   1. 对于 **Table name**（表名称），指定新表的名称。仅使用小写和下划线，例如 `my_select_query_parquet`。

   1. 对于 **Database configuration**（数据库配置），使用选项选择一个现有的数据库或创建一个数据库。

   1. （可选）在 **Result configuration**（结果配置）中，对于 **Location of CTAS query results**（CTAS 查询结果的位置），如果您的工作组查询结果位置设置未覆盖此选项，请执行以下操作之一：
      + 在搜索框中输入一个现有 S3 位置的路径，或选择 **Browse S3**（浏览 S3）以从列表中选择一个位置。
      + 选择 **View**（查看）以打开 Amazon S3 控制台的 **Buckets** （存储桶）页面，您可以在其中查看有关现有存储桶的更多信息并进行选择，或者使用自己的设置创建一个存储桶。

      您需要在 Amazon S3 中指定一个将用于输出数据的空位置。如果指定位置中已存在数据，则查询会失败并出现错误。

      如果工作组的查询结果位置设置覆盖了此位置设置，则 Athena 会在位置 `s3://amzn-s3-demo-bucket/tables/query_id/` 中创建您的表。

   1. 对于 **Data format**（数据格式），指定您的数据将采用的格式。
      + **表类型** – Athena 中的默认表类型是 Apache Hive。
      + **文件格式** – 可选择 CSV、TSV、JSON、Parquet 或 ORC 等选项。有关 Parquet 和 ORC 格式的信息，请参阅 [使用列式存储格式](columnar-storage.md)。
      + **写入压缩** –（可选）选择一个压缩格式。Athena 支持多种用于读取和写入数据的压缩格式，例如从使用多种压缩格式的表中进行读取。例如，当某些 Parquet 文件使用 Snappy 压缩而其他 Parquet 文件使用 GZIP 压缩时，Athena 可以成功读取使用 Parquet 文件格式的表中的数据。同样的原则适用于 ORC、文本文件和 JSON 存储格式。有关更多信息，请参阅 [在 Athena 中使用压缩](compression-formats.md)。
      + **分区** –（可选）选择要分区的列。通过对数据进行分区，您可以限制每个查询扫描的数据量，从而提高性能并降低成本。您可按任何键对数据进行分区。有关更多信息，请参阅 [对您的数据进行分区](partitions.md)。
      + **桶** –（可选）选择要分桶的列。分桶是一种在单个分区内基于特定列将数据进行分组的技术。这些列被称为 *桶键*。通过将相关数据分组到单个桶（分区内的一个文件）中，您可以显著减少 Athena 扫描的数据量，从而提高查询性能并降低成本。有关更多信息，请参阅 [使用分区和分桶](ctas-partitioning-and-bucketing.md)。

   1. 对于 **Preview table query**（预览表查询），检查您的查询。有关查询语法，请参阅 [CREATE TABLE AS](create-table-as.md)。

   1. 选择**创建表**。

Athena 控制台有 SQL 模板，您也可以使用该模板来创建 CTAS 查询。<a name="ctas-create-new"></a>

**使用 SQL 模板创建 CTAS 查询**

使用 `CREATE TABLE AS SELECT` 模板在查询编辑器中创建 CTAS 查询。

1. 在 Athena 控制台中，选择 **Tables and views**（表和视图）旁边的 **Create table**（创建表），然后选择 **CREATE TABLE AS SELECT**。这会使用具有占位符值的 CTAS 查询填充查询编辑器。

1. 在查询编辑器中，根据需要编辑查询。有关查询语法，请参阅 [CREATE TABLE AS](create-table-as.md)。

1. 选择**运行**。

有关示例，请参阅 [CTAS 查询的示例](ctas-examples.md)。



# CTAS 查询的示例
<a name="ctas-examples"></a>

使用以下示例创建 CTAS 查询。有关 CTAS 语法的信息，请参阅[CREATE TABLE AS](create-table-as.md)。

本节内容：
+  [Example: Duplicating a table by selecting all columns](#ctas-example-dupe-table) 
+  [Example: Selecting specific columns from one or more tables](#ctas-example-specify-columns) 
+  [Example: Creating an empty copy of an existing table](#ctas-example-empty-table) 
+  [Example: Specifying data storage and compression formats](#ctas-example-compression) 
+  [Example: Writing query results to a different format](#ctas-example-format) 
+  [Example: Creating unpartitioned tables](#ctas-example-unpartitioned) 
+  [Example: Creating partitioned tables](#ctas-example-partitioned) 
+  [Example: Creating bucketed and partitioned tables](#ctas-example-bucketed) 
+  [Example: Creating an Iceberg table with Parquet data](#ctas-example-iceberg-parquet) 
+  [Example: Creating an Iceberg table with Avro data](#ctas-example-iceberg-avro) 
+  [Example: Creating an S3 table using CTAS](#ctas-example-s3-table) 

**Example 示例：通过选择所有列复制表**  
以下示例通过复制表的所有列来创建表：  

```
CREATE TABLE new_table AS 
SELECT * 
FROM old_table;
```
在同一个示例的下列变化中，您的 `SELECT` 语句还包括 `WHERE` 子句。在这种情况下，查询将只从表中选择满足 `WHERE` 子句的行：  

```
CREATE TABLE new_table AS 
SELECT * 
FROM old_table 
WHERE condition;
```

**Example 示例：从一个或多个表选择特定列**  
以下示例创建运行在其他表的一组列上的新查询：  

```
CREATE TABLE new_table AS 
SELECT column_1, column_2, ... column_n 
FROM old_table;
```
同一个示例的此变化从多个表的特定列创建新表：  

```
CREATE TABLE new_table AS
SELECT column_1, column_2, ... column_n 
FROM old_table_1, old_table_2, ... old_table_n;
```

**Example 示例：创建现有表的空副本**  
以下示例使用 `WITH NO DATA` 创建空的新表，该表与原始表具有相同架构：  

```
CREATE TABLE new_table 
AS SELECT * 
FROM old_table
WITH NO DATA;
```

**Example 示例：指定数据存储和压缩格式**  
借助 CTAS，您可以使用一种存储格式的源表创建不同存储格式的另一个表。  
使用 `format` 属性以将 `ORC`、`PARQUET`、`AVRO`、`JSON` 或 `TEXTFILE` 指定为新表的存储格式。  
对于 `PARQUET`、`ORC`、`TEXTFILE` 和 `JSON` 存储格式，使用 `write_compression` 属性指定新表数据的压缩格式。有关每种文件格式支持的压缩格式的信息，请参阅 [在 Athena 中使用压缩](compression-formats.md)。  
以下示例指定表 `new_table` 中的数据以 Parquet 格式存储并使用 Snappy 压缩。Parquet 的默认压缩格式为 `GZIP`。  

```
CREATE TABLE new_table
WITH (
      format = 'Parquet',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table;
```
以下示例指定表 `new_table` 中的数据以 ORC 格式存储并使用 Snappy 压缩。ORC 的默认压缩格式为 ZLIB。  

```
CREATE TABLE new_table
WITH (format = 'ORC',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table ;
```
以下示例指定表 `new_table` 中的数据以 Textfile 格式存储并使用 Snappy 压缩。Textfile 和 JSON 格式的默认压缩格式都是 GZIP。  

```
CREATE TABLE new_table
WITH (format = 'TEXTFILE',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table ;
```

**Example 示例：将查询结果写入不同格式**  
以下 CTAS 查询从 `old_table` 中选择能够以 CSV 或其他格式存储的所有记录，并创建一个新表，其中包含以 ORC 格式保存到 Amazon S3 的基础数据：  

```
CREATE TABLE my_orc_ctas_table
WITH (
      external_location = 's3://amzn-s3-demo-bucket/my_orc_stas_table/',
      format = 'ORC')
AS SELECT * 
FROM old_table;
```

**Example 示例：创建未分区表**  
以下示例创建未分区的表。表数据以不同格式存储。其中一些示例指定了外部位置。  
以下示例创建使用文本文件存储结果的 CTAS 查询：  

```
CREATE TABLE ctas_csv_unpartitioned 
WITH (
     format = 'TEXTFILE', 
     external_location = 's3://amzn-s3-demo-bucket/ctas_csv_unpartitioned/') 
AS SELECT key1, name1, address1, comment1
FROM table1;
```
在以下示例中，结果以 Parquet 格式存储，并使用默认结果位置：  

```
CREATE TABLE ctas_parquet_unpartitioned 
WITH (format = 'PARQUET') 
AS SELECT key1, name1, comment1
FROM table1;
```
在以下查询中，表以 JSON 格式存储，并从原始表的结果中选择特定列：  

```
CREATE TABLE ctas_json_unpartitioned 
WITH (
     format = 'JSON',  
     external_location = 's3://amzn-s3-demo-bucket/ctas_json_unpartitioned/') 
AS SELECT key1, name1, address1, comment1
FROM table1;
```
在下面的示例中，格式为 ORC：  

```
CREATE TABLE ctas_orc_unpartitioned 
WITH (
     format = 'ORC') 
AS SELECT key1, name1, comment1 
FROM table1;
```
在下面的示例中，格式为 Avro：  

```
CREATE TABLE ctas_avro_unpartitioned 
WITH (
     format = 'AVRO', 
     external_location = 's3://amzn-s3-demo-bucket/ctas_avro_unpartitioned/') 
AS SELECT key1, name1, comment1
FROM table1;
```

**Example 示例：创建分区表**  
以下示例显示了对不同存储格式的分区表的 `CREATE TABLE AS SELECT` 查询，在 `WITH` 子句中使用 `partitioned_by` 和其他属性。有关语法，请参阅 [CTAS 表属性](create-table-as.md#ctas-table-properties)。有关为分区选择列的更多信息，请参阅[使用分区和分桶](ctas-partitioning-and-bucketing.md)。  
在 `SELECT` 语句中列列表的结尾列出分区列。您可以按多个列进行分区，并拥有多达 100 个唯一的分区和存储桶组合。例如，如果未指定存储桶，则可以有 100 个分区。

```
CREATE TABLE ctas_csv_partitioned 
WITH (
     format = 'TEXTFILE',  
     external_location = 's3://amzn-s3-demo-bucket/ctas_csv_partitioned/', 
     partitioned_by = ARRAY['key1']) 
AS SELECT name1, address1, comment1, key1
FROM tables1;
```

```
CREATE TABLE ctas_json_partitioned 
WITH (
     format = 'JSON', 
     external_location = 's3://amzn-s3-demo-bucket/ctas_json_partitioned/', 
     partitioned_by = ARRAY['key1']) 
AS select name1, address1, comment1, key1 
FROM table1;
```

**Example 示例：创建分桶和分区表**  
以下示例显示同时使用分区和分桶在 Amazon S3 中存储查询结果的 `CREATE TABLE AS SELECT` 查询。表结果按照不同列分区和分桶。Athena 支持最多 100 个唯一的存储桶和分区组合。例如，如果您创建包含五个存储桶的表，则支持 20 个分区，每个分区包含五个存储桶。有关语法，请参阅 [CTAS 表属性](create-table-as.md#ctas-table-properties)。  
有关为分桶选择列的信息，请参阅[使用分区和分桶](ctas-partitioning-and-bucketing.md)。  

```
CREATE TABLE ctas_avro_bucketed 
WITH (
      format = 'AVRO', 
      external_location = 's3://amzn-s3-demo-bucket/ctas_avro_bucketed/', 
      partitioned_by = ARRAY['nationkey'], 
      bucketed_by = ARRAY['mktsegment'], 
      bucket_count = 3) 
AS SELECT key1, name1, address1, phone1, acctbal, mktsegment, comment1, nationkey 
FROM table1;
```

**Example 示例：使用 Parquet 数据创建 Iceberg 表**  
以下示例将创建一个包含有 Parquet 数据文件的 Iceberg 表。文件按月根据 `table1` 中的 `dt` 列进行分区。该示例更新了表的保留属性，因此默认情况下，表中每个分支上都会保留 10 个快照。过去 7 天内的快照也会保留。有关 Athena 中 Iceberg 表属性的更多信息，请参阅 [指定表属性](querying-iceberg-creating-tables.md#querying-iceberg-table-properties)。  

```
CREATE TABLE ctas_iceberg_parquet
WITH (table_type = 'ICEBERG',
      format = 'PARQUET', 
      location = 's3://amzn-s3-demo-bucket/ctas_iceberg_parquet/', 
      is_external = false,
      partitioning = ARRAY['month(dt)'],
      vacuum_min_snapshots_to_keep = 10,
      vacuum_max_snapshot_age_seconds = 604800
   ) 
AS SELECT key1, name1, dt FROM table1;
```

**Example 示例：使用 Avro 数据创建 Iceberg 表**  
以下示例将创建一个按 `key1` 分区的包含有 Avro 数据文件的 Iceberg 表。  

```
CREATE TABLE ctas_iceberg_avro
WITH ( format = 'AVRO', 
       location = 's3://amzn-s3-demo-bucket/ctas_iceberg_avro/', 
       is_external = false,
       table_type = 'ICEBERG',
       partitioning = ARRAY['key1']) 
AS SELECT key1, name1, date FROM table1;
```

**Example 示例：使用 CTAS 创建 S3 表**  
以下示例使用 CTAS 创建 S3 表。请注意，位置属性将被忽略，且 `table_type` 默认为 `ICEBERG`：  

```
CREATE TABLE "s3tablescatalog/amzn-s3-demo-bucket"."namespace"."s3-table-name"
WITH (
    format = 'PARQUET'
)
AS SELECT *
FROM source_table;
```
您可以使用与常规 Iceberg 表相同的语法来指定所有其他 Iceberg 表属性，例如分区和存储桶。

# 将 CTAS 和 INSERT INTO 用于 ETL 和数据分析
<a name="ctas-insert-into-etl"></a>

在 Athena 中使用 Create Table as Select ([CTAS](ctas.md)) 和 [INSERT INTO](insert-into.md) 语句可将数据提取、转换和加载 (ETL) 到 Amazon S3 中以进行数据处理。本主题说明了如何使用这些语句对数据集进行分区并将其转换为列式数据格式，以对其进行优化来进行数据分析。

CTAS 语句使用标准 [SELECT](select.md) 查询来创建新表。您可以使用 CTAS 语句创建数据子集以进行分析。在一个 CTAS 语句中，您可以对数据进行分区、指定压缩并将数据转换为列状格式，如 Apache Parquet 或 Apache ORC。在运行 CTAS 查询时，该查询创建的表和分区将自动添加到 [AWS Glue Data Catalog](https://aws.amazon.com/glue)。这使得它创建的新表和分区立即对后续查询可用。

INSERT INTO 语句基于在源表上运行的 SELECT 查询语句将新行插入到目标表中。您可以使用 INSERT INTO 语句通过 CTAS 支持的所有转换来转换 CSV 格式的源表数据并将其加载到目标表数据中。

## 概述
<a name="ctas-insert-into-etl-overview"></a>

在 Athena 中，使用 CTAS 语句执行数据的初始批量转换。然后，使用多个 INSERT INTO 语句对 CTAS 语句所创建的表进行增量更新。

**Steps**
+ [步骤 1：基于原始数据集创建表](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset)
+  [步骤 2：使用 CTAS 对数据进行分区、转换和压缩](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data) 
+  [步骤 3：使用 INSERT INTO 添加数据](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data) 
+  [步骤 4：衡量性能和成本差异](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences) 

## 步骤 1：基于原始数据集创建表
<a name="ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset"></a>

本主题中的示例使用 Simple Storage Service (Amazon S3) 可读取的公开发布的 [NOAA global historical climatology network daily (GHCN-d)](https://registry.opendata.aws/noaa-ghcn/) 数据集的子集。Amazon S3 上的数据具有以下特性。

```
Location: s3://aws-bigdata-blog/artifacts/athena-ctas-insert-into-blog/
Total objects: 41727
Size of CSV dataset: 11.3 GB
Region: us-east-1
```

原始数据存储在不带分区的 Amazon S3 中。文件中的数据采用 CSV 格式，如下所示。

```
2019-10-31 13:06:57  413.1 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0000
2019-10-31 13:06:57  412.0 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0001
2019-10-31 13:06:57   34.4 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0002
2019-10-31 13:06:57  412.2 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0100
2019-10-31 13:06:57  412.7 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0101
```

此示例中的文件大小相对较小。通过将这些文件合并为更大的文件，可以减少文件总数，从而优化查询执行的性能。可以使用 CTAS 和 INSERT INTO 语句来增强查询性能。

**创建基于示例数据集的数据库和表**

1. 在 Athena 控制台中，选择 **US East (N. Virginia)** [美国东部（弗吉尼亚北部）]AWS 区域。请务必在 `us-east-1` 中运行本教程中的所有查询。

1. 在 Athena 查询编辑器中，运行 [CREATE DATABASE](create-database.md) 命令以创建数据库。

   ```
   CREATE DATABASE blogdb
   ```

1. 运行以下语句以[创建表](create-table.md)。

   ```
   CREATE EXTERNAL TABLE `blogdb`.`original_csv` (
     `id` string,
     `date` string,
     `element` string,
     `datavalue` bigint,
     `mflag` string,
     `qflag` string,
     `sflag` string,
     `obstime` bigint)
   ROW FORMAT DELIMITED
     FIELDS TERMINATED BY ','
   STORED AS INPUTFORMAT
     'org.apache.hadoop.mapred.TextInputFormat'
   OUTPUTFORMAT
     'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION
     's3://aws-bigdata-blog/artifacts/athena-ctas-insert-into-blog/'
   ```

## 步骤 2：使用 CTAS 对数据进行分区、转换和压缩
<a name="ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data"></a>

创建表后，您可以使用单个 [CTAS](ctas.md) 语句通过 Snappy 压缩将数据转换为 Parquet 格式，并按年份对数据进行分区。

您在步骤 1 中创建的表具有一个 `date` 字段，其数据格式化为 `YYYYMMDD`（例如，`20100104`）。由于将按 `year` 对新表进行分区，因此，以下过程中的示例语句使用 Presto 函数 `substr("date",1,4)` 从 `date` 字段中提取 `year` 值。

**使用 Snappy 压缩将数据转换为 Parquet 格式（按年份进行分区）**
+ 运行以下 CTAS 语句，并将 *your-bucket* 替换为您的 Amazon S3 存储桶位置。

  ```
  CREATE table new_parquet
  WITH (format='PARQUET',
  parquet_compression='SNAPPY',
  partitioned_by=array['year'],
  external_location = 's3://amzn-s3-demo-bucket/optimized-data/')
  AS
  SELECT id,
           date,
           element,
           datavalue,
           mflag,
           qflag,
           sflag,
           obstime,
           substr("date",1,4) AS year
  FROM original_csv
  WHERE cast(substr("date",1,4) AS bigint) >= 2015
          AND cast(substr("date",1,4) AS bigint) <= 2019
  ```
**注意**  
在此示例中，您创建的表仅包含 2015 年至 2019 年的数据。在步骤 3 中，使用 INSERT INTO 命令将新数据添加到此表。

在查询完成后，请使用以下过程验证您在 CTAS 语句中指定的 Amazon S3 位置中的输出。

**查看 CTAS 语句所创建的分区和 parquet 文件**

1. 要显示创建的分区，请运行以下 AWS CLI 命令。请务必包含最后的正斜杠 (/)。

   ```
   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/
   ```

   输出将显示分区。

   ```
         PRE year=2015/
         PRE year=2016/
         PRE year=2017/
         PRE year=2018/
         PRE year=2019/
   ```

1. 要查看 Parquet 文件，请运行以下命令。请注意，`|` *head -5* 选项（该选项将输出限制为前五个结果）在 Windows 上不可用。

   ```
   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable | head -5
   ```

   输出与以下内容类似。

   ```
   2019-10-31 14:51:05    7.3 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_1be48df2-3154-438b-b61d-8fb23809679d
   2019-10-31 14:51:05    7.0 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_2a57f4e2-ffa0-4be3-9c3f-28b16d86ed5a
   2019-10-31 14:51:05    9.9 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_34381db1-00ca-4092-bd65-ab04e06dc799
   2019-10-31 14:51:05    7.5 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_354a2bc1-345f-4996-9073-096cb863308d
   2019-10-31 14:51:05    6.9 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_42da4cfd-6e21-40a1-8152-0b902da385a1
   ```

## 步骤 3：使用 INSERT INTO 添加数据
<a name="ctas-insert-into-etl-step-3-use-insert-into-to-add-data"></a>

在步骤 2 中，您已使用 CTA 创建一个表，其中包含 2015 年至 2019 年的分区。但原始数据集还包含 2010 年至 2014 年的数据。现在，您使用 [INSERT INTO](insert-into.md) 语句添加该数据。

**使用一个或多个 INSERT INTO 语句向表添加数据**

1. 运行以下 INSERT INTO 命令，并在 WHERE 子句中指定 2015 年之前的年份。

   ```
   INSERT INTO new_parquet
   SELECT id,
            date,
            element,
            datavalue,
            mflag,
            qflag,
            sflag,
            obstime,
            substr("date",1,4) AS year
   FROM original_csv
   WHERE cast(substr("date",1,4) AS bigint) < 2015
   ```

1. 再次运行 `aws s3 ls` 命令，并使用以下语法。

   ```
   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/
   ```

   输出将显示新分区。

   ```
         PRE year=2010/
         PRE year=2011/
         PRE year=2012/
         PRE year=2013/
         PRE year=2014/
         PRE year=2015/
         PRE year=2016/
         PRE year=2017/
         PRE year=2018/
         PRE year=2019/
   ```

1. 要查看通过使用 Parquet 格式的压缩和列式存储获得的数据集的大小减少量，请运行以下命令。

   ```
   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable --summarize
   ```

   以下结果表明，使用 Snappy 压缩进行 parquet 操作后的数据集的大小为 1.2 GB。

   ```
   ...
   2020-01-22 18:12:02 2.8 MiB optimized-data/year=2019/20200122_181132_00003_nja5r_f0182e6c-38f4-4245-afa2-9f5bfa8d6d8f
   2020-01-22 18:11:59 3.7 MiB optimized-data/year=2019/20200122_181132_00003_nja5r_fd9906b7-06cf-4055-a05b-f050e139946e
   Total Objects: 300
        Total Size: 1.2 GiB
   ```

1. 如果将更多的 CSV 数据添加到原始表中，则可使用 INSERT INTO 语句将该数据添加到 parquet 表中。例如，如果您有 2020 年的新数据，则可运行以下 INSERT INTO 语句。该语句可将数据和相关分区添加到 `new_parquet` 表中。

   ```
   INSERT INTO new_parquet
   SELECT id,
            date,
            element,
            datavalue,
            mflag,
            qflag,
            sflag,
            obstime,
            substr("date",1,4) AS year
   FROM original_csv
   WHERE cast(substr("date",1,4) AS bigint) = 2020
   ```
**注意**  
INSERT INTO 语句支持向目标表写入最多 100 个分区。不过，要添加 100 个以上的分区，您可以运行多个 INSERT INTO 语句。有关更多信息，请参阅 [使用 CTAS 和 INSERT INTO 绕过 100 分区限制](ctas-insert-into.md)。

## 步骤 4：衡量性能和成本差异
<a name="ctas-insert-into-etl-step-4-measure-performance-and-cost-differences"></a>

在转换数据后，您可以通过对新表和旧表运行相同的查询并比较结果来衡量性能提升和成本节省。

**注意**  
有关 Athena 每查询成本信息，请参阅 [Amazon Athena 定价](https://aws.amazon.com/athena/pricing)。

**衡量性能提升和成本差异**

1. 对原始表运行以下查询。此查询将查找每个年份值的不同 ID 的数量。

   ```
   SELECT substr("date",1,4) as year,
          COUNT(DISTINCT id)
   FROM original_csv
   GROUP BY 1 ORDER BY 1 DESC
   ```

1. 请注意查询运行的时间和扫描的数据量。

1. 对新表运行相同的查询，并记下查询运行时和扫描的数据量。

   ```
   SELECT year,
     COUNT(DISTINCT id)
   FROM new_parquet
   GROUP BY 1 ORDER BY 1 DESC
   ```

1. 比较结果并计算性能和成本差异。以下示例结果表明，与对旧表执行的测试查询相比，对新表执行的测试查询的速度更快、成本更低。  
****    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/athena/latest/ug/ctas-insert-into-etl.html)

1. 对原始表运行以下示例查询。该查询将计算 2018 年地球上的平均最高温度（摄氏度）、平均最低温度（摄氏度）和平均降雨量（毫米）。

   ```
   SELECT element, round(avg(CAST(datavalue AS real)/10),2) AS value
   FROM original_csv
   WHERE element IN ('TMIN', 'TMAX', 'PRCP') AND substr("date",1,4) = '2018'
   GROUP BY 1
   ```

1. 请注意查询运行的时间和扫描的数据量。

1. 对新表运行相同的查询，并记下查询运行时和扫描的数据量。

   ```
   SELECT element, round(avg(CAST(datavalue AS real)/10),2) AS value
   FROM new_parquet
   WHERE element IN ('TMIN', 'TMAX', 'PRCP') and year = '2018'
   GROUP BY 1
   ```

1. 比较结果并计算性能和成本差异。以下示例结果表明，与对旧表执行的测试查询相比，对新表执行的测试查询的速度更快、成本更低。  
****    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/athena/latest/ug/ctas-insert-into-etl.html)

## 摘要
<a name="ctas-insert-into-etl-summary"></a>

本主题介绍了如何在 Athena 中使用 CTAS 和 INSERT INTO 语句来执行 ETL 操作。您使用一个 CTAS 语句执行了第一组转换，通过 Snappy 压缩将数据转换为了 Parquet 格式。该 CTAS 语句还将数据集从未分区的数据集转换为已分区的数据集。这减小了其大小，并降低了查询的运行成本。在提供新的数据时，您可以使用 INSERT INTO 语句转换数据并将其加载到使用 CTAS 语句创建的表中。

# 使用 CTAS 和 INSERT INTO 绕过 100 分区限制
<a name="ctas-insert-into"></a>

Athena 的每个 `CREATE TABLE AS SELECT`（[CTAS](ctas.md)）查询的分区数不能超过 100 个。同样，您可以使用 [INSERT INTO](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html) 语句向目标表添加最多 100 个分区。

如果超过此限制，则可能会收到错误消息 HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets（HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS：分区/存储桶超过 100 个打开的写入程序限制）。要绕过此限制，您可以使用一个 CTAS 语句和一系列 `INSERT INTO` 语句，每个后一种语句将创建或插入不超过 100 个分区。

本主题中的示例使用名为 `tpch100` 的数据库，其数据驻留在 Amazon S3 存储桶位置 s3://amzn-s3-demo-bucket/ 中。

**使用 CTAS 和 INSERT INTO 创建带 100 多个分区的表**

1. 使用 `CREATE EXTERNAL TABLE` 语句创建一个基于所需字段进行分区的表。

   以下示例语句按列 `l_shipdate` 对数据进行分区。该表具有 2525 个分区。

   ```
   CREATE EXTERNAL TABLE `tpch100.lineitem_parq_partitioned`(
     `l_orderkey` int, 
     `l_partkey` int, 
     `l_suppkey` int, 
     `l_linenumber` int, 
     `l_quantity` double, 
     `l_extendedprice` double, 
     `l_discount` double, 
     `l_tax` double, 
     `l_returnflag` string, 
     `l_linestatus` string, 
     `l_commitdate` string, 
     `l_receiptdate` string, 
     `l_shipinstruct` string, 
     `l_comment` string)
   PARTITIONED BY ( 
     `l_shipdate` string)
   ROW FORMAT SERDE 
     'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION   's3://amzn-s3-demo-bucket/lineitem/'
   ```

1. 运行类似于下文的 `SHOW PARTITIONS <table_name>` 命令以列出分区。

   ```
   SHOW PARTITIONS lineitem_parq_partitioned
   ```

   以下是部分示例结果。

   ```
   /*
   l_shipdate=1992-01-02
   l_shipdate=1992-01-03
   l_shipdate=1992-01-04
   l_shipdate=1992-01-05
   l_shipdate=1992-01-06
   
   ...
   
   l_shipdate=1998-11-24
   l_shipdate=1998-11-25
   l_shipdate=1998-11-26
   l_shipdate=1998-11-27
   l_shipdate=1998-11-28
   l_shipdate=1998-11-29
   l_shipdate=1998-11-30
   l_shipdate=1998-12-01
   */
   ```

1. 运行 CTAS 查询以创建已分区的表。

   以下示例创建一个名为 `my_lineitem_parq_partitioned` 的表，并使用 `WHERE `子句将 `DATE` 限制为 `1992-02-01` 之前的日期。由于示例数据集的开始日期为 1992 年 1 月，因此仅创建 1992 年 1 月的分区。

   ```
   CREATE table my_lineitem_parq_partitioned
   WITH (partitioned_by = ARRAY['l_shipdate']) AS
   SELECT l_orderkey,
            l_partkey,
            l_suppkey,
            l_linenumber,
            l_quantity,
            l_extendedprice,
            l_discount,
            l_tax,
            l_returnflag,
            l_linestatus,
            l_commitdate,
            l_receiptdate,
            l_shipinstruct,
            l_comment,
            l_shipdate
   FROM tpch100.lineitem_parq_partitioned
   WHERE cast(l_shipdate as timestamp) < DATE ('1992-02-01');
   ```

1. 运行 `SHOW PARTITIONS` 命令可验证表是否包含所需的分区。

   ```
   SHOW PARTITIONS my_lineitem_parq_partitioned;
   ```

   示例中的分区对应于 1992 年 1 月内的日期。

   ```
   /*
   l_shipdate=1992-01-02
   l_shipdate=1992-01-03
   l_shipdate=1992-01-04
   l_shipdate=1992-01-05
   l_shipdate=1992-01-06
   l_shipdate=1992-01-07
   l_shipdate=1992-01-08
   l_shipdate=1992-01-09
   l_shipdate=1992-01-10
   l_shipdate=1992-01-11
   l_shipdate=1992-01-12
   l_shipdate=1992-01-13
   l_shipdate=1992-01-14
   l_shipdate=1992-01-15
   l_shipdate=1992-01-16
   l_shipdate=1992-01-17
   l_shipdate=1992-01-18
   l_shipdate=1992-01-19
   l_shipdate=1992-01-20
   l_shipdate=1992-01-21
   l_shipdate=1992-01-22
   l_shipdate=1992-01-23
   l_shipdate=1992-01-24
   l_shipdate=1992-01-25
   l_shipdate=1992-01-26
   l_shipdate=1992-01-27
   l_shipdate=1992-01-28
   l_shipdate=1992-01-29
   l_shipdate=1992-01-30
   l_shipdate=1992-01-31
   */
   ```

1. 使用 `INSERT INTO` 语句可向表添加分区。

   以下示例为 1992 年 2 月内的日期添加分区。

   ```
   INSERT INTO my_lineitem_parq_partitioned
   SELECT l_orderkey,
            l_partkey,
            l_suppkey,
            l_linenumber,
            l_quantity,
            l_extendedprice,
            l_discount,
            l_tax,
            l_returnflag,
            l_linestatus,
            l_commitdate,
            l_receiptdate,
            l_shipinstruct,
            l_comment,
            l_shipdate
   FROM tpch100.lineitem_parq_partitioned
   WHERE cast(l_shipdate as timestamp) >= DATE ('1992-02-01')
   AND cast(l_shipdate as timestamp) < DATE ('1992-03-01');
   ```

1. 再次运行 `SHOW PARTITIONS`。

   ```
   SHOW PARTITIONS my_lineitem_parq_partitioned;
   ```

   示例表现在具有对应于 1992 年 1 月和 2 月内日期的分区。

   ```
   /*
   l_shipdate=1992-01-02
   l_shipdate=1992-01-03
   l_shipdate=1992-01-04
   l_shipdate=1992-01-05
   l_shipdate=1992-01-06
   
   ...
   
   l_shipdate=1992-02-20
   l_shipdate=1992-02-21
   l_shipdate=1992-02-22
   l_shipdate=1992-02-23
   l_shipdate=1992-02-24
   l_shipdate=1992-02-25
   l_shipdate=1992-02-26
   l_shipdate=1992-02-27
   l_shipdate=1992-02-28
   l_shipdate=1992-02-29
   */
   ```

1. 继续使用 `INSERT INTO` 语句，其中每个语句读取和添加的分区数不超过 100 个。继续操作，直到您达到所需的分区数。
**重要**  
在设置 `WHERE` 条件时，请确保查询不会重叠。否则，某些分区可能具有重复数据。