Accessing Public Datasets in BigQuery

BigQuery hosts a large collection of public datasets, real data covering everything from taxi trips to weather records, that anyone can query directly without loading or copying anything. This walks through finding one, exploring it, running a few real queries against it, and saving your work.

Step by Step Process of Accessing Public Datasets in BigQuery

Step One: Open BigQuery

Open the console, then the menu, then BigQuery, then SQL Workspace.

Open Menu > Big Query > SQL Workspace

Step Two: Add Public Data

Click on Add Data > Explore Public Data sets.

Step Three: Find and Select a Dataset

Type NYC bike. The New York Citi Bike trips dataset appears. Click it.

Step Four: Add the Dataset

Click View Dataset.

The dataset is added to your project. Expand it to see what is inside.

Step Five: Open a Table

Go to new_york_citibike, then citibike_trips.

Step Six: Explore the Table

Click Schema to see the table’s columns and types.

Click on Details. It will show the details of the  Table.

Click on Preview. It will show the table.

Step Seven: Run a Query

Click Compose New Query.

Paste in the following, which finds the ten station pairs with the most trips between them:

SELECT
  MIN(start_station_name) AS start_station_name,
  MIN(end_station_name) AS end_station_name,
  COUNT(tripduration) AS num_trips
FROM
  bigquery-public-data.new_york_citibike.citibike_trips
WHERE
  start_station_id != end_station_id
GROUP BY
  start_station_id,
  end_station_id
ORDER BY
  num_trips DESC
LIMIT
  10

Click Run.

The results appear below.

Step Eight: Review Query History and Job Details

Click Query History to see past queries you have run.

Click Job Information for a description of the job itself.

Click Execution Details for a breakdown of how the query actually ran.

To save Query,

Click on Save Query button.

Step Nine: Save the Query

Click Save Query.

Give it a name, choose its visibility, then click Save.

Step Ten: Set a Destination Table for Query Results

Click More, then Query Settings.

In Query settings, you can choose the engine for query.

You can change the destination for your query result. Instead of temporary table, you can choose a table in the project itself.

Select the destination table for query dataset.

Choose the project and dataset.

Give the table name and write preference.

Click Save.

Step Eleven: A Geospatial Distance Query

This query uses BigQuery’s built in geography functions to calculate the real world distance each bike traveled, using each trip’s start and end station coordinates, then totals it per bike:

WITH
  trip_distance AS (
  SELECT
    bikeid,
    ST_Distance(ST_GeogPoint(s.longitude, s.latitude),
      ST_GeogPoint(e.longitude, e.latitude)) AS distance
  FROM
    bigquery-public-data.new_york_citibike.citibike_trips,
    bigquery-public-data.new_york_citibike.citibike_stations AS s,
    bigquery-public-data.new_york_citibike.citibike_stations AS e
  WHERE
    start_station_id = s.station_id
    AND end_station_id = e.station_id)
SELECT
  bikeid,
  SUM(distance) / 1000 AS total_distance
FROM
  trip_distance
GROUP BY
  bikeid
ORDER BY
  total_distance DESC
LIMIT
  5

ST_GeogPoint turns a longitude and latitude pair into a point on the map, and ST_Distance calculates the straight line distance between two such points, in meters. Dividing by 1000 converts the total into kilometers.

Click Run.

The results are saved into the destination table you configured earlier.

Step Twelve: Query a Public Weather Dataset

This pulls daily rainfall readings for a specific weather station from a public weather dataset:

SELECT
  wx.date,
  wx.value / 10.0 AS prcp
FROM
  bigquery-public-data.ghcn_d.ghcnd_2015 AS wx
WHERE
  id = “USW00094728”
  AND qflag IS NULL
  AND element = “PRCP”
ORDER BY
  wx.date

Click Run.

Do You Pay to Query a Public Dataset

Storing a public dataset costs you nothing, Google covers that as the publisher. Querying one is different. Query costs are billed to whichever project runs the query, the same as querying your own data, and they count against the same monthly free tier that applies to all your BigQuery usage, the first terabyte processed each month at no charge. In practice, this means casual use of public datasets is genuinely free for most people, but a large or repeated query against a big public dataset uses up the same free allowance as querying your own tables would.

Common Mistakes to Avoid

• Copying a query from a document with curly quotation marks left in. Retype straight quotes around any string values before running it.
• Assuming a saved query without a destination table will keep its results. Query results land in a temporary table by default, which disappears after your session, unless you set a real destination table in Query Settings.
• Running a broad, unfiltered query against a very large public dataset without checking the estimated bytes processed first. It still counts toward your monthly free tier the same as any other query.

That covers exploring, querying, and saving your work with a BigQuery public dataset. To go further, explore Prwatech’s Google Cloud training program, which includes placement assistance.