Gambaran Umum Langkah 1: Buat tabel berdasarkan dataset asli Langkah 2: Gunakan CTAS untuk mempartisi, mengonversi, dan mengompres data Langkah 3: Gunakan INSERT INTO untuk menambahkan data Langkah 4: Ukur perbedaan kinerja dan biaya Ringkasan

Menggunakan CTAS dan INSERT INTO untuk ETL dan analisis data

Anda dapat menggunakan pernyataan Create Table as Select (CTAS) dan INSERT INTO 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 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 keAWS Glue Data Catalog. 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.

Gambaran Umum

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
Langkah 2: Gunakan CTAS untuk mempartisi, mengonversi, dan mengompres data
Langkah 3: Gunakan INSERT INTO untuk menambahkan data
Langkah 4: Ukur perbedaan kinerja dan biaya

Langkah 1: Buat tabel berdasarkan dataset asli

Contoh dalam topik ini menggunakan subset Amazon S3 yang dapat dibaca dari kumpulan data harian jaringan klimatologi historis global NOAA (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

Di konsol Athena, pilih US East (Virginia N.). Wilayah AWS Pastikan untuk menjalankan semua pertanyaan dalam tutorial ini dius-east-1.
Dalam editor permintaan Athena, jalankan perintahBUAT BASIS DATAUntuk membuat basis data.
```
CREATE DATABASE blogdb
```

Jalankan pernyataan berikut untukUntuk membuat tabel.


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

Setelah Anda membuat tabel, Anda dapat menggunakanCTASuntuk mengkonversi data ke format Parquet dengan kompresi Snappy dan partisi data per tahun.

Tabel yang Anda buat di Langkah 1 memilikidatelapangan dengan tanggal diformat sebagaiYYYYMMDD(misalnya,20100104). Karena tabel baru akan dipartisi padayear, contoh pernyataan dalam prosedur berikut menggunakan fungsi Prestosubstr("date",1,4)untuk mengekstrakyearnilai daridateBidang.

Untuk mengonversi data ke format parket dengan kompresi tajam, partisi berdasarkan tahun

Menjalankan pernyataan CTAS berikut, menggantibucket Andadengan lokasi bucket Amazon S3.


CREATE table new_parquet
WITH (format='PARQUET',
parquet_compression='SNAPPY',
partitioned_by=array['year'],
external_location = 's3://DOC-EXAMPLE-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

Untuk menampilkan partisi yang dibuat, jalankan AWS CLI perintah berikut. Pastikan untuk menyertakan garis miring ke depan akhir (/).
```
aws s3 ls s3://DOC-EXAMPLE-BUCKET/optimized-data/
```
Output menunjukkan partisi.
```
      PRE year=2015/
      PRE year=2016/
      PRE year=2017/
      PRE year=2018/
      PRE year=2019/
```

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://DOC-EXAMPLE-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

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 menggunakanSISIPAN KE.

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

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

Jalankanaws s3 lsPerintah lagi, menggunakan sintaks berikut.


aws s3 ls s3://DOC-EXAMPLE-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/

Untuk melihat pengurangan ukuran set data yang diperoleh dengan menggunakan kompresi dan penyimpanan kolumnar dalam format Parquet, jalankan perintah berikut.


aws s3 ls s3://DOC-EXAMPLE-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

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 untuknew_parquetTabel.
```
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 Menggunakan CTAS dan INSERT INTO untuk bekerja di sekitar batas partisi 100.

Langkah 4: Ukur perbedaan kinerja dan biaya

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, lihatHarga Amazon Athena.

Untuk mengukur keuntungan performa dan perbedaan biaya

Menjalankan kueri berikut pada tabel asli. kueri menemukan jumlah ID yang berbeda untuk setiap nilai tahun.
```
SELECT substr("date",1,4) as year,
       COUNT(DISTINCT id)
FROM original_csv
GROUP BY 1 ORDER BY 1 DESC
```
Perhatikan waktu yang permintaan berlari dan jumlah data yang dipindai.
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
```

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.

Tabel	Waktu Aktif	Data dipindai
Asal	16,88 detik	11,35 GB
Baru	3,79 detik	482,05 MB

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
```
Perhatikan waktu yang permintaan berlari dan jumlah data yang dipindai.

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

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.

Tabel	Waktu Aktif	Data dipindai
Asal	18,65 detik	11,35 GB
Baru	1,92 detik	68 MB

Ringkasan

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.

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Contoh CTAS

Bekerja di sekitar batas partisi 100