Google Cloud Platform – Running Queries in BigQuery
In this article, we’re going to look into how to run a query in BigQuery. Running queries is one of the most fundamental parts of discovering insights from your data.
So let’s ask an outrageous question to BigQuery here and ask it “what is the best jersey number you should choose in order to improve your basketball game?”
It’s no surprise that big data has made its way into the world of professional sports. Teams at every level are starting to gather, process, and analyze data so that they can get the most out of their players and find a competitive edge in the game. To see how this would work, let’s check out the NCAA basketball public data set available in BigQuery. The data set contains play-by-play data for several years’ worth of games. You can get information like fouls, free throws, scores, player numbers, timeouts, basically, as much data as the pros have. So let’s see if we can use this data for today’s question and determine which jersey number is the best three-point shooter.
If you want to follow along, use the BigQuery sandbox where you can analyze data without needing a credit card.
Step 1: Once you’re in the BigQuery sandbox, head over to the Google Cloud Marketplace by clicking on Add Data and then Explore Data Sets.
Step 2: Search for the NCAA basketball and click to launch the data set in the BigQuery UI. The NCAA basketball data set has a number of different tables. Clicking on the play-by-play table, you can first view the table details, which indicates that there are over four million rows of events. That’s a lot of basketball data.
Check the table schema, and you’ll see each column or field of data that is available for each of those events.
But first, let’s look at what those events are. You can start to explore the data by clicking on the Preview tab to view a few rows of sample data. But to get a more comprehensive list of all the event types, the best way to find out is to run a query.
Step 3: Click Query Table and a blank query will appear in the editor with a table reference pre-populated.
Before we write our query, it’s important to know that the language used to communicate with BigQuery is SQL, or Structured Query Language. SQL is also the standard language for communicating with relational databases. BigQuery is ANSI SQL compliant.
Step 4: So now, let’s copy in a SQL query to explore the event types. backticks
The key command in SQL for retrieving data is SELECT, which we’ll use to pull in a list of all of the different event types and number of occurrences from the play-by-play table. Notice that the table reference is in the format of the GCP project name, then data set name, then table name, separated by dots and enclosed with backticks, not quotes. We’ll select all of the event types and group the results so that we can get the count of each event. The query would be as shown below:
Step 5: Once the SQL query is written, click on the green checkmark on the right-hand side of the window to open the query validator. A green checkmark means the query is valid and will show the estimated amount of data that the query will process when you run it. If the query is invalid, a red exclamation point icon is displayed. You can click on it and get some guidance on how to correct the problem.
Step 6: Then click on the Run button and BigQuery gets to work. After the query execution is complete, the query service displays the results as a table on the web UI. You can see that the Event Type column has both three-point made and three-point miss events.
Step 7: Now we can write a new query that selects the jersey number for each play-by-play event and then uses the Event Types column to determine the three-point field goal accuracy for each jersey number. Click Run and then in less than a second we have the answer.
So according to BigQuery number 45 sinks the highest percentage of their three-pointer attempts.
Note: We can see that the table we queried has three gigabytes of data. But BigQuery only read the columns needed and therefore just processed 78 megabytes of data.