DuckDB using polars data frame?

[darked89]

Hello,

I am aware that DuckDB Python library works with pandas data frames as described here:

https://duckdb.org/2021/05/14/sql-on-pandas.html

Would it be possible to do the same with polars data frames without the need to convert these to pandas df?

Best wishes,

Darek Kedra

[hannes]

Yes that is certainly possible, we would have to figure out how to read polars data frames. Is there some documentation available on how they are stored internally?

[darked89]

Incredible response time ;).

Rust is not something I can claim to know much about, but I will try to take a look around the repo/docs and get some help as needed.
Also polars can ingest say CSV "using pyarrow’s native CSV parser.". I guess this implies that the data types in df may end up being arrow compatible, but I have to verify that.

DK

[hannes]

If its also NumPy array at the bottom it should be doable

[Mytherin]

Seems to use Arrow, which should also be doable :)

Polars is a blazingly fast DataFrames library implemented in Rust using Apache Arrow Columnar Format as memory model.

[darked89]

While this looks more tricky to implement, I will vote for the arrow format. We use numpy arrays here and there but the df with different column types are way more common.

Thing to keep in mind: polars probably uses rust arrow implementation which afaik don't have all c++ functionality. Still, this feature gap may be restricted to things like "output parquet as data set", not to core functionality.

DK

[hannes]

It does not matter if it uses Rust, if there's a way of getting a RecordBatchReader or so from Python

[Alex-Monahan]

Their Python API has a to_arrow method

Collect the underlying arrow arrays in an Arrow Table. This operation is mostly zero copy.

Data types that do copy:
CategoricalType

[hannes]

Well then you can already do this with the existing API no? Just do a to_arrow and register that in DuckDB? @Alex-Monahan can you maybe put together a minimal example?

[Alex-Monahan]

Sure! I can do that tomorrow or Monday, no problem.

[Alex-Monahan]

Here is a boilerplate example of using Apache Arrow as the bridge between Polars and DuckDB. This is a key value of Arrow for us - we don't have to build a connector to every other library. We can just use the high speed of Arrow and DuckDB's ability to read it directly for fast workflows on many other libraries!

Please let us know how this goes!

import duckdb
import polars as pl

df = pl.DataFrame(
    {
        "A": [1, 2, 3, 4, 5],
        "fruits": ["banana", "banana", "apple", "apple", "banana"],
        "B": [5, 4, 3, 2, 1],
        "cars": ["beetle", "audi", "beetle", "beetle", "beetle"],
    }
)

# Convert from Polars to Arrow 
# (this handles the non-Arrow CategoricalType data type)
# Aside from CategoricalType columns, it is zero copy so should be performant
arrow_from_polars = df.to_arrow()

duckdb_conn = duckdb.connect(':memory:')

# Once the Arrow table is in Python's local variables, DuckDB can read from it just by using the variable name!
duckdb_result_as_arrow = duckdb_conn.execute("""
    select
        *
        ,'any_new_column_etc.' as test_col
    from arrow_from_polars --DuckDB can deduce that this is an Arrow table and scan it
""").fetch_arrow_table()

print(duckdb_result_as_arrow)

[darked89]

Thank you!

The example works just fine with the python DuckDB 0.3.2 and polars 0.12.15 (python 3.10.2 from conda)

I will check how it behaves with some large data frames especially when it comes to memory usage. But I guess it is: low mem usage dataframes and convenience of SQL queries on data frames: just pick one. As long as RAM for reasonable tasks is cheaper than my time I can live with that. ;)

Best wishes,

Darek Kedra

[Alex-Monahan]

Let us know how it goes! Two quick other pieces of advice in case they are helpful:

If you need to restrict memory usage, DuckDB has a pragma to set a cap on memory use, after which data is pushed to a temporary file. Check out the pragmas page in the docs.

Would you be open to sharing a bit more about your workflow? DuckDB can query some files (csv, parquet) directly, which may save plenty of RAM. What are the main features you are using Polars for?

Thanks for checking out DuckDB!

[asbisen]

@Alex-Monahan i noticed that by default DuckDB uses /tmp as the tmp space. Is there a way to change this default. In cloud VMs we can use much faster and larger ephemeral disk.

[Alex-Monahan]

Yes! Just use the temp_directory configuration option here:
Configuration Docs