> For the complete documentation index, see [llms.txt](https://docs.slingdata.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.slingdata.io/connections/database-connections/bigquery.md). # BigQuery Connect & Ingest data from / to a BigQuery database ## Setup The following credentials keys are accepted: * `project` **(required)** -> The GCP project ID for the project * `dataset` **(required)** -> The default dataset (like a schema) * `gc_bucket` (optional) -> The Google Cloud Storage Bucket to use for loading (Recommended) * `key_file` (optional) -> The path of the Service Account JSON. If not provided, the Google [Application Default Credentials](https://cloud.google.com/docs/authentication/application-default-credentials) will be used. * `key_body` (optional) -> The Service Account JSON key content as a string. You can also provide the JSON content in env var `GC_KEY_BODY`. * `location` (optional) -> The location of the account, such as `US` or `EU`. Default is `US`. * `extra_scopes` (optional) -> An array of strings, which represent scopes to use in addition to `https://www.googleapis.com/auth/bigquery`. e.g. `["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/spreadsheets"]` * `use_adbc` (optional) -> Enable Arrow Database Connectivity (ADBC) driver for high-performance data transfer. See [ADBC](/connections/database-connections/adbc.md) for setup and details. (*v1.5.2+*) * `adbc_uri` (optional) -> Override the automatically constructed ADBC connection URI when using `use_adbc=true`. {% hint style="warning" %} If you'd like to have sling use the machine's Google Cloud Application Default Credentials (usually with `cloud auth application-default login`), don't specify a `key_file` (or the env var `GC_KEY_BODY`). {% endhint %} ### Using `sling conns` Here are examples of setting a connection named `BIGQUERY`. We must provide the `type=bigquery` property: {% code overflow="wrap" %} ```bash $ sling conns set BIGQUERY type=bigquery project= dataset= gc_bucket= key_file=/path/to/service.account.json location= ``` {% endcode %} ### Environment Variable See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#dot-env-file-.env.sling) to learn more about the `.env.sling` file. {% code overflow="wrap" %} ```bash export BIGQUERY='{type: bigquery, project: my-google-project, gc_bucket: my_gc_bucket, dataset: public, location: US, key_file: /path/to/service.account.json}' ``` {% endcode %} You can also provide Sling the Service Account JSON in `key_body` as a string, or via environment variable `GC_KEY_BODY`, instead of a `key_file`. {% code overflow="wrap" %} ```bash export GC_KEY_BODY='{"type": "service_account","project_id": ...........}' ``` {% endcode %} ### Sling Env File YAML See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file. ```yaml connections: BIGQUERY: type: bigquery project: dataset: gc_bucket: key_file: '' # using with `key_body` instead of `key_file` BIGQUERY: type: bigquery project: dataset: gc_bucket: key_body: | { "type": "service_account", ... } ``` ### BigQuery Table Partitioning ```yaml streams: my_schema.another_table: object: my_dataset.{stream_table} target_options: table_keys: partition: [ DATE_TRUNC(transaction_date, MONTH) ] # OR streams: my_schema.another_table: object: my_dataset.{stream_table} target_options: table_ddl: | CREATE TABLE my_dataset.{stream_table} ({col_types}) PARTITION BY DATE_TRUNC(transaction_date, MONTH) OPTIONS ( partition_expiration_days = 3, require_partition_filter = TRUE) ``` ## BigQuery as a target When BigQuery is the target, Sling writes with **BigQuery load jobs**, not streaming inserts. The write path is: 1. **Stage the data as files.** Sling writes the incoming stream to files (CSV by default). If the `gc_bucket` credential is set, the files are staged in that **Google Cloud Storage** bucket; if it is not set, they are staged locally and streamed to the load job. Setting `gc_bucket` is recommended for anything but small loads. 2. **Run a load job.** Sling submits a BigQuery load job to read the staged files into a temporary table. Load jobs are batched and rate-limited, so a load is one job per batch rather than a row-by-row insert. Sling does **not** use BigQuery streaming inserts. 3. **Finalize.** For `full-refresh` the target is dropped and replaced. For `incremental` Sling runs a native BigQuery `MERGE` from the temporary table into the target using your `primary_key` and the resolved `merge_strategy` — all four [merge strategies](/concepts/replication/merge-strategy.md) are supported. ```yaml source: MY_POSTGRES target: BIGQUERY defaults: object: my_dataset.{stream_table} streams: public.users: mode: full-refresh public.events: mode: incremental primary_key: [id] update_key: updated_at ``` {% hint style="info" %} Provide `gc_bucket` so Sling stages through GCS. Without it Sling falls back to a local file stage, which works but is slower and less resilient for large tables. See [Setup](#setup) for the credential. {% endhint %} For BigQuery-specific incremental caveats (partitioned targets, `require_partition_filter`), see [BigQuery Table Partitioning](#bigquery-table-partitioning) above and [BigQuery as a source](#bigquery-as-a-source) below. ## BigQuery as a source BigQuery is a standard SQL source: Sling reads it with a `SELECT` statement, so full-refresh, incremental, and backfill all work. ### Partitioned tables A plain full-refresh reads a partitioned table with `SELECT *` — partitioning by itself does not block the read. If the source table sets **`require_partition_filter = TRUE`**, BigQuery rejects any query that does not filter on the partition column. Sling does not detect this option or inject a partition predicate automatically, so a plain `SELECT *` fails. Add a `where` source option (or `--where` flag) that filters the partition column to satisfy the requirement: ```yaml source: BIGQUERY target: MY_SNOWFLAKE streams: analytics.events: mode: full-refresh source_options: where: "event_date >= '2024-01-01'" ``` ```bash sling run --src-conn BIGQUERY --src-stream analytics.events \ --tgt-conn MY_SNOWFLAKE --tgt-object public.events \ --mode full-refresh --where "event_date >= '2024-01-01'" ``` ### Incremental from BigQuery BigQuery works as an incremental source with `primary_key` + `update_key` like any SQL database. Sling pulls rows where `update_key > max(update_key)` in the target: ```yaml source: BIGQUERY target: MY_SNOWFLAKE streams: analytics.events: mode: incremental primary_key: [id] update_key: updated_at ``` If the table also has `require_partition_filter = TRUE`, the incremental predicate is on the `update_key`, which is not necessarily the partition column. Add a `where` on the partition column, or make the partition column your `update_key`, so BigQuery's filter requirement is met. ### Multiple datasets to Snowflake (full-refresh, wildcards) Use a wildcard stream to replicate every table across datasets. Reference the source dataset in the object name with the `{stream_schema}` runtime variable: ```yaml source: BIGQUERY target: MY_SNOWFLAKE defaults: mode: full-refresh object: raw.{stream_schema}_{stream_table} streams: # all tables in these datasets analytics.*: marketing.*: ``` Source partitioning does not affect full-refresh ingestion (unless `require_partition_filter` is set — see above). If you are facing issues connecting, give [`sling assist`](/sling-cli/ai/assist.md) a try, or reach out to us at , on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues). --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.slingdata.io/connections/database-connections/bigquery.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.