DuckDB (native vs SQLAlchemy)

Beginning in 0.9, JupySQL supports DuckDB via a native connection and SQLAlchemy, both with comparable performance. JupySQL adds a small overhead; however, this overhead is constant.

At the moment, the only difference is that some features are only available when using SQLAlchemy.

Performance comparison (pandas)

import pandas as pd
import numpy as np

num_rows = 1_000_000
num_cols = 100

df = pd.DataFrame(np.random.randn(num_rows, num_cols))

Raw DuckDB

import duckdb

conn = duckdb.connect()

%%timeit
conn.execute("SELECT * FROM df").df()

1.9 s ± 129 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

DuckDB + SQLALchemy

%load_ext sql
%config SqlMagic.autopandas = True
%config SqlMagic.displaycon = False
%sql duckdb:// --alias duckdb-sqlalchemy

%%timeit
_ = %sql SELECT * FROM df

1.85 s ± 11.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

DuckDB + native

%sql conn --alias duckdb-native

%%timeit
_ = %sql SELECT * FROM df

1.83 s ± 22.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Performance comparison (polars)

%config SqlMagic.autopolars = True
%sql duckdb-sqlalchemy

Disabled 'autopandas' since 'autopolars' was enabled.

Switching to connection duckdb-sqlalchemy

Raw DuckDB

%%timeit
conn.execute("SELECT * FROM df").pl()

1.04 s ± 3.26 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

DuckDB + SQLAlchemy

%%timeit
_ = %sql SELECT * FROM df

1.06 s ± 4.26 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

DuckDB + native

%sql duckdb-native

Switching to connection duckdb-native

%%timeit
_ = %sql SELECT * FROM df

1.05 s ± 6.31 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Limitations of using native connections

As of version 0.9.0, the only caveat is that %sqlcmd won’t work with a native connection.

%sqlcmd

UsageError: Missing argument for %sqlcmd. Valid commands are: tables, columns, test, profile, explore, snippets