Integrating with DuckDB

DuckDB is an in-process analytical database. The speed is like that of Polars, which is really fast. Like Polars, DuckDB has built-in integrations with other tools. One example is its integration with Arrow. Just like Polars, DuckDB allows for zero-copy to and from the Arrow format. DuckDB has APIs in many languages such as Python, C, Go, R, and Swift. You can check out DuckDB’s documentation to learn more about it at https://duckdb.org/.

DuckDB has a solid SQL API with a rich set of features. It’s superior to the SQL API in Polars as of the time of writing this. You can also run a SQL query on a Polars DataFrame directly without copying data. Even if you convert a DuckDB relation to a Polars DataFrame or vice versa, it allows you to enable zero-copy operations, thanks to Apache Arrow columnar memory format used both in Polars and DuckDB.

Getting ready

You need to have PyArrow installed as Arrow is the fundamental technology through which DuckDB and Polars can interoperate. If you haven’t, execute the following code in the command line:

pip install duckdb

How to do it...

Here’s how to use DuckDB with Polars. We’ll first see how to run an SQL query on a Polars DataFrame with DuckDB. And then we’ll see how to convert a DuckDB relation to a Polars DataFrame.

1. Run a SQL query with DuckDB directly on a Polars DataFrame:

   import duckdb
   df = pl.DataFrame({
       'a': [1,2,3]
   })
   rel = duckdb.sql('SELECT * FROM df')
   rel.show()

   The preceding code will return the following output:

Figure 10.4 – The result of a DuckDB SQL query on a Polars DataFrame

2. Convert a DuckDB relation to a Polars DataFrame:

   rel.pl()

   The preceding code will return the following output:

Figure 10.5 – A Polars DataFrame converted from a DuckDB relation

How it works...

In step 1, we ran a SQL query on a Polars DataFrame through DuckDB. In step 2, we converted a DuckDB relation to a Polars DataFrame. In both methods, we used DuckDB’s functionalities. DuckDB is already compatible to work with Polars. This is all thanks to DuckDB having the zero-copy integration with Arrow, as well as Polars using Arrow for its memory model.

Although you can run DuckDB SQL on both Polars DataFrame and Polars LazyFrame, the resulting DuckDB relation doesn’t retain the laziness from the input LazyFrame.

Tip

You might wonder which tool you should use (Polars or DuckDB) if you’re solely using SQL. My answer is DuckDB, as the SQL API is more mature and includes the whole list of standard SQL functions, as well as more advanced ones such as those that work on nested data types. It’s just a more full-featured SQL API than that of Polars as of the time of writing.

See also

To learn more about Polars integration with DuckDB or DuckDB in general, please refer to these additional resources:

• https://duckdb.org/
• https://duckdb.org/docs/guides/python/polars.html