5 ms·

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been s

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? 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.

tbrock 5y ago
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.
spookylettuce 5y ago
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.
- tlarkworthy 5y ago
  I second federated table connector (and scheduled queries).
  - deleted 5y ago
    [deleted]
- patwolf 5y ago
  Same here. We previously used Stitch until they dropped the free tier.
- sa46 5y ago
  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.
- nceasy 5y ago
  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
mrbungie 5y ago
An option, albeit a bit complex would be: Debezium => Kafka => Parquet in Google Cloud Storage => BigQuery external queries.
- gunnarmorling 5y ago
  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); 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
jeffbee 5y ago
Bigquery can read directly from cloud SQL.
- pyrophane 5y ago
  Are you referring to federated queries?
  - mikeklaas 5y ago
    You can do something like: CREATE TABLE xx AS SELECT * FROM EXTERNAL_QUERY('postgres.db', 'SELECT * FROM my_table'));
joeswartz 5y ago
One option is to use DBeam (http://github.com/spotify/dbeam http://github.com/spotify/dbeam) to export Avro records into GCS and then load to BigQuery with a load job.
hermanradtke 5y ago
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.
idunno246 5y ago
presumably alooma would be the one, since google aquired them, but two+ years they still arent integrated but stopped accepting new customers
thejosh 5y ago
We use fivetran for Heroku postgres, but also use it for other random sources to Snowflake. It's decent.

tbrock 5y ago

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.

spookylettuce 5y ago

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.

tlarkworthy 5y ago
I second federated table connector (and scheduled queries).
- deleted 5y ago
  [deleted]
patwolf 5y ago
Same here. We previously used Stitch until they dropped the free tier.
sa46 5y ago
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.
nceasy 5y ago
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

mrbungie 5y ago

An option, albeit a bit complex would be: Debezium => Kafka => Parquet in Google Cloud Storage => BigQuery external queries.

gunnarmorling 5y ago
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); 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

jeffbee 5y ago

Bigquery can read directly from cloud SQL.

pyrophane 5y ago
Are you referring to federated queries?
- mikeklaas 5y ago
  You can do something like: CREATE TABLE xx AS SELECT * FROM EXTERNAL_QUERY('postgres.db', 'SELECT * FROM my_table'));

joeswartz 5y ago

One option is to use DBeam (http://github.com/spotify/dbeam http://github.com/spotify/dbeam) to export Avro records into GCS and then load to BigQuery with a load job.

hermanradtke 5y ago

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.

idunno246 5y ago

presumably alooma would be the one, since google aquired them, but two+ years they still arent integrated but stopped accepting new customers

thejosh 5y ago

We use fivetran for Heroku postgres, but also use it for other random sources to Snowflake. It's decent.