A Beginner’s Guide to BigQuery Interface
What is BigQuery?
BigQuery is a data warehouse on the Google Cloud Platform used to query and filter large datasets, aggregate results, and perform complex operations.
It’s SQL workspace allows you to search for public datasets, run SQL queries, and even upload your own data for analysis. Whether you’re working with public datasets, running SQL queries, or uploading your own data, BigQuery’s SQL workspace offers a range of features to support all kinds of data analysis tasks.
Log in to BigQuery
When you log in to BigQuery using the landing page, you will automatically open your project space. This is a high-level overview of your project, including the project information and the current resources being used. From here, you can check your recent activity.
Navigate to your project’s BigQuery Studio by selecting BigQuery from the navigation menu and BigQuery Studio from the dropdown menu.
BiqQuery Studio components
Once you have navigated to BigQuery from the project space, most of the major components of the BigQuery console will be present: the Navigation pane, the Explorer pane, and the SQL Workspace.
The Navigation pane
On the console page, find the Navigation pane. This is how you navigate from the project space to the BigQuery tool. This menu also contains a list of other Google Cloud Project (GCP) data tools.
The Explorer pane
The Explorer pane lists your current projects and any starred projects you have added to your console. It’s also where you’ll find the + ADD button, which you can use to add datasets.
This button opens the Add dialog that allows you to open or import a variety of datasets.
Add Public Datasets
BigQuery offers a variety of public datasets from the Google Cloud Public Dataset Program. Scroll down the Add dialog to the Public Datasets option.
Select Public Datasets. This takes you to the Public Datasets Marketplace, where you can search for and select public datasets to add to your BigQuery console. For example, search for the “noaa lightning” dataset in the Marketplace search bar. When you search for this dataset, you will find NOAA’s Cloud-to-Ground Lightning Strikes data.
Select the dataset to read its description. Select View dataset to create a tab of the dataset’s information within the SQL workspace.
Star and examine Public Datasets
You added the public noaa_lightning dataset to your BigQuery Workspace, so the Explorer pane displays the noaa_lightning dataset, along with the list of other public datasets. These datasets are nested under bigquery-public-data. Star bigquery-public-data by navigating to the top of the Explorer pane and selecting the star next to bigquery-public-data.
Starring bigquery-public-data will enable you to search for and add public datasets by scrolling in the Explorer pane or by searching for them in the Explorer search bar.
For example, you might want to select a different public dataset. If you select the second dataset, “austin_311,” it will expand to list the table stored in it, “311_service_requests.
When you select a table, its information is displayed in the SQL Workspace. Select the 311_service_requests table to examine several tabs that describe it, including:
- Schema, which displays the column names in the dataset
- Details, which contains additional metadata, such as the creation date of the dataset
- Preview, which shows the first rows from the dataset
Additionally, you can select the Query button from the menu bar in the SQL Workspace to query this table.
The SQL Workspace
The final menu pane in your console is the SQL Workspace. This is where you will actually write and execute queries in BigQuery.
The SQL Workspace also gives you access to your personal and project history, which stores a record of the queries you’ve run. This can be useful if you want to return to a query to run it again or use part of it in another query.
