Google BigQuery: The Definitive Guide

Chapter 4. Loading Data into BigQuery

In the previous chapter, we wrote the following query:

SELECT 
  state_name
FROM `bigquery-public-data`.utility_us.us_states_area
WHERE
   ST_Contains(
     state_geom,
     ST_GeogPoint(-122.33, 47.61))

We also learned that the city at the location (-122.33, 47.61) is in the state of Washington. Where did the data for the state_name and state_geom come from?

Note the FROM clause in the query. The owners of the bigquery-public-data project had already loaded the state boundary information into a table called us_states_area in a dataset called utility_us. Because the team shared the utility_us dataset with all authenticated users of BigQuery (more restrictive permissions are available), we were able to query the us_states_area table that is in that dataset.

But how did they get the data into BigQuery in the first place? In this chapter, we look at various ways to load data into BigQuery, starting with the basics.

The Basics

Data values such as the boundaries of US states change rarely,¹ and the changes are small enough that most applications can afford to ignore them. In data warehousing lingo, we call this a slowly changing dimension. As of this writing, the last change of US state boundaries occurred on January 1, 2017, and affected 19 home owners and one gas station.²

State boundary data is, therefore, the type of data that is often loaded just once. Analysts query the single table and ignore the fact that the data could change over time. For example, ...