Faster PostgresSQL to BigQuery Transfers(tech.marksblogg.com)
tech.marksblogg.com
Faster PostgresSQL to BigQuery Transfers
https://tech.marksblogg.com/postgresql-to-bigquery.html
23 comments
I'll take a look into it.
Last year I found ClickHouse did H3 enrichment much faster than PG or BQ so it has become a go-between for larger workloads with simple enrichment requirements.
The dataset in the post is an example anyone can download. I handle 30B+ records at work.
Last year I found ClickHouse did H3 enrichment much faster than PG or BQ so it has become a go-between for larger workloads with simple enrichment requirements.
The dataset in the post is an example anyone can download. I handle 30B+ records at work.
>It would also be good to see GEOS, GDAL and PROJ integrated into ClickHouse.
You can just add feature requests like this to ClickHouse issue tracker. https://github.com/ClickHouse/ClickHouse/issues/45129
You can just add feature requests like this to ClickHouse issue tracker. https://github.com/ClickHouse/ClickHouse/issues/45129
Very cool to see a walkthrough with actual benchmarks. Not entirely surprised that Parquet shines here. Another big advantage of Parquet over CSV is that you don't have to worry about data integrity. Perhaps less relevant for GIS data, but not having to think about things like string escaping is rather nice.
"It would be great to see data vendors deliver data straight into the Cloud Databases of their customers. It would save a lot of client time that's spent converting and uploading files."
Hear hear! Shameless plug: this is exactly what we enable at prequel.co. If there are any data vendors reading this, or anyone who wants easier access to data from their vendor, we're here to help.
edit: quote fmt
"It would be great to see data vendors deliver data straight into the Cloud Databases of their customers. It would save a lot of client time that's spent converting and uploading files."
Hear hear! Shameless plug: this is exactly what we enable at prequel.co. If there are any data vendors reading this, or anyone who wants easier access to data from their vendor, we're here to help.
edit: quote fmt
For someone whose interaction with spatial data is very limited, I found the article to be a treasure trove of information.
Also, thanks for sharing S2! It'll be nice to look at.
Also, thanks for sharing S2! It'll be nice to look at.
I really love working with parquet and the general arrow ecosystem. The performance and cost ratios you can get out of it are really insane. AWS S3 + parquet + athena is one of the best and cheapest databases I've ever used.
[I work on BigQuery]
BigQuery also supports in-place querying of datasets on GCS (or S3/Azure using Omni) via external/BigLake tables. https://cloud.google.com/bigquery/docs/query-cloud-storage-u...
BigQuery also supports in-place querying of datasets on GCS (or S3/Azure using Omni) via external/BigLake tables. https://cloud.google.com/bigquery/docs/query-cloud-storage-u...
Can we get any of Iceberg/Delta/Hudi that isn't terribly complex to setup? Like configurable completely from Standard SQL via the CREATE EXTERNAL TABLE syntax.
Great to share the combination of tech here and very interested to see how others are ingesting spatial data at scale
Anything to go the other way? I’d like to use BQ to warehouse and be able to examine but PG to do heavy analytics due to the cost once you really start doing many repeated queries.
I guess I could just dump directly to CSVs and download but BQ is a nice convenient bottomless data bucket.
I guess I could just dump directly to CSVs and download but BQ is a nice convenient bottomless data bucket.
do you already have an ETL pipeline to BQ? You could use Airbyte[0] for pg to bq. It's pretty heavy if this is your only source, but should work well if you need to query the pg data in bq frequently.
0 - https://airbyte.com/connections/PostgreSQL-to-BigQuery
0 - https://airbyte.com/connections/PostgreSQL-to-BigQuery
They said "the other way" so BQ to Postgres.
BigQuery and Postgres are both sources and destinations in Airbyte, the data can flow either way.
Airbyte looks nice! thanks.
You can utilize BI engine for this exact purpose: https://cloud.google.com/bigquery/docs/bi-engine-query
Does anyone know what tools can be used to stream the result of a mongodb query into bigquery?
Nice to read this, I had a similar type of assignment 15 years ago, visualize the rollout of the fiber optics network across the city. But we had a lot less data to deal with.
Is there a reason not to used federated queries to hit postgres directly?
Federation is slow
(i was the pm for bigquery federation with gcs and cloudsql)
(i was the pm for bigquery federation with gcs and cloudsql)
[1] https://gdal.org
[2] https://www.gaia-gis.it/fossil/libspatialite/index