pg2bq

pg2bq is made for one thing: export tables from PostgreSQL to Google BigQuery.

Why ?

It's useful to keep the data at both places "in-sync" (using cron, airflow, or whatever to schedule the export on a regular basis). If your metadata are on PostgreSQL, but your realtime data are in BigQuery, it's probable you want to join them.

Note: internally, it is using the Spark framework for the sake of simplicity, but no Hadoop cluster is needed. It is configured as a "local" cluster by default, meaning the application is running standalone.

How to run it

• Download the release made on GitHub: pg2bq-1.0.3.zip
• Create a Service Account to Google Cloud which has access to GCS and BigQuery, and create a json key
• Create a configuration file configuration.conf for pg2bq to know where to grab and put the data (HOCON):

  
  jdbc {
  url = "jdbc:postgresql://mypg:5432/mydb"
  user = "myuser"
  password = "mypwd"
  tables = [ "user", "campaign", "website" ]
  }

gcloud { project = "gcloud-project-id" service-account-key-path = "/path/to/service-account-key.json" bq.dataset = "mypg" gcs.tmp-bucket = "pg-export-tmp" }

- Run the application specifying the config file:

GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account-key.json ./bin/pg2db -Dconfig.file=configuration.conf

- Done!

Add this to a scheduler every 10min and enjoy your JOINs in BigQuery.

# What does it do exactly ?

- It exports the data from the tables into DataFrames
- It saves them into GCS as `.avro` to keep the schema along the data: this will avoid to specify/create the BigQuery table schema beforehands.
- It starts BigQuery jobs to import those `.avro` into the respective BigQuery tables.

# Development

To run the application in dev mode, create a proper `application.conf` and run the app:

- via the IDE, just run `Main`
- via sbt, by pre-packaging the whole thing:

$ sbt stage && ./target/universal/stage/bin/pg2bq