Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich. # Verwenden von CTAS und INSERT INTO für ETL und Datenanalyse Sie können Create-Table-as-Select-([CTAS](ctas.md))- und [INSERT-INTO-Anweisungen](insert-into.md) in Athena verwenden, um ETL-Daten für die Datenverarbeitung in Amazon S3 zu extrahieren, transformieren und laden. In diesem Thema wird erläutert, wie Sie diese Anweisungen zum Partitionieren und Konvertieren eines Datasets in das spaltenförmige Datenformat verwenden, um es für die Datenanalyse zu optimieren. CTAS-Anweisungen verwenden standardmäßige [SELECT](select.md)-Abfragen um neue Tabellen zu erstellen. Sie können eine CTAS-Anweisung verwenden, um eine Teilmenge Ihrer Daten für die Analyse zu erstellen. In einer CTAS-Anweisung können Sie die Daten partitionieren, komprimieren und die Daten in ein Spaltenformat wie Apache Parquet oder Apache ORC konvertieren. Wenn Sie die CTAS-Abfrage ausführen, werden die von ihr erstellten Tabellen und Partitionen automatisch dem [AWS Glue Data Catalog](https://aws.amazon.com/glue) hinzugefügt. Dadurch sind die neu erstellten Tabellen und Partitionen sofort für nachfolgende Abfragen verfügbar. INSERT INTO-Anweisungen fügen neue Zeilen basierend auf einer SELECT-Abfrageanweisung, die in einer Quelltabelle ausgeführt wird, in eine Zieltabelle ein. Sie können INSERT INTO-Anweisungen verwenden, um Quelltabellendaten im CSV-Format mit allen Transformationen, die von CTAS unterstützt werden, in Zieltabellendaten zu transformieren und zu laden. ## -Übersicht Verwenden Sie in Athena eine CTAS-Anweisung, um eine erste Batch-Konvertierung der Daten durchzuführen. Verwenden Sie dann mehrere INSERT INTO-Anweisungen, um inkrementelle Aktualisierungen der von der CTAS-Anweisung erstellten Tabelle vorzunehmen. **Schritte** + [Schritt 1: Erstellen einer Tabelle basierend auf dem ursprünglichen Datensatz](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset) + [Schritt 2: Verwenden von CTAS zum Partitionieren, Konvertieren und Komprimieren der Daten](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data) + [Schritt 3: Verwenden von INSERT INTO zum Hinzufügen von Daten](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data) + [Schritt 4: Messen von Leistungs- und Kostendifferenzen](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences) ## Schritt 1: Erstellen einer Tabelle basierend auf dem ursprünglichen Datensatz Das Beispiel in diesem Thema verwendet eine in Amazon S3 lesbare Teilmenge des öffentlich verfügbaren [NOAA Global Historical Climatology Network Daily (GHCN-d)](https://registry.opendata.aws/noaa-ghcn/)-Datensatzes. Die Daten für Amazon S3 haben die folgenden Eigenschaften. ``` 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 ``` Die Originaldaten werden ohne Partitionen in Amazon S3 gespeichert. Die Daten befinden sich im CSV-Format in Dateien wie den folgenden. ``` 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 ``` Die Dateigrößen in diesem Beispiel sind relativ klein. Durch die Zusammenführung in größere Dateien können Sie die Gesamtzahl der Dateien reduzieren und so eine bessere Abfrageausführung ermöglichen. Sie können CTAS- und INSERT INTO-Anweisungen verwenden, um die Abfrageleistung zu verbessern. **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 `us-east-1` ausführen. 1. Führen Sie im Athena-Abfrage-Editor den Befehl [CREATE DATABASE](create-database.md) aus, um eine Datenbank zu erstellen. ``` CREATE DATABASE blogdb ``` 1. Führen Sie die folgende Anweisung aus, um [eine Tabelle zu erstellen](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/' ``` ## Schritt 2: Verwenden von CTAS zum Partitionieren, Konvertieren und Komprimieren der Daten Nachdem Sie eine Tabelle erstellt haben, können Sie die Daten mit einer einzelnen [CTAS](ctas.md)-Anweisung in das Parquet-Format mit Snappy-Komprimierung konvertieren und die Daten nach Jahr partitionieren. Die Tabelle, die Sie in Schritt 1 erstellt haben, enthält ein `date`-Feld, in dem das Datum als `YYYYMMDD` formatiert ist (z. B. `20100104`). Da die neue Tabelle nach `year` partitioniert wird, verwendet die Beispielanweisung im folgenden Verfahren die Presto-Funktion `substr("date",1,4)`, um den `year`-Wert aus dem Feld `date` zu extrahieren. **So konvertieren Sie die Daten in das Parquet-Format mit Snappy Komprimierung, partitioniert nach Jahr** + Führen Sie die folgende CTAS-Anweisung aus und *your-bucket* ersetzen Sie sie durch Ihren Amazon S3 S3-Bucket-Standort. ``` 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. Gehen Sie nach Abschluss der Abfrage folgendermaßen vor, um die Ausgabe an dem Amazon-S3-Speicherort zu überprüfen, den Sie in der CTAS-Anweisung angegeben haben. **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/ ``` 1. 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 ``` ## Schritt 3: Verwenden von INSERT INTO zum Hinzufügen von Daten In Schritt 2 haben Sie CTAS verwendet, um eine Tabelle mit Partitionen für die Jahre 2015 bis 2019 zu erstellen. Der ursprüngliche Datensatz enthält jedoch auch Daten für die Jahre 2010 bis 2014. Nun fügen Sie diese Daten mit einer [INSERT INTO](insert-into.md)-Anweisung hinzu. **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 ``` 1. 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/ ``` 1. 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 ``` 1. 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](ctas-insert-into.md). ## Schritt 4: Messen von Leistungs- und Kostendifferenzen Nachdem Sie die Daten transformiert haben, können Sie die Leistungssteigerungen und Kosteneinsparungen messen, indem Sie dieselben Abfragen in den neuen und alten Tabellen ausführen und die Ergebnisse vergleichen. **Anmerkung** Informationen zu Athena-Kosten pro Abfrage finden Sie unter [Preise für Amazon Athena](https://aws.amazon.com/athena/pricing). **So messen Sie Leistungssteigerungen und Kostenunterschiede** 1. Führen Sie die folgende Abfrage für die ursprüngliche Tabelle aus. Die Abfrage ermittelt die Anzahl der eindeutigen Werte 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 ``` 1. Beachten Sie, wie lange Abfrage ausgeführt wurde und die Menge der gescannten Daten. 1. 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 ``` 1. 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. **** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/de_de/athena/latest/ug/ctas-insert-into-etl.html) 1. 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 ``` 1. Beachten Sie, wie lange Abfrage ausgeführt wurde und die Menge der gescannten Daten. 1. 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 ``` 1. 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. **** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/de_de/athena/latest/ug/ctas-insert-into-etl.html) ## Zusammenfassung In diesem Thema wurde gezeigt, wie ETL-Operationen mit CTAS- und INSERT INTO-Anweisungen in Athena ausgeführt werden. Sie haben den ersten Satz von Transformationen mit einer CTAS-Anweisung durchgeführt, die Daten in das Parquet-Format mit Snappy-Komprimierung konvertiert hat. Die CTAS-Anweisung konvertiert auch das Dataset von nicht partitionierten in partitionierte Daten. Dies reduzierte seine Größe und senkte die Kosten für die Ausführung der Abfragen. Wenn neue Daten verfügbar sind, können Sie sie mit einer INSERT INTO-Anweisung in die Tabelle transformieren und laden, die Sie mit der CTAS-Anweisung erstellt haben.