Query tables in storage .md .md

This guide walks through querying tabular datasets stored in parquet and related file formats. Queries stream directly from disk or cloud storage with PyArrow, Polars or DuckDB.

import lamindb as ln

db = ln.DB("laminlabs/lamindata")
Hide code cell output 
! database has module pertdb, configure it: lamin settings modules set bionty,pertdb

Stream a table from storage

Start with a single parquet file. Get the artifact and open it — nothing is downloaded, you get a lazy pyarrow dataset:

artifact = db.Artifact.filter(
    key__startswith="example_datasets/small", suffix=".parquet"
).first()
dataset = artifact.open()
dataset
Hide code cell output 
<pyarrow._dataset.FileSystemDataset at 0x7fec4d951a20>

Peek at the first few rows:

dataset.head(5).to_pandas()
Hide code cell output
CD8A CD4 CD38 cell_medium cell_type_by_model cell_type_by_expert sample_note
sample4 2 3 4 DMSO B cell NaN None
sample5 3 4 2 IFNG T cell NaN None
sample6 3 5 3 IFNG T cell NaN None

The same .open() call works across parquet files. Call it on a query to stream a whole set of Parquet artifacts as one dataset:

dataset = db.Artifact.filter(
    key__startswith="example_datasets/small", suffix=".parquet", is_latest=True
).open()
dataset
Hide code cell output 
! this query set is unordered, consider using `.order_by()` first to avoid opening the artifacts in an arbitrary order

<pyarrow._dataset.FileSystemDataset at 0x7fec4d993d00>

Or on a collection, which streams all of its member Parquet files together:

collection = db.Collection.get(key="sharded_parquet_collection")
dataset = collection.open()
dataset.to_table().to_pandas()
Hide code cell output 
/opt/hostedtoolcache/Python/3.11.15/x64/lib/python3.11/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html
  from .autonotebook import tqdm as notebook_tqdm
cell_type n_genes percent_mito
index
CGTTATACAGTACC-8 CD4+/CD45RO+ Memory 1034 0.010163
AGATATTGACCACA-1 CD4+/CD45RO+ Memory 1078 0.012831
GCAGGGCTGTATGC-8 CD8+/CD45RA+ Naive Cytotoxic 1055 0.012287
TTATGGCTGGCAAG-2 CD4+/CD25 T Reg 1236 0.023963
CACGACCTGGGAGT-7 CD4+/CD25 T Reg 1010 0.016620
AATCTCACTCAGTG-3 CD4+/CD45RO+ Memory 1183 0.016056
CTAGTTTGGCTTAG-4 CD4+/CD45RO+ Memory 1002 0.018922
ACGCCGGAAGCCTA-6 CD8+/CD45RA+ Naive Cytotoxic 1292 0.018315
CTGACCACCATGGT-4 CD8+/CD45RA+ Naive Cytotoxic 1559 0.024427
AGTTAAACAAACAG-1 CD19+ B 1005 0.019806
CTACGCACAGGGTG-3 CD4+/CD45RO+ Memory 1053 0.012073
CAGACAACAAAACG-7 CD4+/CD25 T Reg 1109 0.012702
GAGGGTGACCTATT-1 CD4+/CD25 T Reg 1003 0.012971
TGACTGGAACCATG-7 Dendritic cells 1277 0.012961
ACGACCCTGTCTGA-3 Dendritic cells 1074 0.017466
GTTATGCTACCTCC-3 CD14+ Monocytes 1201 0.016839
GTGTCAGATCTACT-6 CD14+ Monocytes 1014 0.025417
AAGAACGAACTCTT-6 CD14+ Monocytes 1067 0.019530
TACTCTGACGTAGT-1 Dendritic cells 1118 0.012069
TAAGCTCTTCTGGA-4 CD14+ Monocytes 1059 0.021497

Queries

Filters push down into Parquet row groups — only matching data is read from storage, not the whole file.

import pyarrow.compute as pc

dataset.to_table(filter=pc.field("cell_type").is_valid()).to_pandas().head(5)
Hide code cell output
cell_type n_genes percent_mito
index
CGTTATACAGTACC-8 CD4+/CD45RO+ Memory 1034 0.010163
AGATATTGACCACA-1 CD4+/CD45RO+ Memory 1078 0.012831
GCAGGGCTGTATGC-8 CD8+/CD45RA+ Naive Cytotoxic 1055 0.012287
TTATGGCTGGCAAG-2 CD4+/CD25 T Reg 1236 0.023963
CACGACCTGGGAGT-7 CD4+/CD25 T Reg 1010 0.016620

You can build up from there. Materialize the filtered result once, then compute against it in memory — for example, count rows per cell type without re-reading storage:

counts = (
    dataset.to_table(filter=pc.field("cell_type").is_valid())
    .group_by("cell_type")
    .aggregate([("cell_type", "count")])
    .to_pandas()
)
counts.head()
Hide code cell output
cell_type cell_type_count
0 CD4+/CD45RO+ Memory 5
1 CD8+/CD45RA+ Naive Cytotoxic 3
2 CD4+/CD25 T Reg 4
3 CD19+ B 1
4 Dendritic cells 3

These filters run the same way on every engine below. For heavier patterns — schema evolution, time travel, appends, and concurrent writers — the LaminDB lakehouse benchmark walks through all six operations end to end across the five engines on a shared genomics dataset.

Choosing a query engine

By default Artifact.open() and Collection.open() use pyarrow to lazily open dataframes. polars can also be used by passing engine="polars". Note also that .open(engine="polars") returns a context manager with LazyFrame.

with collection.open(engine="polars") as lazy_df:
    display(lazy_df.collect().to_pandas())
Hide code cell output
cell_type n_genes percent_mito index
0 CD4+/CD45RO+ Memory 1034 0.010163 CGTTATACAGTACC-8
1 CD4+/CD45RO+ Memory 1078 0.012831 AGATATTGACCACA-1
2 CD8+/CD45RA+ Naive Cytotoxic 1055 0.012287 GCAGGGCTGTATGC-8
3 CD4+/CD25 T Reg 1236 0.023963 TTATGGCTGGCAAG-2
4 CD4+/CD25 T Reg 1010 0.016620 CACGACCTGGGAGT-7
5 CD4+/CD45RO+ Memory 1183 0.016056 AATCTCACTCAGTG-3
6 CD4+/CD45RO+ Memory 1002 0.018922 CTAGTTTGGCTTAG-4
7 CD8+/CD45RA+ Naive Cytotoxic 1292 0.018315 ACGCCGGAAGCCTA-6
8 CD8+/CD45RA+ Naive Cytotoxic 1559 0.024427 CTGACCACCATGGT-4
9 CD19+ B 1005 0.019806 AGTTAAACAAACAG-1
10 CD4+/CD45RO+ Memory 1053 0.012073 CTACGCACAGGGTG-3
11 CD4+/CD25 T Reg 1109 0.012702 CAGACAACAAAACG-7
12 CD4+/CD25 T Reg 1003 0.012971 GAGGGTGACCTATT-1
13 Dendritic cells 1277 0.012961 TGACTGGAACCATG-7
14 Dendritic cells 1074 0.017466 ACGACCCTGTCTGA-3
15 CD14+ Monocytes 1201 0.016839 GTTATGCTACCTCC-3
16 CD14+ Monocytes 1014 0.025417 GTGTCAGATCTACT-6
17 CD14+ Monocytes 1067 0.019530 AAGAACGAACTCTT-6
18 Dendritic cells 1118 0.012069 TACTCTGACGTAGT-1
19 CD14+ Monocytes 1059 0.021497 TAAGCTCTTCTGGA-4

LaminDB artifacts are plain Parquet files on S3, so any engine that reads Parquet works. DuckDB, Iceberg, and LanceDB are also supported — you pick based on your query pattern, not on how the data was stored.

.open() returns a lazy PyArrow dataset backed by S3. Filters push down into Parquet row groups.

import pyarrow.compute as pc

dataset = collection.open()
result = dataset.to_table(filter=pc.field("cell_type").is_valid()).to_pandas()

.open(engine="polars") returns a context manager yielding a Polars LazyFrame backed by S3. No data is read until .collect() is called.

import polars as pl

with collection.open(engine="polars") as lazy_df:
    result = lazy_df.filter(pl.col("cell_type").is_not_null()).collect()

DuckDB reads Parquet files directly via read_parquet(). Use .cache() to get a local path, or register a view over S3 paths via httpfs for a collection:

import duckdb

con = duckdb.connect()
con.execute("INSTALL httpfs; LOAD httpfs;")
con.execute("CREATE OR REPLACE SECRET s3 (TYPE s3, PROVIDER credential_chain);")

s3_paths = [str(a.path) for a in collection.ordered_artifacts.all()]
con.execute(f"CREATE OR REPLACE VIEW data AS SELECT * FROM read_parquet({s3_paths})")

result = con.execute("SELECT * FROM data WHERE cell_type IS NOT NULL").df()

Iceberg requires a one-time ingestion into a catalog-managed table on S3. After ingestion you get native partition pruning, metadata-only schema evolution, and snapshot-based time travel.

from pyiceberg.catalog.sql import SqlCatalog
from pyiceberg.expressions import NotNull

arrow = collection.open().to_table()

catalog = SqlCatalog(
    "local", uri="sqlite:///iceberg_catalog.db", warehouse="/tmp/iceberg_warehouse"
)
if not catalog.namespace_exists("demo"):
    catalog.create_namespace("demo")
table = catalog.create_table("demo.data", schema=arrow.schema)
table.append(arrow)

result = table.scan(row_filter=NotNull("cell_type")).to_arrow().to_pandas()

LanceDB ingests data into Lance columnar format on S3 — the only engine here that copies data out of the source Parquet files. In exchange you get combined SQL filtering and vector search, plus versioned appends with time travel.

import lancedb
import pyarrow as pa

arrow = collection.open().to_table()
schema = pa.schema([
    f.with_type(pa.string()) if pa.types.is_dictionary(f.type) else f
    for f in arrow.schema
])
arrow = arrow.cast(schema)
db_lance = lancedb.connect("/tmp/lancedb_warehouse")
table = db_lance.create_table("data", data=arrow, mode="overwrite")

result = table.search().where("cell_type IS NOT NULL", prefilter=True).to_pandas()