How to Connect to BigQuery from Terminal or GUI in PostgreSQL

Galaxy Glossary

How can I connect to BigQuery using both the command line and a GUI?

Shows how to authenticate and run BigQuery SQL from the command line or a graphical SQL editor.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How do I authenticate to BigQuery from the terminal?

Run gcloud auth login and follow the browser prompt. For CI/CD, prefer gcloud auth activate-service-account --key-file=/path/key.json. Both commands store credentials that the bq CLI automatically reuses.

How do I set the default GCP project?

Run gcloud config set project my-gcp-project. A default project prevents No project specified errors in subsequent bq commands.

How do I run SQL queries with the bq CLI?

Use bq query followed by a quoted statement. Example:
bq query --use_legacy_sql=false 'SELECT * FROM `shop.Orders` LIMIT 10'. Add --format=prettyjson to return JSON and --location=US if your dataset is region-specific.

How do I connect BigQuery to a GUI like Galaxy?

In Galaxy, click “Add Connection → BigQuery”. Upload your service-account JSON, then choose the default project and billing location. Galaxy auto-discovers datasets and provides IntelliSense for tables such as Customers and Orders.

Can I join BigQuery data with PostgreSQL tables?

Create a PostgreSQL foreign data wrapper using Cloud SQL Federation or the Simba ODBC driver. Map external BigQuery tables, then query them just like local tables, e.g. SELECT * FROM bigquery.orders LIMIT 10;.

What are best practices for service-account keys?

Grant the key only roles/bigquery.user and rotate it at least every 90 days. Store it in a secrets manager, never in source control.

How do I copy query results to a local CSV?

Run bq query --format=csv --use_legacy_sql=false 'SELECT * FROM `shop.Products`' > products.csv. The file streams directly to your terminal’s working directory.

Why How to Connect to BigQuery from Terminal or GUI in PostgreSQL is important

How to Connect to BigQuery from Terminal or GUI in PostgreSQL Example Usage


bq query --use_legacy_sql=false "SELECT oi.order_id,
       p.name,
       oi.quantity,
       oi.quantity * p.price AS line_total
FROM   `shop.OrderItems` oi
JOIN   `shop.Products`   p ON p.id = oi.product_id
WHERE  oi.order_id = 1234;"

How to Connect to BigQuery from Terminal or GUI in PostgreSQL Syntax


Command-line:
  gcloud auth login
  gcloud auth activate-service-account --key-file=KEY.json
  gcloud config set project PROJECT_ID
  bq query [--use_legacy_sql=false] [--format=prettyjson|csv] [--location=REGION] 'SQL'

GUI (Galaxy) connection string:
  bigquery://projects/PROJECT_ID?location=REGION&creds=/path/key.json

Example SQL using ecommerce schema:
  SELECT c.name,
         SUM(o.total_amount) AS lifetime_value
  FROM   `shop.Customers`  c
  JOIN   `shop.Orders`     o ON o.customer_id = c.id
  GROUP  BY c.name
  ORDER  BY lifetime_value DESC;

Common Mistakes

Mistake: Using the default legacy SQL dialect in <code>bq query</code>. Fix: Always set <code>--use_legacy_sql=false</code> or enable Standard SQL in your CLI profile.
Mistake: Forgetting to set the default project, resulting in permissions errors. Fix: Run <code>gcloud config set project YOUR_PROJECT</code> or add <code>--project_id</code> to every command.

Frequently Asked Questions (FAQs)

Do I need to install the BigQuery client separately from gcloud?

No. The bq CLI ships with the Cloud SDK. After installing gcloud you automatically have the latest bq version.

Can I use environment variables for credentials?

Yes. Set GOOGLE_APPLICATION_CREDENTIALS=/path/key.json to make both the bq CLI and most GUIs pick up the service-account file without manual upload.