BigQuery

​

Overview

​

Connection Details

​

Setting Up Service Account

1. Go to Google Cloud Console
2. Navigate to IAM & Admin > Service Accounts
3. Click Create Service Account
4. Give it a name like plumi-bigquery-reader
5. Grant the BigQuery Data Viewer role
6. Create a JSON key and download it

​

Required Permissions

• roles/bigquery.dataViewer - Read data from tables
• roles/bigquery.jobUser - Run queries

# Grant roles via gcloud
gcloud projects add-iam-policy-binding YOUR_PROJECT \
  --member="serviceAccount:plumi@YOUR_PROJECT.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"

gcloud projects add-iam-policy-binding YOUR_PROJECT \
  --member="serviceAccount:plumi@YOUR_PROJECT.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"

​

Adding to Plumi

1. Go to Data Connectors
2. Select BigQuery
3. Enter your Project ID
4. Paste the service account JSON key
5. Test and save

​

Query Syntax

-- Date functions
SELECT
  DATE_TRUNC(order_date, MONTH) as month,
  SUM(total) as revenue
FROM `project.dataset.orders`
WHERE order_date >= '2024-01-01'
GROUP BY month;

-- Array handling
SELECT
  user_id,
  ARRAY_AGG(product_name) as products
FROM `project.dataset.orders`
GROUP BY user_id;

​

Cost Optimization

• Select only needed columns (avoid SELECT *)
• Use partitioned tables and filter by partition column
• Use LIMIT when exploring data
• Consider using clustered tables

​

Troubleshooting

​

”Access Denied: Project not found”

​

”Access Denied: Dataset not found”