Introduction to Google BigQuery

By Mauricio Ashimine

It is incredible to see how much businesses rely on data today. 80% of business operations are running in the cloud, and almost 100% of business-related data and documents are now stored digitally. In the 1960s, money made the world go around but in today’s markets, “Information is the oil of the 21st century, and analytics is the combustion engine.” (Peter Sondergaard, 2011)

Data helps businesses gain a better understanding of processes, improve resource usage, and reduce waste; in essence, data is a significant driver to boosting business efficiency and profitability.

This reliance on data isn’t without challenges though. A business can have large data warehouses and no efficient way of processing the data in them. There is also the challenge of sorting valuable data from noise, primarily when you collect data from public sources. Amassing data is meaningless without the tools and means to process, analyze, and act on it. So the important questions are: How can you make this process painless and how do you become a successful data-driven company? The answer to both lies in Google BigQuery. 

So, What Is Google BigQuery?

Without the right tools, data collection and processing are not only challenging but costly. To store its first 1TB of data, a business needs to invest heavily in a large and reliable server cluster capable of running calculations and managing multiple storage nodes.

Today, that problem is gone thanks to services like Google Cloud Platform (GCP). GCP doesn’t just make establishing data warehouses easy but the platform also makes collecting and processing large volumes of data affordable. This is referred to as data democratization; cloud services from providers like Google making enterprise-grade data processing accessible to even small home businesses.

A key component to Google’s ecosystem is Google BigQuery. Google BigQuery is a data warehouse suite that handles data processing through SQL commands, all without requiring you to create a cloud computing instance specifically for processing data. 

What BigQuery does is leveraging data stored in buckets and databases through simple ANSI SQL. We know how taxing SQL queries can be when complex, but that is no longer something you need to worry about with BigQuery. BigQuery can run complex SQL queries at incredible speeds, even when you have petabytes of data to analyze.

The secret behind BigQuery’s efficiency and speed is its serverless nature. You are not spooling up a separate BigQuery instance to process data. Google decouples cloud architecture from data management, meaning Google is responsible for maintaining availability and security. Instead, you pay only for the storage you use.

Being serverless and decupled are not the only features offered by Google Big Query. The service also comes with a long list of capabilities.

  • Standard SQL queries for all your data needs. If you are already familiar with SQL or you’ve used frameworks like MS SQL and MySQL before, you will have no trouble adapting to Google BigQuery. There is no steep learning curve to deal with.
  • High availability by nature. Google designs its services to be highly available, and BigQuery is no different. Besides persistent data storage, you also have your data accessible from the nearest nodes without paying extra for CDN services.
  • Granular cost control. With Google BigQuery, storage and computing cost components are separate cost components. You can fine-tune every part of your data infrastructure without jumping through hoops. Even better, you can transparently control costs.
  • Advanced access management. Google BigQuery integrates well with other Google services, so you still get features such as access control and enhanced security with the service. The centralized control dashboard gives you all the features you need to manage access to different segments of your data.
  • Backup and restore automation. Data security features offered by Google BigQuery also include data protection in the form of resilient backups. BigQuery has an intuitive versioning feature that automatically keeps versions of your data for up to seven days, so you can always revert to older versions or undo changes at any point.
  • ML and AI integrations. Of course, in-depth data analysis becomes more powerful when machine learning and artificial intelligence are parts of the equation. You get that with BigQuery ML. You have the option to integrate AI Platform or even TensorFlow to strengthen data analysis routines further.
  • Native multi-cloud support. Despite the deep integration with GCP and Google’s services, Google BigQuery has native support for multi-cloud infrastructure. The solution for multi-cloud integration is BigQuery Omni. It may still be in its early phase, but you have the option to manage multiple cloud-based data infrastructures from BigQuery.
  • Integrated natural language processing. If the availability of suites like TensorFlow is not what you want, you will undoubtedly appreciate Data QnA, which is BigQuery’s own natural language processing unit. The suite is an instance of Analyza, which is already very popular among data scientists. It can be utilized in use cases like chatbots and business intelligence too.
  • Multiple data ingestion methods. Of course, a good data warehouse is nothing without efficient data ingestion pipelines. This is one of the parts where BigQuery shines. Free Data Transfer Service or DTS handles ingestion. It works out of the box with services like Salesforce and other cloud business solutions, plus it works at scale from the beginning.

The list of features goes on with things like access to Google Cloud Public Datasets, detailed logging and monitoring, a built-in alert system, and so much more. Google is trying to put BigQuery in the center of all business data storage and analysis needs. From the feature sets that we’ve seen so far, Google is doing an excellent job at it. 

Google BigQuery Use Cases

From the features and native services, it is easy to imagine how to utilize Google BigQuery to support business data needs. The most common use case for BigQuery is for business intelligence. Businesses can use external data collection tools—or even web scraping—and then direct data in XML, JSON, CSV, and a wide range of other formats to BigQuery.

Preprocessing will be a huge part of using BigQuery for business intelligence. BigQuery can filter unnecessary noise from valuable data and then organize that data into a semi-structured or structured warehouse. This is a huge plus because you will then have more flexibility in doing analysis and generating insights.

Once the data workflow includes basic BI processes, everything else is easy. Predictive analysis, for instance, becomes incredibly easy. As the system consumes more data, it will have the ability to create predictive models that can produce accurate forecasts of crucial business factors (i.e., prices or costs) with high accuracy.

On the other hand, you can also use BigQuery to generate product recommendations and then integrate the system into your existing online storefront or e-commerce platform. This gives your business a huge advantage, especially in a market as competitive as today. Having that ability to present products and services contextually will boost conversion.

Naturally, BigQuery is capable of doing standard data management routines, making it the perfect tool for automating your data processes. Rather than manually synchronizing and structuring data, you can integrate BigQuery to automate the process. This makes directing data to other tools like CRM a lot easier.

Google BigQuery Advantages and Disadvantages

So, what are the advantages and disadvantages of using BigQuery to support your data needs? Let’s start with the advantages, shall we?

Among the many advantages of integrating BigQuery are:

  • It is a fully managed service so that you can focus on the actual data processing and insight generation rather than the data infrastructure itself.
  • There is no limit in storage size and processing power. You can scale BigQuery as you scale your data warehouses. In essence, BigQuery grows alongside your business.
  • Native integration with virtually all data sources. You don’t have to do queries on multiple platforms to get in-depth analysis and insights. A lot of businesses are now using BigQuery to integrate their data points because of this.
  • A-to-Z data warehouse solution that also integrates CDN and high availability. Since data security and automatic backups are also parts of the service, you have nothing else to worry about.
  • It’s simple enough for most business users. If you have experience with SQL, you can use BigQuery.

Despite the advantages—and the many more pros that we cannot list in this article—BigQuery is not without its faults. The first thing you need to keep in mind when using BigQuery is that you have to be mindful of your data warehouse, especially the cost of maintaining one. While BigQuery is very affordable, storing just any data will result in high BigQuery costs in the long run.

The second disadvantage is control over infrastructure management. There are times when you want your data to rest in different clusters, or you need to separate processes across servers. These are not the kind of things you can do on BigQuery because of the simplicity it offers.

Optimizing Data Warehouses

Now that we have identified a couple of disadvantages of using Google BigQuery, it is time to take a closer look at how you can minimize those disadvantages by optimizing your data warehouses. The first thing to keep in mind about optimizing data warehouses is that you want to avoid over-indexing your data. Only use necessary indexes; in fact, you should keep as few indexes as possible, limiting the use of indexes to primary keys and unique constraints only.

You also want to reduce the amount of data as early as possible. This is where preprocessing comes in handy. BigQuery supports preprocessing runtimes like data filtering and sorting, allowing the existing data warehouse to be highly optimized. This is also the part where you minimize noise and separate junk from valuable data.

Lastly, be mindful of how your data is structured. Think about the kind of queries you will run and start reorganizing data to minimize the use of WHERE and functions inside WHERE conditions. You will have a streamlined data process that isn’t just optimized in structure but also highly efficient when queried using Google BigQuery.

GCP BigQuery Best Practices

That brings us to the Google BigQuery best practices. Keeping your queries optimized is how you keep your costs in check. Now that you have an efficient data warehouse (or warehouses) to work with, you can further optimize your BigQuery instance with a few simple steps.

  • Always scan the data that you need and nothing more. Remember that BigQuery calculates scanned data—and not retrieved data—when running queries and calculating cost of use.
  • Use conditions sparingly. When you have an optimized set of tables, you don’t have to use WHERE statement as frequently.
  • Always start with the larger table when designing your queries. This will also optimize your query for performance.
  • Avoid SELECT * whenever possible. When you run SELECT * as part of your queries, you are essentially telling BigQuery to read through all your data. LIMIT will not help you either.
  • Instead, be specific with your queries. As mentioned before, pre-planning your queries allow you to leverage BigQuery to the fullest.
  • Validate queries before running them.
  • Sample data using preview rather than by running queries. The Preview tab is a handy tool to use because it does not cost anything.
  • Use tools like the pricing calculator and the number of bytes billed to ensure that your BigQuery costs are always in check.

And that’s it! With careful planning and a good understanding of the tools you have in hand, you can use BigQuery to benefit from data and resulting business intelligence with more success.

Caylent provides a critical DevOps-as-a-Service function to high growth companies looking for expert support with Kubernetes, cloud security, cloud infrastructure, and CI/CD pipelines. Our managed and consulting services are a more cost-effective option than hiring in-house, and we scale as your team and company grow. Check out some of the use cases, learn how we work with clients, and read more about our DevOps-as-a-Service offering.