Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 37 ยท Architecture and Setup

Setting Up BigQuery: Projects, IAM and the bq CLI

Set up BigQuery for real work: create a Google Cloud project, enable the API, understand IAM roles for data and jobs, install gcloud, and run queries from the bq command line tool.

The console is great for exploring, but real work also uses the command line, scripts and service accounts. This lesson shows how to set up a project, who needs which permission, and how to use the bq command-line tool and the gcloud CLI. All commands are read-only examples, because they need your own Google Cloud account.

Step 1: a project and the API

  1. Sign in to the Google Cloud console and create a project. Choose a clear ID such as acme-analytics-dev; it is permanent.
  2. Link a billing account (skip this if you stay in the sandbox).
  3. The BigQuery API is enabled by default in new projects. Others, such as the Storage and Data Transfer APIs, are enabled when you first use them.

Using separate projects for dev, staging and prod is a common way to isolate cost and access.

Step 2: understand IAM roles

IAM (Identity and Access Management) decides who can do what. BigQuery roles are split into two jobs: reading data, and running queries (jobs).

Role What it allows
roles/bigquery.dataViewer Read table data and metadata
roles/bigquery.dataEditor Read and write tables in datasets
roles/bigquery.dataOwner Everything on data, including sharing
roles/bigquery.jobUser Run queries and load jobs in the project
roles/bigquery.user Run jobs and create datasets
roles/bigquery.admin Full control of BigQuery

The key idea: to run a query you need two things. You need permission to read the data (usually on the dataset) and permission to create a job in the project that will pay for it (jobUser). An analyst with dataViewer on a dataset but no jobUser anywhere sees "Access Denied: User does not have bigquery.jobs.create permission".

flowchart LR
  U["Analyst"] -->|"jobUser on project A"| J["Runs the query, project A is billed"]
  U -->|"dataViewer on dataset in project B"| D["Reads the data in project B"]
  J --> D

Billing follows the project that runs the job; data access follows the dataset permission.

Follow the principle of least privilege: give analysts dataViewer on specific datasets plus jobUser, and give pipelines their own service accounts instead of personal accounts.

Step 3: install gcloud and authenticate

The Google Cloud SDK includes gcloud and bq. After installing it from Google's documentation, sign in and choose a project:

Terminal
gcloud auth login
gcloud config set project acme-analytics-dev
gcloud config list

For code that runs on your laptop (for example a Python script), use Application Default Credentials:

Terminal
gcloud auth application-default login

In production, attach a service account to the compute resource (Cloud Run, a VM, Composer) so no key file ever needs to be stored. Avoid downloading JSON key files unless you have no alternative.

Step 4: the bq tool

bq runs queries and manages objects:

Terminal
# a quick query (standard SQL is the default now)
bq query --use_legacy_sql=false \
  'SELECT name, SUM(number) AS n
   FROM `bigquery-public-data.usa_names.usa_1910_2013`
   WHERE state = "TX" GROUP BY name ORDER BY n DESC LIMIT 3'

# check cost before running: a dry run
bq query --use_legacy_sql=false --dry_run \
  'SELECT * FROM `my-project.shop.orders`'

# list datasets and tables
bq ls
bq ls shop

# inspect a table's schema and metadata
bq show --schema --format=prettyjson shop.orders

# create a dataset in the EU
bq mk --dataset --location=EU acme-analytics-dev:shop

Useful flags:

  • --dry_run validates the SQL and reports the bytes it would process, without running.
  • --maximum_bytes_billed=1000000000 makes the job fail if it would bill more than 1 GB, a cheap safety net.
  • --format=csv (or prettyjson, sparse) changes the output.
  • --location=EU sets where the job runs.

Step 5: client libraries

Most applications use a client library instead of bq. Here is the shape of a Python call (read-only here; it needs credentials):

Python
from google.cloud import bigquery

client = bigquery.Client()  # uses Application Default Credentials
sql = """
    SELECT country, COUNT(*) AS customers
    FROM `acme-analytics-dev.shop.customers`
    GROUP BY country
    ORDER BY customers DESC
"""
for row in client.query(sql).result():
    print(row.country, row.customers)

Libraries exist for Python, Java, Node.js, Go, C# and more, and there is a REST API underneath all of them.

Practising the workflow offline

You cannot call BigQuery from this page, but you can rehearse the habit of checking before you run. A cheap way is to count rows and look at a few columns before running a heavy query, which is the stand-in for a dry run:

SQL Stand-in: look before you aggregate ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

And here is a "least privilege" view that exposes only non-sensitive columns, the stand-in for an authorized view that BigQuery uses to share part of a table (more in the security chapter):

SQL Stand-in: a view that hides personal columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Common setup mistakes

  • Granting bigquery.admin to everyone "to make it work". Use jobUser plus dataViewer.
  • Running queries in the wrong project and getting the bill there.
  • Datasets in different locations (US and EU) that cannot be joined.
  • Sharing a downloaded service-account key file in chat or a repository.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which two permissions does an analyst need to query a table?

    Data access comes from dataset roles; running the query needs bigquery.jobs.create, for example through jobUser.
  2. 2. What does bq query --dry_run do?

    A dry run is free and shows the byte estimate.
  3. 3. Which flag makes a job fail if it would bill more than a limit?

    maximum_bytes_billed is a guard rail against accidentally expensive queries.
  4. 4. What is the preferred way for production code to authenticate?

    Attached service accounts avoid long-lived key files.

Summary

  • Create a project, link billing (optional in the sandbox), and keep dev and prod separate.
  • Querying needs data access plus jobUser (or equivalent) in the billed project.
  • Use gcloud auth for logins and service accounts for production.
  • bq query --dry_run and --maximum_bytes_billed protect your wallet.
  • Client libraries and the REST API cover application use.

Next: BigQuery Data Types