Nice job, this will be useful to many, I think. The duckdb sure seems to have a knack for figuring out what their audience needs.
Querying Postgres Tables Directly from DuckDB
11–20 of 41 posts
Re: Querying Postgres Tables Directly from DuckDB
#12DuckDB is awesome! I find it the easiest way to ingest data from various sources then query it into a form I can do analytics on. The datasets I work on are a bit too big for pandas, but spark is way overkill for them. DuckDB lets me efficiently work on them using only a single computer.
DuckDB doesn’t also need to load datasets into memory like pandas? I guess it depends what you’re doing to the data.
Re: Querying Postgres Tables Directly from DuckDB
#13I was looking into exporting a database from DuckDB to Postgres for use with Google Data Studio, but whenever I was exporting DuckDB was attaching decimals to integers. This was throwing off the entire import into Postgres. Think I’ll just try this out.
Re: Querying Postgres Tables Directly from DuckDB
#14This might end up being the best way to etl postgres tables to parquet. From everything else that I tried, doing a copy to CSV and then converting to parquet was the fastest but can be a pain when dealing with type conversions.
Exactly, I almost started writing a tool for that, because nothing else I found supports arrays and composite types. Now let's hope DuckDB does support that :)
If you don't use arrays and composites, Spark should be able to do it, right?
Re: Querying Postgres Tables Directly from DuckDB
#15SQLite and DuckDB are excellent demonstrations that the computers we have on our desks (and even in our pockets) are ridiculously fast and capable for almost everything but the largest workloads. They're a stark contrast to framework bloat that has stolen our CPU cycles and given us perceptible lag when typing and scrolling.
Re: Querying Postgres Tables Directly from DuckDB
#16Wonder if there is anything like this for ClickHouse.
edit: to query pg from clickhouse not clickhouse from duckdb
Re: Querying Postgres Tables Directly from DuckDB
#17This might end up being the best way to etl postgres tables to parquet. From everything else that I tried, doing a copy to CSV and then converting to parquet was the fastest but can be a pain when dealing with type conversions.
Exactly, I almost started writing a tool for that, because nothing else I found supports arrays and composite types. Now let's hope DuckDB does support that :) If you don't use arrays and composites, Spark should be able to do it, right?
Yes, DuckDB supports writing lists and structs to Parquet!
https://github.com/duckdb/duckdb/pull/2832
Does that help or do you have any other questions?
Re: Querying Postgres Tables Directly from DuckDB
#18DuckDB is brilliant. Something I’m working I uses SQLite, there is an analytical search users can perform, it scans most of the DB with multiple joins. It would take about 45 seconds to run the query. I exported the dataset and imported it into DuckDB. The literal same SQL query now takes only 1.5 seconds.
It really shows the difference in how a column store is so much better for curtain queries.
Re: Querying Postgres Tables Directly from DuckDB
#19Wonder if there is anything like this for ClickHouse. edit: to query pg from clickhouse not clickhouse from duckdb
Yes, ClickHouse can query Postgres for maybe two years already.
https://clickhouse.com/docs/en/integrations/postgresql/postg...
You can connect to Postgres using a table function:
SELECT ... FROM postgresql(...)
You can create a table with ENGINE = PostgreSQL and use it like a normal table.
You can create a database with ENGINE = PostgreSQL and it will represent all the tables from the PostgreSQL database.
And finally, you can replicate data from PostgreSQL in realtime with CDC into ClickHouse tables.
Re: Querying Postgres Tables Directly from DuckDB
#20Wonder if there is anything like this for ClickHouse. edit: to query pg from clickhouse not clickhouse from duckdb
Yes, ClickHouse can query Postgres for maybe two years already. https://clickhouse.com/docs/en/integrations/postgresql/postg... You can connect to Postgres using a table function: SELECT ... FROM postgresql(...) You can create a table with ENGINE = PostgreSQL and use it like a normal table. You can create a database with ENGINE = PostgreSQL and it will represent all the tables from the PostgreSQL database. And finall…
Similar features available for MySQL, MongoDB and SQLite.