# BigQuery

## 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](https://docs.slingdata.io/connections/database-connections/adbc) 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=<project> dataset=<dataset> gc_bucket=<gc_bucket> key_file=/path/to/service.account.json location=<location>
```

{% endcode %}

### Environment Variable

See [here](https://docs.slingdata.io/sling-cli/environment#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/sling-cli/environment#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
connections:
  BIGQUERY:
    type: bigquery
    project: <project>
    dataset: <dataset>
    gc_bucket: <gc_bucket>
    key_file: '<key_file>'

  # using with `key_body` instead of `key_file`
  BIGQUERY:
    type: bigquery
    project: <project>
    dataset: <dataset>
    gc_bucket: <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)
```

If you are facing issues connecting, please reach out to us at <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).