Overview

Google BigQuery can be used as a data source or a data destination in Mitto.

Source plugin example: Query

Destination plugin examples: CSV, Salesforce, SQL

Google BigQuery as a data destination

Mitto automatically

• Creates Google BigQuery datasets if they don't exist
• Creates Google BigQuery dataset tables if they don't exist
• Determines data types for Google BigQuery columns
• Adds new columns to Google BigQuery tables based on new fields in source systems
• Adjusts Google BigQuery tables based on changes in source data

Google BigQuery specific setup

The Google BigQuery API requires using a service account key which is downloaded as a JSON file and stored in Mitto.

Below is an example of what this key file contains:

{
  "type": "service_account",
  "project_id": "mitto-183418",
  "private_key_id": "...",
  "private_key": "...",
  "client_email": "...",
  "client_id": "...",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "..."
}

The JSON key file contains the project_id which is used in the database url.

Add the role BigQuery User to the service account.

Below is the database url structure for connecting to a Google BigQuery database:

bigquery://<project_id>/<dataset>

Here's an example of using Google BigQuery as a destination in a CSV job:

NOTE: When outputting to Google BigQuery, leave "Schema" blank and append the dataset name to the end of the output database URL.

SQL

Mitto can send SQL statements to Google BigQuery. Use Google BigQuery syntax in these Mitto SQL jobs.