# BigQuery
> Load data from Google BigQuery into MotherDuck using the duckdb-bigquery community extension.
BigQuery is Google Cloud's fully-managed, serverless data warehouse that lets you run SQL queries on Google's infrastructure.

## Choose an execution environment

This integration runs the `bigquery` community extension in a local DuckDB
process. Use it from the DuckDB CLI or a DuckDB SDK for interactive work.

MotherDuck cloud SQL, including read-only MCP queries, cannot install or load
community extensions. For scheduled ingestion that runs on MotherDuck, use a
[Flight](/concepts/flights/) and load the extension in the Flight's in-process
DuckDB runtime. See [Where code runs](/concepts/execution-environments/) for
the boundary, and [Incrementally ingest BigQuery into MotherDuck](/cookbook/flight-bigquery-ingest/)
for the scheduled pattern.

To load data from BigQuery into MotherDuck, use the [`duckdb-bigquery` community extension](https://github.com/hafenkran/duckdb-bigquery). It reads through the BigQuery Storage Read API with parallel streams, filter pushdown, and Arrow compression — and loads results straight into DuckDB or MotherDuck without any glue code.

## Prerequisites

- DuckDB installed (using the CLI or Python).
- Access to a GCP project with BigQuery enabled.
- Valid Google Cloud credentials, provided through one of:
  - the `GOOGLE_APPLICATION_CREDENTIALS` environment variable, or
  - `gcloud auth application-default login`.

Minimum required IAM roles:

- `BigQuery Data Viewer`
- `BigQuery Job User`

## Loading data from BigQuery into MotherDuck

The following examples use the [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli.mdx), but you can use any local DuckDB client.

### Install and load the extension

```sql
INSTALL bigquery FROM community;
LOAD bigquery;
```

### Attach a BigQuery project

To read data from your project, attach it like you would attach a DuckDB database:

```sql
ATTACH 'project=my-gcp-project' AS bq (TYPE bigquery, READ_ONLY);
```

To read from a public dataset, use the following syntax:

```sql
ATTACH 'project=bigquery-public-data dataset=pypi billing_project=my-gcp-project'
AS bq_public (TYPE bigquery, READ_ONLY);
```

### Query a table

Once attached, you can query BigQuery tables directly using standard SQL syntax:

```sql
SELECT * FROM bq.dataset_name.table_name LIMIT 10;
```

Behind the scenes, this uses `bigquery_scan`. The extension also exposes two functions you can call directly:

**`bigquery_scan`** — for direct reads from a single table:

```sql
SELECT * FROM bigquery_scan('my_gcp_project.my_dataset.my_table');
```

**`bigquery_query`** — for custom [GoogleSQL](https://cloud.google.com/bigquery/docs/introduction-sql), including views and external tables that the Storage Read API can't access on its own:

```sql
SELECT * FROM bigquery_query(
  'my_gcp_project',
  'SELECT * FROM `my_gcp_project.my_dataset.my_table` WHERE column = "value"'
);
```

Both functions share the same Arrow scan engine. For very large reads, you can enable parallel read streams by relaxing DuckDB's default ordering guarantee:

```sql
SET preserve_insertion_order = FALSE;
```

### Load data into MotherDuck

Verify the `motherduck_token` environment variable is set, then attach MotherDuck:

```sql
ATTACH 'md:';
```

Use `CREATE TABLE ... AS` to create a new table, or `INSERT INTO ... SELECT` to append data to an existing one:

```sql
CREATE DATABASE IF NOT EXISTS pypi_playground;
USE pypi_playground;

CREATE TABLE IF NOT EXISTS duckdb_sample AS
SELECT *
FROM bq_public.pypi.file_downloads
WHERE project = 'duckdb'
AND timestamp = TIMESTAMP '2025-05-26 00:00:00'
LIMIT 100;
```

## Load on a schedule with a Flight

A [Flight](/concepts/flights) is Python that MotherDuck schedules and runs next to your data. Use one instead of running the copy by hand when the load should repeat on a cron, retry on a transient failure, and keep a run history.

Because community extensions load in a DuckDB client rather than on MotherDuck's server-side runtime, the Flight installs `bigquery` in its own in-process DuckDB first, then loads `motherduck` and attaches:

```python
import duckdb

def main():
    con = duckdb.connect()
    con.execute("INSTALL bigquery FROM community; LOAD bigquery;")
    con.execute("LOAD motherduck;")
    con.execute("ATTACH 'md:';")
    con.execute("ATTACH 'project=my-gcp-project' AS bq (TYPE bigquery, READ_ONLY)")
    con.execute(
        "CREATE OR REPLACE TABLE my_db.main.events AS "
        "SELECT * FROM bq.analytics.events"
    )

if __name__ == "__main__":
    main()
```

Load `bigquery` before `motherduck`, as on [DuckDB extensions in MotherDuck](/concepts/duckdb-extensions), so the BigQuery scan stays in the local client instead of being routed to MotherDuck.

The service-account JSON is a credential, so keep it in a [Flight secret](/sql-reference/motherduck-sql-reference/create-secret#flight-secrets) rather than in the Flight's source or config. Each `PARAMS` key is injected into the run as an environment variable, which the Flight writes to a file and points `GOOGLE_APPLICATION_CREDENTIALS` at:

```sql
CREATE SECRET gcp_creds IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {
        'GOOGLE_APPLICATION_CREDENTIALS_JSON': '<service_account_json>'
    }
);
```

Create the Flight with [`MD_CREATE_FLIGHT`](/sql-reference/motherduck-sql-reference/flights/md-create-flight), passing the Python above as `source_code`, `flight_secret_names := ['gcp_creds']`, and a `schedule_cron` for the cadence you want. [Incrementally ingest BigQuery into MotherDuck](/cookbook/flight-bigquery-ingest) is a ready-made version that handles the credentials file, watermarks, and backfill windows.


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Fdatabases%2Fbigquery%2F&page_title=BigQuery&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.