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

# Google BigQuery connector

> Use the Google BigQuery connector to index data from a BigQuery table.

export const Records = () => <Tooltip tip="A record is a searchable object in an Algolia index. Each record consists of named attributes." cta="Algolia records" href="/doc/guides/sending-and-managing-data/prepare-your-data#algolia-records">
    records
  </Tooltip>;

export const Index = () => <Tooltip tip="An Algolia index is a searchable dataset that consists of records and configuration settings. These settings define how the records are searched and ranked.">
    index
  </Tooltip>;

Use the Google BigQuery connector to index <Records /> from a BigQuery table without writing an indexing script.
In this quickstart, you import a sample product dataset into BigQuery,
create a source for the BigQuery table,
use an Algolia <Index /> destination,
and create an on-demand synchronization task.

## Before you begin

Make sure you have:

* **An Algolia account**.
  [Create one for free](https://www.algolia.com/users/sign_up) if you don't already have one.
* **A Google Cloud account and project**.
  Select or create the project where you want to store the sample BigQuery table.
* **The BigQuery API enabled for your Google Cloud project**.
  If the BigQuery API isn't enabled,
  enable it from the [BigQuery API page](https://console.cloud.google.com/apis/library/bigquery.googleapis.com).
* **Permission to create and load BigQuery datasets and tables,
  create a Google service account and key, and grant IAM roles**.
  If your organization restricts service account keys or IAM changes,
  ask your Google Cloud administrator to complete those steps.

## Set up the Google BigQuery connector

Create a source, destination, transformation, and synchronization task for the sample BigQuery table.
The source identifies the BigQuery table to read.
The destination identifies the Algolia index.
The transformation enriches the records.
The task determines when the connector runs.

<Steps>
  <Step title="Select your project in Cloud Hub">
    Open [Cloud Hub](https://console.cloud.google.com/cloud-hub/home) and
    select the Google Cloud project where you want to create the BigQuery dataset.
    Make a note of the project's **ID**.
    You use this value when configuring the [connector source](#source).

    Select the project itself, not an App Hub app or an app-enabled folder.
    You don't need to configure an App Hub app for this quickstart.
  </Step>

  <Step title="Set the quickstart variables">
    In the Google Cloud console's top toolbar,
    select the **Activate Cloud Shell** (`>_`) icon.

    Set the variables:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    PROJECT_ID="$GOOGLE_CLOUD_PROJECT"
    DATASET_ID="algolia_connector_demo"
    TABLE_ID="apparel"
    LOCATION="US"
    SERVICE_ACCOUNT_ID="algolia-bigquery-reader"
    SERVICE_ACCOUNT_EMAIL="${SERVICE_ACCOUNT_ID}@${PROJECT_ID}.iam.gserviceaccount.com"
    ```

    <Note>
      Paste these commands exactly as shown.
      Cloud Shell sets `GOOGLE_CLOUD_PROJECT` to the project you selected in Cloud Hub.
      Change `LOCATION` to use another [BigQuery location](https://cloud.google.com/bigquery/docs/locations).
    </Note>
  </Step>

  <Step title="Create the BigQuery dataset">
    Create a dataset for the sample table:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    bq --location="$LOCATION" mk \
    --dataset \
    "$PROJECT_ID:$DATASET_ID"
    ```
  </Step>

  <Step title="Download the sample data">
    Download the newline-delimited JSON sample data:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    curl --fail --silent --show-error --location \
    --output apparel.ndjson \
    'https://raw.githubusercontent.com/algolia/quickstarts/main/sample-data/apparel.ndjson'
    ```
  </Step>

  <Step title="Load the sample data into BigQuery">
    <Warning>
      The `--replace` option overwrites any existing table named `apparel`
      in the selected dataset.
      Change `TABLE_ID` if you need to preserve an existing table.
    </Warning>

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    bq --location="$LOCATION" load \
    --replace \
    --autodetect \
    --source_format=NEWLINE_DELIMITED_JSON \
    "$PROJECT_ID:$DATASET_ID.$TABLE_ID" \
    apparel.ndjson
    ```

    For more information,
    see [Loading JSON data from a local file](https://cloud.google.com/bigquery/docs/batch-loading-data#loading_json_data_from_a_local_file).
  </Step>

  <Step title="Verify the BigQuery import">
    Count the imported rows and unique object IDs:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    bq --location="$LOCATION" query \
    --use_legacy_sql=false \
    "SELECT
        COUNT(*) AS records,
        COUNT(DISTINCT objectID) AS unique_object_ids
    FROM \`${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}\`"
    ```

    Confirm that `records` and `unique_object_ids` have the same value.
  </Step>

  <Step title="Create the connector service account">
    Create a dedicated service account for the Algolia connector:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    gcloud iam service-accounts create "$SERVICE_ACCOUNT_ID" \
    --display-name="Algolia BigQuery reader" \
    --project="$PROJECT_ID"
    ```
  </Step>

  <Step title="Grant the BigQuery permissions">
    Grant the service account permission to run BigQuery jobs in the project:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    gcloud projects add-iam-policy-binding "$PROJECT_ID" \
    --member="serviceAccount:$SERVICE_ACCOUNT_EMAIL" \
    --role="roles/bigquery.jobUser"
    ```

    Grant the service account read access to the quickstart dataset:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    bq --location="$LOCATION" query \
    --use_legacy_sql=false \
    "GRANT \`roles/bigquery.dataViewer\`
    ON SCHEMA \`${PROJECT_ID}.${DATASET_ID}\`
    TO \"serviceAccount:${SERVICE_ACCOUNT_EMAIL}\""
    ```
  </Step>

  <Step title="Create and download the service account key">
    Create a JSON key:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    gcloud iam service-accounts keys create \
    algolia-bigquery-key.json \
    --iam-account="$SERVICE_ACCOUNT_EMAIL" \
    --project="$PROJECT_ID"
    ```

    Download the key from Cloud Shell:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    cloudshell download algolia-bigquery-key.json
    ```

    Your browser downloads `algolia-bigquery-key.json`.
    You upload this file when you configure the connector source.

    If your organization prevents service account key creation,
    ask your Google Cloud administrator whether they can create an approved key for this dedicated service account.
  </Step>

  <Step title="Choose the Google BigQuery connector">
    Go to the Algolia dashboard and select your Algolia application.
    Open the [**Connectors**](https://dashboard.algolia.com/connectors) page.

    Find **Google BigQuery**, then select **Connect**.
  </Step>

  <Step title="Configure the Google service account authentication">
    Under **Google Service Account**,
    select **Search or create an authentication**,
    then **Create a new Google Service Account authentication**.

    Upload `algolia-bigquery-key.json` as the **Service Account File**.

    In **Name**, enter `BigQuery quickstart service account`,
    then select **Create authentication**.
  </Step>

  <Step title="Configure the BigQuery source" id="source">
    Under **Select your BigQuery table**,
    enter or select the following BigQuery details:

    | Field                          | Value                                  |
    | ------------------------------ | -------------------------------------- |
    | **Project ID**                 | Your project's `ID` value in Cloud Hub |
    | **Dataset ID**                 | `algolia_connector_demo`               |
    | **Table name**                 | `apparel`                              |
    | **Unique property identifier** | `objectID`                             |

    Leave the custom SQL statement empty.
    Without a custom SQL statement, the connector reads all rows and columns from the selected table.

    In **Connector name**, enter `Quickstart products BigQuery source`,
    then select **Create source**.
  </Step>

  <Step title="Delete the service account file">
    After you have created the source,
    delete the key file from Cloud Shell:

    ```sh Cloud Shell icon=square-terminal theme={"system"}
    rm -f algolia-bigquery-key.json
    ```

    Delete the downloaded copy from your computer after uploading it to Algolia.
    Deleting these files doesn't revoke the key used by the connector.
  </Step>

  <Step title="Transform the records" id="transform">
    This transformation adds a `price_range` attribute to each record.
    After the connector indexes your records, you can display `price_range` or configure it as a facet.

    Select **Transform using the code editor** and replace the placeholder **Transformation code** with this function:

    ```js JavaScript icon=code expandable theme={"system"}
    async function transform(record, helper) {
        const price = Number(record.price);
        if (!Number.isFinite(price)) {
            return record;
        }
        if (price < 25) {
            record.price_range = "Under $25";
        } else if (price < 50) {
            record.price_range = "$25 to $49";
        } else if (price < 100) {
            record.price_range = "$50 to $99";
        } else {
            record.price_range = "$100 and up";
        }
        return record;
    }
    ```

    Select **Save**.
  </Step>

  <Step title="Choose the destination index">
    Under **Configure your destination**, select **Create a new destination**.

    Under **Search**, enter `quickstart-products` as the index name.

    <Warning>
      If you enter the name of an existing index,
      the connector overwrites the records and settings in that index.
    </Warning>
  </Step>

  <Step title="Create the destination" id="destination">
    Under **Index credentials**, select **Create one for me**.

    To use an existing API key,
    choose one with the `addObject`, `deleteIndex`, and `editSettings`
    [ACLs](/doc/guides/security/api-keys#access-control-list-acl).

    In **Name**, enter `BigQuery quickstart products destination`,
    then select **Create destination**.
  </Step>

  <Step title="Create and run an on-demand synchronization task" id="task">
    Under **Configure your task**, select **On demand**.

    Select **Full reindexing**.

    Select **Create task**, then **Run**.

    Wait for the task to finish.
  </Step>

  <Step title="Verify the indexed records">
    Open the
    [`quickstart-products` index in the Algolia dashboard](https://dashboard.algolia.com/explorer/browse/quickstart-products).

    The index contains the imported product records,
    with attributes such as `title`, `description`, `product_type`,
    `price`, `price_range`, and `showcase_image`.
  </Step>
</Steps>

<Info>
  You can use this index as the data source for
  [Build your first search experience](/doc/guides/get-started/quickstart).

  Before you build the UI,
  [configure `product_type` as an attribute for faceting](/doc/guides/managing-results/refine-results/faceting/how-to/declaring-attributes-for-faceting-with-dashboard)
  in the `quickstart-products` index.
</Info>

## Index your own BigQuery data

Authenticate the connector with a dedicated Google service account.
Don't reuse a service account that can update or delete your production BigQuery data.

### Authentication

Grant the service account these roles:

| Resource                                 | Role                                               |
| ---------------------------------------- | -------------------------------------------------- |
| Google Cloud project used to run queries | BigQuery Job User (`roles/bigquery.jobUser`)       |
| BigQuery dataset you want to index       | BigQuery Data Viewer (`roles/bigquery.dataViewer`) |

If your organization uses a custom IAM role instead,
include these permissions:

```txt BigQuery permissions theme={"system"}
bigquery.datasets.get
bigquery.datasets.getIamPolicy
bigquery.jobs.create
bigquery.models.export
bigquery.models.getData
bigquery.models.getMetadata
bigquery.models.list
bigquery.routines.get
bigquery.routines.list
bigquery.tables.createSnapshot
bigquery.tables.export
bigquery.tables.get
bigquery.tables.getData
bigquery.tables.getIamPolicy
bigquery.tables.list
resourcemanager.projects.get
```

### Cloud Storage data

If the connector service account reads the table's Cloud Storage objects directly,
grant it the Storage Object Viewer role (`roles/storage.objectViewer`) on each source bucket.

If your organization uses a custom IAM role instead, include these permissions:

```txt Cloud Storage permissions theme={"system"}
storage.folders.get
storage.objects.get
storage.objects.list
```

### JSON key

Create a Google service account JSON key and upload the key file when you [configure the BigQuery source](#source).

<Warning>
  A service account JSON key is a long-lived credential.
  Store it securely, don't add it to source control,
  and delete local copies after uploading it to Algolia.
</Warning>

### Customize the data you index with SQL

By default, the connector imports all rows and columns from the selected table.

In the BigQuery source's **Custom SQL statement** field,
enter a GoogleSQL statement to filter rows, combine columns, or calculate attributes.
Use `%s` as the placeholder for the selected table.

For example:

```sql SQL icon="database" theme={"system"}
SELECT *
FROM %s
WHERE status = 'Available'
```

The query result must include the column selected as the source's unique identifier.
Its value must be present, unique, and stable for every returned row.
An existing `objectID` column is suitable.

If your table uses a composite key,
return its components as a single column.
For example:

```sql SQL icon="database" theme={"system"}
SELECT
  TO_JSON_STRING(
    STRUCT(
      account_id AS account_id,
      product_id AS product_id
    )
  ) AS objectID,
  title,
  description,
  price
FROM %s
```

<Note>
  You can instead assign `objectID` in an Algolia transformation if the BigQuery source provides a stable unique identifier.
</Note>

### Transformations

Use transformations to add computed attributes or change values before the connector indexes your records.
To use a transformed attribute for search, faceting, or ranking,
update the relevant [index settings](/doc/api-reference/settings-api-parameters),
such as `searchableAttributes`, `attributesForFaceting`, or `customRanking`.

<Note>
  Test transformations with repeated task runs.
  Transformations that read from and write to the same attribute,
  or extract a value and then delete the source attribute,
  can produce different results when the task runs again.
</Note>

### Synchronization schedule

After you create a task,
you can [edit it in the Algolia dashboard](https://dashboard.algolia.com/connectors/tasks) to choose when the connector runs:

* **On demand**. Run the connector manually.
* **Scheduled**. Select a predefined schedule or enter a custom [cron expression](https://crontab.guru/).

### Indexing strategy

You can choose how the connector updates your index.
For information about full reindexing, full record updates, and partial record updates,
see [Data synchronization strategies](/doc/guides/sending-and-managing-data/send-and-update-your-data/in-depth/the-different-synchronization-strategies).

<Warning>
  To stay within your BigQuery quota,
  don't schedule a Google BigQuery connector task to run more than once per day.
</Warning>

## Limitations

This connector is subject to the following limitations:

* [Connectors limits](/doc/guides/scaling/algolia-service-limits/#connectors-limits)
* [Transformation limits](/doc/guides/scaling/algolia-service-limits/#data-transformation-and-fetch-limits)

## See also

* [Connector overview](/doc/guides/sending-and-managing-data/send-and-update-your-data/connectors)
* [JSON connector](/doc/guides/sending-and-managing-data/send-and-update-your-data/connectors/json)
* [Prepare your records for indexing](/doc/guides/sending-and-managing-data/prepare-your-data)
* [Transform your data with code](/doc/guides/sending-and-managing-data/send-and-update-your-data/how-to/transform-your-data-with-code)