BIGQUERY

Google BigQuery: A Beginner’s Guide

intro

As businesses grow, managing and analyzing data becomes challenging. Traditional data warehouses have become expensive to scale and can no longer handle large data sets.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Fortunately, Google BigQuery solves these problems as a cloud-based data warehouse. It is designed to handle large sets of data fast by using a serverless architecture. With Google BigQuery, you don’t have to worry about managing servers or resources.

In this guide, you will learn what Google BigQuery is, why should you use it, and how to get started. Also, you will learn how to add data into Google BigQuery and how to query the data.

What is Google BigQuery?

Google BigQuery is a serverless data warehouse provided by Google Cloud. Google BigQuery enables businesses to store, query, and analyze large volumes of data efficiently and quickly.

The design of Google BigQuery enables it to handle petabyte-scale datasets by utilizing a distributed architecture to manage complex analytical workloads. In Google BigQuery, businesses can derive valuable insights from their data and make data-driven decisions.

Uses of Google BigQuery and Why to use it

Google BigQuery helps you manage and analyze your data with built-in features like machine learning, geospatial analysis, and business intelligence. Some use cases for Google BigQuery include:

  • Business Intelligence: Google BigQuery can be used to analyze business data such as sales, customer interactions, and marketing campaigns.
  • Internet of Things (IoT): Google BigQuery can be used to analyze IoT data such as sensor readings and device logs.
  • Machine Learning: Google BigQuery can be used to train machine learning models on large datasets.
  • Data Warehousing: Google BigQuery can be used to store large amounts of structured and semi-structured data.
  • Real-time Analytics: Google BigQuery can be used to analyze real-time data such as website traffic and social media feeds.

Here are some reasons you should use Google BigQuery:

  • Scalability: BigQuery is built to handle massive datasets, ranging from gigabytes to petabytes. It can effortlessly scale its processing power to accommodate the growing needs of your organization, ensuring fast and reliable query performance even as your data volumes increase.
  • Speed: With its distributed architecture and optimized query execution, BigQuery enables high-speed data analysis. It can process complex queries in seconds or minutes, allowing for near real-time insights and faster decision-making.
  • Serverless and Managed: BigQuery is a fully managed service, so you don't have to worry about infrastructure provisioning, software updates, or maintenance. Google takes care of the underlying infrastructure, ensuring high availability and freeing up your team's time to focus on data analysis.
  • Cost-effective: BigQuery follows a pay-as-you-go pricing model, where you only pay for the storage used and the data processed. There are no upfront costs or long-term commitments, making it a cost-effective choice for organizations of all sizes.
  • Integration with Google Cloud ecosystem: BigQuery seamlessly integrates with other Google Cloud services, such as Google Analytics, Google Cloud Storage, and Google Data Studio.

Getting Started with Google BigQuery

Getting started with Google BigQuery is user-friendly because it is a cloud service, and you don’t have to install any software. To get started with Google BigQuery, follow the steps below.

Step 1: To get started with Google BigQuery, navigate to Google Cloud Platform and click the console button on the top right side of the page.

Google Cloud Platform Homepage.
Google Cloud Platform Homepage.

Step 2: Complete the steps that follow, and you will be redirected to create a project by clicking the Create Project button.

Clicking the Create Project Button in Google BigQuery.
Clicking the Create Project Button in Google BigQuery.

Step 3: Next, give your project a name and click the create button to create your project.

Giving the project a name in Google BigQuery.
Giving the project a name in Google BigQuery.

Step 4: After that, create a BigQuery dataset by opening the hamburger menu beside your project and clicking the Create dataset button.

Creating a BigQuery dataset in Google BigQuery.
Creating a BigQuery dataset in Google BigQuery.

Step 5: You will then be prompted to add a dataset id and select a data location. After that, click the Create Dataset button.

Adding dataset id and selecting a data location.
Adding dataset id and selecting a data location.

A new dataset is created in your project and you can click it to open it.

The created dataset in Google BigQuery.
The created dataset in Google BigQuery.

Adding data into Google BigQuery

Let us add a supermarkets' stores dataset CSV file as the data source into Google BigQuery. You can download it by navigating to this Kaggle web page.

Supermarkets stores' dataset in Kaggle.
Supermarkets stores' dataset in Kaggle.

To add data into Google BigQuery, follow the steps below.

Step 1: On the Google Cloud Platform, open the dataset you created earlier and click the Create Table button.

Opening the dataset and clicking the Create Table button.
Opening the dataset and clicking the Create Table button.

Step 2: After that, you will be prompted to add a data source. In the Create Table From field, select the Upload option.

Selecting the upload option for the Create Table from a field.
Selecting the upload option for the Create Table from a field.

Step 3: Next, click the Browse button, and navigate to where the supermarkets’ stores dataset CSV file was saved, and select it.

Clicking the browse button to select dataset CSV file.
Clicking the browse button to select dataset CSV file.

Step 4: Give the table data a name in the destination section.

Giving table a name in Google BigQuery.
Giving table a name in Google BigQuery.

Step 5: After that, check the Auto detect checkbox to generate schema automatically once the table is created. Then click the Create Table button to create the table.

Checking the auto detect checkbox and creating a table.
Checking the auto detect checkbox and creating a table.

Once the table is created, you can view the supermarkets’ stores data under the preview tab.

Supermarkets Stores data in Google BigQuery.
Supermarkets Stores data in Google BigQuery.

Next, we need to query and visualize data in Google BigQuery using DbVisualizer, a database SQL client.

Connecting Google BigQuery With DbVisualizer

DbVisualizer is a universal database tool that provides a user-friendly interface for working with different database management systems (DBMS). It includes everything you need to develop, connect and manage your BigQuery database. It supports BigQuery-specific object types and everything else that makes BigQuery great.

To install DbVisualizer, navigate to the DbVisualizer download page. Then download the recommended installer for your operating system. After that, execute the installer you downloaded and follow the instructions.

Follow the user guide here to learn how to get started with DbVisualizer. Once DbVisualizer is installed and running on your computer, follow the steps below to connect it with Google BigQuery.

Step 1: On DbVisualizer, click Create a Connection button as shown below.

Clicking create a connection button in DbVisualizer.
Clicking create a connection button in DbVisualizer.

Step 2: Search and double-tap the Google BigQuery driver from the popup menu on the left side of your screen, as shown below.

Searching Google BigQuery driver in DbVisualizer.
Searching Google BigQuery driver in DbVisualizer.

An object view tab for the Google BigQuery connection is opened.

Object view tab for the Google BigQuery connection.
Object view tab for the Google BigQuery connection.

Step 3: In the connection tab, select Connection Type as OAuth 1.

Selecting Connection Type as OAuth 1 in DbVisualizer.
Selecting Connection Type as OAuth 1 in DbVisualizer.

Step 4: Navigate back to Google BigQuery, get your project Id, and add it to the Project Id field on the connection tab.

Adding Google BigQuery project id in DbVisualizer.
Adding Google BigQuery project id in DbVisualizer.

Step 5: Click the Connect button in the connection tab and the Google BigQuery driver will pop-up a dialog where you can obtain a URL.

Google BigQuery driver pop up dialog in DbVisualizer.
Google BigQuery driver pop up dialog in DbVisualizer.

Step 6: Copy the URL into a browser and log in via Google. You will get a token string that you can paste into the same dialog where you copied the URL. After that, Click the OK button on the dialog to establish a connection.

Viewing data on Google BigQuery in DbVisualizer.
Viewing data on Google BigQuery in DbVisualizer.

You’ve now established a connection between DbVisualizer and Google BigQuery!

Querying Data In Google BigQuery Using DbVisualizer

To run a query on data in Google BigQuery using DbVisualizer, follow the steps below.

Step 1: Press the (Ctrl+T) keys to open a SQL editor.

SQL editor in DbVisualizer.
SQL editor in DbVisualizer.

Step 2: Enter the SQL query below into the SQL editor and then press the (Ctrl+Enter) keys to run the query. The query determines the top 10 stores by sales from the stores' data on Google BigQuery.

Copy
        
1 SELECT BigQueryDemoTable.Store_ID_, BigQueryDemoTable.Store_Sales AS Sales 2 FROM biqquerydemo1.BigQueryDemoDataset.BigQueryDemoTable 3 GROUP BY BigQueryDemoTable.Store_ID_, BigQueryDemoTable.Store_Sales 4 ORDER BY BigQueryDemoTable.Store_Sales 5 DESC LIMIT 10

The SQL query results are shown in a table with two columns of Store_ID and Sales.

SQL query results in DbVisualizer.
SQL query results in DbVisualizer.

Visualizing Data In Google BigQuery Using DbVisualizer

When using DbVisualizer, you can present data from any table in a configurable chart displayed in a line, bar, area, or pie chart. The charting support is similar to that of MsExcel. The charts can be exported as an image to file, printed, or copied to the system clipboard for easy sharing.

To activate the chart view, click the rightmost button in the result toolbar, as shown below.

Activating chart view in DbVisualizer.
Activating chart view in DbVisualizer.

In the chart, select Store_ID as the X-axis and Sales as the Y-axis, as shown below.

A chart in DbVisualizer.
A chart in DbVisualizer.

See how easy everything is? Why not try DbVisualizer today?

Conclusion

In conclusion, Google BigQuery is a powerful and efficient cloud-based data warehouse provided by Google Cloud to offer a scalable and cost-effective solution for businesses to store, query, and analyze large volumes of data.

Google BigQuery can also work in conjunction with multiple types of software solutions like SQL clients, and SQL clients like DbVisualizer can help query and visualize the data existing in the service.

We hope that you’ve found this blog useful - make sure to follow our blog for updates and news in the database space until next time.

Dbvis download link img
About the author
Bonnie
Bonnie
Bonnie is a web developer and technical writer creating easy-to-understand technical articles.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL STUFF: Insert a String Into Another in SQL Server

author Antonello Zanini tags SQL SERVER 7 min 2024-07-18
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

OpenSearch vs. ElasticSearch: What to Choose?

author Lukas Vileikis tags Search 5 min 2024-07-08
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Types of Databases Explained

author Lukas Vileikis tags MySQL 6 min 2024-07-01
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗