Google BigQuery

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`