> ## Documentation Index
> Fetch the complete documentation index at: https://astronomer.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Create a BigQuery connection in Airflow

[BigQuery](https://cloud.google.com/bigquery) is Google's fully managed and serverless data warehouse. Integrating BigQuery with Airflow lets you execute BigQuery jobs from a DAG.

There are multiple ways to connect Airflow and BigQuery, all of which require a [GCP Service Account](https://cloud.google.com/docs/authentication#service-accounts):

* Use the contents of a service account key file directly in an Airflow connection.
* Copy the service account key file to your Airflow project.
* Store the contents of a service account key file in a secrets backend.
* Use a Kubernetes service account to integrate Airflow and BigQuery. This is possible only if you run Airflow on Astro or Google Kubernetes Engine (GKE).

Using a Kubernetes service account is the most secure method because it doesn't require storing a secret in Airflow's metadata database, on disk, or in a secrets backend. The next most secure connection method is to store the contents of your service account key file in a secrets backend.

<Tip>
  If you're an Astro user, Astronomer recommends using workload identity to authorize your Deployments to BigQuery. This eliminates the need to specify secrets in your Airflow connections or copying credentials file to your Astro project. See [Authorize Deployments to your cloud](/docs/astro/authorize-deployments-to-your-cloud).
</Tip>

## Prerequisites

* The [Astro CLI](/docs/cli/v1.43/overview).
* A locally running [Astro project](/docs/cli/v1.43/get-started-cli).
* A Google Cloud project with [BigQuery API](https://cloud.google.com/bigquery/docs/enable-transfer-service#enable-api) enabled.
* Permissions to create an IAM service account or use an existing one. See [Google documentation](https://cloud.google.com/iam/docs/manage-access-service-accounts).

## Get connection details

A connection from Airflow to Google BigQuery requires the following information:

* Service account name
* Service account key file
* Google Cloud Project ID

Complete one of the following sets of steps to retrieve these values:

<details>
  <summary>Key File Value</summary>

  This method requires you to save the contents of your service account key file in your Airflow connection.

  1. In your Google Cloud console, select your Google Cloud project and copy its **ID**.
  2. [Create a new service account](https://cloud.google.com/iam/docs/service-accounts-create).
  3. [Grant roles](https://cloud.google.com/iam/docs/grant-role-console) to your service account so that it can access BigQuery. See [BigQuery roles](https://cloud.google.com/bigquery/docs/access-control#bigquery) for a list of available roles and the permissions.
  4. [Add a new JSON key file](https://cloud.google.com/iam/docs/keys-create-delete#iam-service-account-keys-create-console) to the service account.
  5. Copy the contents of the key file.
</details>

<details>
  <summary>Key File In Container</summary>

  This method requires you to mount your service account key file to your Airflow containers.

  1. In your Google Cloud console, select your Google Cloud project and copy its **ID**.
  2. [Create a new service account](https://cloud.google.com/iam/docs/service-accounts-create).
  3. [Grant roles](https://cloud.google.com/iam/docs/grant-role-console) to your service account so that it can access BigQuery. See [BigQuery roles](https://cloud.google.com/bigquery/docs/access-control#bigquery) for a list of available roles and the permissions.
  4. [Add a new JSON key file](https://cloud.google.com/iam/docs/keys-create-delete#iam-service-account-keys-create-console) to the service account.
  5. Download the key file.
</details>

<details>
  <summary>Key File In Secrets Backend</summary>

  You can save your service account key file to any secrets backend. See [Configure a secrets backend](/docs/astro/secrets-backend) for steps on how to configure several popular secrets backend services to use with Airflow on Astro. For example, if you use Google Secret Manager as a secrets backend:

  1. In the Google Cloud console, select your Google Cloud project and copy its **ID**.
  2. [Create a new service account](https://cloud.google.com/iam/docs/service-accounts-create).
  3. [Grant roles](https://cloud.google.com/iam/docs/grant-role-console) to your service account so that it can access BigQuery. See [BigQuery roles](https://cloud.google.com/bigquery/docs/access-control#bigquery) for a list of available roles and the permissions.
  4. [Add a new JSON key file](https://cloud.google.com/iam/docs/keys-create-delete#iam-service-account-keys-create-console) to the service account.
  5. Download the key file.
  6. [Create a secret](https://cloud.google.com/secret-manager/docs/create-secret-quickstart) in Google Secret Manager and upload the key file from Step 5 as a secret value. Then, copy the ID of your secret name.
  7. Follow Astronomer's documentation to [configure secrets backend](/docs/astro/secrets-backend) for your Astro project.

  You can now use this secret in your Airflow connections.
</details>

<details>
  <summary>Kubernetes Service Account</summary>

  A [Kubernetes service account](https://kubernetes.io/docs/reference/access-authn-authz/service-accounts-admin/) provides an identity to the processes running in a Pod. The process running inside a Pod can use this identity of its associated service account to authenticate to the cluster's API server. This is also referred to as Workload Identity in [GCP](https://cloud.google.com/kubernetes-engine/docs/concepts/workload-identity) and [Azure](https://learn.microsoft.com/en-us/azure/aks/learn/tutorial-kubernetes-workload-identity).

  This method can't be used in a local Airflow environment. It is available to use with Airflow on Astro or OSS Airflow running on Kubernetes clusters.

  If you're running Airflow in a GKE cluster, complete the following steps:

  1. In your Google Cloud console, open the Google Cloud project where you're running BigQuery and copy its **ID**.
  2. [Enable Workload Identity](https://cloud.google.com/kubernetes-engine/docs/how-to/workload-identity) and [configure Airflow to use workload identity](https://cloud.google.com/kubernetes-engine/docs/how-to/workload-identity#authenticating_to). Copy the name for the Kubernetes service account that you create.
  3. Go to **IAM**, then click **Service Accounts** and search for your Kubernetes service account. If you don't see your service account, click **+ ADD** to add your service account to your Google Cloud project.
  4. [Grant roles](https://cloud.google.com/iam/docs/grant-role-console) to your service account to access BigQuery. See [BigQuery roles](https://cloud.google.com/bigquery/docs/access-control#bigquery) for a list of available roles and the permissions.

  After you complete these steps, any Google Cloud connection you create in the Deployment will use your workload identity by default to access BigQuery.
</details>

## Create your connection

<Info>
  Astro users can also create connections using the [Astro Environment Manager](/docs/astro/manage-connections-variables#astro-environment-manager), which stores connections in an Astro-managed secrets backend. These connections can be shared across multiple deployed and local Airflow environments. See [Create Airflow connections in the Astro UI](/docs/astro/create-and-link-connections).
</Info>

<details>
  <summary>Key File Value</summary>

  1. Open your Astro project and add the following line to your `requirements.txt` file:

     ```text wrap theme={null}
     apache-airflow-providers-google
     ```

     This installs the Google Cloud provider package, which makes the Google Cloud connection type available in Airflow.

  2. Run `astro dev restart` to restart your local Airflow environment and apply your changes in `requirements.txt`.

  3. In the Airflow UI for your local Airflow environment, go to **Admin** > **Connections**. Click **+** to add a new connection, then choose the **Google Cloud** connection type.

  4. Fill out the following connection fields using the information you retrieved from [Get connection details](#get-connection-details):

     * **Connection Id**: Enter a name for the connection.
     * **Keyfile JSON**: Enter the contents of the key file.

  5. Click **Test**. After the connection test succeeds, click **Save**.

       <Frame>
         <img src="https://mintcdn.com/astronomer/dALHYMAz3j7hCvzV/images/img/examples/connection-gcp-key-in-ui.png?fit=max&auto=format&n=dALHYMAz3j7hCvzV&q=85&s=1b2ef41f9a23ed752c6a60d5bc49e677" alt="GCP-connection-key-in-ui" width="1754" height="1378" data-path="images/img/examples/connection-gcp-key-in-ui.png" />
       </Frame>
</details>

<details>
  <summary>Key File In Container</summary>

  1. Open your Astro project and add the following line to your `requirements.txt` file:

     ```text wrap theme={null}
     apache-airflow-providers-google
     ```

     This installs the Google Cloud provider package, which makes the Google Cloud connection type available in Airflow.

  2. Add the key file to your `include` folder. This will make it available to Airflow at `/usr/local/airflow/include/<your-key-file>.json`.

  3. Restart or start your local Airflow using `astro dev restart` to apply your changes in `requirements.txt`.

  4. In the Airflow UI for your local Airflow environment, go to **Admin** > **Connections**. Click **+** to add a new connection, then choose the **Google Cloud** connection type.

  5. Fill out the following connection fields using the information you retrieved from [Get connection details](#get-connection-details):

     * **Connection Id**: Enter a name for the connection.
     * **Keyfile Path**: Enter the path of your key file.

  6. Click **Test connection**. After the connection test succeeds, click **Save**.

       <Frame>
         <img src="https://mintcdn.com/astronomer/dALHYMAz3j7hCvzV/images/img/examples/connection-gcp-key-in-airflow-container.png?fit=max&auto=format&n=dALHYMAz3j7hCvzV&q=85&s=8832665bdd67767a63b02c3e6d647104" alt="GCP-connection-key-in-airflow-container" width="1737" height="1378" data-path="images/img/examples/connection-gcp-key-in-airflow-container.png" />
       </Frame>
</details>

<details>
  <summary>Key File In Secrets Backend</summary>

  1. Open your Astro project and add the following line to your `requirements.txt` file:

     ```text wrap theme={null}
     apache-airflow-providers-google
     ```

     This will install the Google Cloud provider package, which makes the Google Cloud connection type available in Airflow.

  2. Run `astro dev restart` to restart your local Airflow environment and apply your changes in `requirements.txt`.

  3. In the Airflow UI for your local Airflow environment, go to **Admin** > **Connections**. Click **+** to add a new connection, then choose the **Google Cloud** connection type.

  4. Fill out the following connection fields using the information you retrieved from [Get connection details](#get-connection-details):

     * **Connection Id**: Enter a name for the connection.
     * **Keyfile Secret Project Id**: Enter the **ID** of the Google Cloud project.
     * **Keyfile Secret Name**: Enter the ID of your secret name.

  5. Click **Test connection**. After the connection test succeeds, click **Save**.

       <Frame>
         <img src="https://mintcdn.com/astronomer/dALHYMAz3j7hCvzV/images/img/examples/connection-gcp-key-in-secret-manager.png?fit=max&auto=format&n=dALHYMAz3j7hCvzV&q=85&s=d4e1d5548cb2ece4641aca47ed3a459e" alt="GCP-connection-key-in-secret-manager" width="1752" height="1386" data-path="images/img/examples/connection-gcp-key-in-secret-manager.png" />
       </Frame>
</details>

<details>
  <summary>Kubernetes Service Account</summary>

  1. Open your Airflow project and add the following line to your `requirements.txt` file:

     ```text wrap theme={null}
     apache-airflow-providers-google
     ```

     This will install the Google Cloud provider package, which makes the Google Cloud connection type available in Airflow.

  2. Run `astro dev restart` to restart your local Airflow environment and apply your changes in `requirements.txt`.

  3. In your Airflow UI, go to **Admin** > **Connections**. Click the **+** sign to add a new connection, select the connection type as **Google Cloud**.

  4. Fill out the following connection fields using the information you retrieved from [Get connection details](#get-connection-details):

     * **Connection Id**: Enter a name for the connection.
     * **Project Id**: Enter the **ID** of the Google Cloud project.

  5. Click **Test connection**. After the connection test succeeds, click **Save**.

       <Frame>
         <img src="https://mintcdn.com/astronomer/dALHYMAz3j7hCvzV/images/img/examples/connection-gcp-workload-identity.png?fit=max&auto=format&n=dALHYMAz3j7hCvzV&q=85&s=f633757f2584a3f14f2296fbc23dc795" alt="GCP-connection-using-workload-identity" width="1754" height="1386" data-path="images/img/examples/connection-gcp-workload-identity.png" />
       </Frame>
</details>

## How it works

Airflow uses the [`python-bigquery`](https://github.com/googleapis/python-bigquery) library to connect to GCP BigQuery through the [`BigQueryHook`](https://airflow.apache.org/docs/apache-airflow-providers-google/stable/_api/airflow/providers/google/cloud/hooks/bigquery/index.html). If you don't define specific key credentials in the connection, Google defaults to using [Application Default Credentials (ADC)](https://cloud.google.com/docs/authentication/application-default-credentials). This means when you use Workload Identity to connect to BigQuery, Airflow relies on ADC to authenticate.

## See also

* [Apache Airflow Google provider package documentation](https://airflow.apache.org/docs/apache-airflow-providers-google/stable/connections/gcp.html)
* BigQuery Modules in the [Airflow Registry](https://airflow.apache.org/registry/)
* [Import and export Airflow connections using the Astro CLI](/docs/astro/import-export-connections-variables#using-the-astro-cli-local-environments-only)