DuckDB python API: query composition

Question

Suppose I use DuckDB with python, for querying an Apache parquet file test.pq with a table containing two columns f1 and f2.

r1 = duckdb.query("""
SELECT f1 FROM parquet_scan('test.pq') WHERE f2 > 1
""")

Now I would like to use r1 result in another query, like:

duckdb.query("""SELECT * FROM r1 WHERE f1 > 10""")

However, last instruction gives: RuntimeError: Catalog Error: Table with name r1 does not exist!

Am I missing a DuckDB method equivalent to Apache Spark registerTempTable() ?

Pedro Holanda · Accepted Answer · 2021-07-30 12:54:32Z

4

r1 = duckdb.query("""
SELECT f1 FROM parquet_scan('test.pq') WHERE f2 > 1
""")

Does not create a table called r1, but actually creates a relation that is nothing else than an execution plan. Hence if you call an execute on that, would actually execute a query that scans the parquet file

result = r1.execute()

If you want to query it as a table, you basically have two options.

You create a view from your relation

r1.create_view('table_name')

You change your SQL query to create a duckdb table

conn = duckdb.connect()
conn.execute("create table t as SELECT f1 FROM parquet_scan('test.pq') where f2 > 1 ")

Note that in 1 you will actually load the parquet data to a Duck table, while with 2 you will be constantly reading the parquet data itself.

Finally, if you just want to stack up filters, then you could do:

r2 = r1.filter("f1>10")

There is more info on the Python Relational Api on Duckdb's website, more specifically at: https://duckdb.org/docs/api/python

https://github.com/duckdb/duckdb/blob/master/examples/python/duckdb-python.py

Hopefully, that was helpful! ;-)

answered Jul 30, 2021 at 12:54

Pedro Holanda

3111 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wisperwind Over a year ago

Isn't it exactly the other way around, creating a view as in 1 will read the parquet again and again for each query, and 2 will create and in-memory duckdb table and query that? Otherwise, this would completely counter to the intuition I have for a "view".

Collectives™ on Stack Overflow

DuckDB python API: query composition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related