2021-11-26

DuckDB – Der Herr der Enums: Die Gefährten der Categorical und Factors

Pedro Holanda

String-Typen gehören zu den am häufigsten genutzten Typen. Oft haben String-Spalten aber nur wenige verschiedene Werte. Eine Länderspalte hat zum Beispiel nie mehr als ein paar hundert eindeutige Einträge. Einen solchen Typ als einfachen String zu speichern, verschwendet Speicher und kostet Abfrageleistung. Besser ist, diese Spalten dictionary-kodiert zu speichern. Bei der Dictionary-Kodierung wird die Spalte in zwei Teile geteilt: die Kategorie und die Werte. Die Kategorie speichert die eigentlichen Strings, die Werte speichern einen Verweis auf die Strings. Die Kodierung ist unten dargestellt.

Früher haben Nutzer Dictionary-Kodierung manuell umgesetzt, indem sie Lookup-Tabellen angelegt und IDs per Join zurückübersetzt haben. Umgebungen wie Pandas und R unterstützen diese Typen eleganter. Pandas Categorical und R Factors sind Typen, mit denen String-Spalten mit vielen Duplikaten über Dictionary-Kodierung effizient gespeichert werden.

Dictionary-Kodierung spart nicht nur enorm Speicher, sie erlaubt dem System auch, mit Zahlen statt Strings zu arbeiten, was die Abfrageleistung stark steigert. Durch geringeren RAM-Verbrauch ermöglichen ENUMs DuckDB außerdem, deutlich größere Datensätze zu verarbeiten.

Damit DuckDB sich vollständig in diese kodierten Strukturen einfügt, haben wir Enum-Typen umgesetzt. Dieser Beitrag zeigt Code-Snippets zur Nutzung von ENUM-Typen über die SQL-API und die Python-/R-Clients und die Leistungsvorteile von Enums gegenüber normalen Strings. Soweit wir wissen, ist DuckDB das erste RDBMS, das nativ mit Pandas-Categorical-Spalten und R-Factors integriert.

SQL

Unsere Enum-SQL-Syntax ist stark von Postgres inspiriert. Unten zeigen wir, wie der Typ ENUM angelegt und genutzt wird.

CREATE TYPE lotr_race AS ENUM ('Mayar', 'Hobbit', 'Orc');

CREATE TABLE character (
    name text,
    race lotr_race
);

INSERT INTO character VALUES ('Frodo Quackins','Hobbit'), ('Quackalf ', 'Mayar');

-- We can perform a normal string comparison
-- Note that 'Hobbit' will be cast to a lotr_race
-- hence this comparison is actually a fast integer comparison
SELECT name FROM character WHERE race = 'Hobbit';
----
Frodo Quackins

ENUM-Spalten verhalten sich genau wie normale VARCHAR-Spalten. Sie können in String-Funktionen (etwa LIKE oder substring) genutzt, verglichen, sortiert werden usw. Die einzige Ausnahme: ENUM-Spalten können nur die Werte halten, die in der Enum-Definition angegeben sind. Das Einfügen eines Werts, der nicht zur Definition gehört, führt zu einem Fehler.

DuckDB-ENUMs sind derzeit statisch (Werte können nach der ENUM-Definition nicht hinzugefügt oder entfernt werden). ENUM-Updates stehen jedoch auf der Roadmap für die nächste Version.

Mehr dazu in der Dokumentation.

Python

Setup

Zuerst müssen wir DuckDB und Pandas installieren. Die Installation beider Bibliotheken in Python ist unkompliziert:

# Python Install
pip install duckdb
pip install pandas

Nutzung

Pandas-Spalten vom Typ categorical werden direkt in DuckDBs ENUM-Typen gewandelt:

import pandas as pd
import duckdb

# Our unencoded data.
data = ['Hobbit', 'Elf', 'Elf', 'Man', 'Mayar', 'Hobbit', 'Mayar']

# 'pd.Categorical' automatically encodes the data as a categorical column
df_in = pd.DataFrame({'races': pd.Categorical(data),})

# We can query this dataframe as we would any other
# The conversion from categorical columns to enums happens automatically
df_out = duckdb.execute("SELECT * FROM df_in").df()

R

Setup

Wir müssen DuckDB nur in unserem R-Client installieren, dann kann es losgehen.

# R Install
install.packages("duckdb")

Nutzung

Ähnlich wie im vorherigen Beispiel mit Pandas werden R-Factor-Spalten automatisch in DuckDBs ENUM-Typen gewandelt.

library ("duckdb")

con <- dbConnect(duckdb::duckdb())
on.exit(dbDisconnect(con, shutdown = TRUE))

# Our unencoded data.
data <- c('Hobbit', 'Elf', 'Elf', 'Man', 'Mayar', 'Hobbit', 'Mayar')

# Our R dataframe holding an encoded version of our data column
# 'as.factor' automatically encodes it.
df_in <- data.frame(races=as.factor(data))


duckdb::duckdb_register(con, "characters", df_in)
df_out <- dbReadTable(con, "characters")

Benchmark-Vergleich

Um die Leistung von DuckDB bei Operationen auf kategorialen Spalten von Pandas-DataFrames zu zeigen, stellen wir einige Benchmarks vor. Der Quellcode liegt auf GitHub. In unseren Benchmarks konsumieren und produzieren wir immer Pandas-DataFrames.

Datensatz

Unser Datensatz besteht aus einem DataFrame mit 4 Spalten und 10 Millionen Zeilen. Die ersten beiden Spalten heißen race und subrace und stehen für Rassen. Beide sind kategorial, mit denselben Kategorien, aber unterschiedlichen Werten. Die anderen beiden Spalten race_string und subrace_string sind die String-Darstellungen von race und subrace.

def generate_df(size):
  race_categories = ['Hobbit', 'Elf', 'Man', 'Mayar']
  race = np.random.choice(race_categories, size)
  subrace = np.random.choice(race_categories, size)
  return pd.DataFrame({'race': pd.Categorical(race),
                       'subrace': pd.Categorical(subrace),
                       'race_string': race,
                       'subrace_string': subrace,})

size = pow(10,7) #10,000,000 rows
df = generate_df(size)

Gruppierte Aggregation

Im Benchmark zur gruppierten Aggregation zählen wir, wie viele Figuren jeder Rasse in der Spalte race bzw. race_string unserer Tabelle vorkommen.

def duck_categorical(df):
  return con.execute("SELECT race, count(*) FROM df GROUP BY race").df()

def duck_string(df):
  return con.execute("SELECT race_string, count(*) FROM df GROUP BY race_string").df()

def pandas(df):
  return df.groupby(['race']).agg({'race': 'count'})

def pandas_string(df):
  return df.groupby(['race_string']).agg({'race_string': 'count'})

Die Tabelle unten zeigt die Zeiten dieser Operation. Man sieht den Vorteil des Gruppierens über kodierte Werte statt Strings: DuckDB ist 4× schneller, wenn es über kleine vorzeichenlose Werte gruppiert.

Filter

Im Filter-Benchmark zählen wir, wie viele Hobbit-Figuren in der Spalte race bzw. race_string unserer Tabelle vorkommen.

def duck_categorical(df):
  return con.execute("SELECT count(*) FROM df WHERE race = 'Hobbit'").df()

def duck_string(df):
  return con.execute("SELECT count(*) FROM df WHERE race_string = 'Hobbit'").df()

def pandas(df):
  filtered_df = df[df.race == "Hobbit"]
  return filtered_df.agg({'race': 'count'})

def pandas_string(df):
  filtered_df = df[df.race_string == "Hobbit"]
  return filtered_df.agg({'race_string': 'count'})

Beim DuckDB-Enum-Typ wandelt DuckDB den String Hobbit in einen Wert im ENUM um, der ein vorzeichenloser Integer ist. Dann können wir schnelle numerische Vergleiche statt teurer String-Vergleiche machen, was die Leistung stark verbessert.

Enum–Enum-Vergleich

In diesem Benchmark vergleichen wir unsere beiden Zuchtspalten auf Gleichheit: race und subrace bzw. race_string und subrace_string.

def duck_categorical(df):
  return con.execute("SELECT count(*) FROM df WHERE race = subrace").df()

def duck_string(df):
  return con.execute("SELECT count(*) FROM df WHERE race_string = subrace_string").df()

def pandas(df):
  filtered_df = df[df.race == df.subrace]
  return filtered_df.agg({'race': 'count'})

def pandas_string(df):
  filtered_df = df[df.race_string == df.subrace_string]
  return filtered_df.agg({'race_string': 'count'})

DuckDB-ENUMs können direkt über ihre kodierten Werte verglichen werden. Das ergibt einen ähnlichen Zeitunterschied wie zuvor, wieder weil wir numerische Werte statt Strings vergleichen können.

Speicher

In diesem Benchmark vergleichen wir die Speichereinsparung von ENUM-Typen gegenüber Strings.

race_categories = ['Hobbit', 'Elf', 'Man','Mayar']
race = np.random.choice(race_categories, size)
categorical_race = pd.DataFrame({'race': pd.Categorical(race),})
string_race = pd.DataFrame({'race': race,})
con = duckdb.connect('duck_cat.db')
con.execute("CREATE TABLE character AS SELECT * FROM categorical_race")
con = duckdb.connect('duck_str.db')
con.execute("CREATE TABLE character AS SELECT * FROM string_race")

Die Tabelle unten zeigt die Unterschiede der DuckDB-Dateigröße, wenn dieselbe Spalte als Enum oder als einfacher String gespeichert wird. Weil die Dictionary-Kodierung die String-Werte nicht wiederholt, sehen wir eine Reduktion um eine Größenordnung.

Was ist mit den Fortsetzungen?

Es gibt drei Hauptrichtungen, die wir in den folgenden DuckDB-Versionen zu ENUMs verfolgen.

1. Automatische Speicherkodierung: Wie in der Einleitung beschrieben, definieren Nutzer Datenbankspalten häufig als Strings, obwohl sie eigentlich ENUMs sind. Unsere Idee ist, diese Spalten automatisch zu erkennen und dictionary-kodiert zu speichern, ohne Eingabe des Nutzers und völlig unsichtbar für ihn.
2. ENUM-Updates: Wie in der Einleitung gesagt, sind unsere ENUMs derzeit statisch. Wir werden das Einfügen und Entfernen von ENUM-Kategorien erlauben.
3. Integration mit anderen Datenformaten: Wir wollen unsere Integration mit Datenformaten ausbauen, die ENUM-ähnliche Strukturen umsetzen.

Feedback

Wenn Sie Probleme bei der Nutzung unserer ENUMs haben, öffnen Sie bitte ein Issue in unserem Issue-Tracker!