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.
• DuckDB can also read cloud storage itself, with its httpfs and azure extensions and their secrets. Every view opens a connection of its own, so give the secrets as persistent secrets, which every connection loads.
• Once DuckDB reaches a storage, list its URL scheme in the url_schemes engine option, e.g. ["s3"]. The views then read it by URL.
• 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.

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.
  • Off the storages it reads itself, DuckDB reads through the explorer's filesystem.
  • DuckDB cannot read through filesystems without modification times, such as fsspec's HTTP and zip filesystems. There, it does not view Parquet.
• 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.
  • Files up to max_whole_file_bytes, 10 MB by default, are copied at once.
  • For larger files, you choose between copying the whole file and reading its first 10 MB into memory.
• DuckDB opens only local database files. The explorer copies other storages' databases to the kernel's disk first, and asks before copying one larger than max_whole_file_bytes.
• A copy is kept for 10 minutes after its last view closes, and while the remote file is unchanged. The copies are deleted when the kernel exits.
• A database shows its tables and views in a tree, by schema. The data browser opens the one you choose.
• "Python code" reads by URL the storages that DuckDB's extensions reach: local disks, S3, Google Cloud Storage, Azure, HTTP(S) and Hugging Face.
  • Elsewhere, such as on SFTP, the code registers fsspec's filesystem with DuckDB.
  • A remote database's code downloads it with fsspec, then opens it.

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 supports' 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.