Tables/DataFrames - requirements

This issue is a placeholder for gathering thoughts and discussion on requirements for storage of columnar tables (a la bcolz). The idea is to explore how the functionalities currently available in zarr and bcolz might be brought together. This is not necessarily something that will happen in zarr, just an attempt to pull together thoughts and discussion at this stage.

Requirements

Below is a list of possible requirements. If you have any thoughts or comments on the requirements, please add a comment below, including if requirements should be non-requirements (e.g., if the requirement can be achieved by using zarr with dask).

Store data from a pandas DataFrame without loss.
Load data into a pandas DataFrame without loss.
Store data from a NumPy recarray without loss.
Load data into a NumPy recarray without loss.
Play well with dask.dataframe. E.g., it should be possible to write a from_zarr() function into dask.dataframe, which would allow out-of-core dask computations against the zarr stored data.
Append a single row.
Append multiple rows as a block.
Iterate over rows.
Iterate over rows matching a query.
Evaluate an expression against columns (e.g., "a + b * c").
Parallel query (not sure what this means)?
Parallel append (not sure what this means)?
Add a column.
Delete a column.

@jreback

To follow up on this, here are a few potted thoughts. To be clear, I don’t have any significant bandwidth to work on this, and so I don’t expect anything to happen in the short term, just mulling some ideas.

An important question is, should zarr provide some kind of columnar table functionality. I.e., is it worth it, and who would use it, and what for? Especially given the traction of parquet, what would/could zarr do that cannot easily be done via parquet? Also, should zarr try to replicate the functionality of bcolz so it could serve as a replacement and so combine maintenance efforts? And there are plenty of other big players in columnar data storage, with way more time and money than we are likely to be able to muster. Some things I think are relatively special about zarr are (1) flexibility to layer on top of a range of storage systems, including distributed storage; (2) support for parallel read and write, with support for locking at various levels down to individual data chunks; (3) support for a range of fast compressors and filters; (4) relatively easy to get under the hood because of pure Python implementation. Are these features unique and/or valuable enough for columnar data applications to make the effort worthwhile? If so, what are the driving use cases?

Assuming it is worth doing, then what is the architecture? In particular, to what extent does the implementation build on the existing Group/Array classes? Could the new functionality be layered entirely on top of the existing architecture, or is any new fundamental architecture required?

In the work @jreback did (#84), a new top-level “Frame” class was created alongside Group and Array, with its own metadata key (‘.zframe’). An alternative “layered” approach would be to construct Frame as a sub-class of Group, where each child Array is treated as a column and is constrained to be 1-dimensional and have the same length.

The layered approach is appealing because it could allow maximal re-use of existing functionality, and potentially could avoid the need for any changes to the storage specification. However, it would be worth exploring the performance implications of a layered architecture.

In particular, I expect an important use case would be appending rows (or blocks of rows) to an existing table. If using a purely layered architecture, then each column (Array) would maintain its own metadata. Appending to a table would require updating the metadata for each Array to set a new shape. If a table had a large number of columns, and if some kind of network-based storage (e.g., S3) was being used, then the network latency involved in updating all these metadata files could impact on performance. Also, if any of these update operations failed, the table could be left in an inconsistent state, with different columns having different lengths.

It might be possible to get the best of both worlds, i.e., take a layered approach, but introduce some workarounds for the metadata consistency issue above. For example, we could add a Frame class as a sub-class of Group, but use special attributes to store some additional metadata. In particular, the current length of the table could be set as the value of an attribute. It would probably also be necessary to store the list of column names as an attribute, to allow columns to be put in a particular order by the user. Then we could add a Column class as a sub-class of Array. In the Column class we could override how the shape of the array is determined, which would be looked up via the parent Frame, rather than from the .zarray metadata. We could also prevent resize or append operations on the Column class, to force these to be done via the parent Frame instead.

As well as the shape, it would probably also be worth constraining the chunk length to be the same across all columns, as aligning chunks would improve performance for any chunked computation. As with the shape, this could be done by setting the chunk length as a special attribute of the parent Frame, and the column class then overrides Array to look up chunk length via its parent, rather than from its own metadata.

One question is whether, under this design, each column would still have it’s own .zarray metadata key? Each column would need to be able to have its own dtype, compressor and/or filter configurations, and so those would need to be stored somewhere. These metadata fields would not be expected to change, so if each column did have it’s own .zarray, it would only need to be read infrequently and could be cached. To avoid confusion/inconsistency, if each column did have a .zarray, it would probably be a good idea to set shape and chunks fields to null.

What would be the value of this kind of layered approach? It would mean that frames and columns would get recognised (as groups and arrays) by the existing (vanilla, non-frame-aware) machinery. At least that machinery would know they existed, and would not accidentally overwrite them if a user requested to create a vanilla group or array with the same name. But if columns were arrays with shape and chunks metadata set to null, then they could not be read by the vanilla array machinery.

The alternative would be to package up all metadata for the frame and all its columns into a single JSON resource, and store it under a special attribute. But this is almost the same as creating a new .zframe metadata key.

It feels like I’m slightly going round in circles here, but wanted to explore some options.

@ivirshup

I'd propose that this could likely be migrated to zarr-specs if not a more design-y or perhaps community repo.

cc: @ivirshup in case there's interest.

@joshmoore

I'd totally agree with that @joshmoore.

I think it's pretty easy to specify a dataframe/ table on top of zarr, and don't think it needs to be defined in the core zarr spec.

That said, table support could be improved by:

irregular chunking
some effort toward chunks having binary compatibility with arrow buffers

But these don't need tables to be in the main spec.

Some thoughts I had on a train:

Defining a dataframe library on top of zarr would, I think, mean splitting each column into a separate array (or at least, one array per data type and/or encoding scheme) and having the table itself represented by a group. This is done by pytables, I think?

The concepts of the sharding codec gives a good foundation for tabular data.

zarr_format: 3
attributes: ...
chunk_key_encoding:
  ...

node_type: table

n_rows: 100

chunk_grid:
  name: regular
  configuration:
    # must be 1D
    chunk_shape: [10]

columns:
  - name: colA
    data_type: "float32"
    fill_value: "Infinity"
    # per-column attributes, as supported by arrow
    attributes: ...
    # re-use existing Zarr A->A, A->B, B->B codecs; input array is always 1D
    codecs:
      - ...
  - name: colB
    data_type: "uint64"
    fill_value: 0
    attributes: ...
    codecs:
      - ...

codecs:
  # required "table->bytes codec" which packs the byte arrays
  # generated by `columns[*].codecs` into a shard-like structure
  - name: table_indexed
    configuration:
      index_location: end
      index_codecs:
        - name: bytes
          configuration:
            endian: little
        - name: crc32c
  # optional bytes-to-bytes codec(s)
  - ...

So, this would introduce a new node type, a new codec type (table->bytes) with an API which allows addressing partial encoding/decoding by column index and optionally row range.

This arrangement allows full re-use of zarr storage and data codecs, probably a fair bit of shared code with the sharding_indexed codec, all the benefits of zarr chunking, with arrow-like per-column encoding schemes and lazy column access. Keeping every element of a single row in the same chunk (i.e. not the pytables layout) has pros and cons.

Compatibility with Arrow IPC (=feather v2) and/or parquet formats could be achieved with a table->bytes codec which reads the metadata and extracts the bytes for each column, and array-> bytes codecs which read those column bytes. You'd want to make sure the arrow/parquet chunks are set to be equal to the zarr chunks.

It would also be nice to hijack the chunk key encoding to allow hive partitioning, where the first N chunk key elements are derived from the (URL-encoded) JSON representation of the partition value.

(table->bytes)

Wouldn't 1 logical row be written to multiple arrays? In this case, the output is not bytes but more like a {array name : write declaration} mapping, where the write declaration probably includes bytes and a byte range offset

to satisfy the needs of tabular data users, we would almost certainly need a generic optional / missing data type. see zarr-developers/zarr-extensions#33

also worth considering the possibility of using the struct data type to pack multiple "columns" into a single array. This is not the ideal layout for readers, but this form is obligatory for some performance-sensitive data writers.

worth considering the possibility of using the zarr-developers/zarr-extensions#45

The struct type is an array-of-structs representation (like sqlite), where a table chunk as I envision it should be struct-of-arrays (like arrow), because the idea is to not fetch columns you're not interested in.

Wouldn't 1 logical row be written to multiple arrays? In this case, the output is not bytes but more like a {array name : write declaration} mapping

In the pytables layout, one logical row would be written to multiple zarr arrays. The arrays could have different chunking schemes, so for row R, field A and field B do not share a chunk index.

In the layout I'm proposing, one logical row would be written to a single chunk of a single Zarr node, but the fields of that row would not be consecutive on disk - so it would be like an arrow RecordBatch.

For rows 1, 2, 3, and fields A and B, the on-disk layout would be A1 A2 A3 B1 B2 B3, rather than A1 B1 A2 B2 A3 B3. IMO APIs should be designed around accessing this layout, as they are with arrow.

that's helpful clarification. To me it seems like array-of-structs and struct-of-arrays are two different stored layouts for representing the same basic data structure, which is a 1D sequence of records. Selecting just 1 column is far cheaper with struct-of-arrays, but it's still possible with array-of-structs.

This makes me wonder what we get from introducing a new node, vs improving support for record data types. The array representation would allow an N-dimensional array of records, which might offer some features not typically found in ordinary tabular formats, while also supporting the conventional 1D sequence of records.

I agree that the idea of tabular data can be achieved today with zarr arrays and struct (AoS) data types. So the question isn't "can these data be represented?", it's "does this API and layout support tabular workflows?". In my opinion, it doesn't. Columnar/ SoA layouts allow compression per data type which can be much more efficient when representing e.g. categorical or serial data; they allow sparse-ish access to individual columns which is useful for filtering. Row-wise relational databases deal with these issues by having a server process close to the data; we are much closer to the arrow use case where latency is high and volume is large. Once you can assume relatively efficient access to subsets of columns you can design APIs around that e.g. for pushing down predicates when filtering. That's why I think a proper tabular layout should have a different node type and should be SoA, so that they can expose different APIs.

so that they can expose different APIs.

What APIs do you have in mind? I'm not at all familiar with the pandas or polars internals so I have no idea what their IO primitives look like, but figuring out the minimum API surface area needed to wrap either of those two around zarr data would be interesting. And missing data support in Zarr will definitely be important at that interface.

Related: AnnData's DataFrame spec https://anndata.readthedocs.io/en/stable/fileformat-prose.html#dataframes , which seems to take a pytables-like (pytabular?) approach of the table being a zarr group with 1D child arrays each representing columns. Strengths: everything is already well-supported, scales well. Weaknesses: chunks either end up very small (poor latency trade-off) or very long (lots of values to decompress and search through when filtering), can't think of a way to do hive partitioning.

What APIs do you have in mind?

Things like specific methods for predicate pushdowns (i.e. only retrieving columns involved in filter statements to do the filtering, then retrieving the rest later). This is not really worth implementing at if you allow the data to be in AoS layout because you would download all of the data anyway, then toss most of it, then download the filtered subset again. And columnwise compression, where people can carefully select codecs which suit their needs (e.g. delta-encoding serial data) rather than throwing a generic compressor at it.

worth considering the possibility of using the zarr-developers/zarr-extensions#45

The struct type is an array-of-structs representation

I'd just add that with an alternate codec to bytes, the struct type could be encoded as SoA. I have some thoughts from a while ago here: zarrs/zarrs#154. Also, I suppose implementations could represent decoded struct elements with a SoA or AoS layout in memory (runtime option?), the spec doesn't constrain such things.

what does the array / chunk machinery of Zarr offer vs referring to a parquet file or similar tabular resource via array / group attributes?

what does the array / chunk machinery of Zarr offer vs referring to a parquet file or similar tabular resource via array / group attributes?

Very little, as far as storing tabular data goes. But it does allow us to re-use the stores, type system, chunk key layout, and codecs, rather than requiring implementors and users to wrap their head around a parallel ecosystem. I guess there are advantages in predictably-sized chunks and some guarantee of equivalent metadata contents compared to a directory of feather/parquet files where I don't think there is such a guarantee.

@clbarnes

I just came across this issue so I'll chime in as someone actively maintaining a "dataframe" spec

There are hairy edges. What if you write a subarray that isn't 1d? Does this invalidate the group? What if your on-disk data isn't serializable to a dataframe-like object in-memory? This could be a point in favor of a new node_type but also maybe just a zarr extension?
This is based purely on my understanding/observation and not on my personal experience with arrow/parquet Categoricals are tricky, but also a big part of the reason to use dataframes, at least for our community. For example, AFAIK, because arrow forces per-record-batch dictionaries, you end up with APIs like this for rectifying mismatches in categories across batches. But I think, at least for our users, a big win is just knowing what are a categorical type's categories. So having all the categories stored separately is nice. The arrow strategy also might not scale well with a high-ish density of categories - if you have 1_000_000 categories in a 1_000_000_000 row dataset evenly distributed, your on-disk usage might be quite a bit worse than you think because you might end up storing as many categories as rows per record-batch. See also Why are categoricals not implemented using Arrow Dictionaries? pola-rs/polars#7429
You can make the anndata implementation much faster by just fetching columns in parallel, I assume. There's no reason we don't do this. Someone should implement it! But it gets at the fact that, from my perception, as @clbarnes hinted at, most workloads are reading/appending while databases are kind of transactional or really for having a lot of control. So doing column fetches/chunk filtering is usually good enough (in theory).
I do think that this is closely related to zarr's support for arrow (Arrow ipc Array -> Bytes codec #3613), which is something I would love to see.

Weaknesses: chunks either end up very small (poor latency trade-off) or very long (lots of values to decompress and search through when filtering), can't think of a way to do hive partitioning.

Please feel free to open an issue about this though! I would love to understand a bit more the tradeoffs but have no use-cases!

What if you write a subarray that isn't 1d? Does this invalidate the group?

concerns like this suggest thinking about a "table" as a collection of formally named, mutually consistent arrays, rather than a particular node type. so with the right attributes convention a group could e.g. declare multiple tables. sub-arrays and sub-groups that are not declared as part of the table would have no impact on the table.

@ilan-gold

I wish I had discovered this discussion previously but perhaps better I didn't because I think some of the points raised may have discouraged my experimentation. In the NASA/earth sciences space, we have an immediate operational requirement for querying large numbers of arrays (that are heterogenous in ways which prevent them from being concatenated into a single cube along a common dimension). Traditionally we rely on external metadata indexing systems to reference them but this often presents synchronization and latency problems when dealing with high throughput data producers. With the advent of Icechunk we can manage but raw array writing and corresponding metadata in a single ACID transaction. Since this is something we needed immediately and we built https://developmentseed.org/zarr-datafusion-search/#why using the "pytabular" single group multiple 1d array model that was discussed above (see https://developmentseed.org/zarr-datafusion-search/architecture/zarr). Datafusion has very flexible predicate pushdown machinery which allows us low level control of our chunk retrieval (though I'm sure @ilan-gold and @LDeakin can suggest some improvements to my naive zarrs usage 😆 ). I discovered this thread as I just encountered a need to ingest some Arrow data containing struct columns and I was investigating structured dtype support in zarrs (for the moment because this is a very specific use case we can flatten these structs into 1d columns). This is a great discussion and I'll continue to track how you all are thinking about this at a lower level 🙇 .

@d-v-b

Short of our individual need for categorical columns (which can itself be a zarr convention but also could be something anndata specific maybe), I would propose making a dataframe zarr convention along the lines of what @d-v-b is describing.

@clbarnes @sharkinsspatial would you be interested in joining up for this? cc @tomwhite Re: my email on chromosome/position 1d arrays. It looks like @sharkinsspatial is also looking at structured arrays but again, not super interested in this.

@sharkinsspatial

P.S @sharkinsspatial that is a very cool tool. I've been following development from afar and think it's great. Is there interest in formalizing a zarr convention for geometries as VariableLengthBytes potentially as well? I'm not super familiar with all the different geometry encodings though.

It would be good to sync up with anndata where possible; I think we only really need a list of columns in the containing group.

@clbarnes

@clbarnes and anyone else interested: https://github.com/ilan-gold/zarr-convention-dataframe

Navigation Menu

Repository navigation

Tables/DataFrames - requirements #149

Requirements

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Tables/DataFrames - requirements #149

Description

Requirements

Activity

alimanfoo commented on Nov 9, 2017

joshmoore commented on May 4, 2022

ivirshup commented on May 4, 2022

clbarnes commented on Mar 20, 2026

d-v-b commented on Mar 20, 2026

d-v-b commented on Mar 20, 2026

clbarnes commented on Mar 20, 2026

d-v-b commented on Mar 20, 2026

clbarnes commented on Mar 20, 2026

d-v-b commented on Mar 20, 2026

clbarnes commented on Mar 20, 2026

LDeakin commented on Mar 20, 2026

d-v-b commented on Mar 21, 2026

clbarnes commented on Mar 23, 2026

ilan-gold commented on Apr 8, 2026

d-v-b commented on Apr 8, 2026

sharkinsspatial commented on May 7, 2026

ilan-gold commented on May 8, 2026

ilan-gold commented on May 8, 2026

clbarnes commented on May 8, 2026

ilan-gold commented on Sep 18, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions