feat(python): add filters argument to DeltaTable.to_pandas() for filter pushdown

[MrPowers]

Reading Delta tables into pandas DataFrames could be more intuitive.

Here's the current syntax:

import pyarrow.dataset as ds

dt = DeltaTable(f"{pathlib.Path.home()}/data/delta/G1_1e8_1e2_0_0")
dataset = dt.to_pyarrow_dataset()
condition = ds.field("id1") == "id016"
df = dataset.to_table(filter=condition, columns=["id1", "id2", "v1"]).to_pandas()

Could we provide an interface that's more similar to pandas.read_parquet? Something like this:

import deltalake

dt = DeltaTable(f"{pathlib.Path.home()}/data/delta/G1_1e8_1e2_0_0")
df = dt.filters([("id1", "==", "id016")]).to_pandas()

The list of tuples of how predicates are passed in pandas.read_parquet.

I'm open to other interfaces too. I think something that hides pyarrow.dataset, is less code, and is familiar with existing pandas syntax would be good.

[wjones127]

Have you tried DeltaTable.to_pandas()? I think that's exactly what you are looking for. https://delta-io.github.io/delta-rs/python/api_reference.html#deltalake.table.DeltaTable.to_pandas

[MrPowers]

@wjones127 - Here's the signature for that function to_pandas(partitions=None, columns=None, filesystem=None).

I don't see any filters argument for metadata-based file skipping. Let me know if I am missing something!

[wjones127]

I see. We can extend that method to pass down filters in the dataset.

[MrPowers]

Great! Can we expose the filters to use this syntax [("id1", "==", "id016")] instead of this ds.field("id1") == "id016"?

[MrPowers]

We may even want to combine the partitions and filters functionality. That's what pandas and pyarrow do from what I understand. See here.

[wjones127]

Yes, I think we'll deprecate partitions in favor of filters, but leave both in for now. And we can use DNF filters if you want. Or maybe can can accept either?

[ognis1205]

Hi, guys. I apologize for interrupting your argument. Although it may take me some time to fully grasp the structure of delta-rs, I would like to work on this issue. Would it be possible to assign me to this issue?

[wjones127]

We would gladly accept a PR for this. 😄

Note there is a function called filters_to_expression that can handle converting the DNF filter format into PyArrow expressions. (code).

[ognis1205]

Thank you @wjones127 @MrPowers!

Once there's a consensus on the implementation, please feel free to assign me to this issue, and I will follow the discussion here as well. Also, I will check the source code that you linked.

[MrPowers]

@ognis1205 - I think there is a consensus on the next steps and you can get started.

See here for more information on the DNF filter format that Will is referring to.

Here's the existing to_pandas method signature: to_pandas(partitions=None, columns=None, filesystem=None)

You can submit a PR that will update the method signature to this: to_pandas(partitions=None, columns=None, filesystem=None, filters=None)

Let me know if you'd like any additional clarification!

[ognis1205]

Thanks again @MrPowers @wjones127 !!

[ognis1205]

@wjones127 @MrPowers

Hi, everyone! I have a question.
While making a PR for this issue, I encountered the following error:

https://github.com/delta-io/delta-rs/actions/runs/4926020116/jobs/8800992032?pr=1349#step:8:18

So, I am considering implementing a fallback function for pyarrow.parquet.core.filters_to_expression that is compatible with PyArrow 7.0.0. However, I came across this comment on another PR:

#1315 (comment)

Should I still implement the fallback function if the Python deltalake package will soon drop support for Python 3.7?
Would it be okay to implement a stub function that does nothing instead?

EDIT:
I checked the PyArrow API doc. Only PyArrow >= 10.0.0 supports pyarrow.parquet.core.filters_to_expression.
So I think I should implement the fallback function in either case.

[wjones127]

Yes, we will drop support for Python 3.7, but we are keeping support (for now) for PyArrow 7.

[ognis1205]

Yes, we will drop support for Python 3.7, but we are keeping support (for now) for PyArrow 7.

Thanks for answering!

[ognis1205]

@MrPowers @wjones127
FYI:
I created a PR for this issue.

[FrankPortman]

Sorry for bumping an ancient ticket - maybe the docs online are outdated, but my understanding is this new (old) route supports OR clauses indirectly (or maybe that was the intent, either way it appears to support OR), while partition_filters still doesn't. Further the more flexible filters don't extend into files or file_uris methods. The change was only for to_pandas(). E.G. this ticket: #1923

Is my understanding of the current limitations correct? I'm interested in helping contribute either code or documentation updates.