How to manage schema migrations in Google BigQuery

Question

How to manage schema migrations for Google BigQuery, we have used Liquibase and Flyway in the past. What kind of tools can we use to manage schema modifications and the like (e.g. adding a new column) across dev/staging environments.

Answer 1

Found open source framework for BigQuery schema migration

https://github.com/medjed/bigquery_migration

One more solution

https://robertsahlin.com/automatic-builds-and-version-control-of-your-bigquery-views/

PS

In flyway someone opened the ticket to support BigQuery.

Answer 2

Flyway, a very popular database migration tool, now offers support for BigQuery as a beta, while pending certification.

You can get access to the beta version here: https://flywaydb.org/documentation/database/big-query after answering a short survey.

I've tested it from the command line and it works great! Took me about an hour to get familiar with Flyway's configuration, and now calling it with a yarn command.

Here's an example for a NodeJS project with the following files structure:

package.json
fireway/
    <SERVICE_ACCOUNT_JSON_FILE>
    flyway.conf
    migrations/
        V1_<YOUR_MIGRATION>.sql

package.json

{
  ...
  "scripts": {
    ...
    "migrate": "flyway -configFiles=flyway/flyway.conf migrate"
  },
  ...
}

and flyway.conf:

flyway.url=jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<YOUR_PROJECT_ID>;OAuthType=0;OAuthServiceAcctEmail=<SERVICE_ACCOUNT_NAME>;OAuthPvtKeyPath=flyway/<SERVICE_ACCOUNT_JSON_FILE>;

flyway.schemas=<YOUR_DATASET_NAME>
flyway.user=
flyway.password=

flyway.locations=filesystem:./flyway/migrations
flyway.baselineOnMigrate=true

Then you can just call yarn migrate any time you have new migrations to apply.

Answer 3

I created an adapter for Sequel, sequel-bigquery, so we could manage our BigQuery database schema as a set of Ruby migration files which use Sequel's DSL - the same way we do for our PostgreSQL database.

Example

# migrations/bigquery/001_create_people_table.rb

Sequel.migration do
  change do
    create_table(:people) do
      String :name, null: false
      Integer :age, null: false
      TrueClass :is_developer, null: false
      DateTime :last_skied_at
      Date :date_of_birth, null: false
      BigDecimal :height_m
      Float :distance_from_sun_million_km
    end
  end
end

require 'sequel-bigquery'
require 'logger'

db = Sequel.connect(
  adapter: :bigquery,
  project: 'your-gcp-project',
  database: 'your_bigquery_dataset_name',
  location: 'australia-southeast2',
  logger: Logger.new(STDOUT),
)

Sequel.extension(:migration)

Sequel::Migrator.run(db, 'migrations/bigquery')