The Google Cloud Platform hosts all kinds of tools for data storage and management, but two of the most versatile and popular are Bigtable and BigQuery. While each service is a database, the key difference between the two lies in their names. Bigtable (BT) is literally a “big table” that scales to petabytes if not terabytes for storing and collecting your data. BigQuery (BQ), on the other hand, conducts a “big query” into your massive troves of data.
Each database has other unique attributes that define when and how to use it. These topics, along with use cases, case stories, and costs associated with each product, are covered in the following sections.
Bigtable, Google Cloud’s fully-managed database for hefty analytical and operational workloads, powers major Google products like Google Search, Google Maps, and Gmail. The database supports high read/write per second speed, processes reads/writes at ultra-low latency, and scales to billions of rows and thousands of columns for massive troves of data.
Bigtable is ideal for Cloud data visualization products, such as BigQuery, DataFlow, and DataProc. It integrates well with Big Data tools such as Hadoop, DataFlow, Beam, and Apache HBase.
Bigtable Use Cases
Bigtable is best-used for instances with lots of data, such as the following:
Time series data, e.g., CPU usage over time for multiple servers.
Financial data, e.g., currency exchange rates.
Marketing data, like customers’ purchase histories and preferences.
Internet of things data, such as usage reports from home appliances.
Fraud detection, i.e. detecting fraud in real time on ongoing transactions.
Product recommendation engines to handle thousands of personalized recommendations.
BigQuery is Google Cloud’s serverless fully-managed service that helps you ingest, stream, and analyze massive troves of information in seconds. In contrast to Bigtable, BigQuery is a query engine that helps you import and then analyze your data.
Since BigQuery uses SQL (Structured Query Language), this database is ideal for Amazon Redshift, which uses SQL to analyze structured and semi-structured data across data warehouses, operational databases, and data lakes.
BigQuery Use Cases
BigQuery is commonly used for instances that include:
Real-time fraud detection; BQ ingests and analyzes massive amounts of data in real-time to identify or prevent unauthorized financial activity.
Real-time analytics; BQ is immensely useful for businesses or organizations that need to analyze their latest business data.
Log analysis; BQ reviews, interprets, and understands computer-generated log files.
Complex data pipeline processing; BQ manages and interprets the steps of one or more complex data pipelines generated by source systems or applications.
Similarities Between Bigtable and BigQuery
Each database boasts ultra-low latency on the order of single-digit microseconds, high-performance and speed on the order of 10,000 rows per second, and powerful scalability that enables you to scale (or descale) for additional storage capacity. Both are end-to-end managed and thoroughly secure as they encrypt at-rest and transit data.
Differences Between Bigtable and BigQuery
While Bigtable collates and manages your data, BigQuery collates and analyzes those troves of data.
Bigtable resembles an Online Transaction Processing (OLTP) tool, where you can execute a number of transactions occurring concurrently—such as online banking, shopping, order entries, or text messages. BigQuery, in contrast, is ideal for OLAP (Online Analytical Processing) — for creating analytical business reports or dashboards. In short, for anything related to business analysis, such as for scrolling through last year’s logs to see how to improve business.
While Bigtable is NoSQL — mandatory for its flexible database — BigQuery uses SQL, making it ideal for performing complex queries on heavy-duty transactions. Don’t expect BigQuery to be used as a regular relational database or for CRUD (to Create, Read, Update, and Delete data). It’s immutable, which means its information is encoded so that it can’t be edited or removed.
Companies use Bigtable for structuring and managing their massive troves of data,while they use BigQuery for mining insight from these troves of data. Below are a few examples of how businesses have used each in practice:
Digital fraud detection and payment solution company Ravelin uses Bigtable to store and query 1.2 billion transactions of more than 230 million active users.
AdTech provider OpenX uses Bigtable to serve more than 30,000 brands, more than 1,200 websites, and more than 2,000 premium mobile apps, and processes more than 150 billion ad requests per day.
Dow Jones DNA uses Bigtable for fast, robust storage of key events that the company has documented in over 30 years of news content.
UPS uses BigQuery to achieve precise package volume forecasting for the company.
Major League Baseball is expanding its fan base with highly-personalized immersive experiences. They analyze their marketing using BigQuery.
The Home Depot uses BigQuery to manage customer service and keep 50,000 items routinely stocked across 2,000 stores.
When using BigQuery, you pay for storage (based on how much data you store). There are two storage rates: active storage ($0.020 per GB), or long-term storage ($0.010 per GB). With both, the first ten GB are free each month. You also pay for processing queries. Query costs are either on-demand (i.e., charged by the amount of data processed per query), or flat-rate.
BigQuery also charges for certain other operations, such as streaming results and the use of its Storage API. Loading and exporting data is free. For details, see BigQuery pricing.
Using Bigtable, you pay for storage and bandwidth. Here’s all you need to know on Bigtable pricing across countries.
If you’re ready to start using or testing either product for a current or upcoming project, you can create a Bigtable instance using Cloud Console’s project selector page, or Cloud’s Bigtable Admin API. BigQuery is accessible via Google Cloud Console, The BigQuery REST API, or an external tool such as a Jupyter notebook or business intelligence platform.