How to Use Google BigQuery: A 5-Step Practical Guide

What is Google BigQuery?

Google BigQuery is a serverless data warehouse available from the Google Cloud Platform (GCP) that allows users to analyze terabytes of data in a matter of seconds and petabytes in a matter of minutes.

BigQuery architecture is based on a distributed system called Dremel, which is capable of running SQL queries on large datasets by partitioning the query execution into slots. This provides fairness when multiple users are querying data simultaneously.

Unlike traditional relational databases that store data row by row, BigQuery stores data in columnar format, which enables a high compression ratio and scan bandwidth. However, you can also use BigQuery with data stored in other Google Cloud services such as BigTable, Google Cloud Storage, Cloud SQL, and Google Drive.

Source

However, this tabular data is mainly used by data scientists whose use cases include running advanced syntax queries of the client libraries.

Marketing teams, on the other hand, benefit from visualizing BigQuery data through third-party reporting tools such as Whatagraph, Tableau, and Looker.

So what makes Whatagraph stand out as a BigQuery pipeline?

If you run a small non-tech team or a marketing agency that handles hundreds of accounts, you need a quick and hassle-free way to load your clients' data to BigQuery.

Whatagraph not only saves the time needed to load data from multiple sources to Google BigQuery but allows you to create beautiful interactive dashboards on the BigQuery data with just a few clicks.

No code knowledge or data engineering is required.

Why should you use Google BigQuery?

• Serverless big data storage: Export data and run ad-hoc analyses when you need them.
• Petabyte scale: Fast and easy to use on data of any size.
• Real-time analytics: High-speed streaming insertion BigQuery API enables almost real-time insights into your business data.
• ETL data load: Load data from databases and applications outside the Google Cloud Platform using third-party ETL (Extract, Transform, Load) tools.

• Flexible pricing: Choose the BigQuery pricing model that suits your needs. You pay for storage and data analysis, while visualization is free.
• Data encryption & security: You get complete access control over the data stored in BigQuery. Thanks to granular access and permissions management, your data is always encrypted in storage and transit.
• Standard SQL: BigQuery supports SQL dialect, which is ANSI:2011 compliant and reduces the need for code rewrite.
• Built-in AI: BigQuery provides a flexible foundation for machine learning and artificial intelligence.
• Base for BI: BigQuery forms the backbone for modern business solutions, which enables data integration, transformation, analysis, visualization, and reporting.
• Public data: If you don’t have your own data, run queries on more than 100 public datasets from a range of industries, compliments of Google.
• Sandbox account: A free tier account you can use to explore the capabilities of BigQuery.

How to use Google BigQuery?

Step 1: Create a data set in BigQuery

To start, download the latest version of the dataset in CSV format to your local computer.

Step 2: Upload the data to BigQuery

In the Google Cloud Platform, navigate to the Google BigQuery Console in the Big Data section.

Locate the Create Dataset button on the right side panel and start the creation process. Give the dataset a unique identifier and select the geographical location to store and process the cloud data.

Now select the newly created dataset and click the Create Table button. Use Upload as the source method, CSV as the file format, and select the local dataset from your device.

Give the table a name and tick the Auto Detect option for the schema. Save by hitting the button at the bottom of the panel.

Step 3: Use BigQuery to query the stored data

Once you upload and store the dataset in BigQuery, you’ll be able to start querying the data using standard SQL immediately.

BigQuery data analytics is powerful enough to give you advanced SQL capabilities to run queries that provide aggregated query results.

Step 4: Add the dataset to Google Cloud

Since Google BigQuery supports several external data sources, you can achieve similar results and capabilities even if you use Google Cloud Storage (GCS) as data storage for the dataset file.

This way, you can create a new Google Cloud Storage bucket and upload the dataset file.

To learn how to import data to BigQuery on schedule, I suggest you read our BigQuery Tutorial.

Step 5: Using the dataset in Google Cloud

First, you must create a new table under your BigQuery dataset and select Google Cloud Storage as the source. Fill in the GCS bucket name and file location with CSV as the format. Give it a different name from the previously created table.

The newly created table will become immediately available in the interface. The data can be queried in the same manner as any other data you store in BigQuery.

To test it, use the same aggregation query by updating the FROM clause with the new table name.

Conclusion

BigQuery is a robust cloud-based platform for data exploration and analysis in a world where the influx of data is growing at an astonishing rate.

If you need BigQuery to accelerate data migration from different apps, I suggest you try Whatagraph’s data transfer service.

Whatagraph has connectors for the most popular marketing platforms, allowing you to load data in BigQuery in just a few steps.

No command-line tool, Java or Python required.

Book a demo call with our product team to find out how to use Google BigQuery data to create rich visual dashboards that will impress your clients.