Google BigQuery connector

Use the Google BigQuery connector to index 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 destination, and create an on-demand synchronization task.

Before you begin

Make sure you have:

An Algolia account. Create one for free 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.
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.

Select your project in Cloud Hub

Open Cloud Hub 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.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.

Set the quickstart variables

In the Google Cloud console’s top toolbar, select the Activate Cloud Shell (>_) icon.Set the variables:

Cloud Shell

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"

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.

Create the BigQuery dataset

Create a dataset for the sample table:

Cloud Shell

bq --location="$LOCATION" mk \
--dataset \
"$PROJECT_ID:$DATASET_ID"

Download the sample data

Download the newline-delimited JSON sample data:

Cloud Shell

curl --fail --silent --show-error --location \
--output apparel.ndjson \
'https://raw.githubusercontent.com/algolia/quickstarts/main/sample-data/apparel.ndjson'

Load the sample data into BigQuery

The --replace option overwrites any existing table named apparel in the selected dataset. Change TABLE_ID if you need to preserve an existing table.

Cloud Shell

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.

Verify the BigQuery import

Count the imported rows and unique object IDs:

Cloud Shell

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.

Create the connector service account

Create a dedicated service account for the Algolia connector:

Cloud Shell

gcloud iam service-accounts create "$SERVICE_ACCOUNT_ID" \
--display-name="Algolia BigQuery reader" \
--project="$PROJECT_ID"

Grant the BigQuery permissions

Grant the service account permission to run BigQuery jobs in the project:

Cloud Shell

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:

Cloud Shell

bq --location="$LOCATION" query \
--use_legacy_sql=false \
"GRANT \`roles/bigquery.dataViewer\`
ON SCHEMA \`${PROJECT_ID}.${DATASET_ID}\`
TO \"serviceAccount:${SERVICE_ACCOUNT_EMAIL}\""

Create and download the service account key

Create a JSON key:

Cloud Shell

gcloud iam service-accounts keys create \
algolia-bigquery-key.json \
--iam-account="$SERVICE_ACCOUNT_EMAIL" \
--project="$PROJECT_ID"

Download the key from Cloud Shell:

Cloud Shell

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.

Choose the Google BigQuery connector

Go to the Algolia dashboard and select your Algolia application. Open the Connectors page.Find Google BigQuery, then select Connect.

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.

Configure the BigQuery 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.

Delete the service account file

After you have created the source, delete the key file from Cloud Shell:

Cloud Shell

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.

Transform the records

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:

JavaScript

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.

Choose the destination index

Under Configure your destination, select Create a new destination.Under Search, enter quickstart-products as the index name.

If you enter the name of an existing index, the connector overwrites the records and settings in that index.

Create the destination

Under Index credentials, select Create one for me.To use an existing API key, choose one with the addObject, deleteIndex, and editSettings ACLs.In Name, enter BigQuery quickstart products destination, then select Create destination.

Create and run an on-demand synchronization task

Under Configure your task, select On demand.Select Full reindexing.Select Create task, then Run.Wait for the task to finish.

Verify the indexed records

Open the quickstart-products index in the Algolia dashboard.The index contains the imported product records, with attributes such as title, description, product_type, price, price_range, and showcase_image.

You can use this index as the data source for Build your first search experience.Before you build the UI, configure product_type as an attribute for faceting in the quickstart-products index.

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:

BigQuery permissions

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:

Cloud Storage permissions

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.

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.

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

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

SELECT
  TO_JSON_STRING(
    STRUCT(
      account_id AS account_id,
      product_id AS product_id
    )
  ) AS objectID,
  title,
  description,
  price
FROM %s

You can instead assign objectID in an Algolia transformation if the BigQuery source provides a stable unique identifier.

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, such as searchableAttributes, attributesForFaceting, or customRanking.

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.

Synchronization schedule

After you create a task, you can edit it in the Algolia dashboard to choose when the connector runs:

On demand. Run the connector manually.
Scheduled. Select a predefined schedule or enter a custom cron expression.

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.

To stay within your BigQuery quota, don’t schedule a Google BigQuery connector task to run more than once per day.

Limitations

This connector is subject to the following limitations:

Search and Discovery platform

Optimization and Personalization

AI-powered experiences

Production and scale

Google BigQuery connector

Before you begin

Set up the Google BigQuery connector

Index your own BigQuery data

Authentication

Cloud Storage data

JSON key

Customize the data you index with SQL

Transformations

Synchronization schedule

Indexing strategy

Limitations

See also

​Before you begin

​Set up the Google BigQuery connector

​Index your own BigQuery data

​Authentication

​Cloud Storage data

​JSON key

​Customize the data you index with SQL

​Transformations

​Synchronization schedule

​Indexing strategy

​Limitations

​See also

Before you begin

Set up the Google BigQuery connector

Index your own BigQuery data

Authentication

Cloud Storage data

JSON key

Customize the data you index with SQL

Transformations

Synchronization schedule

Indexing strategy

Limitations

See also