Re:Logical replication and decoding for Cloud SQL for Postgr...
by tbrock on Saturday June 05, 2021 @01:28PM
(#27406939)
We get data into BigQuery from pg using a custom airflow plugin but recently started doing something similar on AWS with DMS to get data from postgres -> redshift.
DMS is awesome. No code is the best code. Big query is great but not having a “snap-your-fingers and the data is there” connector makes it a PITA for me to maintain.
As a result… we are using more redshift.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by joeswartz on Saturday June 05, 2021 @05:06PM
(#27408457)
One option is to use DBeam (http://github.com/spotify/dbeam) to export Avro records into GCS and then load to BigQuery with a load job.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by spookylettuce on Saturday June 05, 2021 @01:35PM
(#27407002)
I tend to utilize bigquery's external connections and scheduled queries to periodically clone my "hot" PG data to an BQ table for long term storage and analysis.
It seems so much easier to go PG=>BQ than the other way around.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by sa46 on Saturday June 05, 2021 @04:29PM
(#27408221)
Is the idea something like a scheduled:
INSERT INTO bq_table SELECT * FROM EXTERNAL_QUERY('');
I'm guessing you're on the hook for keeping the schema up to date with the Postgres schema.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by nceasy on Saturday June 05, 2021 @08:18PM
(#27409551)
I also use the external data connector, the problem is when you have two projects in different locations. Then I go for psycopg2 and uploat it to bigquery using bigquery magic lines
Re:Logical replication and decoding for Cloud SQL for Postgr...
by patwolf on Saturday June 05, 2021 @01:53PM
(#27407163)
Same here. We previously used Stitch until they dropped the free tier.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by tlarkworthy on Saturday June 05, 2021 @01:48PM
(#27407119)
I second federated table connector (and scheduled queries).
Re:Logical replication and decoding for Cloud SQL for Postgr...
by hermanradtke on Saturday June 05, 2021 @05:12PM
(#27408490)
We went with StitchData over FiveTran. We also had to build a custom way to delete records. Hopefully we can get rid of this soon.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by mrbungie on Saturday June 05, 2021 @01:36PM
(#27407022)
An option, albeit a bit complex would be:
Debezium => Kafka => Parquet in Google Cloud Storage => BigQuery external queries.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by gunnarmorling on Saturday June 05, 2021 @06:04PM
(#27408790)
Why the Parquet step? You should be able to do straight Debezium -> Kafka -> BQ, using the BQ sink connector for Kafka Connect (https://github.com/confluentinc/kafka-connect-bigquery); we have users using this with the Debezium MySQL connector, I'd expect this to work equally for Postgres.
Disclaimer: working on Debezium
Re:Logical replication and decoding for Cloud SQL for Postgr...
by thejosh on Saturday June 05, 2021 @11:04PM
(#27410412)
We use fivetran for Heroku postgres, but also use it for other random sources to Snowflake.
It's decent.
Re:Logical replication and decoding for Cloud SQL for Postgr...
by idunno246 on Saturday June 05, 2021 @07:27PM
(#27409283)
presumably alooma would be the one, since google aquired them, but two+ years they still arent integrated but stopped accepting new customers
Re:Logical replication and decoding for Cloud SQL for Postgr...
by jeffbee on Saturday June 05, 2021 @01:48PM
(#27407124)
Bigquery can read directly from cloud SQL.
Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes.
Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.