# クエリ結果からテーブルを作成する (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 の両方を含んでいます。ただし、Service Quotas の観点から、Athena の CTAS クエリは DML として扱われることに注意してください。Athena の Service Quotasの詳細については、「[サービスクォータ](service-limits.md)」を参照してください。

以下の目的で CTAS クエリを使用します。
+ raw データセットのクエリを繰り返さずに、1 回のステップでクエリの結果からテーブルを作成する。これにより raw データセットの操作が簡単になります。
+ クエリ結果を変換し、テーブルを 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)
+ [ETL で CTAS および INSERT INTO を使用する](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 Part 2) では、クエリ式で指定されたテーブル行の順序が保証されるのは、`ORDER BY` 句が直後に含まれるクエリ式だけです。SQL のテーブルはどのような場合でも本来は順序付けられておらず、`ORDER BY` をサブクエリ句に追加してもクエリのパフォーマンスが低下するだけで、出力は順序付けられません。したがって、Athena CTAS クエリでは、データが書き込まされた際に `ORDER BY` 句によって指定された順序が保持される保証はありません。

## クエリ結果を保存する形式を選択する
<a name="ctas-considerations-limitations-formats-for-query-results"></a>

CTAS の結果は、`PARQUET`、`ORC`、`AVRO`、`JSON`、および `TEXTFILE` で保存できます。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>

JSON 形式と TEXTFILE 形式の CTAS クエリ結果には、`GZIP` 圧縮が使用されます。Parquet の場合、デフォルトは `GZIP` で、`GZIP` または `SNAPPY` も使用可能です。ORC の場合、デフォルトは `ZLIB` で、`LZ4`、`SNAPPY`、`ZLIB`、または `ZSTD` も使用できます。圧縮を指定する 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>

CTAS クエリの結果は Amazon S3 で暗号化することができます。これは 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 で圧縮されているといった、Parquet ファイル形式を使用するテーブル内のデータも、Athena は正常に読み込むことができます。同様なことが ORC、テキストファイル、および JSON のストレージ形式に対しても当てはまります。詳細については、「[Athena で圧縮を使用する](compression-formats.md)」を参照してください。
      + **パーティション** – (オプション) パーティションする列を選択します。データをパーティションすると、各クエリによってスキャンされるデータの量が制限されるため、パフォーマンスが向上し、コストが削減されます。任意のキーでデータをパーティションに分割することができます。詳細については、「[データのパーティション化](partitions.md)」を参照してください。
      + **バケット** – (オプション) バケット化する列を選択します。バケットとは、特定の列に基づいてデータを 1 つのパーティションにまとめる手法です。これらの列は*バケットキー*と呼ばれます。関連データを 1 つのバケット (パーティション内のファイル) にグループ化することで、Athena でスキャンされるデータ量を大幅に削減できるため、クエリのパフォーマンスが向上し、コストが削減されます。詳細については、「[パーティショニングとバケット化を使用する](ctas-partitioning-and-bucketing.md)」を参照してください。

   1. **[Preview table query]** (テーブルクエリのプレビュー) でクエリを確認します。クエリ構文については、「[CREATE TABLE AS](create-table-as.md)」を参照してください。

   1. **[テーブルの作成]** を選択します。

Athena コンソールには、CTAS クエリの作成にも使用できる SQL テンプレートがあります。<a name="ctas-create-new"></a>

**SQL テンプレートを使用して CTAS クエリを作成するには**

クエリエディタで CTAS クエリを作成するには、`CREATE TABLE AS SELECT` テンプレートを使用します。

1. Athena コンソールで、**[Tables and views]** (テーブルとビュー) の横にある **[Create table]** (テーブルの作成) をクリックし、次に **[CREATE TABLE AS SELECT]** をクリックします。これにより、プレースホルダに値が設定された CTAS クエリが、クエリエディタに入力されます。

1. 必要に応じて、クエリエディタでクエリを編集します。クエリ構文については、「[CREATE TABLE AS](create-table-as.md)」を参照してください。

1. **[Run]** (実行) を選択します。

例については「[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 例: 1 つ、または複数のテーブルから特定の列を選択する**  
次の例では、別のテーブルからの列のセットで実行される新しいクエリが作成されます。  

```
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` 内のデータは　Snappy 圧縮を使用した Parquet 形式で保存されることを指定しています。Parquet のデフォルトの圧縮は `GZIP` です。  

```
CREATE TABLE new_table
WITH (
      format = 'Parquet',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table;
```
次の例では、テーブル `new_table` 内のデータは、Snappy 圧縮を使用した ORC 形式で保存されることを指定しています。ORC のデフォルトの圧縮は ZLIB です。  

```
CREATE TABLE new_table
WITH (format = 'ORC',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table ;
```
次の例では、テーブル `new_table` 内のデータは、Snappy 圧縮を使用したテキストファイル形式で保存されることを指定しています。テキストファイルと JSON 形式のデフォルトの圧縮は GZIP です。  

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

**Example 例: クエリ結果を異なる形式で書き込む**  
以下の CTAS クエリは、CSV 形式、または別の形式で保存されている可能性がある `old_table` からすべてのレコードを選択し、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 例: パーティションされたテーブルを作成する**  
次の例では、`WITH` 句で `partitioned_by` と他のプロパティを使用した、異なるストレージ形式のパーティション テーブルに対する `CREATE TABLE AS SELECT` クエリを示します。構文については、「[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 個の一意のバケットとパーティションの組み合わせをサポートします。たとえば、5 個のバケットを持つテーブルを作成する場合、それぞれ 5 個のバケットを持つ 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 テーブルを作成します。location プロパティは省略され、`table_type` はデフォルトで `ICEBERG` に設定されます。  

```
CREATE TABLE "s3tablescatalog/amzn-s3-demo-bucket"."namespace"."s3-table-name"
WITH (
    format = 'PARQUET'
)
AS SELECT *
FROM source_table;
```
パーティション分散やバケット化など、その他の Iceberg テーブルのプロパティはすべて、通常の Iceberg テーブルと同じ構文で指定できます。

# ETL およびデータ分析での CTAS および INSERT INTO を使用する
<a name="ctas-insert-into-etl"></a>

Athena で Create Table as Select ([CTAS](ctas.md)) と [INSERT INTO](insert-into.md) ステートメントを使用して、データ処理のための Amazon S3 へのデータの抽出、変換、ロード (ETL) を行うことができます。このトピックでは、これらのステートメントを使用することで、データセットをパーティション分割して列指向データ形式に変換し、データ分析用に最適化する方法について説明します。

CTAS ステートメントでは、標準の [SELECT](select.md) クエリを使用して新しいテーブルを作成します。CTAS ステートメントを使用して、分析用のデータのサブセットを作成できます。1 つの 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>

このトピックの例では、一般公開されている [NOAA Global Historical Climatology Network Daily (GHCN-D)](https://registry.opendata.aws/noaa-ghcn/) データセットを使用した、Amazon S3 の読み取り可能なサブセットを使用します。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 コンソールで、AWS リージョン リージョンに **[US East (N. Virginia)]** (米国東部 (バージニア北部)) を選択します。このチュートリアルのクエリは、すべて `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>

テーブルを作成したら、1 つの [CTAS](ctas.md) ステートメントを使用して、データを Snappy 圧縮で Parquet 形式に変換し、データを年ごとにパーティション化できます。

ステップ 1 で作成したテーブルには、日付が `YYYYMMDD` としてフォーマットされた `date` フィールド (例: `20100104`) があります。新しいテーブルが `year` でパーティション化されるため、次の手順のサンプルステートメントでは、Presto 関数 `substr("date",1,4)` を使用して `date` フィールドから値 `year` を抽出します。

**Snappy 圧縮を使用してデータを Parquet 形式に変換し、年ごとにパーティション分割する**
+ *your-bucket* をお使いの Amazon S3 バケットの場所に置き換えてから、以下の CTAS ステートメントを実行します。

  ```
  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 ファイルを表示するには、以下のコマンドを実行します。出力を最初の 5 つの結果に制限する `|` *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 では、CTAS を使用して 2015 年から 2019 年までのデータのパーティションを持つテーブルを作成しました。しかし、元のデータセットには、2010 年から 2014 年までのデータも含まれています。次に、[INSERT INTO](insert-into.md) ステートメントを使用してそのデータを追加します。

**1 つ以上の INSERT INTO ステートメントを使用してテーブルにデータを追加する**

1. WHERE 句で 2015 年より前の年を指定して、以下の INSERT INTO コマンドを実行します。

   ```
   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/ja_jp/athena/latest/ug/ctas-insert-into-etl.html)

1. 元のテーブルで次のサンプルクエリを実行します。このクエリでは、2018 年の地球の平均最高温度 (摂氏)、平均最低気温 (摂氏)、平均降水量 (mm) を計算します。

   ```
   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/ja_jp/athena/latest/ug/ctas-insert-into-etl.html)

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

このトピックでは、Athena で CTAS と INSERT INTO ステートメントを使用して ETL オペレーションを実行する方法を説明しました。最初の変換セットは、データを Snappy 圧縮で Parquet 形式に変換する CTAS ステートメントを使用して実行しました。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 ステートメントと、それぞれ最大 100 個のパーティションを作成または挿入する一連の `INSERT INTO` ステートメントを使用できます。

このトピックの例では、Amazon S3 バケットの場所 s3://amzn-s3-demo-bucket/ にデータが格納されている、`tpch100` という名前のデータベースを使用します。

**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. 各々が読み取りと追加を 100 パーティション以上実行しない `INSERT INTO` ステートメントを引き続き使用します。必要なパーティション数に達するまで続行します。
**重要**  
`WHERE` 条件を設定するときは、クエリが重複しないようにしてください。そうしなければ、一部のパーティションに重複するデータが発生する可能性があります。