Connect your BigQuery Organization

See the automated setup for a Python script to set everything up, or follow the standard guide below.

Prerequisites

Permissions

A user with the following roles is needed to complete this setup:

Billing Account Costs Manager or Billing Account Administrator.
Organization Admin (or another role that includes resourcemanager.organizations.setIamPolicy)

Enabled APIs

Ensure the following APIs are enabled on the project that will host the billing dataset and service account:

bigquery.googleapis.com
iam.googleapis.com
iamcredentials.googleapis.com
cloudresourcemanager.googleapis.com

Enable TABLE_STORAGE_BY_ORGANIZATION

SELECT uses INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION to provide storage visibility across your org. This view must be explicitly enabled for each region where your BigQuery data resides.

First, discover all regions in use across your organization by running this in Cloud Shell:

1gcloud asset search-all-resources \
2  --scope=organizations/${ORG_ID} \
3  --asset-types=bigquery.googleapis.com/Dataset \
4  --format="value(location)" | sort -u | tr '[:upper:]' '[:lower:]' | \
5while read region; do
6  echo "ALTER ORGANIZATION SET OPTIONS (\`region-${region}.enable_info_schema_storage\` = TRUE);"
7done

This outputs an ALTER ORGANIZATION statement for each region. Paste and run the output in BigQuery Studio as a user with the roles/bigquery.admin role. For example, if your org has data in US and EU the output would be:

1ALTER ORGANIZATION SET OPTIONS (`region-us.enable_info_schema_storage` = TRUE);
2ALTER ORGANIZATION SET OPTIONS (`region-eu.enable_info_schema_storage` = TRUE);

Note: Allow up to 24 hours for historical data to become available after enabling. SELECT will not be able to display storage data until this step is complete.

Connecting your account

There are two components of setup. Firstly, enabling the billing data exports, and secondly granting SELECT read access to BigQuery and the exported data.

For the setup, you can use an existing project, or create a new one dedicated to the Select.

For the rest of this guide, we'll use customer-billing-project to refer to the project that will host both the billing export dataset and the service account SELECT uses.

Step 1: Enable billing exports

SELECT needs three billing exports (usage, pricing, and committed use discounts) to give you complete cost visibility. Each are written to BigQuery, and should be configured to land in the same dataset.

In the Google Cloud Console, navigate to Billing. You will be redirected to the Cloud Billing account page.
In the left pane, click Billing export.
Follow the three next steps.

1a. Detailed usage cost export

On the BigQuery export tab, find Detailed usage cost and click Enable detailed export.
For Project, select customer-billing-project.
For the dataset, click create new dataset:
- It's important to create a dataset specific to SELECT so you can limit SELECT's access to just the billing data.
- Enter a dataset ID of your choosing (e.g. billing_export). Note this down, you'll need it later.
- Choose a multi-region location (US or EU).
Click Save.

1b. Pricing data export

Back on the Billing export page, find Pricing data and click Enable pricing export.
Select the same project and dataset as in 1a.
Click Save.

1c. Committed use discount (CUD) data export

Find Committed use discount data and click Enable CUD export.
Select the same project and dataset as in 1a.
Click Save.

It can take up to 24 hours for the first batch of billing data to appear.

Step 2: Create the custom org role

Rather than binding multiple predefined roles separately, create a single custom role at the org level that consolidates all permissions SELECT needs. This gives you one binding to manage and makes it easy to audit exactly what access SELECT has.

This custom role combines the permissions from three predefined roles — roles/bigquery.resourceViewer, roles/bigquery.metadataViewer, and roles/browser — plus the single additional permission bigquery.config.get, which is required to detect fluid scaling on BigQuery reservations and is not included in any predefined role.

1gcloud iam roles create SelectOrgViewer \
2  --organization=${ORG_ID} \
3  --title="SELECT Org Viewer" \
4  --description="Grants SELECT read-only metadata access across the org" \
5  --permissions=\
6bigquery.bireservations.get,\
7bigquery.capacityCommitments.get,\
8bigquery.capacityCommitments.list,\
9bigquery.config.get,\
10bigquery.datasets.get,\
11bigquery.datasets.getIamPolicy,\
12bigquery.jobs.get,\
13bigquery.jobs.list,\
14bigquery.jobs.listAll,\
15bigquery.jobs.listExecutionMetadata,\

Step 3: Create the service account

In your project UI navigate to IAM → Service Accounts → Create Service Account and give it a name, e.g. select-viewer. Click Create and continue, then skip the optional role and group steps and click Done.

Alternatively, in Cloud Shell or any environment with gcloud authenticated:

1gcloud iam service-accounts create select-viewer \
2  --display-name="SELECT replicator viewer" \
3  --project=${CUSTOMER_PROJECT}

This creates the service account select-viewer@${CUSTOMER_PROJECT}.iam.gserviceaccount.com. Note this email down — you'll need it in the final step.

Now attach the custom role you created in Step 2, plus project-level roles to allow SELECT to run queries and use read sessions:

1# Attach the custom org role
2gcloud organizations add-iam-policy-binding ${ORG_ID} \
3  --member=serviceAccount:select-viewer@${CUSTOMER_PROJECT}.iam.gserviceaccount.com \
4  --role=organizations/${ORG_ID}/roles/SelectOrgViewer
5
6# Grant project-level roles to run queries and read sessions
7for ROLE in roles/bigquery.jobUser roles/bigquery.readSessionUser; do
8  gcloud projects add-iam-policy-binding ${CUSTOMER_PROJECT} \
9    --member=serviceAccount:select-viewer@${CUSTOMER_PROJECT}.iam.gserviceaccount.com \
10    --role=$ROLE
11done

Step 4: Grant read access to the billing dataset

Grant bigquery.dataViewer scoped to only the billing export dataset. This ensures SELECT has no access to your company's data.

In BigQuery Studio, navigate to the billing export dataset you created.
Click Share> Manage Permissions.
Click Add principal.
Add select-viewer service account.
Assign roles BigQuery Dataset Viewer.

Alternatively, In Cloud Shell or any environment with gcloud authenticated:

1gcloud alpha bq datasets add-iam-policy-binding \
2  ${BILLING_DATASET} \
3  --project=${CUSTOMER_PROJECT} \
4  --member=serviceAccount:select-viewer@${CUSTOMER_PROJECT}.iam.gserviceaccount.com \
5  --role=roles/bigquery.dataViewer

Step 5: Allow SELECT to impersonate the service account

SELECT's backend service account ([email protected]) authenticates by impersonating the service account you just created. No passwords or keys required.

Go to IAM & Admin → Service Accounts in the project.
Find select-viewer@... in the list.
Check the box next to it.
On the right side, click Manage Access.
Click Add Principal.
Enter [email protected] as the principal
Select role Service Account Token Creator
Save

Alternatively, In Cloud Shell or any environment with gcloud authenticated:

1gcloud iam service-accounts add-iam-policy-binding \
2  select-viewer@${CUSTOMER_PROJECT}.iam.gserviceaccount.com \
3  --member=serviceAccount:[email protected] \
4  --role=roles/iam.serviceAccountTokenCreator \
5  --project=${CUSTOMER_PROJECT}

Add connection to SELECT

Navigate to the connections tab in SELECT's settings.
Click the Add Connection button.
Enter a connection name (e.g. your organization's name), and fill out all the details from the previous steps:
- Project ID (e.g. customer-project)
- Billing export dataset name (e.g. billing_export)
- Billing Account ID (eg. ABCDEF-12345-67810)
Click Add. SELECT will run a test query to verify the connection.

That's it! Hold tight and you'll receive an email when the initial sync is complete, usually within a couple of hours.

If your BigQuery project is inside a VPC SC perimeter, you will see this error when adding the connection in Select.dev:

VPC Service Controls: Request is prohibited by organization's policy.vpcServiceControlsUniqueIdentifier: <unique-id>

Follow the steps below to resolve it.

VPC Service Controls (optional)

If your GCP organization has VPC Service Controls enabled with a service perimeter around BigQuery, SELECT's queries will be blocked by default. You'll need to explicitly allow SELECT through the perimeter. Only follow these steps if your organization uses VPC Service Controls.

Recommended: Allow by service account identity

Since SELECT authenticates by impersonating your select-viewer service account, the most reliable approach is an ingress rule scoped to SELECT's backend service account ([email protected]). This is more robust than IP-based allowlisting as it doesn't depend on network routing.

Create an ingress rule YAML file:

1- ingressFrom:
2    identityType: ANY_IDENTITY
3    sources:
4    - resource: "projects/813120990659"
5  ingressTo:
6    operations:
7    - serviceName: bigquery.googleapis.com
8      methodSelectors:
9      - method: "*"
10    resources:
11    - "*"

Why ANY_IDENTITY and not a specific service account?

Select's backend service account ([email protected]) is in a different GCP organization than your VPC SC perimeter. VPC SC's ANY_SERVICE_ACCOUNT identity type only matches service accounts within the same organization as the perimeter — it will never match a cross-org SA. ANY_IDENTITY is required to cover cross-org service accounts.

This is still secure because:

The source is locked to Select's project (813120990659) — no other GCP project can use this rule
IAM enforces that only [email protected] can impersonate your select-viewer SA
Your select-viewer SA has read-only BigQuery permissions only

Apply it to your service perimeter:

1gcloud access-context-manager perimeters update YOUR_PERIMETER_NAME \
2  --add-ingress-policies=ingress-select.yaml \
3  --policy=YOUR_POLICY_ID

Alternative: Allow by IP address

⚠️ IP allowlisting will not work for most customers. Select.dev runs on Google Cloud Run, which routes all traffic through Google's internal network. GCP redacts the source IP to gce-internal-ip for all Cloud Run traffic, meaning IP-based access levels will never match the request. Google's own documentation recommends ingress rules over IP-based access levels for this reason. Only use IP allowlisting if you are connecting via a self-hosted proxy that preserves the source IP.

Note: Always test perimeter changes in dry-run mode before enforcing them to avoid accidentally blocking other services. See Google's VPC SC dry-run documentation for details.

Automated setup

1#!/usr/bin/env python3
2"""
3SELECT for BigQuery — automated onboarding script.
4
5Idempotent. Re-running is safe; resources are created/granted only when
6missing.
7
8Prerequisites:
9  1. Your billing export is ALREADY configured in the Cloud Console for the
10     dataset named below. Without an existing dataset we can't grant access
11     to it. See:
12     https://cloud.google.com/billing/docs/how-to/export-data-bigquery-setup
13  2. You're authenticated as a principal that has, at minimum:
14       - Service Usage Admin on PROJECT_ID
15       - Project IAM Admin on PROJECT_ID
16       - Organization Admin (or a role with resourcemanager.organizations.
17         setIamPolicy AND iam.roles.create/update on ORG_ID) on ORG_ID
18     Run once locally:
19       gcloud auth application-default login
20
21Install:
22  pip install google-cloud-bigquery google-auth requests
23
24Run:
25  Edit the CONFIG block below, then:
26    python setup-select.py
27"""
28
29# ============================================================
30#  CONFIG — edit these values, then run the script.
31# ============================================================
32PROJECT_ID       = "customer-billing-project"
33ORG_ID           = "123456789012"
34# Either `dataset_name` (the dataset lives in PROJECT_ID) or
35# `other-project.dataset_name` if the billing export lives elsewhere.
36BILLING_DATASET  = "billing_export"
37SA_NAME          = "select-viewer"
38# region-us, region-eu, region-asia-east1, etc. — whichever region your jobs run in.
39REGION           = "region-us"
40# ============================================================
41
42
43import json
44import sys
45import time
46from datetime import datetime
47
48import google.auth
49import google.auth.transport.requests
50import requests
51from google.api_core import exceptions as gax_exc
52from google.auth import impersonated_credentials
53from google.cloud import bigquery
54
55
56SELECT_BACKEND_SA = "[email protected]"
57
58REQUIRED_APIS = [
59    "bigquery.googleapis.com",
60    "iam.googleapis.com",
61    "iamcredentials.googleapis.com",
62    "cloudresourcemanager.googleapis.com",
63    "serviceusage.googleapis.com",
64]
65
66PROJECT_ROLES = [
67    "roles/bigquery.jobUser",
68    "roles/bigquery.readSessionUser",
69]
70
71# Custom org-level role granted to the SELECT viewer SA. Replaces the previous
72# predefined ORG_ROLES (resourceViewer / metadataViewer / browser) so we can
73# grant the exact read-only metadata surface SELECT needs — including
74# bigquery.config.get and resourcemanager.organizations.get, which no single
75# predefined role covers.
76ORG_CUSTOM_ROLE_ID    = "SelectOrgViewer"
77ORG_CUSTOM_ROLE_TITLE = "SELECT Org Viewer"
78ORG_CUSTOM_ROLE_DESC  = "Grants SELECT read-only metadata access across the org"
79ORG_CUSTOM_ROLE_PERMISSIONS = [
80    "bigquery.bireservations.get",
81    "bigquery.capacityCommitments.get",
82    "bigquery.capacityCommitments.list",
83    "bigquery.config.get",
84    "bigquery.datasets.get",
85    "bigquery.datasets.getIamPolicy",
86    "bigquery.jobs.get",
87    "bigquery.jobs.list",
88    "bigquery.jobs.listAll",
89    "bigquery.jobs.listExecutionMetadata",
90    "bigquery.models.getMetadata",
91    "bigquery.models.list",
92    "bigquery.propertyGraphs.get",
93    "bigquery.propertyGraphs.list",
94    "bigquery.reservationAssignments.list",
95    "bigquery.reservationAssignments.search",
96    "bigquery.reservationGroups.get",
97    "bigquery.reservationGroups.list",
98    "bigquery.reservations.get",
99    "bigquery.reservations.list",
100    "bigquery.reservations.listFailoverDatasets",
101    "bigquery.routines.get",
102    "bigquery.routines.list",
103    "bigquery.tables.get",
104    "bigquery.tables.getIamPolicy",
105    "bigquery.tables.list",
106    "dataplex.projects.search",
107    "resourcemanager.organizations.get",
108    "resourcemanager.projects.get",
109    "resourcemanager.projects.getIamPolicy",
110    "resourcemanager.projects.list",
111]
112
113
114# ---------- output helpers ----------
115
116def step(msg: str) -> None:
117    print(f"\n==> {msg}")
118
119
120def ok(msg: str) -> None:
121    print(f"    [OK] {msg}")
122
123
124def warn(msg: str) -> None:
125    print(f"    [!]  {msg}")
126
127
128def fail(msg: str) -> None:
129    print(f"    [X]  {msg}")
130
131
132# ---------- REST helper ----------
133
134_CREDS = None
135_AUTH_REQ = google.auth.transport.requests.Request()
136
137
138def _creds():
139    global _CREDS
140    if _CREDS is None:
141        _CREDS, _ = google.auth.default(
142            scopes=["https://www.googleapis.com/auth/cloud-platform"],
143        )
144    if not _CREDS.valid:
145        _CREDS.refresh(_AUTH_REQ)
146    return _CREDS
147
148
149def gcp_api(method: str, url: str, body: dict | None = None,
150            allow: tuple[int, ...] = ()) -> dict | None:
151    """REST call against any GCP JSON API. Returns parsed JSON (or None for
152    no-content). `allow` swallows specific HTTP statuses (e.g. 409 ALREADY
153    EXISTS) and returns None for them — used for idempotency."""
154    headers = {
155        "Authorization": f"Bearer {_creds().token}",
156        "Content-Type": "application/json",
157    }
158    resp = requests.request(method, url, headers=headers,
159                            data=json.dumps(body) if body is not None else None,
160                            timeout=60)
161    if resp.status_code in allow:
162        return None
163    if resp.status_code >= 400:
164        raise RuntimeError(
165            f"{method} {url} -> HTTP {resp.status_code}\n{resp.text}"
166        )
167    return resp.json() if resp.content else None
168
169
170# ---------- input parsing ----------
171
172def parse_billing_dataset() -> tuple[str, str]:
173    if "." in BILLING_DATASET:
174        proj, ds = BILLING_DATASET.split(".", 1)
175        return proj, ds
176    return PROJECT_ID, BILLING_DATASET
177
178
179# ---------- steps ----------
180
181def enable_apis() -> None:
182    step("Step 1/7 — enable required APIs")
183    body = {"serviceIds": REQUIRED_APIS}
184    op = gcp_api(
185        "POST",
186        f"https://serviceusage.googleapis.com/v1/projects/{PROJECT_ID}/services:batchEnable",
187        body=body,
188    )
189    # Long-running op — poll briefly.
190    op_name = op.get("name") if op else None
191    if op_name and not op.get("done"):
192        for _ in range(30):
193            time.sleep(2)
194            resp = gcp_api("GET", f"https://serviceusage.googleapis.com/v1/{op_name}")
195            if resp and resp.get("done"):
196                if "error" in resp:
197                    fail(f"batchEnable error: {resp['error']}")
198                    return
199                break
200    for api in REQUIRED_APIS:
201        ok(api)
202
203    # If the billing dataset is in a different project, make sure BigQuery is
204    # enabled there too. Best-effort: the customer running onboarding usually
205    # does NOT own the billing dataset's project, so a 403 here is expected
206    # and harmless — bigquery.googleapis.com is essentially always already
207    # enabled in any project that hosts a billing export.
208    bp, _ = parse_billing_dataset()
209    if bp != PROJECT_ID:
210        try:
211            gcp_api(
212                "POST",
213                f"https://serviceusage.googleapis.com/v1/projects/{bp}/services:batchEnable",
214                body={"serviceIds": ["bigquery.googleapis.com"]},
215            )
216            ok(f"bigquery.googleapis.com on {bp} (billing dataset host)")
217        except RuntimeError as e:
218            if "403" in str(e) or "PERMISSION_DENIED" in str(e):
219                warn(f"can't enable APIs on {bp} (no permission). Assuming "
220                     "bigquery.googleapis.com is already enabled there.")
221            else:
222                raise
223
224
225def sa_email() -> str:
226    return f"{SA_NAME}@{PROJECT_ID}.iam.gserviceaccount.com"
227
228
229def create_service_account() -> None:
230    step(f"Step 2/7 — service account {sa_email()}")
231    existing = gcp_api(
232        "GET",
233        f"https://iam.googleapis.com/v1/projects/{PROJECT_ID}/serviceAccounts/{sa_email()}",
234        allow=(404,),
235    )
236    if existing:
237        ok("already exists")
238        return
239    gcp_api(
240        "POST",
241        f"https://iam.googleapis.com/v1/projects/{PROJECT_ID}/serviceAccounts",
242        body={
243            "accountId": SA_NAME,
244            "serviceAccount": {"displayName": "SELECT replicator viewer"},
245        },
246        # 409 if it was created between our describe and create — fine.
247        allow=(409,),
248    )
249    ok("created")
250
251
252def _add_iam_member(get_url: str, set_url: str, role: str, member: str) -> bool:
253    """Read-modify-write on a Cloud IAM policy. Returns True if the policy
254    was changed (member added), False if member was already present."""
255    policy = gcp_api("POST", get_url, body={}) or {}
256    bindings = policy.get("bindings", [])
257    for b in bindings:
258        if b.get("role") == role:
259            if member in b.get("members", []):
260                return False
261            b.setdefault("members", []).append(member)
262            break
263    else:
264        bindings.append({"role": role, "members": [member]})
265    policy["bindings"] = bindings
266    gcp_api("POST", set_url, body={"policy": policy})
267    return True
268
269
270def grant_project_iam() -> None:
271    step(f"Step 3/7 — project IAM on {PROJECT_ID}")
272    member = f"serviceAccount:{sa_email()}"
273    get_url = f"https://cloudresourcemanager.googleapis.com/v1/projects/{PROJECT_ID}:getIamPolicy"
274    set_url = f"https://cloudresourcemanager.googleapis.com/v1/projects/{PROJECT_ID}:setIamPolicy"
275    for role in PROJECT_ROLES:
276        changed = _add_iam_member(get_url, set_url, role, member)
277        ok(f"{role} {'granted' if changed else '(already present)'}")
278
279
280def ensure_org_custom_role() -> str:
281    """Create (or reconcile) the SelectOrgViewer custom role at the org level.
282    Idempotent: PATCHes the permission set on re-run so newly added
283    permissions (e.g. bigquery.config.get) land without manual intervention.
284    Returns the full role name to bind."""
285    step(f"Step 4/7 — custom org role {ORG_CUSTOM_ROLE_ID} on organizations/{ORG_ID}")
286    role_name = f"organizations/{ORG_ID}/roles/{ORG_CUSTOM_ROLE_ID}"
287    role_body = {
288        "title": ORG_CUSTOM_ROLE_TITLE,
289        "description": ORG_CUSTOM_ROLE_DESC,
290        "includedPermissions": ORG_CUSTOM_ROLE_PERMISSIONS,
291        "stage": "GA",
292    }
293    existing = gcp_api("GET", f"https://iam.googleapis.com/v1/{role_name}",
294                       allow=(404,))
295    if existing:
296        # A previously deleted custom role sticks around soft-deleted for 7
297        # days; undelete before patching or the PATCH fails.
298        if existing.get("deleted"):
299            gcp_api("POST", f"https://iam.googleapis.com/v1/{role_name}:undelete",
300                    body={})
301            ok("undeleted")
302        gcp_api(
303            "PATCH",
304            f"https://iam.googleapis.com/v1/{role_name}"
305            "?updateMask=title,description,includedPermissions,stage",
306            body=role_body,
307        )
308        ok(f"reconciled ({len(ORG_CUSTOM_ROLE_PERMISSIONS)} permissions)")
309    else:
310        gcp_api(
311            "POST",
312            f"https://iam.googleapis.com/v1/organizations/{ORG_ID}/roles",
313            body={"roleId": ORG_CUSTOM_ROLE_ID, "role": role_body},
314            # 409 if it was created between our GET and create — fine.
315            allow=(409,),
316        )
317        ok(f"created ({len(ORG_CUSTOM_ROLE_PERMISSIONS)} permissions)")
318    return role_name
319
320
321def grant_org_iam(role_name: str) -> None:
322    step(f"Step 5/7 — bind {ORG_CUSTOM_ROLE_ID} on organizations/{ORG_ID}")
323    member = f"serviceAccount:{sa_email()}"
324    get_url = f"https://cloudresourcemanager.googleapis.com/v3/organizations/{ORG_ID}:getIamPolicy"
325    set_url = f"https://cloudresourcemanager.googleapis.com/v3/organizations/{ORG_ID}:setIamPolicy"
326    changed = _add_iam_member(get_url, set_url, role_name, member)
327    ok(f"{ORG_CUSTOM_ROLE_ID} {'granted' if changed else '(already present)'}")
328
329
330def grant_dataset_iam() -> None:
331    bp, bd = parse_billing_dataset()
332    step(f"Step 6/7 — bigquery.dataViewer on {bp}:{bd}")
333    client = bigquery.Client(project=bp)
334    try:
335        ds = client.get_dataset(bigquery.DatasetReference(bp, bd))
336    except gax_exc.NotFound:
337        fail(f"Dataset {bp}:{bd} does not exist. Configure the billing export "
338             "in the Cloud Console first, then re-run.")
339        sys.exit(1)
340    already = any(
341        e.role == "READER" and e.entity_id == sa_email()
342        for e in ds.access_entries
343    )
344    if already:
345        ok("READER already granted")
346        return
347    ds.access_entries = list(ds.access_entries) + [
348        bigquery.AccessEntry(role="READER", entity_type="userByEmail",
349                             entity_id=sa_email()),
350    ]
351    client.update_dataset(ds, ["access_entries"])
352    ok("READER granted")
353
354
355def grant_token_creator() -> None:
356    step(f"Step 7/7 — allow {SELECT_BACKEND_SA} to impersonate {sa_email()}")
357    get_url = f"https://iam.googleapis.com/v1/projects/{PROJECT_ID}/serviceAccounts/{sa_email()}:getIamPolicy"
358    set_url = f"https://iam.googleapis.com/v1/projects/{PROJECT_ID}/serviceAccounts/{sa_email()}:setIamPolicy"
359    changed = _add_iam_member(get_url, set_url,
360                              "roles/iam.serviceAccountTokenCreator",
361                              f"serviceAccount:{SELECT_BACKEND_SA}")
362    ok(f"tokenCreator {'granted' if changed else '(already present)'}")
363
364
365# ---------- verification ----------
366
367def _impersonated_client() -> bigquery.Client:
368    src, _ = google.auth.default(
369        scopes=["https://www.googleapis.com/auth/cloud-platform"],
370    )
371    tgt = impersonated_credentials.Credentials(
372        source_credentials=src,
373        target_principal=sa_email(),
374        target_scopes=["https://www.googleapis.com/auth/cloud-platform"],
375        lifetime=600,
376    )
377    return bigquery.Client(credentials=tgt, project=PROJECT_ID)
378
379
380def _dry_run(client: bigquery.Client, label: str, sql: str) -> bool:
381    cfg = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
382    try:
383        job = client.query(sql, job_config=cfg)
384        gb = (job.total_bytes_processed or 0) / 1e9
385        ok(f"{label}  (dry-run, est. {gb:.3f} GB if executed)")
386        return True
387    except Exception as e:
388        fail(f"{label}: {e}")
389        return False
390
391
392def verify() -> bool:
393    step("Verification — dry-runs (free) + one cheap read on billing export")
394    # Wait for IAM to propagate — tokenCreator was granted seconds ago.
395    waits = [0, 15, 30, 30]
396    client = _impersonated_client()
397    for attempt, delay in enumerate(waits, start=1):
398        if delay:
399            print(f"    retrying impersonation in {delay}s "
400                  f"(attempt {attempt}/{len(waits)})...")
401            time.sleep(delay)
402        try:
403            client.query("SELECT 1").result()
404            ok(f"impersonation works (attempt {attempt})")
405            break
406        except Exception as e:
407            last_err = e
408    else:
409        fail(f"impersonation failed: {last_err!r}")
410        return False
411
412    all_ok = True
413    # Dry-run probes: prove permission to read the views without scanning.
414    all_ok &= _dry_run(
415        client, "INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION",
416        f"SELECT job_id FROM `{REGION}`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION "
417        f"WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MINUTE)",
418    )
419    all_ok &= _dry_run(
420        client, "INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION",
421        f"SELECT table_schema FROM "
422        f"`{REGION}`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION",
423    )
424    all_ok &= _dry_run(
425        client, f"INFORMATION_SCHEMA.SCHEMATA ({PROJECT_ID})",
426        f"SELECT schema_name FROM "
427        f"`{PROJECT_ID}.{REGION}`.INFORMATION_SCHEMA.SCHEMATA",
428    )
429
430    # One real read on the billing dataset. dry-run is enough for query-level
431    # views; here we want to actually exercise the dataset-level READER grant.
432    # We constrain on _PARTITIONTIME so the scan is tiny — LIMIT alone would
433    # not reduce the scan.
434    bp, bd = parse_billing_dataset()
435    try:
436        tables = list(client.list_tables(f"{bp}.{bd}"))
437    except Exception as e:
438        fail(f"list tables in {bp}:{bd}: {e}")
439        return False
440    billing = next((t for t in tables
441                    if t.table_id.startswith("gcp_billing_export_resource_v1_")),
442                   None)
443    if billing is None:
444        warn(f"no gcp_billing_export_resource_v1_* table in {bp}:{bd} yet — "
445             "the first export can take up to 24h. Re-run verify later.")
446        return all_ok
447
448    sql = (
449        f"SELECT cost FROM `{bp}.{bd}.{billing.table_id}` "
450        f"WHERE DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY) "
451        f"LIMIT 1"
452    )
453    try:
454        rows = list(client.query(sql).result(max_results=1))
455        ok(f"read {len(rows)} row(s) from {billing.table_id} "
456           f"(partition-pruned to last 2 days)")
457    except Exception as e:
458        fail(f"billing read: {e}")
459        all_ok = False
460    return all_ok
461
462
463# ---------- main ----------
464
465def main() -> None:
466    if PROJECT_ID in ("customer-billing-project",) or ORG_ID == "123456789012":
467        print("Edit the CONFIG block at the top of this script first.",
468              file=sys.stderr)
469        sys.exit(2)
470
471    bp, bd = parse_billing_dataset()
472    print("SELECT for BigQuery — onboarding")
473    print(f"  PROJECT_ID:        {PROJECT_ID}")
474    print(f"  ORG_ID:            {ORG_ID}")
475    print(f"  BILLING_DATASET:   {bp}:{bd}")
476    print(f"  SERVICE_ACCOUNT:   {sa_email()}")
477    print(f"  REGION:            {REGION}")
478
479    try:
480        enable_apis()
481        create_service_account()
482        grant_project_iam()
483        org_role_name = ensure_org_custom_role()
484        grant_org_iam(org_role_name)
485        grant_dataset_iam()
486        grant_token_creator()
487    except Exception as e:
488        fail(f"setup failed: {e}")
489        sys.exit(1)
490
491    verify_ok = verify()
492
493    print()
494    print("============================================================")
495    print(" Paste these into select.dev → Settings → BigQuery → Add Account:")
496    print("============================================================")
497    print(f"  Organization ID:                {ORG_ID}")
498    print(f"  Billing export project ID:      {bp}")
499    print(f"  Billing export dataset name:    {bd}")
500    print(f"  Service account email:          {sa_email()}")
501    print("============================================================")
502    if not verify_ok:
503        print("\nNote: one or more verification checks failed above. IAM grants")
504        print("can take a couple minutes to propagate; if the failures look")
505        print("transient, re-run the verify step.")
506        sys.exit(1)
507
508
509if __name__ == "__main__":
510    main()