H5DB API Reference

This document describes the public SQL functions provided by the h5db extension.

Contents

• Table Functions
  • h5_tree(filename_or_filenames, projected_attributes...)
  • h5_ls(filename_or_filenames[, group_path], projected_attributes...)
  • h5_read(filename_or_filenames, dataset_path, ...)
  • h5_attributes(filename_or_filenames, object_path)
• Scalar Functions
  • h5_first_file(filename_or_filenames)
  • h5_ls(filename, group_path[, projected_attributes...])
  • h5_ls_swmr(filename, group_path[, projected_attributes...])
  • h5_read(filename, dataset_path)
  • h5_attributes(filename, object_path)
  • h5_rse(run_starts_path, values_path)
  • h5_ree(run_ends_path, values_path)
  • h5_index()
  • h5_attr([name[, default_value]])
  • h5_alias(name, definition)
• Test Functions
  • h5db_version()
• Remote Access
  • SFTP Secrets
• Multi-File Inputs and Globbing
  • Expansion and Read Semantics
  • Filename Filter Pruning
  • Examples
• Settings
  • h5db_swmr_default (BOOLEAN)
  • h5db_batch_size (VARCHAR)
  • h5db_scalar_read_memory_limit (VARCHAR)
• Type Mapping
  • HDF5 to DuckDB Type Conversion
  • Multi-Dimensional Arrays
• Error Handling
• Performance Notes
• Limitations
• See Also

Table Functions

h5_tree(filename_or_filenames, projected_attributes...)

Recursively lists namespace entries in an HDF5 file. The result is path-oriented: each absolute namespace path is emitted as its own row, even when multiple paths resolve to the same underlying object. Selected HDF5 attributes can be projected as additional columns with h5_attr(...).

Parameters:

• filename_or_filenames (VARCHAR or VARCHAR[]): Local path or remote URL to the HDF5 file, a glob pattern, or a list of exact filenames/URLs and/or glob patterns. See Remote Access and Multi-File Inputs and Globbing.
• projected_attributes (variadic, optional): Zero or more projected attribute markers:
  • h5_attr()
  • h5_attr(name)
  • h5_attr(name, default_value)
  • h5_alias(alias_name, h5_attr(...))
• filename (BOOLEAN or VARCHAR, named, optional): Add a visible filename column. true uses the column name filename; a string uses the provided column name instead. Without this option, hidden virtual filename remains available by explicit reference.
• swmr (BOOLEAN, named, optional): Open in SWMR read mode (default: false)

Returns: Table with columns:

• path (VARCHAR): Absolute namespace path for the emitted row
• type (VARCHAR): Entry type:
  • group
  • dataset
  • datatype
  • link for dangling or otherwise unresolved local links
  • external for external links
• dtype (VARCHAR): Data type for datasets (e.g., int32, float64, string)
• shape (UBIGINT[]): Dataset dimensions
  • NULL for non-dataset rows
  • [] for scalar datasets
  • [d0, d1, ...] for array datasets
• one additional column per projected attribute
  • h5_attr() produces column h5_attr with type MAP(VARCHAR, VARIANT)
  • h5_attr(name) produces column name with type VARIANT
  • h5_attr(name, default_value) produces column name with the type of default_value
  • h5_alias(...) overrides the default output name
• hidden virtual column filename (VARCHAR), available by explicit reference

Traversal Semantics:

• / is returned as a group row.
• Hard-link aliases, soft-link aliases, and other namespace aliases are path-complete: if two paths resolve to the same local object, both paths appear.
• Resolved group aliases are traversed recursively in a cycle-safe way.
• Dangling links are returned as type = 'link' leaf rows.
• External links are returned as type = 'external' leaf rows and are not traversed.
• Projected attributes and dataset metadata are populated only for resolved local rows. Unresolved and external rows use projected defaults.
• Row order is left unspecified to allow efficient reads, use ORDER BY if the order of the output matters.

Projected Attributes:

• Projected attributes are declared with h5_attr(...) and may be renamed with h5_alias(...).
• Only resolved local rows read attribute values. Unresolved and external rows use projected defaults.
• Projected output names must be unique under DuckDB's case-insensitive identifier matching; duplicate projected names or collisions with path, type, dtype, or shape fail at bind time.
• See h5_attr(...) below for bind-time rules, default handling, string behavior, and supported attribute forms.

Example:

SELECT * FROM h5_tree('data.h5');
SELECT * FROM h5_tree('data.h5', swmr := true);
SELECT * FROM h5_tree('runs/run_*.h5');
SELECT * FROM h5_tree(['runs/calibration.h5', 'runs/run_*.h5']);

-- Project an attribute as an extra column
SELECT path, type, NX_class
FROM h5_tree(
    'data.h5',
    h5_attr('NX_class', NULL::VARCHAR)
);

-- Distinguish identical paths across multiple files
SELECT filename, path
FROM h5_tree('runs/run_*.h5')
WHERE path = '/entry/data';

-- Project all attributes as one map-valued column
SELECT path, h5_attr
FROM h5_tree(
    'data.h5',
    h5_attr()
);

-- Rename a projected attribute column
SELECT path, type, time
FROM h5_tree(
    'data.h5',
    h5_alias('time', h5_attr('count_time', 0::DOUBLE))
);

---

h5_ls(filename_or_filenames[, group_path], projected_attributes...)

Lists the immediate children of a group. This is the shallow counterpart to h5_tree(). The scalar form is documented below.

The table form:

SELECT * FROM h5_ls('data.h5');
SELECT * FROM h5_ls('data.h5', '/entry/instrument');

returns the same row shape as h5_tree(), but only for the immediate children of the requested group.

Parameters:

• filename_or_filenames (VARCHAR or VARCHAR[]): Local path or remote URL to the HDF5 file, a glob pattern, or a list of exact filenames/URLs and/or glob patterns. See Remote Access and Multi-File Inputs and Globbing.
• group_path (VARCHAR, optional): Group path to list. Defaults to / in the table form.
• projected_attributes (variadic, optional): Zero or more projected attribute markers:
  • h5_attr()
  • h5_attr(name)
  • h5_attr(name, default_value)
  • h5_alias(alias_name, h5_attr(...))
• filename (BOOLEAN or VARCHAR, named, optional): Add a visible filename column. true uses the column name filename; a string uses the provided column name instead. Without this option, hidden virtual filename remains available by explicit reference.
• swmr (BOOLEAN, named, optional): Open in SWMR read mode (default: false)

Returns: Table with the same row shape as h5_tree(), plus hidden virtual column filename (VARCHAR)

Semantics:

• the path must resolve to a group, otherwise the function errors
• only immediate children are returned; the requested group itself is not returned
• external groups can be listed if the external target resolves successfully
• row typing, dtype, and shape are the same as h5_tree()
• projected attributes use the same h5_attr(...) semantics
• projected output names must be unique under DuckDB's case-insensitive identifier matching and must not collide with path, type, dtype, or shape

Examples:

-- List the root group's immediate children
SELECT * FROM h5_ls('data.h5');

-- List one specific group
SELECT * FROM h5_ls('data.h5', '/entry/instrument');

-- List the same group across multiple files
SELECT * FROM h5_ls('runs/run_*.h5', '/entry/instrument');

-- Project attributes on the immediate children
SELECT path, NX_class
FROM h5_ls(
    'data.h5',
    '/entry/instrument',
    h5_attr('NX_class', NULL::VARCHAR)
);

---

h5_read(filename_or_filenames, dataset_path, ...)

Reads data from one or more datasets in an HDF5 file.

Parameters:

• filename_or_filenames (VARCHAR or VARCHAR[]): Local path or remote URL to the HDF5 file, a glob pattern, or a list of exact filenames/URLs and/or glob patterns. See Remote Access and Multi-File Inputs and Globbing.
• dataset_path (VARCHAR or STRUCT): Dataset path(s) to read. Use h5_rse() or h5_ree() for run-encoded columns
• h5_index() can be provided to add a virtual index column named index
• h5_alias(name, definition) can be used to rename a column definition
• Additional dataset paths can be provided (variadic arguments)
• filename (BOOLEAN or VARCHAR, named, optional): Add a visible filename column. true uses the column name filename; a string uses the provided column name instead. Without this option, hidden virtual filename remains available by explicit reference.
• swmr (BOOLEAN, named, optional): Open in SWMR read mode (default: false)

Returns: Table with one column per dataset, plus hidden virtual column filename (VARCHAR)

Column Naming: Column names are extracted from the last component of the dataset path, for example /group/data becomes data. Output names must be unique under DuckDB's case-insensitive identifier matching. Use h5_alias(...) when two dataset paths or generated columns would otherwise collide.

Scalar Datasets:

• Scalar (rank-0) datasets are returned as constant columns.
• If all selected datasets are scalar, h5_read() returns a single row.
• If any non-scalar dataset is present, scalar columns are broadcast to the row count of the non-scalar datasets.
• If multiple non-scalar regular datasets are selected, the output row count is the minimum outer dimension among them.
• Datasets with an HDF5 null dataspace are read like scalar datasets with a NULL value.

Multi-file Semantics:

• Rows are concatenated file by file.
• h5_index() is the outermost-dimension row index within each matched file.
• Duplicate filename matches are preserved.
• filename identifies which file produced each row.
• All matched files must have compatible column definitions.

Type Support:

• Numeric: int8, int16, int32, int64, uint8, uint16, uint32, uint64, float16, float32, float64
• Strings: fixed-length and variable-length
• Arrays: 1D-4D multi-dimensional arrays

Examples:

-- Read a single dataset
SELECT * FROM h5_read('data.h5', '/measurements');

-- Read multiple datasets
SELECT * FROM h5_read('data.h5', '/timestamps', '/temperatures', '/pressures');

-- Read from nested groups
SELECT * FROM h5_read('data.h5', '/experiment1/group_a/data');

-- Read matching local or SFTP files
SELECT * FROM h5_read('runs/run_*.h5', '/counts');
SELECT * FROM h5_read('sftp://beamline.example.org/data/run_*.h5', '/entry/data');

-- Expand a list of exact files and/or patterns in order
SELECT * FROM h5_read(
    ['runs/calibration.h5', 'runs/run_*.h5'],
    '/counts'
);

-- Materialize filename into SELECT *
SELECT *
FROM h5_read('runs/run_*.h5', '/counts', filename := true);

-- Mix regular and run-start encoded columns
SELECT * FROM h5_read(
    'data.h5',
    '/timestamp',
    h5_rse('/status_run_starts', '/status_values')
);

-- Add a virtual index column
SELECT * FROM h5_read('data.h5', h5_index(), '/measurements');

-- Rename a column definition
SELECT * FROM h5_read('data.h5', h5_alias('idx', h5_index()), '/measurements');

-- Enable SWMR read mode
SELECT * FROM h5_read('data.h5', '/measurements', swmr := true);

---

h5_attributes(filename_or_filenames, object_path)

Reads attributes from an object or the file root.

Parameters:

• filename_or_filenames (VARCHAR or VARCHAR[]): Local path or remote URL to the HDF5 file, a glob pattern, or a list of exact filenames/URLs and/or glob patterns. See Remote Access and Multi-File Inputs and Globbing.
• object_path (VARCHAR): Path to the target object, or / for root
• filename (BOOLEAN or VARCHAR, named, optional): Add a visible filename column. true uses the column name filename; a string uses the provided column name instead. Without this option, hidden virtual filename remains available by explicit reference.
• swmr (BOOLEAN, named, optional): Open in SWMR read mode (default: false)

Returns: One row per matched file where each non-virtual column represents one attribute

• Column names are the attribute names
• Column types match the attribute types (numeric, string, or arrays)
• If the target object has no attributes, the function raises IO Error: Object has no attributes: ... in file: ...
• Invalid UTF-8 string attribute values raise an error in table-valued h5_attributes()
• Multiple matched files must have the same attribute names, types, and order.
• Attribute output names must be unique under DuckDB's case-insensitive identifier matching.
• Hidden virtual column filename (VARCHAR), available by explicit reference.

Type Support:

• Numeric scalars and string scalars
• Simple numeric and string attribute dataspaces up to rank 4, returned as nested DuckDB LISTs
• HDF5 H5T_ARRAY-typed attributes are supported when they are 1D
• Empty numeric and string attribute arrays are supported
• H5S_NULL attributes are returned as typed NULL

Examples:

-- Read attributes from a dataset
SELECT * FROM h5_attributes('data.h5', '/measurements');

-- Read the same object's attributes across many files
SELECT filename, units
FROM h5_attributes('runs/run_*.h5', '/measurements');

-- Read specific attributes
SELECT units, description FROM h5_attributes('data.h5', '/temperature');

-- Read attributes from a group
SELECT * FROM h5_attributes('data.h5', '/experiment1');

-- Read file-level (root) attributes
SELECT * FROM h5_attributes('data.h5', '/');

-- Enable SWMR read mode
SELECT * FROM h5_attributes('data.h5', '/measurements', swmr := true);

-- Materialize filename into SELECT *
SELECT *
FROM h5_attributes('runs/run_*.h5', '/measurements', filename := true);

---

Scalar Functions

h5_first_file(filename_or_filenames)

Planning-time helper that expands a filename input using the same h5db filename expansion rules as table-valued functions and returns the first expanded filename.

Use this when one part of a query should read only one representative file from a filename pattern or filename list, while another part of the query still reads every matched file.

Parameters:

• filename_or_filenames (VARCHAR or VARCHAR[]): Local path or remote URL to an HDF5 file, a glob pattern, or a list of exact filenames/URLs and/or glob patterns. See Remote Access and Multi-File Inputs and Globbing.

Returns: VARCHAR

Semantics:

• An exact filename is returned unchanged.
• A single glob returns the first file after h5db's normal sorted glob expansion.
• A VARCHAR[] input expands entries left-to-right and returns the first expanded filename.
• h5_first_file(...) is evaluated while the statement is bound, not while rows are executed. This is what lets it be used as an argument to typed table functions such as h5_read(...), which need a concrete file at bind time to infer their output schema.
• The argument must be known while the query is planned, for example a literal, constant expression, or prepared statement parameter. Per-row column values are not supported.
• SFTP inputs use h5db's SFTP expansion rules.
• A glob pattern that matches no files raises an error.

Examples:

SELECT h5_first_file('runs/run_*.h5');

-- Read detector metadata once, while reading event data from every matched file.
WITH pixels AS (
    FROM h5_read(
        h5_first_file('runs/run_*.h5'),
        '/entry/instrument/detector/pixel_id',
        '/entry/instrument/detector/x',
        '/entry/instrument/detector/y'
    )
),
events AS (
    FROM h5_read(
        'runs/run_*.h5',
        '/entry/events/pixel_id',
        '/entry/events/time'
    )
)
SELECT *
FROM events
JOIN pixels USING (pixel_id);

---

h5_ls(filename, group_path[, projected_attributes...])

Returns the immediate children of a group as a MAP(VARCHAR, STRUCT(...)) keyed by child name. This is the scalar counterpart to the table-valued h5_ls().

Parameters:

• filename (VARCHAR): File path or remote URL. May vary per row. See Remote Access.
• group_path (VARCHAR): Group path to list. May vary per row.
• projected_attributes (variadic, optional): Zero or more h5_attr(...) or h5_alias(..., h5_attr(...)) expressions. These must be constant expressions.

Returns: MAP(VARCHAR, STRUCT(path, type, dtype, shape, ...projected attrs...))

Semantics:

• group_path must resolve to a group or the function errors
• filename is used as a path or URL per input row; lists and glob patterns are not expanded
• NULL filename yields NULL
• NULL group_path yields NULL
• named parameters such as swmr := true or filename := true are not supported in the scalar form
• projected attributes use the same h5_attr(...) semantics as table h5_ls()
• projected output names must be unique under DuckDB's case-insensitive identifier matching and must not collide with path, type, dtype, or shape

Examples:

SELECT h5_ls('data.h5', '/entry/instrument');

WITH groups(path) AS (
    VALUES ('/'), ('/entry/instrument')
)
SELECT path, cardinality(h5_ls('data.h5', path))
FROM groups;

WITH files(filename) AS (
    VALUES ('run_1.h5'), ('run_2.h5')
)
SELECT filename, cardinality(h5_ls(filename, '/entry/instrument'))
FROM files;

h5_ls_swmr(filename, group_path[, projected_attributes...])

Scalar variant of h5_ls() that forces swmr = true.

Parameters: Same as scalar h5_ls()

Returns: Same as scalar h5_ls()

Example:

SELECT h5_ls_swmr('data.h5', '/entry/instrument');

h5_read(filename, dataset_path)

Reads one HDF5 dataset and returns it as VARIANT.

This is the scalar counterpart to table-valued h5_read(). Use it when the filename or dataset path comes from another query and you want one scalar value per input row.

Parameters:

• filename (VARCHAR): File path or remote URL. May vary per row. See Remote Access.
• dataset_path (VARCHAR): Path to an HDF5 dataset. May vary per row.

Returns: VARIANT

Semantics:

• the target path must resolve to a dataset
• numeric and string datasets are supported
• scalar datasets become primitive variant values
• non-scalar datasets are fully materialized as row-major nested arrays matching the HDF5 dimensions
• empty datasets become empty arrays; an HDF5 null dataspace becomes SQL NULL
• materialization is subject to h5db_scalar_read_memory_limit, which defaults to '64MB'; use table-valued h5_read() to scan large datasets
• missing files or missing datasets raise an error
• filename is used as a path or URL per input row; lists and glob patterns are not expanded
• NULL filename yields NULL
• NULL dataset_path yields NULL
• named parameters such as swmr := true or filename := true are not supported in the scalar form

Examples:

SELECT h5_read('data.h5', '/entry/run_number');

WITH datasets(path) AS (
    VALUES ('/entry/run_number'), ('/entry/title')
)
SELECT path, h5_read('data.h5', path)
FROM datasets;

WITH files(filename) AS (
    VALUES ('run_1.h5'), ('run_2.h5')
)
SELECT filename, h5_read(filename, '/entry/run_number')
FROM files;

SELECT TRY_CAST(h5_read('data.h5', '/entry/small_matrix') AS DOUBLE[][]);

h5_attributes(filename, object_path)

Returns all attributes on one object as a MAP(VARCHAR, VARIANT).

This is the scalar counterpart to table-valued h5_attributes(). Use it when the filename or object path comes from another query and you want one value per input row.

Parameters:

• filename (VARCHAR): File path or remote URL. May vary per row. See Remote Access.
• object_path (VARCHAR): Object path whose attributes should be read. May vary per row. '' is treated as /.

Returns: MAP(VARCHAR, VARIANT)

Semantics:

• attribute names are map keys
• attribute values are stored as VARIANT
• an object with no attributes returns {}
• unsupported attribute values become NULL variant map entries
• invalid UTF-8 string attributes may be represented as BLOB values inside the variant
• filename is used as a path or URL per input row; lists and glob patterns are not expanded
• NULL filename yields NULL
• NULL object_path yields NULL
• named parameters such as swmr := true or filename := true are not supported in the scalar form

Examples:

SELECT h5_attributes('data.h5', '/entry');

WITH objects(path) AS (
    VALUES ('/entry'), ('/entry/instrument')
)
SELECT path, h5_attributes('data.h5', path)
FROM objects;

WITH files(filename) AS (
    VALUES ('run_1.h5'), ('run_2.h5')
)
SELECT filename, h5_attributes(filename, '/entry')
FROM files;

h5_rse(run_starts_path, values_path)

Creates a run-start encoded (RSE) column specification for use with h5_read().

Parameters:

• run_starts_path (VARCHAR): Path to dataset containing run start indices
• values_path (VARCHAR): Path to dataset containing values for each run

Returns: STRUCT with fields {encoding, run_starts, values}

Requirements:

• run_starts must be an integer dataset, non-decreasing
• run_starts and values must have the same length
• At least one non-scalar regular column must be present in the h5_read() call to determine total row count

Notes:

• If run_starts[0] > 0, rows before the first run start are returned as NULLs.
• Duplicate starts are allowed and represent zero-length runs; the last run at a given start is the visible value.

Example:

SELECT * FROM h5_read(
    'data.h5',
    '/row_index',                                  -- Regular column determines row count
    h5_rse('/state_starts', '/state_vals')        -- RSE column expands based on run_starts
);

For detailed information on run-start encoding, see RSE_USAGE.md.

---

h5_ree(run_ends_path, values_path)

Creates a run-end encoded (REE) column specification for use with h5_read().

Parameters:

• run_ends_path (VARCHAR): Path to dataset containing inclusive run end indices
• values_path (VARCHAR): Path to dataset containing values for each run

Returns: STRUCT with fields {encoding, run_ends, values}

Requirements:

• run_ends must be an integer dataset, non-decreasing
• run_ends and values must have the same length
• At least one non-scalar regular column must be present in the h5_read() call to determine total row count

Notes:

• run_ends are inclusive: a run ending at 2 includes row 2.
• If the final run end is before the last row, later rows are returned as NULLs.
• Duplicate ends are allowed and represent zero-length runs.

Example:

SELECT * FROM h5_read(
    'data.h5',
    '/row_index',
    h5_ree('/state_ends', '/state_vals')
);

---

h5_index()

Adds a virtual index column for the outermost dimension when used with h5_read().

Parameters: none

Returns: STRUCT tag used by h5_read()

Default column name: index

When h5_read(...) reads multiple files, h5_index() still means the outermost-dimension row index within the current file, so it starts at 0 for each matched file.

Example:

SELECT index, measurements FROM h5_read('data.h5', h5_index(), '/measurements');

Predicate pushdown: range-like filters on the index column (for example index >= 100, index BETWEEN 10 AND 20, or comparison-cast forms with bind-time constants) are used to reduce I/O when the planner can normalize them into simple comparisons.

---

h5_attr([name[, default_value]])

Creates a projected-attribute definition for use with h5_tree() or h5_ls().

Parameters:

• name (VARCHAR, optional): Attribute name to read. Any non-NULL bind-time-resolved VARCHAR expression is allowed.
• default_value (ANY, optional): Bind-time constant default value. Its type becomes the output type of the projected column for single-attribute projection. If omitted, h5_attr(name) defaults to NULL::VARIANT.

Returns: STRUCT wrapper used by h5_tree() and h5_ls()

Bind-time rules:

• h5_attr(name) is shorthand for h5_attr(name, NULL::VARIANT)
• h5_attr() defaults to output column name h5_attr
• h5_attr(name) and h5_attr(name, default_value) default to output column name name
• h5_alias(...) overrides the default output name
• name must resolve to a non-NULL VARCHAR value at bind time
• constant expressions such as lower('STRING_ATTR') are allowed
• row-dependent projected-attribute definitions are not allowed
• if default_value is provided, it must be a bind-time constant expression with a concrete type
• typed NULL defaults such as NULL::VARCHAR are allowed
• untyped NULL defaults are rejected

Runtime behavior:

• h5_attr() projects all attributes into one MAP(VARCHAR, VARIANT) column named h5_attr
• on resolved rows with no attributes, h5_attr() produces {}
• on unresolved or external rows, h5_attr() produces NULL
• unsupported attribute values inside h5_attr() become NULL map entries
• missing attributes use default_value or NULL::VARIANT when the default is omitted
• present attributes are first converted using the same nominal HDF5-to-DuckDB type rules as h5_attributes()
• single-attribute projections are then cast to the type of default_value

String behavior:

• VARIANT-typed or BLOB-typed projected scalar string attributes preserve invalid UTF-8 values as BLOB
• flexible projected string arrays preserve invalid elements as BLOB, including VARIANT[], VARIANT[N], BLOB[], and BLOB[N] outputs
• text-typed projected attributes still fail on invalid UTF-8 string values

Supported attribute forms:

• numeric and string scalars
• simple 1D numeric and string arrays
• simple multidimensional numeric and string attribute dataspaces up to rank 4
• 1D H5T_ARRAY-typed attributes
• empty numeric and string arrays
• H5S_NULL attributes as typed NULL

Example:

SELECT path, h5_attr
FROM h5_tree(
    'data.h5',
    h5_attr()
);

SELECT path, type, NX_class
FROM h5_tree(
    'data.h5',
    h5_attr('NX_class')
);

SELECT path, type, NX_class
FROM h5_tree(
    'data.h5',
    h5_attr('NX_class', NULL::VARCHAR)
);

---

h5_alias(name, definition)

Renames a column definition when used with h5_read() or a projected-attribute definition when used with h5_tree() or h5_ls().

Parameters:

• name (VARCHAR): Column name to use in the output
• definition (VARCHAR or STRUCT): A dataset path, a column definition like h5_rse(), h5_ree(), or h5_index(), or a projected attribute definition from h5_attr()

Returns: STRUCT wrapper used by h5_read(), h5_tree(), and h5_ls()

The alias name participates in the same case-insensitive duplicate-name checks as normal output column names.

Example:

SELECT * FROM h5_read(
    'data.h5',
    h5_alias('idx', h5_index()),
    h5_alias('temp_c', '/temperature')
);

SELECT path, time
FROM h5_tree(
    'data.h5',
    h5_alias('time', h5_attr('count_time', 0::DOUBLE))
);

---

Test Functions

These functions are provided for testing and verification purposes:

h5db_version()

Returns version metadata for h5db and linked libraries.

Returns: STRUCT with fields:

• h5db_version (VARCHAR): h5db extension version reported by the build
• hdf5_version (VARCHAR): linked HDF5 library version
• sftp_library_version (VARCHAR): linked libssh2 version string

Example:

SELECT h5db_version();

---

Remote Access

All h5db functions that open HDF5 files accept local paths or remote URLs as filename.

• DuckDB-backed remote schemes such as http://, https://, s3://, s3a://, s3n://, r2://, gcs://, gs://, and hf:// are opened through DuckDB's filesystem stack. h5db auto-loads the required DuckDB extension when needed.
• sftp:// URLs are handled by h5db's built-in SFTP backend.
• Remote reads assume the file does not change while it is being read. This applies to both DuckDB-backed remote paths and SFTP.
• The table-valued h5db functions accept swmr := true for local files. Remote table-function opens accept the parameter, but H5F_ACC_SWMR_READ is not used for remote paths.
• Remote reads use DuckDB's external file cache when enable_external_file_cache is enabled. HDF5 metadata reads and other small reads are cached as reusable byte ranges. Large raw dataset reads are routed through cached reads only up to a per-query budget, currently 200 MiB; after that h5db uses direct remote reads for the rest of the query.

SFTP Secrets

Before using sftp:// URLs, create a DuckDB secret of type sftp whose scope matches the URL you want to access.

Example:

CREATE OR REPLACE SECRET beamline_sftp (
    TYPE sftp,
    SCOPE 'sftp://beamline.example.org/',
    USERNAME 'alice',
    USE_AGENT true,
    KNOWN_HOSTS_PATH '/home/alice/.ssh/known_hosts'
);

SELECT * FROM h5_read(
    'sftp://beamline.example.org/data/run001.h5',
    '/entry/data'
);

Required secret fields:

• USERNAME
• exactly one of PASSWORD, KEY_PATH, or USE_AGENT
• at least one of KNOWN_HOSTS_PATH or HOST_KEY_FINGERPRINT

Optional secret fields:

• KEY_PASSPHRASE: passphrase for an encrypted private key
• PORT: default 22
• HOST_KEY_ALGORITHMS: comma-separated libssh2 host-key algorithms accepted during the SSH handshake, in preference order. If omitted, matching entries in KNOWN_HOSTS_PATH are used to prefer known host-key algorithms; otherwise libssh2 uses its default host-key algorithm list. Example: 'ssh-ed25519,ecdsa-sha2-nistp256'

Notes:

• HOST_KEY_FINGERPRINT accepts OpenSSH-style SHA256 fingerprints (SHA256:<base64>) and SHA1 hex fingerprints.
• Password auth via PASSWORD and explicit key-file auth via KEY_PATH with optional KEY_PASSPHRASE are also supported.
• USE_AGENT uses libssh2's SSH agent support. On Unix-like systems this uses the agent exposed through SSH_AUTH_SOCK. On Windows, libssh2 uses its supported agent backends (for example Pageant/OpenSSH) when available.
• sftp:// URLs must not include usernames or passwords.

---

Multi-File Inputs and Globbing

The table-valued h5_tree(...), table-valued h5_ls(...), h5_read(...), and h5_attributes(...) accept:

• a single filename or URL (VARCHAR)
• a glob pattern (VARCHAR)
• a list of exact filenames/URLs and/or glob patterns (VARCHAR[])

Globbing is supported for local paths, for sftp:// URLs, and for DuckDB-backed remote schemes when the underlying DuckDB filesystem supports globbing.

Glob syntax:

• *
• ?
• bracket classes such as [12] or [a-z]
• one recursive ** segment per pattern

Expansion and Read Semantics

• A single glob expands to lexicographically sorted matches.
• A VARCHAR[] input expands entries left-to-right.
• Duplicate files are preserved and are processed more than once.
• A pattern that matches no files raises an error, following DuckDB's normal multi-file reader behavior.
• h5_read(...) concatenates rows file by file.
• h5_attributes(...) emits one row per matched file.
• h5_index() is the outermost-dimension row index within each matched file.
• Table-valued h5_tree(...), table-valued h5_ls(...), h5_read(...), and h5_attributes(...) expose a hidden virtual filename column that can be referenced explicitly.
• filename := true adds filename to the visible output schema.
• filename := 'source_file' adds the same visible filename column but uses the provided column name instead of filename. In that case the column must be referenced as source_file; hidden filename is not also available.
• All matched files in h5_read(...) must have compatible column definitions.
• All matched files in h5_attributes(...) must expose the same attributes in the same order with the same names and types.
• Scalar h5_ls(...), scalar h5_read(...), and scalar h5_attributes(...) accept one filename/URL expression per row and do not expand filename lists or glob patterns.
• For local paths and DuckDB-backed remote schemes, glob expansion uses DuckDB's filesystem stack. For sftp:// URLs, glob expansion is handled by h5db's SFTP backend and matches local globbing behavior.
• Glob expansion follows DuckDB's other multi-file reader semantics such as read_parquet(...). In particular, recursive ** does not traverse symlink directories.
• On Windows, HDF5 1.14.6 with its native file driver fails to open a file symlink when the symlink itself is passed as the filename. h5db does not resolve those paths before calling HDF5, so final-component file symlink paths are not supported there.

Filename Filter Pruning

h5_tree(...) and table-valued h5_ls(...) can apply selective filters on the virtual filename column before opening each expanded file. This includes hidden filename filters and filters on the visible renamed column when using filename := 'source_file'.

Useful shapes include simple comparisons, IN, LIKE, and filename predicates inside AND filters, as long as DuckDB pushes the filter into the table function and the predicate can be evaluated from the filename alone. Dynamic filters whose values come from joins, semi-joins, scalar subqueries, or later query blocks may still open all expanded files.

This file-open pruning currently applies only to h5_tree(...) and table-valued h5_ls(...). h5_read(...) and h5_attributes(...) do not use filename filters to avoid opening files.

Examples

SELECT * FROM h5_tree('runs/**/*.h5');

SELECT * FROM h5_read(
    ['runs/run_001.h5', 'runs/run_*.h5'],
    '/counts'
);

SELECT * FROM h5_read(
    'sftp://beamline.example.org/data/run_*.h5',
    '/entry/data'
);

SELECT filename, units
FROM h5_attributes('runs/run_*.h5', '/entry/data');

SELECT path, type
FROM h5_tree('runs/**/*.h5')
WHERE filename LIKE '%/run_042.h5';

SELECT source_file, path
FROM h5_ls('runs/run_*.h5', '/entry', filename := 'source_file')
WHERE source_file IN ('runs/run_001.h5', 'runs/run_002.h5');

---

Settings

h5db_swmr_default (BOOLEAN)

Default SWMR read mode for h5db functions when no explicit swmr := ... named parameter is provided. Defaults to false.

Example:

SET h5db_swmr_default = true;

h5db_batch_size (VARCHAR)

Target batch size used by h5_read for read-ahead caching and scan sizing. Accepts DuckDB memory-size strings such as '1MB', '8MB', '512KB'. Defaults to '1MB'. Values above 1GB are clamped to 1GB.

This is a target, not a hard memory cap. For HDF5 chunked datasets, h5_read may align cache windows upward to the dataset's first-dimension HDF5 chunk size.

Example:

SET h5db_batch_size = '4MB';

h5db_scalar_read_memory_limit (VARCHAR)

Maximum estimated peak memory for scalar h5_read materialization in one output chunk. Accepts DuckDB memory-size strings such as '16MB' and '1GB'. Defaults to '64MB'.

The estimate includes the typed input, nested-array and VARIANT encoding overhead, the copy into the result vector, and values already retained in the same output chunk. It is deliberately conservative and is not based on the compressed HDF5 file size. Set the value to 'none' to disable the guard explicitly.

This is an h5db materialization guard, independent of DuckDB's memory_limit.

The guard is best-effort rather than a hard bound on process memory usage. In particular, the configured bound is not guaranteed for variable-length strings.

SET h5db_scalar_read_memory_limit = '16MB';

-- Deliberate opt-in to unbounded scalar materialization:
SET h5db_scalar_read_memory_limit = 'none';

---

Type Mapping

HDF5 to DuckDB Type Conversion

HDF5 Type	Size	DuckDB Type
H5T_INTEGER (signed)	1 byte	TINYINT
H5T_INTEGER (signed)	2 bytes	SMALLINT
H5T_INTEGER (signed)	4 bytes	INTEGER
H5T_INTEGER (signed)	8 bytes	BIGINT
H5T_INTEGER (unsigned)	1 byte	UTINYINT
H5T_INTEGER (unsigned)	2 bytes	USMALLINT
H5T_INTEGER (unsigned)	4 bytes	UINTEGER
H5T_INTEGER (unsigned)	8 bytes	UBIGINT
H5T_FLOAT	2 bytes	FLOAT
H5T_FLOAT	4 bytes	FLOAT
H5T_FLOAT	8 bytes	DOUBLE
H5T_STRING	variable/fixed	VARCHAR

Numeric datasets and attributes are converted from their HDF5 file representation to the host-native memory representation before DuckDB values are constructed. This includes widening 16-bit HDF5 floats to DuckDB FLOAT and numeric attributes with an H5T_ARRAY datatype.

Multi-Dimensional Arrays

Multi-dimensional HDF5 datasets normally map their inner dimensions to fixed DuckDB ARRAY types:

HDF5 Shape	DuckDB Type
[N]	N rows of scalar
[N, M]	N rows of TYPE[M]
[N, M, P]	N rows of TYPE[P][M]
[N, M, P, Q]	N rows of TYPE[Q][P][M]

When a dataset row is at least 64 KiB, h5_read instead maps every inner dimension to a nested LIST. For example, a wide dataset with shape [N, 2048, 2048] and type SMALLINT is returned as SMALLINT[][]. Each value remains rectangular with the HDF5 extents, but the extents are not encoded in the DuckDB type.

This fallback prevents DuckDB from eagerly allocating 2,048 complete fixed arrays per output vector.

Note: Datasets with more than 4 dimensions are not currently supported. Note: Multi-dimensional string datasets are not currently supported.

---

Error Handling

All functions provide clear error messages for common issues:

• File not found: "IO Error: Failed to open HDF5 file"
• Invalid path: "IO Error: Failed to open dataset/object: ... in file: ..."
• Unsupported type: "IO Error: Unsupported HDF5 type"
• Invalid run-encoded data: "IO Error: RSE run_starts must be non-decreasing: ... in file: ..." or "IO Error: REE run_ends must be non-decreasing: ... in file: ..."
• No attributes in table h5_attributes(): "IO Error: Object has no attributes: ... in file: ..."

---

Performance Notes

• h5_read projection pushdown: Only reads columns actually used by your query, skipping unused datasets entirely for significant performance gains
• h5_read predicate pushdown: Range-like filters with static constants are applied during scan for run-encoded and h5_index() columns to reduce I/O. Supported shapes include =, <, <=, >, >=, BETWEEN, bind-time-foldable RHS expressions, and comparison-cast forms that normalize to those operators. Unsupported boolean shapes such as OR, !=, IS DISTINCT FROM, or expressions like index + 1 > 10 remain post-scan filters.
• h5_tree/h5_ls filename filter pruning: Filters on the hidden or visible renamed filename column can remove files from expanded glob/list inputs before the file is opened. This applies only to h5_tree(...) and table-valued h5_ls(...), not to h5_read(...) or h5_attributes(...).
• Chunked reading: Data is read in chunks with optimized cache management for memory efficiency
• Hyperslab selection: Uses HDF5's hyperslab selection for efficient partial reads
• Run-encoding optimization: Run-start and run-end encoded data is expanded on-the-fly with O(1) amortized cost per row
• Parallel scanning: h5_read can scan different row ranges in parallel where the dataset layout and query allow it

---

Limitations

1. Compound types (HDF5 structs) are not currently supported
2. Enum types are not currently supported
3. Opaque, bitfield, reference, time-like, and non-string variable-length HDF5 type classes are not supported
4. Datasets with >4 dimensions are not supported
5. Multi-dimensional string datasets are not supported
6. Attribute dataspaces with >4 dimensions are not supported

---

See Also

• ../README.md - Project overview and quick start
• RSE_USAGE.md - Detailed guide to run-start encoding
• developer/DEVELOPER.md - Development and building guide