Query tables in storage ¶

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.

For an in-depth example and a complex dataset, read this blog post: blog.lamin.ai/1000genomes.

import lamindb as ln

db = ln.DB("laminlabs/lamindata")

Open a dataset for queries¶

To open an artifact as a pyarrow dataset call .open() with default arguments:

artifact = db.Artifact.filter(
    key__startswith="example_datasets/small", suffix=".parquet"
).first()
dataset = artifact.open()
dataset

To open a collection (a sharded dataset), call:

collection = db.Collection.get(key="sharded_parquet_collection")
dataset = collection.open()
dataset

We recommended to use Collection to manage large-scale datasets as this gives you typical lakehouse guarantees. For more background, read Architecture.

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)

Show code cell output 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()

Show code cell output Hide code cell output

	cell_type	cell_type_count
0	Dendritic cells	3
1	CD14+ Monocytes	4
2	CD4+/CD45RO+ Memory	5
3	CD8+/CD45RA+ Naive Cytotoxic	3
4	CD4+/CD25 T Reg	4

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())

Show code cell output Hide code cell output

! run input wasn't tracked, call `ln.track()` and re-run

	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 files or folders on S3, so any engine that reads the format works. DuckDB and Iceberg read Parquet. A .lance artifact opens with artifact.lance.open() as a LanceDB table. You pick the engine based on your query pattern.

PyArrow

.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()

Polars

.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

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

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

A .lance artifact is one LanceDB table. Register an existing directory with from_lance(). artifact.lance.open() returns that table, and filters run in Lance.

import pandas as pd

df = pd.DataFrame(
    {
        "cell_type": ["T cell", None, "B cell"],
        "n_genes": [1000, 800, 1200],
    }
)
artifact = ln.Artifact.from_dataframe(df, key="example_datasets/small.lance").save()
table = artifact.lance.open()

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