Arrow Expressions and Vortex DataSets

[paultiq]

Issue Description

Vortex's compute differs significantly from Arrow compute in terms of supported predicates. #5781 provides one list where Vortex doesn't support things that Arrow supports... and #5765 is an example where Vortex's compute supports things Arrow doesn't.

Assuming that 5781 will take some time to resolve (to achieve parity with Arrow), this leads to a number of problems... especially with duckdb which will attempt to pushdown unsupported expressions. For example, the following query will raise an exception in duckdb:

• "select * from vortex_dataset where some_timestamp > today()"

* duckdb's CanPushdown assumes all Arrow sources support the same expressions.

I'm unclear on whether this is an Arrow problem, a Vortex problem or a duckdb problem:

• Vortex could provide feature parity / a superset of Arrow's compute kernels
• Arrow could provide some mechanism to indicate differences in implementations
• Duckdb could (with difficulty) detect the backend and/or gracefully handle pushdown failures, and fall back to something graceful: I propose this here Arrow Pushdowns fail when *any* column is a string_view duckdb/duckdb-python#227 (comment)
• Vortex could recommend not using DataSets, and direct users to read_vortex instead.

One thing Vortex can do is add test cases for:

• Arrow vs Vortex compute coverages
• Tests a variety of SQL expressions & data types in DuckDB

DuckDB Example

import vortex as vx
import duckdb
import pyarrow as pa
from datetime import datetime

con = duckdb.connect()

arr = pa.array([datetime(2024, 1, 1), datetime(2024, 6, 15), datetime(2024, 12, 31)])
table = pa.table({"ts": arr})
vx.io.write(table, '/tmp/test_timestamp.vortex')

ds = vx.open("/tmp/test_timestamp.vortex").to_dataset()
result = con.execute("SELECT * FROM ds WHERE ts > '2024-06-01'").fetchall()
print(result)

Error Traceback (most recent call last)
Cell In[8], line 13
10 vx.io.write(table, '/tmp/test_timestamp.vortex')
12 ds = vx.open("/tmp/test_timestamp.vortex").to_dataset()
---> 13 result = con.execute("SELECT * FROM ds WHERE ts > '2024-06-01'").fetchall()
14 print(result)

Error: NotImplementedError: <EMPTY MESSAGE>

At:
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(39): literal
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(199): expression
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(146): function_argument
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(132): scalar_function
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(203): expression
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(224): expression_reference
/home/extra/git/goodenv/.venv/lib/python3.13/site-packages/vortex/substrait.py(246): extended_expression

Arrow Example

All this does is demonstrate that certain kernels aren't implemented. The point here is not that the kernel isn't implement, but just to demonstrate why the above (duckdb) example occurs.

Parquet Works

import pyarrow as pa
import pyarrow.parquet as pq
from datetime import datetime
import pyarrow.dataset as dataset


arr = pa.array([datetime(2024, 1, 1), datetime(2024, 6, 15), datetime(2024, 12, 31)])
table = pa.table({"ts": arr})

pq.write_table(table, '/tmp/test_timestamp.parquet')

ds = dataset.dataset("/tmp/test_timestamp.parquet", format="parquet")
scanner = ds.scanner(filter=dataset.field("ts") > datetime(2024, 6, 15))
scanner.to_table()

Vortex Fails

import vortex as vx
import pyarrow as pa
from datetime import datetime
import pyarrow.dataset as dataset

arr = pa.array([datetime(2024, 1, 1), datetime(2024, 6, 15), datetime(2024, 12, 31)])
table = pa.table({"ts": arr})
vx.io.write(table, '/tmp/test_timestamp.vortex')

ds = vx.open("/tmp/test_timestamp.vortex").to_dataset()
scanner = ds.scanner(filter=dataset.field("ts") > datetime(2024, 6, 15))
scanner.to_table()

Expected Behavior

.

Actual Behavior

.

Reproduction Steps

.

OS Version Information

Ubuntu 24.04

I acknowledge that:

• I have searched the Vortex repository (both open and closed Discussions and Issues) and confirm this is not a duplicate of an existing issue or discussion.
• I have checked the "Preview" tab on all text fields to ensure that everything looks right, and have wrapped all configuration and code in code blocks with a group of three backticks (```) on separate lines.

[joseph-isaacs]

We need to support more substrait conversions in vortex.

it would be better to use the vortex duckdb extension:

    con = duckdb.connect()
    con.execute("INSTALL vortex; LOAD vortex;")

    arr = pa.array([datetime(2024, 1, 1), datetime(2024, 6, 15), datetime(2024, 12, 31)])
    table = pa.table({"ts": arr})

    file_path = tmp_path / "test_timestamp.vortex"
    vx.io.write(table, str(file_path))

    # Use the vortex extension's vortex_scan function
    result = con.execute(f"SELECT * FROM vortex_scan('{file_path}') WHERE ts > '2024-06-01'").fetchall()

    assert len(result) == 2, f"Expected 2 rows, got {len(result)}: {result}"
    # The results should be 2024-06-15 and 2024-12-31
    timestamps = sorted([r[0] for r in result])
    assert timestamps[0] == datetime(2024, 6, 15)
    assert timestamps[1] == datetime(2024, 12, 31)

[paultiq]

The intent of this discussion was the general issue that arises when Arrow expressions are used with Vortex datasets: the expectation is that Vortex datasets could be swapped in for other datasets, and this discussion shows that this expectation doesn't hold true in all cases.

DuckDB was chosen to illustrate the problem. This investigation stemmed from duckdb/duckdb-python#187.

Agree that other workarounds exist, such as rewriting w/ Vortex expressions and (if it's DuckDB specific) using the DuckDB extension

[robert3005]

This the same problem we have elsewhere internally. I think @danking was working on fixing it but not sure what he settled on there.

[danking]

You might be thinking of the bad integration between Vortex, Arrow, and duckdb around string_views:

• Arrow Expressions on Vortex Datasets raise ArrowNotImplementedError on string_views #5759
• paultiq resolved this here: Map view types when converting arrow_expressions to vortex expressions #5765
• Corresponding Vortex discussion: Arrow Expressions on Vortex Datasets raise ArrowNotImplementedError on string_views #5725
• My original issue at duckdb-python before I realized this wasn't specifically a duckdb problem: ArrowNotImplementedError: Function 'equal' has no kernel matching input types (string_view, string) duckdb/duckdb-python#187

I think this is a separate by related problem. The Arrow Dataset API implicitly accepts every possible Arrow expression. Our subclass, VortexDataset does not support every possible Arrow expression. There's no API method that says "I support this arrow expression". We could raise in scanner, but that probably triggers a panic in duckdb.

[infogulch]

The Arrow Dataset API implicitly accepts every possible Arrow expression. Our subclass, VortexDataset does not support every possible Arrow expression.

What's the shape of the missing support? A handful of predicates? Hundreds of predicates? Something that requires more surgery on the expression system itself like arbitrary expression nesting, recursion, or vector ops?

[danking]

I think we're talking about hundreds. For example, every element-wise operation listed under pyarrow.compute is a valid Arrow Expression. Those expressions could be passed, via the Arrow Dataset API (e.g. scanner), to a VortexDataset. Vortex only knows about a very small subset of those operations.

---

I'm beginning to think the right solution is for Vortex to split an Arrow expression into "during-read" and "post-read" parts. The former is converted to Vortex and sent into Vortex. The latter is evaluated on each record batch after it is read. I think this can be done fully in Python, but it's subtle.

You want to be able to avoid reading rows where the a column is negative in this expression:

and(col("a") > 0, list_contains(col("b"), 5))

But you'll still need to do some post-facto filtering using list_contains.

Some expressions are impossible to push down at all, like just or(col("a") > 0, list_contains(col("b"), 5)). Vortex can evaluate the first operand during read, so maybe you still want to push that down? I think this can be done with a one-pass bottom-up expression transform, but it's definitely a bit subtle.

[danking]

I kinda think paultiq's last suggestion, recommend not using the Arrow Dataset API, is the best strategy (other than modifying the Arrow Dataset API). Even if we were to partition the expression and push parts down, as I describe in a sibling thread, we would still evaluate the "post-read" expression ourselves. I suspect we don't want to do that: we want duckdb to evaluate everything except for the expressions we can push down!

[gatesn]

I think there are a few follow ups here. Let me know if I've missed anything.

1. Dataset Filter Pushdown

The Dataset API clearly states that all filters must be applied by the Scanner:

Scan will return only the rows matching the filter. If possible the predicate will be pushed down to exploit the partition information or internal metadata found in the data source, e.g. Parquet statistics. Otherwise filters the loaded RecordBatches before yielding them.

Vortex Dataset should indeed uphold that API. For any expression where push-down into Vortex is not supported, it must be successfully evaluated after reading the batch from Vortex, and before returning to the caller.

The problem is that Vortex will by default return StringView for string data, and ListView for list data. Both of which have disappointingly little support from the native Apache Arrow libraries. I think the fix here is for our Dataset API to default to not use the "view" arrays, and be configurable to do so when desired.

#5913

2. Vortex Substrait

The reason we even involve substrait here is because PyArrow has no API to traverse an expression tree. Therefore the only way to unpack the expression in order for us to convert it to Vortex (or ideally partition it into supported / unsupported expressions) is to convert it to substrait.

Substrait is an odd API. In theory it is a cross-system query/expression language. In practice, most systems define their own set of extension functions to model their exact semantics. Admittedly, the builtin extensions tend to follow Arrow semantics.

Vortex's substrait support right now is rather lacking, so this conversion often fails.

We should define Vortex <-> Substrait conversion as a Rust crate since we will want to support these conversions in Python, Java, C++, and other bindings.

#5912

3. Vortex DuckDB

Now that Vortex ships as a core extension in DuckDB, we should expose our Vortex DuckDB integration through the PyVortex APIs, rather than using DuckDB + Arrow Dataset.

#5914