Duckdb: Support Arrow for table scans and result sets

[fsaintjacques]

Would you consider a adding support for arrow result set (and eventually consuming) format? That would give you immediate bindings for many languages (C++, Java, Javascript, go, rust, R, python, ...).

[hannesmuehleisen]

Hello @fsaintjacques , we have looked at Arrow. We even made a dependency-free version of it. However, to integrate with R and Python we already generate the native structures and our C/C++ API is vectorized too. So at the moment is not so clear how DuckDB would benefit from Arrow. Are we missing something?

[fsaintjacques]

Well, the list of other languages, Java, Javascript, go, rust, C#, matlab, Ruby. Support for exporting to some databases (in columnar format), mapd, kudu, dremio ...

AFAIK, the memory representation of the ResultSet is I think, compatible with Arrow, so it might just be small shims to write. I can try to submit a PR.

[hannesmuehleisen]

We have to weigh the cost of a dependency on Arrow and its transitive dependencies against the benefits of the features you described. DuckDB has zero external dependencies at the moment. We had looked before (https://github.com/cwida/duckdb/issues/96) and abandoned the effort precisely because of this. I would reconsider if Arrow would move towards no dependencies as well. Are there any plans?

[hannesmuehleisen]

See also #141 , there we can have the dependency only in the Python frontend, but that makes less sense for Arrow I fear.

[fsaintjacques]

Thank you Hannes for looking into this. We can always open a discussion in Arrow's mailing list to minimize the number of transitive dependencies, or add more feature flag to disable them.

[pitrou]

@hannesmuehleisen For the record, we have been working on a C-based ABI specification (called "Arrow C data interface") that would avoid you depending on the Arrow C++ runtime. For now the spec is still in draft, but comments welcome:
https://github.com/apache/arrow/pull/5442

You can view a formatted version of the spec document here:
https://github.com/apache/arrow/blob/72cf19b7880e994736b6b7f9e5f9712529c65396/docs/source/format/CDataInterface.rst

There's even a proof-of-concept here where the C data interface is being used to pass zero-copy Arrow data between Python and R:
https://github.com/pitrou/arrow/pull/5

[hannesmuehleisen]

@pitrou from a first glance, I like what I see there. I can imagine adding this stand-alone header and add a wrapper for DuckDB result sets or scans.

[wesm]

Now that we've formally adopted https://github.com/apache/arrow/blob/master/docs/source/format/CDataInterface.rst and have a reference implementation available in pyarrow, we are interested if you wanted to take another look? If DuckDB exported Arrow using this memory interface, you could use pyarrow for dealing with NumPy and pandas conversions and save yourselves a great deal of code maintenance and testing. It wouldn't require you to take on any code dependencies into DuckDB since you can just copy the Arrow C header file into the project.

[hannesmuehleisen]

Yes, this is a sensible approach. Thanks for making this possible!

[hannesmuehleisen]

See also https://github.com/cwida/duckdb/issues/97#issuecomment-539837304

[hannesmuehleisen]

@wesm I am going to give this a go now. We are going to have two components: 1) A scan function, so DuckDB can read Arrow arrays in table scans, and 2) a way to fetch result sets as arrow arrays.

[wesm]

Cool, using the C interface right? Note we are discussing adding a standardized iterator interface to the API (eg to enable iterating over pyarrow.Table as RecordBatch objects which can be exported with the C interface), your input on what would be useful there would be helpful.

cc @pitrou

[hannesmuehleisen]

@wesm @pitrou yes, with the C interface. I have a very small demo in the arrow branch, file test/arrow/arrow.cpp

[wesm]

OK, let us know when you'd like us to review a PR or provide some other feedback.

Here's the ML thread about adding an iterator data structure to the C API

https://lists.apache.org/thread.html/r25e8a05296d49f0b47d187e9244967055b299f36a4fc7258a2aeb133%40%3Cdev.arrow.apache.org%3E

[hannesmuehleisen]

I think the ArrowArrayStream is a good idea, I just emulated something like it here: https://github.com/cwida/duckdb/blob/arrow/test/arrow/arrow.cpp#L229
Otherwise it works well enough already.

[wesm]

Cool, @pitrou or @bkietz should be able to take a look. Without introducing that concept natively in pyarrow, the interface will be somewhat limited -- with our Datasets API, we could yield an iterator that can be feed a DuckDB scanner so that the entire dataset does not have to be materialized in memory. The datasets API can also do some simple predicate pushdown.

[hannesmuehleisen]

I managed to get an end-to-end integration to sort of work. Here is a Python example:

````python
import pyarrow.parquet as pq
import duckdb

table2 = pq.read_table('../../test/sql/copy/parquet/data/userdata1.parquet')
print(duckdb.from_arrow_table(table2).query("arrow", "SELECT * FROM arrow WHERE id < 10").fetchdf())
````

Indeed there is no dependency on arrow within the duckdb core, excellent. However we need a hard dependency on pyarrow in the duckdb python wrapper, since someone needs to call arrow::py::unwrap_table() and ExportArray etc. I wonder whether this could also be made more lightweight as well by exposing a pointer to a list of ArrowArrayStream from the python arrow table?

My next step would be the result set conversion, stay tuned...

[pitrou]

There are non-official APIs to export/import C pointers in pure Python (those are used for the R<->Python bridge). Hopefully this avoids having to call the C++ API:
https://github.com/apache/arrow/blob/master/python/pyarrow/table.pxi#L959

[hannesmuehleisen]

@pitrou ah great, thanks, will have a go with this!

[hannesmuehleisen]

@pitrou that worked, excellent. We are getting to something merge-able for the scan. Will prototype the result set conversion next. Thanks for the pointer!

[wesm]

Note that tables whose columns are not all contiguous (which will happen when you have a column with a lot of string data when reading a Parquet file) will run into the iterator problem that I discussed above

[hannesmuehleisen]

@wesm I am aware, but this matches up nicely with our data chunk model, so i am exporting every data chunk as its own record batch.

@pitrou I got both directions to basically work now, here is a code sample:
````python
import duckdb
import pyarrow.parquet as pq

rel_from_arrow = duckdb.arrow(pq.read_table('../../test/sql/copy/parquet/data/userdata1.parquet'))
print(rel_from_arrow)

arrow_from_rel = rel_from_arrow.project('id, first_name, last_name, email, gender, ip_address, cc, country, birthdate, salary, title, comments').arrow()
print(arrow_from_rel.to_pandas())
````

One of the issues remaining is that string data is copied on export due to arrow's layout relying on subsequent offsets giving the length. Perhaps you have an idea?

Overall, will spend some more time on sanity checks and additional data types, but then I think we can merge this!

[Alex-Monahan]

This is super exciting! Does this re-open the door to a WASM compiled DuckDB? Arrow could allow transfer both in and out of the JS layer, correct?

[wesm]

@wesm I am aware, but this matches up nicely with our data chunk model, so i am exporting every data chunk as its own record batch.

I'm a bit more concerned about enabling DuckDB to scan record batch streams produced by the Arrow Datasets API. Otherwise you're having to load the whole thing into memory before scanning it (i.e. what pq.read_table is doing).

One of the issues remaining is that string data is copied on export due to arrow's layout relying on subsequent offsets giving the length. Perhaps you have an idea?

AFAIK you should only need to generate the validity and offsets buffers if the string data is already laid out end-to-end in a big heap?

[hannesmuehleisen]

I'm a bit more concerned about enabling DuckDB to scan record batch streams produced by the Arrow Datasets API. Otherwise you're having to load the whole thing into memory before scanning it (i.e. what pq.read_table is doing).

Yes that is a very good point, we should allow streaming records in (and out).

AFAIK you should only need to generate the validity and offsets buffers if the string data is already laid out end-to-end in a big heap?

It is not, that's just the thing. DuckDB uses a mixture of inlined and pointer-ed strings and there is no mapping this to Arrow's layout as far as I can tell. But we will probably address this by allowing multiple string representations eventually. For the first iteration i'm okay with copying strings.

[wesm]

Got it. Arrow is swizzling-free by design so there are pros and cons

[pitrou]

There's a draft proposal for a C array stream API here, you may want to take a look and give feedback:
https://github.com/apache/arrow/pull/8052

[hannesmuehleisen]

@pitrou will just try to use this struct already in duckdb and see where we get...

[hannesmuehleisen]

See also #866