jupyter-storage-explorer-duckdb-support

DuckDB support for Storage Explorer, the Jupyter Storage Explorer plugin for DuckDB as an engine: the data browser on Parquet, CSV, JSON Lines, Delta Lake tables and DuckDB databases, and code loading them.

Installation

pip install "jupyter-storage-explorer[duckdb]"          # the plugin and its libraries
pip install "jupyter-storage-explorer[duckdb-support]"  # the plugin only, with the libraries you have

The libraries are not the plugin's dependencies, so that installing it never changes those of a shared environment. The first command installs them, as does jupyter-storage-explorer-duckdb-support[full]:

Delta Lake tables need DuckDB's delta extension, not a Python package. DuckDB downloads it at the first query, unless its settings or your network forbid it. Install it beforehand with duckdb.install_extension("delta") where the kernel cannot download it.

Prerequisites

• DuckDB reads local files itself. On other storages, the plugin reads through the explorer's filesystem, with the explorer's credentials: DuckDB needs no configuration.
• To read cloud storage itself, DuckDB needs credentials: see Authentication.
• Delta Lake tables are read only by URL: on local disks and the storages of url_schemes. DuckDB cannot read them through the explorer's filesystem.

Authentication

DuckDB reads cloud storage itself with its httpfs and azure extensions. It is quicker than through the explorer's filesystem, and the only way to view Delta Lake tables there.

Give the credentials as DuckDB persistent secrets. Every view opens a connection of its own, and every connection loads them. The credential_chain provider finds the credentials as the cloud's SDK does:

import duckdb

duckdb.sql(
    "CREATE PERSISTENT SECRET (TYPE s3, PROVIDER credential_chain, PROFILE 'personal')"
)
duckdb.sql(
    "CREATE PERSISTENT SECRET (TYPE azure, PROVIDER credential_chain, ACCOUNT_NAME 'account')"
)

Then list the storages' URL schemes in the url_schemes engine option, so that the views read them by URL:

StorageExplorer(
    "abfs://lake@account.dfs.core.windows.net",
    engine_options={"duckdb": {"*": {"url_schemes": ["s3", "abfs"]}}},
)

How DuckDB reads your data

Parquet files and folders are read lazily: DuckDB reads only the columns and row groups that each page, filter or sort needs. It cannot read Parquet through filesystems without modification times, such as fsspec's HTTP and zip filesystems.

DuckDB reads a whole CSV or JSON Lines file again for each filter, sort or count. Off the storages it reads itself, the explorer first copies the file to the kernel's disk. For files over max_whole_file_bytes, 10 MB by default, you choose between copying the whole file and reading its first 10 MB.

A database shows its tables and views in a tree, by schema, and the data browser opens the one you choose. DuckDB opens only local database files, so the explorer copies other storages' databases to the kernel's disk first. It asks first for files over max_whole_file_bytes.

Plugin options

plugin_options gives the plugin's options:

StorageExplorer(
    "s3://my-bucket/lake", plugin_options={"duckdb-support": {"priority": 10}}
)

In the launcher's tabs, plugin_options() in launcher.py gives them:

def plugin_options():
    return {"duckdb-support": {"enabled": False}}

Engine options

The plugin's options, in engine_options["duckdb"] (by format, or "*" for all), for the data browser and the code:

StorageExplorer(
    "s3://my-bucket/lake",
    engine_options={
        "duckdb": {
            "*": {"config": {"threads": 4}, "url_schemes": ["s3"]},
            "csv": {"na_values": ["NA"], "date_format": "%d/%m/%Y"},
            "delta": {"version": 3},
        },
    },
)

Every format takes:

The other options are each format's reader's arguments, of the same names, with their descriptions from DuckDB's documentation. Those not set are not passed, so DuckDB's defaults, shown below, apply, except where the explorer sets its own.

Parquet (parquet)

ParquetByDuckDBOptions: the arguments of read_parquet.

CSV (csv)

CsvByDuckDBOptions: the arguments of read_csv. DuckDB's sniffer detects the dialect, the header and the types of the options marked "detected".

JSON Lines (json-lines)

JsonLinesByDuckDBOptions: the arguments of read_json.

Delta Lake (delta)

DeltaLakeByDuckDBOptions: the named parameters of the delta extension's delta_scan.

DuckDB databases (duckdb)

DuckDBOptions: the options every format takes. The database is opened read only, with config.