Integrates DuckDB with Google BigQuery, allowing direct querying and management of BigQuery datasets
Installing and Loading
INSTALL bigquery FROM community;
LOAD bigquery;
Example
-- Attach to your BigQuery Project
D ATTACH 'project=my_gcp_project' AS bq (TYPE bigquery, READ_ONLY);
-- Show all tables in all datasets in the attached BigQuery project
D SHOW ALL TABLES;
┌──────────┬──────────────────┬──────────┬──────────────┬───────────────────┬───────────┐
│ database │ schema │ name │ column_names │ column_types │ temporary │
│ varchar │ varchar │ varchar │ varchar[] │ varchar[] │ boolean │
├──────────┼──────────────────┼──────────┼──────────────┼───────────────────┼───────────┤
│ bq │ quacking_dataset │ duck_tbl │ [i, s] │ [BIGINT, VARCHAR] │ false │
| bq | barking_dataset | dog_tbl | [i, s] | [BIGINT, VARCHAR] │ false |
└──────────┴──────────────────┴──────────┴──────────────┴───────────────────┴───────────┘
-- Select data from a specific table in BigQuery
D SELECT * FROM bq.quacking_dataset.duck_tbl;
┌───────┬────────────────┐
│ i │ s │
│ int32 │ varchar │
├───────┼────────────────┤
│ 12 │ quack 🦆 │
│ 13 │ quack quack 🦆 │
└───────┴────────────────┘
About bigquery
The DuckDB BigQuery Extension integrates DuckDB with Google BigQuery, allowing direct querying and management of BigQuery datasets. For detailed setup and usage instructions, visit the extension repository.
Added Functions
| function_name | function_type | description | comment | example |
|---|---|---|---|---|
| bigquery_attach | table | Attach to a BigQuery project. | ATTACH 'project=my_gcp_project' as bq (TYPE bigquery); | |
| bigquery_scan | table | Scan a single table directly from BigQuery. | SELECT * FROM bigquery_scan('my_gcp_project.quacking_dataset.duck_tbl'); | |
| bigquery_query | table | Run a custom GoogleSQL query in BigQuery and read the results. | SELECT * FROM bigquery_query('bq', 'SELECT * FROM quacking_dataset.duck_tbl WHERE duck_id = 123'); | |
| bigquery_execute | table | Execute an arbitrary GoogleSQL query in BigQuery. | CALL bigquery_execute('bq', 'CREATE SCHEMA deluxe_dataset OPTIONS(location="us", default_table_expiration_days=3.75);') | |
| bigquery_jobs | table | List jobs in a BigQuery project. | SELECT * FROM bigquery_jobs('bq'); | |
| bigquery_clear_cache | table | Clear the internal caches to refetch the most current project information from BigQuery. | CALL bigquery_clear_cache(); |
Added Settings
| name | description | input_type | scope |
|---|---|---|---|
| bq_curl_ca_bundle_path | Path to the CA bundle for curl | VARCHAR | GLOBAL |
| bq_debug_show_queries | DEBUG SETTING: print all queries sent to BigQuery to stdout | BOOLEAN | GLOBAL |
| bq_experimental_filter_pushdown | Whether to use filter pushdown (currently experimental) | BOOLEAN | GLOBAL |
| bq_experimental_use_info_schema | Whether to fetch table infos from BQ information schema (currently experimental). Can be significantly faster than fetching from REST API. | BOOLEAN | GLOBAL |