31 ms·

If anybody's interested I've written a script in python that executes a query in a postgres db and saves the results to a parquet file (the script is not as sim

If anybody's interested I've written a script in python that executes a query in a postgres db and saves the results to a parquet file (the script is not as simple as one would expect because of the fact that parquet is not as simple as csv and because the amount of data may be huge). The parquet file is a columnar friendly friendly that can then be simply inserted to clickhouse or duckdb or even queried directly. This script and a cron job are enough for my (not very complex) needs on replicating my postgres data on clickhouse for fast queries. https://github.com/spapas/pg-parquet-py https://github.com/spapas/pg-parquet-py

zX41ZdbW 2y ago
ClickHouse can query PostgreSQL directly, plus you can create a refreshable materialized view, which will do it automatically on a schedule: https://clickhouse.com/docs/sql-reference/table-functions/postgresql https://clickhouse.com/docs/sql-reference/table-functions/po... https://clickhouse.com/docs/materialized-view/refreshable-materialized-view https://clickhouse.com/docs/materialized-view/refreshable-ma... Additionally, you can set up incremental import with https://clickhouse.com/blog/postgres-cdc-connector-clickpipes-public-beta https://clickhouse.com/blog/postgres-cdc-connector-clickpipe...
asah 2y ago
nice. there's also https://github.com/exyi/pg2parquet https://github.com/exyi/pg2parquet which is written in rust. Not sure of pros vs cons. if you use AWS, you can upload to s3 and query via Athena. AWS Glue is a little klunky but it works and if your query load is small then it's cheap and very reliable.
halfcat 2y ago
Thanks for sharing. I’m curious, would pandas, polars, or similar work here (read_sql/to_parquet)? Or is there a challenge around larger than memory data sets (or some other issue I’m not thinking of)?
- spapas82 2y ago
  That's a good question. Unfortunately I haven't tried it because I am not really familiar with pandas.

zX41ZdbW 2y ago

ClickHouse can query PostgreSQL directly, plus you can create a refreshable materialized view, which will do it automatically on a schedule: https://clickhouse.com/docs/sql-reference/table-functions/postgresql https://clickhouse.com/docs/sql-reference/table-functions/po... https://clickhouse.com/docs/materialized-view/refreshable-materialized-view https://clickhouse.com/docs/materialized-view/refreshable-ma... Additionally, you can set up incremental import with https://clickhouse.com/blog/postgres-cdc-connector-clickpipes-public-beta https://clickhouse.com/blog/postgres-cdc-connector-clickpipe...

asah 2y ago

nice. there's also https://github.com/exyi/pg2parquet https://github.com/exyi/pg2parquet which is written in rust. Not sure of pros vs cons. if you use AWS, you can upload to s3 and query via Athena. AWS Glue is a little klunky but it works and if your query load is small then it's cheap and very reliable.

halfcat 2y ago

Thanks for sharing. I’m curious, would pandas, polars, or similar work here (read_sql/to_parquet)? Or is there a challenge around larger than memory data sets (or some other issue I’m not thinking of)?

spapas82 2y ago
That's a good question. Unfortunately I haven't tried it because I am not really familiar with pandas.