# 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 ステートメントで作成したテーブルにデータを変換およびロードできます。