How to Query Data in BigQuery: Simple Guide

To query data in BigQuery, use standard SQL commands like SELECT to retrieve data from tables. You write queries in the BigQuery console or via API, specifying the dataset and table you want to access.

Syntax

The basic syntax to query data in BigQuery uses standard SQL:

• SELECT: Choose columns to retrieve.
• FROM: Specify the dataset and table.
• WHERE: Filter rows based on conditions.
• LIMIT: Restrict the number of rows returned.

sql

SELECT column1, column2
FROM `project.dataset.table`
WHERE condition
LIMIT 10;

Example

This example retrieves the first 5 names and ages from a sample dataset where age is greater than 20.

sql

SELECT name, age
FROM `bigquery-public-data.usa_names.usa_1910_2013`
WHERE age > 20
LIMIT 5;

Output

name       | age
-----------|-----
Mary       | 21
John       | 25
Robert     | 30
Patricia   | 22
Michael    | 28

Common Pitfalls

Common mistakes when querying BigQuery include:

• Not using backticks ` around project, dataset, and table names.
• Forgetting to specify the full table path project.dataset.table.
• Using legacy SQL instead of standard SQL (BigQuery defaults to standard SQL).
• Not limiting results, which can cause large data scans and higher costs.

sql

/* Wrong: Missing backticks and full table path */
SELECT name, age
FROM usa_names.usa_1910_2013
WHERE age > 20
LIMIT 5;

/* Right: Full path with backticks */
SELECT name, age
FROM `bigquery-public-data.usa_names.usa_1910_2013`
WHERE age > 20
LIMIT 5;

Quick Reference

Clause	Purpose	Example
SELECT	Choose columns to show	SELECT name, age
FROM	Specify table to query	FROM `project.dataset.table`
WHERE	Filter rows	WHERE age > 20
LIMIT	Limit number of rows	LIMIT 10
ORDER BY	Sort results	ORDER BY age DESC

Key Takeaways

Always use backticks around full table names in the format `project.dataset.table`.

Use standard SQL syntax with SELECT, FROM, WHERE, and LIMIT clauses to query data.

Limit your query results to control cost and speed.

BigQuery queries run in the cloud and can handle very large datasets efficiently.

Check for syntax errors and use the BigQuery console for easy query testing.