Verwenden von CTAS und INSERT INTO für ETL und Datenanalyse

So erstellen Sie eine Datenbank und eine Tabelle basierend auf dem Beispiel-Dataset

1. Wählen Sie in der Athena-Konsole die USA Ost (Nord-Virginia) AWS-Region. Stellen Sie sicher, dass Sie alle Abfragen in diesem Lernprogramm in ausführen us-east-1.

2. Führen Sie im Athena-Abfrage-Editor den Befehl CREATE DATABASE aus, um eine Datenbank zu erstellen.

   CREATE DATABASE blogdb

3. Führen Sie die folgende Anweisung aus, um eine Tabelle zu erstellen.

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

So konvertieren Sie die Daten in das Parquet-Format mit Snappy Komprimierung, partitioniert nach Jahr

• Führen Sie die folgende CTAS-Anweisung aus und ersetzen Sie your-bucket durch Ihren Amazon-S3-Bucket-Speicherort.

  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

  Anmerkung

  In diesem Beispiel enthält die Tabelle, die Sie erstellen, nur die Daten von 2015 bis 2019. In Schritt 3 fügen Sie dieser Tabelle neue Daten hinzu, indem Sie den Befehl INSERT INTO verwenden.

So zeigen Sie die Partitionen und Parquet -Dateien an, die von der CTAS-Anweisung erstellt wurden

1. Führen Sie den folgenden AWS CLI-Befehl aus, um die erstellten Partitionen anzuzeigen. Achten Sie darauf, den letzten Schrägstrich (/) einzuschließen.

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

   Die Ausgabe zeigt die Partitionen.

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

2. Führen Sie den folgenden Befehl aus, um die Parquet-Dateien anzuzeigen. Beachten Sie, dass die Option | head -5, die die Ausgabe auf die ersten fünf Ergebnisse beschränkt, unter Windows nicht verfügbar ist.

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

   Die Ausgabe sieht in etwa folgendermaßen aus.

   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

So fügen Sie der Tabelle Daten mit einer oder mehreren INSERT INTO-Anweisungen hinzu

1. Führen Sie den folgenden INSERT INTO-Befehl aus und geben Sie die Jahre vor 2015 in der WHERE-Klausel an.

   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. Führen Sie den aws s3 ls-Befehl mit der folgenden Syntax erneut aus.

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

   Die Ausgabe zeigt die neuen Partitionen.

         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. Führen Sie den folgenden Befehl aus, um die Verringerung der Größe des Datasets anzuzeigen, die durch Komprimierung und Säulenspeicherung im Parquet-Format erzielt wurde.

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

   Die folgenden Ergebnisse zeigen, dass die Größe des Datensatzes nach Parquet mit Snappy-Komprimierung 1,2 GB beträgt.

   ...
   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. Wenn der ursprünglichen Tabelle mehr CSV-Daten hinzugefügt werden, können Sie diese Daten mit INSERT INTO-Anweisungen zur Parquet-Tabelle hinzufügen. Wenn Sie beispielsweise neue Daten für das Jahr 2020 haben, können Sie die folgende INSERT INTO-Anweisung ausführen. Die Anweisung fügt die Daten und die entsprechende Partition zur Tabelle new_parquet hinzu.

   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

   Anmerkung

   Die Anweisung INSERT INTO unterstützt das Schreiben von maximal 100 Partitionen in die Zieltabelle. Um jedoch mehr als 100 Partitionen hinzuzufügen, können Sie mehrere INSERT INTO-Anweisungen ausführen. Weitere Informationen finden Sie unter Verwenden von CTAS und INSERT INTO zum Umgehen des Limits von 100 Partitionen.

So messen Sie Leistungssteigerungen und Kostenunterschiede

1. Führen Sie die folgende Abfrage für die ursprüngliche Tabelle aus. Die Abfrage findet die Anzahl der eindeutigen IDs für jeden Wert des Jahres.

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

2. Beachten Sie, wie lange Abfrage ausgeführt wurde und die Menge der gescannten Daten.

3. Führen Sie dieselbe Abfrage für die neue Tabelle aus und achten Sie dabei auf die Abfrageausführungszeit und die Menge der gescannten Daten.

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

4. Vergleichen Sie die Ergebnisse und berechnen Sie die Leistungs- und Kostendifferenz. Die folgenden Beispielergebnisse zeigen, dass die Testabfrage für die neue Tabelle schneller und billiger war als die Abfrage für die alte Tabelle.

   Tabelle	Laufzeit	Gescannte Daten
   Original	16,88 Sekunden	11,35 GB
   Neu	3,79 Sekunden	428,05 MB

5. Führen Sie die folgende Beispielabfrage für die ursprüngliche Tabelle aus. Die Abfrage berechnet die durchschnittliche Höchsttemperatur (Celsius), durchschnittliche Mindesttemperatur (Celsius) und durchschnittliche Niederschlagsmenge (mm) für die Erde im Jahr 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. Beachten Sie, wie lange Abfrage ausgeführt wurde und die Menge der gescannten Daten.

7. Führen Sie dieselbe Abfrage für die neue Tabelle aus und achten Sie dabei auf die Abfrageausführungszeit und die Menge der gescannten Daten.

   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. Vergleichen Sie die Ergebnisse und berechnen Sie die Leistungs- und Kostendifferenz. Die folgenden Beispielergebnisse zeigen, dass die Testabfrage für die neue Tabelle schneller und billiger war als die Abfrage für die alte Tabelle.

   Tabelle	Laufzeit	Gescannte Daten
   Original	18,65 Sekunden	11,35 GB
   Neu	1,92 Sekunden	68 MB