2023-02-24

JupySQL-Plots mit DuckDB

Guest post by Eduardo Blancas

Einführung

Datenvisualisierung ist für alle in der Datenarbeit wesentlich: Muster werden sichtbar, die sonst schwer zu erkennen wären. Der übliche Weg für tabellarische Datensätze führt über Pandas und Matplotlib. Mit wachsenden Daten stößt das schnell an Grenzen, weil Pandas erheblichen Speicher-Overhead mitbringt – selbst mittlere Datensätze zu plotten wird schwierig.

In diesem Beitrag nutzen wir JupySQL und DuckDB, um Datensätze größer als der Speicher effizient auf dem Laptop zu plotten. JupySQL ist ein Fork von ipython-sql, der SQL-Zellen in Jupyter ergänzt und vom Team bei Ploomber aktiv gepflegt und weiterentwickelt wird.

JupySQL plus DuckDB ergibt eine starke, benutzerfreundliche lokale SQL-Verarbeitung, besonders mit JupySQLs neuen Plot-Funktionen. Keine dicken (und teuren!) EC2-Maschinen, kein komplexes verteiltes Framework! Starten Sie mit JupySQL und DuckDB über unseren Jupyter-Notebook-Guide oder direkt mit einem Beispiel-Colab-Notebook!

JupySQL soll die beste SQL-Erfahrung in Jupyter bieten. Feedback gern als Issue auf GitHub!

Das Problem

Eine wesentliche Grenze von pandas und matplotlib bei der Visualisierung: Alle Daten müssen in den Speicher, larger-than-memory-Datensätze sind schwer zu plotten. Durch den Overhead von pandas lassen sich zudem manche kleineren Datensätze nicht visualisieren, von denen man denkt, sie „passten“ in den Speicher.

Laden wir einen Beispiel-.parquet-Datensatz mit pandas, um den Speicher-Overhead zu zeigen:

from urllib.request import urlretrieve

_ = urlretrieve("https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet",
                "yellow_tripdata_2022-01.parquet")

Die heruntergeladene .parquet-Datei belegt 36 MB auf der Platte:

ls -lh *.parquet

-rw-r--r--  1 eduardo  staff    36M Jan 18 14:45 yellow_tripdata_2022-01.parquet

Laden wir die .parquet-Datei als DataFrame und schauen, wie viel Speicher sie braucht:

import pandas as pd

df = pd.read_parquet("yellow_tripdata_2022-01.parquet")

df_mb = df.memory_usage().sum() / (1024 ** 2)
print(f"Data frame takes {df_mb:.0f} MB")

Data frame takes 357 MB

Wie Sie sehen, brauchen wir fast 10× so viel Speicher wie die Dateigröße. Bei diesem Overhead müssen wir bei larger-than-memory viel konservativer sein: „Mittlere“ Dateien passen nach dem Laden oft nicht mehr. Das ist erst der Anfang der Speicherprobleme.

Beim Plotten müssen Daten oft vorverarbeitet werden, bevor sie sich visualisieren lassen. Ohne Vorsicht kopieren diese Schritte die Daten und treiben den Speicher hoch. Ein praktisches Beispiel.

Unser Beispieldatensatz enthält eine Beobachtung je NYC-Yellow-Cab-Fahrt im Januar 2022. Ein Boxplot für die Fahrstrecke:

import matplotlib.pyplot as plt

plt.boxplot(df.trip_distance)
_ = plt.title("Trip distance")

Wow! Manche New Yorker scheinen Taxifahrten wirklich zu mögen! Lassen wir die Taxi-Fans beiseite, um die Visualisierung zu verbessern, und berechnen das 99. Perzentil als Cutoff:

cutoff = df.trip_distance.quantile(q=0.99)
cutoff

19.7

Jetzt filtern wir Beobachtungen über dem Cutoff; vorher eine Hilfsfunktion für den Speicherverbrauch:

import psutil

def memory_used():
    """Returns memory used in MB"""
    mem = psutil.Process().memory_full_info().uss / (1024 ** 2)
    print(f"Memory used: {mem:.0f} MB")

memory_used()

Memory used: 941 MB

Jetzt die Beobachtungen filtern:

df_ = df[df.trip_distance < cutoff]

Das Histogramm plotten:

plt.boxplot(df_.trip_distance)
_ = plt.title("Trip distance (top 1% observations removed)")

Mit den entfernten Top-1%-Ausreißern sehen die Zahlen vernünftiger aus. Es gibt ein paar Fahrten über 10 Meilen (vielleicht Uptown-New-Yorker auf dem Weg nach Brooklyn zu leckerer Pizza?)

Wie viel Speicher nutzen wir jetzt?

memory_used()

Memory used: 1321 MB

380 MB mehr! Eine 36-MB-Parquet-Datei wurde nach Laden und einem Vorverarbeitungsschritt zu >700 MB im Speicher!

Mit pandas passt in der Praxis also viel weniger in den Speicher, als man denkt. Selbst mit 16 GB RAM im Laptop ist man bei der Datensatzgröße stark begrenzt. Natürlich ließe sich Speicher sparen, indem nur die geplottete Spalte geladen und unnötige Kopien gelöscht werden – in der Praxis passiert das nie. Beim Explorieren weiß man selten im Voraus, welche Spalten nötig sind; die Zeit ist besser in Analyse und Visualisierung investiert als ins manuelle Löschen von Kopien.

Angesichts dieser Herausforderung käme ein verteiltes Framework in Frage; das macht den Prozess aber viel komplexer und löst das Problem nur teilweise, weil Statistiken verteilt berechnet werden müssten. Alternativ eine größere Maschine – vergleichsweise einfach (aber teuer!), wenn Cloud-Ressourcen verfügbar sind. Trotzdem müssen Daten verschoben, eine neue Umgebung eingerichtet werden usw. Zum Glück gibt es DuckDB!

DuckDB: Ein stark skalierbares Backend für statistische Visualisierungen

Funktionen wie hist (Histogramm) oder boxplot machen in matplotlib zwei Schritte:

1. Zusammenfassungsstatistiken berechnen
2. Daten plotten

boxplot ruft zum Beispiel boxplot_stats auf, das die zum Zeichnen nötigen Statistiken liefert. Für einen Boxplot brauchen wir mehrere Zusammenfassungsstatistiken, etwa das 25., 50. und 75. Perzentil. Das folgende Diagramm zeigt einen Boxplot mit Beschriftungen:

Der Flaschenhals beim Ansatz pandas + matplotlib ist die Funktion boxplot_stats: Sie verlangt ein numpy.array oder eine pandas.Series und zwingt uns, alle Daten in den Speicher zu laden. Wir können aber eine neue Version von boxplot_stats bauen, die die Aggregation an eine andere analytische Engine schiebt.

Wir haben DuckDB gewählt, weil es extrem leistungsfähig und einfach zu nutzen ist. Kein Server, keine komplexe Konfiguration: pip install, auf die Datendateien zeigen – und schon lassen sich Millionen von Datenpunkten aggregieren.

Die vollständige Implementierung steht hier; im Wesentlichen haben wir matplotlibs boxplot_stats von Python nach SQL übersetzt. Die folgende Abfrage berechnet die drei nötigen Perzentile: 25., 50. (Median) und 75.:

%load_ext sql
%sql duckdb://

%%sql
-- We calculate the percentiles all at once and
-- then convert from list format into separate columns
-- (Improving performance by reducing duplicate work)
WITH stats AS (
  SELECT
    percentile_disc([0.25, 0.50, 0.75]) WITHIN GROUP
      (ORDER BY "trip_distance") AS percentiles
  FROM 'yellow_tripdata_2022-01.parquet'
)
SELECT
  percentiles[1] AS q1,
  percentiles[2] AS median,
  percentiles[3] AS q3
FROM stats;

Sobald alle Statistiken da sind, rufen wir bxp auf; die Funktion zeichnet den Boxplot aus den Eingabestatistiken.

Dieser Ablauf steckt bereits in JupySQL; einen Boxplot erzeugen Sie mit %sqlplot boxplot. Schauen wir uns das an. Zuerst der Speicherverbrauch, zum Vergleich mit der pandas-Version:

memory_used()

Memory used: 1351 MB

Den Boxplot erzeugen:

%sqlplot boxplot --table yellow_tripdata_2022-01.parquet --column trip_distance

Wieder all diese Ausreißer. Den Cutoff berechnen:

%%sql
SELECT percentile_disc(0.99) WITHIN GROUP (ORDER BY trip_distance)
FROM 'yellow_tripdata_2022-01.parquet'

Eine Abfrage, die das obere 1 % der Beobachtungen herausfiltert. Die Option --save speichert den SQL-Ausdruck; wir führen ihn nicht aus.

%%sql --save no-outliers --no-execute
SELECT *
FROM 'yellow_tripdata_2022-01.parquet'
WHERE trip_distance < 19.7;

no-outliers können wir jetzt in %sqlplot boxplot nutzen:

%sqlplot boxplot --table no-outliers --column trip_distance --with no-outliers

memory_used()

Memory used: 1375 MB

Der Speicherverbrauch blieb praktisch gleich (23 MB Unterschied, vor allem durch neu importierte Module). Weil DuckDB die Aggregation übernimmt, lädt, aggregiert und gibt die SQL-Engine den Speicher frei, sobald wir fertig sind – deutlich effizienter, als alle Daten auf einmal zu laden und ungewollte Kopien zu behalten.

Histogram-Statistiken mit DuckDB berechnen

Das Rezept lässt sich auf andere statistische Visualisierungen übertragen, etwa Histogramme.

Ein Histogramm zeigt die Verteilung eines Datensatzes und macht Muster wie Modalität, Ausreißer, Wertebereich usw. sichtbar. Wie beim Boxplot heißt das mit pandas + matplotlib: alle Daten auf einmal in den Speicher; dann aggregiert und plottet matplotlib.

Wir schieben die Aggregation nach DuckDB: Bin-Positionen (X-Achse) und Höhen (Y-Achse), dann übergeben wir das an matplotlibs Funktion bar für das Histogramm.

Die Implementierung hat zwei Schritte.

Zuerst, bei der vom Nutzer gewählten Bin-Zahl (N_BINS), berechnen wir BIN_SIZE:

%%sql
SELECT (max(trip_distance) - min(trip_distance)) / N_BINS
FROM 'yellow_tripdata_2022-01.parquet';

Dann, mit BIN_SIZE, die Zahl der Beobachtungen je Bin:

%%sql
SELECT
    floor("trip_distance" / BIN_SIZE) * BIN_SIZE,
    count(*) AS count
FROM 'yellow_tripdata_2022-01.parquet'
GROUP BY 1
ORDER BY 1;

Die Idee der zweiten Abfrage: Bei N_BINS weist floor("trip_distance" / BIN_SIZE) jede Beobachtung ihrem Bin zu (1, 2, …, N_BINS); die Multiplikation mit der Bin-Größe ergibt den Wert auf der X-Achse, der Count den Wert auf der Y-Achse. Danach rufen wir die Plot-Funktion bar auf.

All diese Schritte stecken im Befehl %sqplot histogram:

%sqlplot histogram --table no-outliers --column trip_distance --with no-outliers

Schlussbemerkung

Dieser Beitrag hat einen starken Ansatz zum Plotten großer Datensätze mit JupySQL und DuckDB gezeigt. Wer große Datensätze visualisieren muss, findet in DuckDB unvergleichliche Einfachheit und Flexibilität.

Bei Ploomber bauen wir einen vollwertigen SQL-Client für Jupyter! Features wie automatisches Dataset-Profiling, Autovervollständigung und mehr kommen! Also Updates im Blick behalten auf LinkedIn! Wenn Ihnen Features fehlen, damit SQL in Jupyter richtig gut wird, öffnen Sie bitte ein Issue!

JupySQL ist ein aktiv gepflegter Fork von ipython-sql und bleibt vollständig kompatibel. Mehr im GitHub-Repository und in der Dokumentation.

Selbst ausprobieren

Zum Ausprobieren dieses Colab-Notebook oder das folgende Snippet in Jupyter:

from urllib.request import urlretrieve

urlretrieve("https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet",
            "yellow_tripdata_2022-01.parquet")

%pip install jupysql duckdb-engine --quiet
%load_ext sql
%sql duckdb://
%sqlplot boxplot --table yellow_tripdata_2022-01.parquet --column trip_distance

Hinweis: Befehle, die mit % oder %% beginnen, funktionieren nur in Jupyter/IPython. In einer normalen Python-Sitzung siehe die Python-API.