BigQuery query magic

Run a query on a public dataset

%%bigquery
SELECT name, SUM(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY name
ORDER BY count DESC
LIMIT 10

Display verbose output

%%bigquery --verbose
SELECT name, SUM(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY name
ORDER BY count DESC
LIMIT 10

Explicitly specify a project

project_id = 'your-project-id'%%bigquery --project $project_id
SELECT name, SUM(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY name
ORDER BY count DESC
LIMIT 10

Assign the query results to a variable

%%bigquery df
SELECT name, SUM(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY name
ORDER BY count DESC
LIMIT 10
df

Run a parameterized query

params = {"limit": 10}%%bigquery --params $params
SELECT name, SUM(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY name
ORDER BY count DESC
LIMIT @limit

--

--

--

Machine Learning Scientist

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Download Hp Touchsmart Iq500 Repair Manual

All You Need to Know About These 5 Cool Python Libraries!!

Simple python code to enhance your code like time profiling,printing the output in a neat way & others as unidecode…

Binary Search Algorithm

Learning MongoDB Part 2

Takeaways from building a CNN in Keras

Scaling with Pandas beyond the millions (of records)

How to leverage Acceptance Tests in your iOS Apps

How to implement a log printing library from 0 to 1

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Mohamed Niang

Mohamed Niang

Machine Learning Scientist

More from Medium

Build Data Warehouse in Google Cloud using Cloud Functions, Big Query and Google Storage.

Using Explainable AI in BigQuery ML

How to time-travel with BigQuery and undelete tab

Incremental data ingestion pipelines in BigQuery