Gunakan CTAS dan INSERT INTO untuk ETL dan analisis data

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 dius-east-1.

2. Di editor kueri Athena, jalankan CREATEDATABASEperintah untuk membuat database.

   CREATE DATABASE blogdb

3. 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/'

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

• Jalankan CTAS pernyataan 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.

Untuk melihat partisi dan file parket 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/

2. 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

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

1. Jalankan INSERT INTO perintah berikut, tentukan tahun sebelum 2015 dalam WHERE klausa.

   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

2. Jalankanaws s3 lsPerintah 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/

3. 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

4. Jika lebih banyak CSV data ditambahkan ke tabel asli, Anda dapat menambahkan data tersebut ke meja parket dengan menggunakan INSERT INTO pernyataan. Misalnya, jika Anda memiliki data baru untuk tahun 2020, Anda dapat menjalankan INSERT INTO pernyataan berikut. 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

   INSERTINTOPernyataan ini mendukung penulisan 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.

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

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

3. 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

4. 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

5. 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

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

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

8. 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