# BigQuery setup

Recurve's BigQuery connector enables you to seamlessly integrate your BigQuery data warehouse as a destination for your analytics database. This guide walks you through establishing a secure connection using Google Cloud service accounts, from initial setup to your first successful connection.

You'll learn how to:

* Prepare a sample dataset in your Google Cloud Project (GCP).
* Create and configure a service account with sufficient permissions.
* Establish a connection between Recurve and BigQuery.

### Prerequisites

* You already join a Recurve organization.
* You've already created a [GCP project](https://developers.google.com/workspace/guides/create-project).

### Prepare sample dataset

To demonstrate the connetion process, we'll be using the `jaffle_shop` dataset — a fictional e-commerce store's data provided by the dbt community. This dataset offers a practical example of typical e-commerce data structures that mirror real-world scenarios. 

You can use the [jafgen](https://github.com/dbt-labs/jaffle-shop-generator) CLI tool generate synthetic data for any specified year range.

The dataset includes these tables:

* Customers (who place Orders)
* Orders (from those Customers)
* Products (the food and beverages the Orders contain)
* Order Items (of those Products)
* Supplies (needed for making those Products)
* Stores (where the Orders are placed and fulfilled)
* Tweets (Customers sometimes issue Tweets after placing an Order)

Or simply download the generated data below:

{% file src="<https://786945529-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFTrGKWnjusKCQj11CkuL%2Fuploads%2FAOLKexe7XNlrUoVxpZcV%2Fjaffle-data.zip?alt=media&token=9d3a923d-04e2-4f72-892d-16e19540298c>" %}

### Ingest sample data

Follow these steps to ingest the data:

1. Go to your [GCP project console](https://console.cloud.google.com/bigquery).
2. Create a new dataset:
   1. Expand the *Actions* option and select [**Create dataset**](https://cloud.google.com/bigquery/docs/datasets#create-dataset).&#x20;
   2. Enter provide a name for the dataset in **Dataset ID**. You can leave the other fields as default.
   3. Click **Create dataset**.

      <figure><img src="https://786945529-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFTrGKWnjusKCQj11CkuL%2Fuploads%2FFl5yeuKv6HPoIW7Y7b7I%2FBigQuery_create_dataset.png?alt=media&#x26;token=7474ab1f-21a2-450c-b813-f6484a828919" alt=""><figcaption></figcaption></figure>
3. Create tables for the dataset.
   1. Click on the *Actions* option of the dataset and select **Create table**.
   2. Choose **Upload** as the creation method, select the CSV file, and name the table after the file name. For example, we upload the `raw_orders.csv` and name the table `raw_orders`.
   3. In **Schema**, check **Auto detect** to automatically generate the schema for the table.
   4. Click **Create table**.

      <figure><img src="https://786945529-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFTrGKWnjusKCQj11CkuL%2Fuploads%2FNmJXNYB0mPaBvT5vcwhu%2FBigQuery_create_table.png?alt=media&#x26;token=69c21d35-8682-4c77-a875-bdb8cebf733b" alt="" width="375"><figcaption></figcaption></figure>
   5. Repeat the four steps above to create and upload the other tables.

### Create a service account

After loading data into the project, you need to create a Google Service Account. This account represents the identity and permissions that the connector can use to authenticate and interact with BigQuery.

Follow these steps:

1. Go to the [GCP credentials wizard](https://console.cloud.google.com/apis/credentials/wizard) and select your project.
2. Enable BigQuery API:
   1. Select **BigQuery API** from the dropdown.
   2. Choose **Application data** for data processing type.
   3. Click **Next**. This navigates to to the **Create service account** section.
3. Configure your service account:
   1. Provide a descriptive name for your service account.
   2. Click **Create and continue**.
   3. Assign the two following roles:
      * BigQuery Job User.
      * BigQuery Data Editor.
   4. Click **Done**.

      <figure><img src="https://786945529-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFTrGKWnjusKCQj11CkuL%2Fuploads%2FUNuxUYr8jarD7sT82DyK%2FBigQuery_create_service_account.png?alt=media&#x26;token=a61e203e-0366-46e8-bc4d-27c0eebc999e" alt="" width="375"><figcaption></figcaption></figure>
4. After the service account is created, the console navigates you to the [Credentials](https://console.cloud.google.com/apis/credentials) page, where you can generate keys to authenticate the account. Locate the **Service Accounts** rows and click on the account
   1. Navigate to the **Keys** tab.
   2. Click the **Add key** ->  **Create new key**.
   3. Select **JSON** as the **Key type** and click **Create**. This will download the JSON file that contains authentication credentials and metadata.

      <figure><img src="https://786945529-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFTrGKWnjusKCQj11CkuL%2Fuploads%2FY1dXob1JANTkIAIsZ5Q2%2FBigQuery_create_private_key.png?alt=media&#x26;token=71eca027-56b8-48a8-a462-b83bd590fa82" alt="" width="563"><figcaption></figcaption></figure>

### Connect Recurve to BigQuery

With the Google Service Account key, you now can set up a connection to BigQuery from Recurve.

Follow these steps:

1. Open the left sidebar.
2. Navigate to  **Connections** -> **Destinations**.
3. Click **+ Create connection**.
4. Select **Google BigQuery**.
5. Fill in the fields with the information from the JSON key file.&#x20;
   * **Destination name**: the name to identify this specific connection in your organization.
   * **Auth Type**: `service_account`&#x20;
   * **Google Project ID**: the unique identifier for your GCP project
   * **Google Auth Private Key ID**: the unique identifier for your service account's private key
   * **Google Auth Private Key**: the actual private key used for authentication
   * **Client ID**: your service account's unique identifier
   * **Client Email**: the email address associated with your service account
6. Select **Development environment** and click **Test connection**.

   <figure><img src="https://786945529-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFTrGKWnjusKCQj11CkuL%2Fuploads%2FbzympnZ3CYJPkevGzaEu%2FRecurve_bigquery_connection_tested.png?alt=media&#x26;token=661fd608-6792-4f7d-a8a1-cdf70a94011e" alt="" width="563"><figcaption></figcaption></figure>
7. Once the connection is successfully tested, click **Create Destination**.

The connection is now ready to be used in a project.