Abfragen optimieren

Verwenden Sie die in diesem Abschnitt beschriebenen Techniken zur Abfrageoptimierung, um Abfragen schneller ausführen zu lassen oder um das Problem für Abfragen zu umgehen, die die Ressourcenlimits in Athena überschreiten.

Verknüpfungen optimieren

Es gibt viele verschiedene Strategien für die Ausführung von Verknüpfungen in einer verteilten Abfrage-Engine. Zwei der gängigsten sind verteilte Hash-Joins und Abfragen mit komplexen Join-Bedingungen.

Platzieren Sie in einem verteilten Hash-Join große Tabellen links und kleine Tabellen rechts

Der gebräuchlichste Join-Typ verwendet einen Gleichheitsvergleich als Join-Bedingung. Athena führt diese Art von Join als verteilten Hash-Join aus.

Bei einem verteilten Hash-Join erstellt die Engine aus einer der Seiten des Joins eine Nachschlagetabelle (Hashtabelle). Diese Seite wird die Build-Seite genannt. Die Datensätze der Build-Seite sind auf die Knoten verteilt. Jeder Knoten erstellt eine Nachschlagetabelle für seine Teilmenge. Die andere Seite des Joins, die so genannte Test-Seite, wird dann durch die Knoten gestreamt. Die Datensätze von der Testseite werden auf die gleiche Weise wie auf der Build-Seite auf die Knoten verteilt. Auf diese Weise kann jeder Knoten die Verknüpfung durchführen, indem er die entsprechenden Datensätze in seiner eigenen Nachschlagetabelle nachschlägt.

Wenn die auf der Build-Seite der Verknüpfung erstellten Nachschlagetabellen nicht in den Arbeitsspeicher passen, können Abfragen fehlschlagen. Selbst wenn die Gesamtgröße der Build-Seite kleiner als der verfügbare Speicher ist, können Abfragen fehlschlagen, wenn die Verteilung der Datensätze stark verzerrt ist. Im Extremfall könnten alle Datensätze denselben Wert für die Join-Bedingung haben und müssen in den Speicher eines einzelnen Knotens passen. Selbst eine Abfrage mit weniger Verzerrungen kann fehlschlagen, wenn ein Satz von Werten an denselben Knoten gesendet wird und die Summe der Werte den verfügbaren Speicher übersteigt. Knoten können zwar Datensätze auf die Festplatte übertragen, aber ein Datenverlust verlangsamt die Abfrageausführung und kann nicht ausreichen, um zu verhindern, dass die Abfrage fehlschlägt.

Athena versucht, Verbindungen neu anzuordnen, um die größere Relation als Testseite und die kleinere Relation als Build-Seite zu verwenden. Da Athena die Daten in Tabellen jedoch nicht verwaltet, verfügt es nur über begrenzte Informationen und muss häufig davon ausgehen, dass die erste Tabelle größer und die zweite Tabelle kleiner ist.

Gehen Sie beim Schreiben von Verknüpfungen mit gleichheitsbasierten Join-Bedingungen davon aus, dass die Tabelle links vom JOIN-Schlüsselwort die Testseite und die Tabelle rechts die Build-Seite ist. Stellen Sie sicher, dass die rechte Tabelle, die Build-Seite, die kleinere der Tabellen ist. Wenn es nicht möglich ist, die Build-Seite der Verknüpfung so klein zu machen, dass sie in den Arbeitsspeicher passt, sollten Sie mehrere Abfragen ausführen, die Teilmengen der Build-Tabelle verknüpfen.

Wird verwendetEXPLAIN, um Abfragen mit komplexen Verknüpfungen zu analysieren

Abfragen mit komplexen Verbindungsbedingungen (z. B. Abfragen, die LIKE, > oder andere Operatoren verwenden) sind häufig rechenintensiv. Im schlimmsten Fall muss jeder Datensatz von einer Seite der Verknüpfung mit jedem Datensatz auf der anderen Seite der Verknüpfung verglichen werden. Da die Ausführungszeit mit dem Quadrat der Anzahl der Datensätze wächst, besteht bei solchen Abfragen das Risiko, dass die maximale Ausführungszeit überschritten wird.

Um im Voraus herauszufinden, wie Athena Ihre Anfrage ausführt, können Sie die EXPLAIN-Anweisung verwenden. Weitere Informationen erhalten Sie unter Verwenden von EXPLAIN und EXPLAIN ANALYZE in Athena und Verstehen Sie die Ergebnisse der EXPLAIN Athena-Erklärung.

Reduzieren Sie den Umfang der Fensterfunktionen oder entfernen Sie sie

Da es sich bei Fensterfunktionen um ressourcenintensive Vorgänge handelt, können sie dazu führen, dass Abfragen langsam ausgeführt werden oder sogar fehlschlagen und die Meldung Erschöpfte Ressourcen bei diesem Skalierungsfaktor abfragen angezeigt wird. Fensterfunktionen behalten alle Datensätze, mit denen sie arbeiten, im Speicher, um ihr Ergebnis zu berechnen. Wenn das Fenster sehr groß ist, kann der Speicherplatz der Fensterfunktion knapp werden.

Um sicherzustellen, dass Ihre Abfragen innerhalb der verfügbaren Speichergrenzen ausgeführt werden, reduzieren Sie die Größe der Fenster, in denen Ihre Fensterfunktionen ausgeführt werden. Dazu können Sie eine PARTITIONED BY-Klausel hinzufügen oder den Geltungsbereich vorhandener Partitionierungsklauseln einschränken.

Verwenden Sie Funktionen, die keine Fenster sind

Manchmal können Abfragen mit Fensterfunktionen ohne Fensterfunktionen neu geschrieben werden. So können Sie z. B. anstelle von row_number für die Suche nach den ersten N Datensätzen ORDER BY und LIMIT verwenden. Anstatt row_number oder rank zu verwenden, um Datensätze zu deduplizieren, können Sie Aggregatfunktionen wie max_by, min_by und arbiträr verwenden.

Nehmen wir zum Beispiel an, Sie haben einen Datensatz mit Aktualisierungen von einem Sensor. Der Sensor meldet regelmäßig seinen Batteriestatus und enthält einige Metadaten wie den Standort. Wenn Sie den letzten Batteriestatus für jeden Sensor und seinen Standort wissen möchten, können Sie diese Abfrage verwenden:

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

Da Metadaten wie der Standort für jeden Datensatz identisch sind, können Sie die arbitrary-Funktion verwenden, um einen beliebigen Wert aus der Gruppe auszuwählen.

Um den letzten Batteriestatus abzurufen, können Sie die max_by-Funktion verwenden. Die max_by-Funktion wählt den Wert für eine Spalte aus dem Datensatz aus, in dem der Maximalwert einer anderen Spalte gefunden wurde. In diesem Fall gibt sie den Batteriestatus für den Datensatz mit dem Zeitpunkt der letzten Aktualisierung innerhalb der Gruppe zurück. Diese Abfrage wird schneller ausgeführt und benötigt weniger Speicher als eine entsprechende Abfrage mit einer Fensterfunktion.

Optimieren Sie Aggregationen

Wenn Athena eine Aggregation durchführt, verteilt es die Datensätze mithilfe der Spalten in der GROUP BY-Klausel auf die Worker-Knoten. Um die Zuordnung von Datensätzen zu Gruppen so effizient wie möglich zu gestalten, versuchen die Knoten, Datensätze im Speicher zu behalten, sie aber bei Bedarf auf die Festplatte zu übertragen.

Es ist auch eine gute Idee, die Aufnahme überflüssiger Spalten in GROUP BY-Klauseln zu vermeiden. Da weniger Spalten weniger Speicher benötigen, ist eine Abfrage, die eine Gruppe mit weniger Spalten beschreibt, effizienter. Numerische Spalten verbrauchen auch weniger Speicher als Zeichenketten. Wenn Sie beispielsweise einen Datensatz aggregieren, der sowohl eine numerische Kategorie-ID als auch einen Kategorienamen hat, verwenden Sie in der GROUP BY-Klausel nur die Kategorie-ID-Spalte.

Manchmal enthalten Abfragen Spalten in der GROUP BY-Klausel, um die Tatsache zu umgehen, dass eine Spalte entweder Teil der GROUP BY-Klausel oder ein Aggregatausdruck sein muss. Wenn diese Regel nicht befolgt wird, erhalten Sie möglicherweise eine Fehlermeldung wie die folgende:

EXPRESSION_ NOT _AGGREGATE: Zeile 1:8: 'Kategorie' muss ein Aggregatausdruck sein oder in GROUP der BY-Klausel vorkommen

Um zu vermeiden, dass der GROUP BY-Klausel redundante Spalten hinzugefügt werden müssen, können Sie die arbitrary-Funktion verwenden, wie im folgenden Beispiel.

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

Die ARBITRARY-Funktion gibt einen beliebigen Wert aus der Gruppe zurück. Die Funktion ist nützlich, wenn Sie wissen, dass alle Datensätze in der Gruppe denselben Wert für eine Spalte haben, der Wert die Gruppe jedoch nicht identifiziert.

Optimieren Sie die Top N Abfragen

Die ORDER BY-Klausel gibt die Ergebnisse einer Abfrage in sortierter Reihenfolge zurück. Athena verwendet verteilte Sortierung, um den Sortiervorgang parallel auf mehreren Knoten auszuführen.

Wenn Sie nicht unbedingt möchten, dass Ihr Ergebnis sortiert wird, vermeiden Sie das Hinzufügen einer ORDER BY-Klausel. Vermeiden Sie auch, innere Abfragen mit ORDER BY zu versehen, wenn sie nicht unbedingt notwendig sind. In vielen Fällen kann der Abfrageplaner überflüssige Sortierungen entfernen, dies kann jedoch nicht garantiert werden. Eine Ausnahme von dieser Regel ist, wenn eine interne Abfrage einen wichtigen N-Vorgang ausführt, z. B. die Suche nach den N neuesten oder N gängigsten Werten.

Wenn Athena ORDER BY zusammen mit LIMIT sieht, versteht es, dass Sie eine N-Top-Abfrage ausführen, und verwendet entsprechend dedizierte Vorgänge.

Anmerkung

Obwohl Athena häufig auch Fensterfunktionen wie row_number, die Top-N verwendet, erkennen kann, empfehlen wir die einfachere Version, die ORDER BY und LIMIT verwendet. Weitere Informationen finden Sie unter Reduzieren Sie den Umfang der Fensterfunktionen oder entfernen Sie sie.

Nur die erforderlichen Spalten einschließen

Wenn Sie eine Spalte nicht unbedingt benötigen, nehmen Sie sie nicht in Ihre Abfrage auf. Je weniger Daten eine Abfrage verarbeiten muss, desto schneller wird sie ausgeführt. Dies reduziert sowohl den benötigten Speicher als auch die Datenmenge, die zwischen den Knoten gesendet werden muss. Wenn Sie ein spaltenorientiertes Dateiformat verwenden, reduziert die Reduzierung der Anzahl der Spalten auch die Datenmenge, die aus Amazon S3 gelesen wird.

Athena hat keine spezifische Begrenzung für die Anzahl der Spalten in einem Ergebnis, aber die Art und Weise, wie Abfragen ausgeführt werden, begrenzt die mögliche kombinierte Größe von Spalten. Die kombinierte Größe von Spalten umfasst ihre Namen und Typen.

Der folgende Fehler wird beispielsweise durch eine Beziehung verursacht, die die Größenbeschränkung für einen Beziehungsdeskriptor überschreitet:

GENERIC_ INTERNAL _ERROR: io.airlift.bytecode. CompilationException

Um dieses Problem zu umgehen, reduzieren Sie die Anzahl der Spalten in der Abfrage, oder erstellen Sie Unterabfragen und verwenden Sie eine JOIN, die eine kleinere Datenmenge abruft. Wenn Sie Abfragen haben, die SELECT * in der äußersten Abfrage ausführen, sollten Sie die * in eine Liste mit nur den Spalten ändern, die Sie brauchen.

Optimieren Sie Abfragen mithilfe von Näherungen

Athena unterstützt Näherungsaggregatfunktionen zum Zählen verschiedener Werte, der häufigsten Werte, Perzentile (einschließlich ungefährer Mediane) und zum Erstellen von Histogrammen. Verwenden Sie diese Funktionen immer dann, wenn keine exakten Werte benötigt werden.

Im Gegensatz zu COUNT(DISTINCT col)-Vorgängen benötigt approx_distinct viel weniger Speicher und läuft schneller. In ähnlicher Weise werden bei der Verwendung von numeric_histogram anstelle von Histogramm Näherungsmethoden und damit weniger Speicher verwendet.

Optimieren LIKE

Sie können LIKE es verwenden, um passende Zeichenketten zu finden, aber bei langen Zeichenketten ist das rechenintensiv. Die Funktion regexp_like ist in den meisten Fällen eine schnellere Alternative und bietet auch mehr Flexibilität.

Oft können Sie eine Suche optimieren, indem Sie die gesuchte Teilzeichenfolge verankern. Wenn Sie beispielsweise nach einem Präfix suchen, ist es viel besser, 'zu verwendensubstr%' statt '%substr%'. Oder, falls du benutztregexp_like, '^substr'.

Verwenden Sie UNION ALL anstelle von UNION

UNION ALL und UNION sind zwei Möglichkeiten, die Ergebnisse von zwei Abfragen zu einem Ergebnis zu kombinieren. UNION ALL verkettet die Datensätze aus der ersten Abfrage mit der zweiten und UNION macht dasselbe, entfernt aber auch Duplikate. UNION muss alle Datensätze verarbeiten und die Duplikate finden, was speicher- und rechenintensiv ist, aber UNION ALL ist ein relativ schneller Vorgang. Sofern Sie Datensätze nicht deduplizieren müssen, verwenden Sie UNION ALL, um die beste Leistung zu erzielen.

UNLOADFür große Ergebnismengen verwenden

Wenn erwartet wird, dass die Ergebnisse einer Abfrage umfangreich sind (z. B. Zehntausende von Zeilen oder mehr), verwenden Sie diese Option, UNLOAD um die Ergebnisse zu exportieren. In den meisten Fällen ist dies schneller als das Ausführen einer normalen Abfrage, und die Verwendung von UNLOAD gibt Ihnen auch mehr Kontrolle über die Ausgabe.

Wenn die Ausführung einer Abfrage abgeschlossen ist, speichert Athena das Ergebnis als einzelne unkomprimierte CSV Datei auf Amazon S3. Dies dauert länger als UNLOAD, nicht nur, weil das Ergebnis unkomprimiert ist, sondern auch, weil der Vorgang nicht parallelisiert werden kann. Im Gegensatz dazu schreibt UNLOAD die Ergebnisse direkt von den Worker-Knoten und nutzt die Parallelität des Rechenclusters voll aus. Darüber hinaus können Sie so konfigurieren, UNLOAD dass die Ergebnisse in komprimiertem Format und in anderen Dateiformaten wie JSON Parquet geschrieben werden.

Weitere Informationen finden Sie unter UNLOAD.

Verwenden Sie CTAS oder GlueETL, um häufig verwendete Aggregationen zu materialisieren

Das „Materialisieren“ einer Abfrage ist eine Möglichkeit, die Abfrageleistung zu beschleunigen, indem vorab berechnete komplexe Abfrageergebnisse (z. B. Aggregationen und Verknüpfungen) zur Wiederverwendung in nachfolgenden Abfragen gespeichert werden.

Wenn viele Ihrer Abfragen dieselben Verknüpfungen und Aggregationen enthalten, können Sie die allgemeine Unterabfrage als neue Tabelle materialisieren und dann Abfragen für diese Tabelle ausführen. Sie können die neue Tabelle mit Erstellen Sie eine Tabelle aus Abfrageergebnissen (CTAS) oder einem speziellen ETL Tool wie Glue erstellenETL.

Nehmen wir zum Beispiel an, Sie haben ein Dashboard mit Widgets, die verschiedene Aspekte eines Auftragsdatensatzes zeigen. Jedes Widget hat seine eigene Abfrage, aber die Abfragen verwenden alle dieselben Verknüpfungen und Filter. Eine Bestelltabelle wird mit einer Einzelpostentabelle verknüpft, und es gibt einen Filter, der nur die letzten drei Monate anzeigt. Wenn Sie die gemeinsamen Features dieser Abfragen identifizieren, können Sie eine neue Tabelle erstellen, die von den Widgets verwendet werden kann. Dadurch wird Duplikation reduziert und die Leistung verbessert. Der Nachteil ist, dass Sie die neue Tabelle auf dem neuesten Stand halten müssen.

Abfrageergebnisse wiederverwenden

Es ist üblich, dass dieselbe Abfrage innerhalb eines kurzen Zeitraums mehrmals ausgeführt wird. Dies kann beispielsweise der Fall sein, wenn mehrere Personen dasselbe Daten-Dashboard öffnen. Wenn Sie eine Abfrage ausführen, können Sie Athena anweisen, zuvor berechnete Ergebnisse wiederzuverwenden. Sie geben das maximale Alter der Ergebnisse an, die wiederverwendet werden sollen. Wenn dieselbe Abfrage zuvor innerhalb dieses Zeitrahmens ausgeführt wurde, gibt Athena diese Ergebnisse zurück, anstatt die Abfrage erneut auszuführen. Weitere Informationen finden Sie unter Abfrageergebnisse in Athena wiederverwenden hier im Amazon-Athena-Benutzerhandbuch und im AWS -Big-Data-Blog Kosten reduzieren und die Abfrageleistung verbessern mit Amazon Athena Query Result Reuse.