This documentation describes the integration of MindsDB with Google BigQuery, a fully managed, AI-ready data analytics platform that helps you maximize value from your data. The integration allows MindsDB to access data stored in the BigQuery warehouse and enhance it with AI capabilities.

​
Prerequisites

Before proceeding, ensure the following prerequisites are met:
  1. Install MindsDB locally via Docker or Docker Desktop.
  2. To connect BigQuery to MindsDB, install the required dependencies following this instruction.

​
Connection

Establish a connection to your BigQuery warehouse from MindsDB by executing the following SQL command: 
CREATE DATABASE bigquery_datasource
WITH
   engine = "bigquery",
   parameters = {
      "project_id": "bgtest-1111",
      "dataset": "mydataset",
      "service_account_keys": "/tmp/keys.json"
   };
Required connection parameters include the following:
  • project_id: The globally unique identifier for your project in Google Cloud where BigQuery is located.
  • dataset: The default dataset to connect to.
Optional connection parameters include the following:
  • service_account_keys: The full path to the service account key file.
  • service_account_json: The content of a JSON file defined by the service_account_keys parameter.
One of service_account_keys or service_account_json has to be provided to establish a connection to BigQuery.

​
Usage

Retrieve data from a specified table in the default dataset by providing the integration name and table name: 
SELECT *
FROM bigquery_datasource.table_name
LIMIT 10;
Retrieve data from a specified table in a different dataset by providing the integration name, dataset name and table name: 
SELECT *
FROM bigquery_datasource.dataset_name.table_name
LIMIT 10;
Run SQL in any supported BigQuery dialect directly on the connected BigQuery database: 
SELECT * FROM bigquery_datasource (

   --Native Query Goes Here
   SELECT *
   FROM t1
   WHERE t1.a IN (SELECT t2.a
                  FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);

);
The above examples utilize bigquery_datasource as the datasource name, which is defined in the CREATE DATABASE command.

​
Troubleshooting Guide

Database Connection Error
  • Symptoms: Failure to connect MindsDB with the BigQuery warehouse.
  • Checklist:
    1. Make sure that the Google Cloud account is active and the Google BigQuery service is enabled.
    2. Confirm that the project ID, dataset and service account credentials are correct. Try a direct BigQuery connection using a client like DBeaver.
    3. Ensure a stable network between MindsDB and Google BigQuery.
SQL statement cannot be parsed by mindsdb_sql
  • Symptoms: SQL queries failing or not recognizing table names containing spaces or special characters.
  • Checklist:
    1. Ensure table names with spaces or special characters are enclosed in backticks. Examples: _ Incorrect: SELECT _ FROM integration.travel data _ Incorrect: SELECT _ FROM integration.‘travel data’ _ Correct: SELECT _ FROM integration.`travel data`