Use pyarrow.substrait to execute scans on Pyarrow Datasets

[kdbrooks]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
The current Dataset TableProvider is ugly and converts DataFusion Exprs to Pyarrow expressions in large match statements. I propose we move towards using Substrait as the method for translating from DataFusion to / from Pyarrow. This was not available yet when this TableProvider was made.

Describe the solution you'd like
Now that both Pyarrow and Datafusion support substrait we could clean up and improve the Pyarrow DataSet TableProvider and ExecutionPlan by using pyarrow.substrait to execute the scan.

Describe alternatives you've considered
Keep the existing ugly Dataset code.

[wjones127]

Hi @kylebrooks-8451. I'm working on thinking about what would be a better API / protocol for datasets in apache/arrow#33986 (comment), and I'd welcome your input. Making Substrait expressions part of the dataset API is something we are considering, but it's not yet clear the ecosystem for it is strong enough yet.

[kdbrooks]

Hi @wjones127 - Thanks for reaching out, I would vote in favor of adding in Substrait expressions to the Dataset API. I think it would allow plugging in other execution engines into PyArrow and standardize converting plans between frameworks. I read through the issue you linked, I think what we really want here is option 1 from that thread:

An interface for consuming data from a dataset-like object, without having to be a pyarrow.dataset.Dataset (or Scanner) instance.

If Substrait gets us there then great.

[timsaucer]

I think this issue is stale because I cannot find these large match statements. Please feel free to reopen if you think we need another look at this.