Connecting BigQuery to Bicycle
Connect to Google BigQuery for cloud data warehousing and analytics. Bicycle reads BigQuery tables as SIGNAL sources for metrics, events, and dashboards.
Requirements
To follow the steps in this guide, you'll need:
- Permission to create connectors in Bicycle
- A Google Cloud project with the BigQuery API enabled
- Access to the BigQuery datasets and tables Bicycle should read
- Permission in Google Cloud to create or manage a service account and grant IAM roles
| Item | Description |
|---|---|
| Project ID | The Google Cloud project that contains the target BigQuery datasets |
| Dataset (optional) | Limit discovery to one dataset; omit to discover all datasets visible to the service account |
| Table Pattern (optional) | Regex used to filter table names during discovery |
| Authentication | Service account JSON key |
Prepare BigQuery access
Create a dedicated service account for Bicycle. Do not use personal Google Cloud credentials for production connections.
Create a service account
- Go to the Google Cloud Console.
- Select the project that contains the BigQuery datasets Bicycle should read.
- Navigate to IAM & Admin > Service Accounts.
- Click Create service account.
- Give the service account a recognizable name, such as
bicycle-bigquery-reader. - Click Create and continue.
Grant BigQuery roles
Grant the service account the least access required for the datasets Bicycle should read.
| Role | Required | Purpose |
|---|---|---|
BigQuery Data Viewer (roles/bigquery.dataViewer) | Yes | Read dataset metadata and table data |
BigQuery Job User (roles/bigquery.jobUser) | Yes | Run BigQuery jobs in the project |
BigQuery Metadata Viewer (roles/bigquery.metadataViewer) | Recommended | List datasets and table metadata during connection testing and discovery |
BigQuery Read Session User (roles/bigquery.readSessionUser) | Optional | Use the BigQuery Storage Read API when available |
For least privilege, grant BigQuery Data Viewer on only the datasets Bicycle should read. BigQuery Job User and BigQuery Metadata Viewer are usually granted at the project level because Bicycle validates the connection by listing datasets in the project.
Create a JSON key
- Open the service account.
- Go to Keys > Add key > Create new key.
- Select JSON, then click Create.
- Download the JSON key file and store it securely.
The service account JSON key can access BigQuery as the service account. Keep it in your organization's secrets manager, rotate it according to your security policy, and redact it from screenshots.
Setup in Bicycle
Step 1: Open Connectors and select BigQuery
In Bicycle, go to Connectors. Under Recommended, find BigQuery and click + Connect.
Step 2: Choose Service Account
Enter a connection name that identifies the environment, such as BigQuery Production. Select Service Account as the Connector method, then click Next.
Step 3: Add BigQuery connection details
On the Add details step, fill in the BigQuery connection fields and paste the service account JSON key.
| Field | Required | Description |
|---|---|---|
| Project ID | Yes | Google Cloud project ID for the BigQuery project Bicycle should query |
| Dataset | No | Dataset to discover. If omitted, Bicycle discovers all datasets the service account can list in the project |
| Table Pattern | No | Regex used to filter discovered table names, for example orders_.* |
| Service Account JSON | Yes | Full contents of the downloaded JSON key file |
| Key ID | No | Non-sensitive key identifier from the JSON key |
| Service Account Email | No | Service account email, such as bicycle-bigquery-reader@project.iam.gserviceaccount.com |
| Service Account Project ID | No | Project ID from the service account JSON key |
Example values:
- Project ID:
my-gcp-project - Dataset:
analytics - Table Pattern:
orders_.* - Service Account JSON: paste the full JSON key contents
Step 4: Test the connection
Click Test connection. Bicycle validates the service account JSON and confirms it can list datasets in the configured project. When the test succeeds, you will see Ready to add this connection.
Step 5: Add the connection
Click Add connection. When the connection is created successfully, you will see a confirmation with the connection name, ID, and method (Service Account). Click Close to return to the Connectors page.
Discovery and stream setup
Bicycle discovers tables from the configured BigQuery project:
- If Dataset is empty, Bicycle lists all datasets visible to the service account.
- If Dataset is set, Bicycle discovers tables from only that dataset.
- If Table Pattern is set, Bicycle includes only table names that match the pattern.
- Discovered streams are named as
dataset/table.
When you configure a BigQuery stream, Bicycle requires a cursor field and a start time for incremental ingestion. The end time is optional.
Troubleshooting
If Test connection fails or no tables appear after connecting, check the following.
| Error or symptom | Likely cause | Fix |
|---|---|---|
Service account key JSON is missing | The Service Account JSON field is empty | Paste the full JSON key contents from the service account key file |
Project ID is required | The Project ID field is empty | Enter the Google Cloud project ID that contains the target BigQuery datasets |
| Failed to connect to BigQuery | Invalid JSON key, disabled service account, missing BigQuery API, or insufficient IAM permissions | Confirm the key is valid, the service account is active, BigQuery is enabled, and required IAM roles are granted. If the error mentions listing datasets, add BigQuery Metadata Viewer at the project level |
| Connection succeeds but no tables are discovered | Missing dataset permissions, wrong dataset name, or Table Pattern filters out all tables | Verify the service account can list the dataset and tables, confirm the dataset name, and test the table pattern |
| Some datasets are missing when Dataset is empty | The service account cannot list or read those datasets | Grant access on each dataset Bicycle should discover |
To verify access outside Bicycle, ask your Google Cloud administrator to impersonate the service account or use its key to list datasets and tables in the configured project.
Capabilities
| Capability | Description |
|---|---|
| SIGNAL | Read data from BigQuery tables with incremental sync via a cursor field |
What's next?
Now that BigQuery is connected, you can:
- Discover and ingest tables from the configured project and dataset
- Use the connection as a SIGNAL source for metrics, events, and dashboards
- Configure streams with a cursor field and start time for incremental sync