Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

# Gunakan CTAS dan INSERT INTO untuk ETL dan analisis data
<a name="ctas-insert-into-etl"></a>

Anda dapat menggunakan pernyataan Create Table as Select ([CTAS](ctas.md)) dan [INSERT INTO](insert-into.md) di Athena untuk extract, transform, and load (ETL) data ke Amazon S3 untuk pemrosesan data. Topik ini menunjukkan cara menggunakan pernyataan ini untuk partisi dan mengkonversi set data ke format data columnar untuk mengoptimalkan untuk analisis data.

Pernyataan CTAS menggunakan kueri [SELECT](select.md) standar untuk membuat tabel baru. Anda dapat menggunakan pernyataan CTAS untuk membuat subset dari data Anda untuk analisis. Dalam satu pernyataan CTAS, Anda dapat partisi data, menentukan kompresi, dan mengkonversi data ke dalam format columnar seperti Apache Parquet atau Apache ORC. Saat Anda menjalankan permintaan CTAS, tabel dan partisi yang dibuat secara otomatis ditambahkan ke[AWS Glue Data Catalog](https://aws.amazon.com/glue). Ini membuat tabel baru dan partisi yang menciptakan segera tersedia untuk kueri berikutnya.

INSERT INTO pernyataan memasukkan baris baru ke dalam tabel tujuan berdasarkan pernyataan kueri SELECT yang berjalan pada tabel sumber. Anda dapat menggunakan INSERT INTO pernyataan untuk mengubah dan memuat data tabel sumber dalam format CSV ke data tabel tujuan menggunakan semua transformasi yang didukung CTAS.

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

Di Athena, menggunakan pernyataan CTAS untuk melakukan konversi batch awal data. Kemudian gunakan beberapa INSERT INTO pernyataan untuk membuat update inkremental ke tabel yang dibuat oleh pernyataan CTAS.

**Langkah-langkah**
+ [Langkah 1: Buat tabel berdasarkan dataset asli](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset)
+  [Langkah 2: Gunakan CTAS untuk mempartisi, mengonversi, dan mengompres data](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data) 
+  [Langkah 3: Gunakan INSERT INTO untuk menambahkan data](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data) 
+  [Langkah 4: Ukur perbedaan kinerja dan biaya](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences) 

## Langkah 1: Buat tabel berdasarkan dataset asli
<a name="ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset"></a>

Contoh dalam topik ini menggunakan subset Amazon S3 yang dapat dibaca dari kumpulan data harian jaringan [klimatologi historis global NOAA](https://registry.opendata.aws/noaa-ghcn/) (GHCN-D) yang tersedia untuk umum. Data di Amazon S3 memiliki karakteristik sebagai berikut.

```
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
```

Data asli disimpan di Amazon S3 tanpa partisi. Data dalam format CSV dalam fail seperti berikut.

```
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
```

Ukuran file dalam sampel ini relatif kecil. Dengan menggabungkan mereka ke dalam file yang lebih besar, Anda dapat mengurangi jumlah total file, memungkinkan performa kueri yang lebih baik. Anda dapat menggunakan CTAS dan INSERT INTO pernyataan untuk meningkatkan performa kueri.

**Untuk membuat basis data dan tabel berdasarkan set data sampel**

1. Di konsol Athena, pilih **US East (Virginia N.).** Wilayah AWS Pastikan untuk menjalankan semua pertanyaan dalam tutorial ini di`us-east-1`.

1. Dalam editor permintaan Athena, jalankan perintah[BUAT BASIS DATA](create-database.md)Untuk membuat basis data. 

   ```
   CREATE DATABASE blogdb
   ```

1. Jalankan pernyataan berikut untuk[Untuk membuat tabel](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/'
   ```

## Langkah 2: Gunakan CTAS untuk mempartisi, mengonversi, dan mengompres data
<a name="ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data"></a>

Setelah Anda membuat tabel, Anda dapat menggunakan[CTAS](ctas.md)untuk mengkonversi data ke format Parquet dengan kompresi Snappy dan partisi data per tahun.

Tabel yang Anda buat di Langkah 1 memiliki`date`lapangan dengan tanggal diformat sebagai`YYYYMMDD`(misalnya,`20100104`). Karena tabel baru akan dipartisi pada`year`, contoh pernyataan dalam prosedur berikut menggunakan fungsi Presto`substr("date",1,4)`untuk mengekstrak`year`nilai dari`date`Bidang.

**Untuk mengonversi data ke format parket dengan kompresi tajam, partisi berdasarkan tahun**
+ Jalankan pernyataan CTAS berikut, ganti *your-bucket* dengan lokasi bucket Amazon S3 Anda.

  ```
  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
  ```
**catatan**  
Dalam contoh ini, tabel yang Anda buat hanya mencakup data dari 2015 hingga 2019. Pada Langkah 3, Anda menambahkan data baru ke tabel ini menggunakan INSERT INTO perintah.

Saat permintaan selesai, gunakan prosedur berikut untuk memverifikasi output di lokasi Amazon S3 yang Anda tentukan dalam pernyataan CTAS.

**Untuk melihat partisi dan file Parquet yang dibuat oleh pernyataan CTAS**

1. Untuk menampilkan partisi yang dibuat, jalankan AWS CLI perintah berikut. Pastikan untuk menyertakan garis miring ke depan akhir (/).

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

   Output menunjukkan partisi.

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

1. Untuk melihat file Parquet, jalankan perintah berikut. Perhatikan bahwa opsi `|` *head-5*, yang membatasi output untuk lima hasil pertama, tidak tersedia pada Windows.

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

   Output menyerupai berikut.

   ```
   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
   ```

## Langkah 3: Gunakan INSERT INTO untuk menambahkan data
<a name="ctas-insert-into-etl-step-3-use-insert-into-to-add-data"></a>

Pada langkah 2, Anda menggunakan CTAS untuk membuat tabel dengan partisi untuk tahun 2015 hingga 2019. Namun, set data asli juga berisi data untuk tahun 2010 hingga 2014. Sekarang Anda menambahkan data yang menggunakan[SISIPAN KE](insert-into.md).

**Untuk menambahkan data ke tabel menggunakan satu atau lebih INSERT INTO pernyataan**

1. Jalankan perintah INSERT INTO berikut, menentukan tahun sebelum 2015 di klausa WHERE.

   ```
   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. Jalankan`aws s3 ls`Perintah lagi, menggunakan sintaks berikut.

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

   Output menunjukkan partisi baru.

   ```
         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. Untuk melihat pengurangan ukuran set data yang diperoleh dengan menggunakan kompresi dan penyimpanan kolumnar dalam format Parquet, jalankan perintah berikut.

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

   Hasil berikut menunjukkan bahwa ukuran set data setelah Parquet dengan kompresi Snappy adalah 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. Jika lebih banyak data CSV ditambahkan ke tabel asli, Anda dapat menambahkan data tersebut ke tabel Parquet dengan menggunakan INSERT INTO pernyataan. Sebagai contoh, jika Anda memiliki data baru untuk tahun 2020, Anda bisa menjalankan berikut INSERT INTO pernyataan. Pernyataan itu menambahkan data dan partisi yang relevan untuk`new_parquet`Tabel.

   ```
   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
   ```
**catatan**  
Pernyataan INSERT INTO mendukung menulis maksimal 100 partisi ke tabel tujuan. Namun, untuk menambahkan lebih dari 100 partisi, Anda dapat menjalankan beberapa INSERT INTO pernyataan. Untuk informasi selengkapnya, lihat [Gunakan CTAS dan INSERT INTO untuk bekerja di sekitar batas partisi 100](ctas-insert-into.md).

## Langkah 4: Ukur perbedaan kinerja dan biaya
<a name="ctas-insert-into-etl-step-4-measure-performance-and-cost-differences"></a>

Setelah Anda mengubah data, Anda dapat mengukur performa keuntungan dan penghematan biaya dengan menjalankan kueri yang sama pada tabel baru dan lama dan membandingkan hasil.

**catatan**  
Untuk informasi biaya per permintaan Athena, lihat[Harga Amazon Athena](https://aws.amazon.com/athena/pricing).

**Untuk mengukur keuntungan performa dan perbedaan biaya**

1. Menjalankan kueri berikut pada tabel asli. Kueri menemukan jumlah yang berbeda IDs untuk setiap nilai tahun ini.

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

1. Perhatikan waktu yang permintaan berlari dan jumlah data yang dipindai.

1. Menjalankan kueri yang sama pada tabel baru, mencatat runtime kueri dan jumlah data yang dipindai.

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

1. Bandingkan hasilnya dan hitung perbedaan performa dan biaya. Hasil contoh berikut menunjukkan bahwa permintaan tes di tabel baru lebih cepat dan lebih murah daripada permintaan di tabel tua.  
****    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/id_id/athena/latest/ug/ctas-insert-into-etl.html)

1. Menjalankan kueri contoh berikut pada tabel asli. Kueri menghitung suhu maksimum rata-rata (Celcius), suhu minimum rata-rata (Celcius), dan curah hujan rata-rata (mm) untuk Bumi pada tahun 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. Perhatikan waktu yang permintaan berlari dan jumlah data yang dipindai.

1. Menjalankan kueri yang sama pada tabel baru, mencatat runtime kueri dan jumlah data yang dipindai.

   ```
   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. Bandingkan hasilnya dan hitung perbedaan performa dan biaya. Hasil contoh berikut menunjukkan bahwa permintaan tes di tabel baru lebih cepat dan lebih murah daripada permintaan di tabel tua.  
****    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/id_id/athena/latest/ug/ctas-insert-into-etl.html)

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

Topik ini menunjukkan cara untuk melakukan operasi ETL menggunakan CTAS dan INSERT INTO pernyataan di Athena. Anda melakukan rangkaian transformasi pertama menggunakan pernyataan CTAS yang mengubah data ke format Parquet dengan kompresi Snappy. Pernyataan CTAS juga dikonversi set data dari non-dipartisi untuk dipartisi. Ini mengurangi ukurannya dan menurunkan biaya menjalankan kueri. Saat data baru menjadi tersedia, Anda dapat menggunakan INSERT INTO pernyataan untuk mengubah dan memuat data ke dalam tabel yang Anda buat dengan pernyataan CTAS.