What's the difference between BigQuery and Bigtable? [closed]

Question

Is there any reason why someone would use Bigtable instead of BigQuery? Both seem to support Read and Write operations with the latter offering also advanced 'Query' operations.

I need to develop an affiliate network (thus I need to track clicks and 'sales') so I'm quite confused by the difference because BigQuery seems to be just Bigtable with a better API.

Furlani

I would say BigTable is more like a low level and less managed Cloud Datastore for people that need better performance. – Sasaki

This past SO thread may help too: #34438072 – Sniggle

@Sasaki yeah I know that but is there any reason for someone to maintain the same data in both BigTable and BigQuery instead to of BigQuery only? – Furlani

BigTable is very good at single row, or small range lookups. BigQuery is very good at the big picture. If you want analytics with efficient drilldown, you use BigQuery for the large scale analytics, and BigTable for analytics over small ranges. – Gestate

System Properties Comparison Google BigQuery vs. Google Cloud Bigtable vs. Google Cloud Datastore. !image (Disclaimer! Copied from db-engines.com) – Ozieozkum

BigQuery is an enterprise data warehouse for large amounts of relational structured data. And Bigtable is a NoSQL wide-column database optimized for heavy reads and writes. cloud.google.com/blog/topics/developers-practitioners/… – Bareilly

Answer 1

The difference is basically this:

BigQuery is a query Engine for datasets that don't change much, or change by appending. It's a great choice when your queries require a "table scan" or the need to look across the entire database. Think sums, averages, counts, groupings. BigQuery is what you use when you have collected a large amount of data, and need to ask questions about it.

BigTable is a database. It is designed to be the foundation for a large, scaleable application. Use BigTable when you are making any kind of app that needs to read and write data, and scale is a potential issue.

Thalassa

Bit of an unfortunate naming convention by Google here. When I typically think of SQL - I think of Tabular data structure whereas when I think of Queries I think of key-value or lookup type of database. In this case, BigTable is No-SQL whereas BigQuery Sql database. – Keary

@Michael Manoochehri cloud.google.com/bigquery/… please read quote below: – Flasher

You can share access to a permanent external table with users (including service accounts) or groups. To query the external table, your users or groups need to be granted (at a minimum): The bigquery.dataViewer role at the dataset level or higher to access the dataset that contains the external table The bigquery.user role at the project level or higher in order to run query jobs The bigtable.reader role in Cloud Bigtable which provides read-only access to metadata and tables – Flasher

Why to access bigtable I need a permission which contains word bigquery ? – Flasher

Answer 2

First and foremost: There is no concept of primary key/index concept in BigQuery. So you can't update a record using its "keys" as it will result in full table scan(sure you can if you have have a bunch of money to throw away). Consider sinking to Bigquery just like an append only tape. If you have a need to analyze the latest state of a record, you will have to resort to other strategies, for example, a scheduled merge query get the latest record for a "key" from a staging table and update the reporting table

READ ON..

This may help a bit in deciding between different datastore solutions that Google cloud offers (Disclaimer! Copied from Google Cloud page)

If your requirement is a live database, BigTable is what you need (Not really an OLTP system though). If it is more of an analytics kind of purpose, then BigQuery is what you need!

Think of OLTP vs OLAP; Or if you are familiar with Cassandra vs Hadoop, BigTable roughly equates to Cassandra, BigQuery roughly equates to Hadoop (Agreed, it's not a fair comparison, but you get the idea)

https://cloud.google.com/images/storage-options/flowchart.svg

Note

Please keep in mind that Bigtable is not a relational database and it does not support SQL queries or JOINs, nor does it support multi-row transactions. Also, it is not a good solution for small amounts of data. If you want an RDBMS OLTP, you might need to look at cloudSQL (mysql/ postgres) or spanner.

Cost Perspective

https://mcmap.net/q/205226/-google-bigtable-vs-bigquery-for-storing-large-number-of-events. Quoting the relevant parts here.

The overall cost boils down to how often you will 'query' the data. If it's a backup and you don't replay events too often, it'll be dirt cheap. However, if you need to replay it daily once, you will start triggering the 5$/TB scanned very easily. We were surprised too how cheap inserts and storage were, but this is ofc because Google expects you to run expensive queries at some point in time on them. You'll have to design around a few things though. E.g. AFAIK streaming inserts have no guarantees of being written to the table and you have to poll frequently on tail of list to see if it was really written. Tailing can be done efficiently with time range table decorator, though (not paying for scanning whole dataset).

If you don't care about order, you can even list a table for free. No need to run a 'query' then.

Edit 1

Cloud spanner is relatively young, but is powerful and promising. At least, google marketing claims that it's features are best of both worlds (Traditional RDBMS and noSQL)

Perishable

You can share access to a permanent external table with users (including service accounts) or groups. To query the external table, your users or groups need to be granted (at a minimum): The bigquery.dataViewer role at the dataset level or higher to access the dataset that contains the external table The bigquery.user role at the project level or higher in order to run query jobs The bigtable.reader role in Cloud Bigtable which provides read-only access to metadata and tables – Flasher

I took the quote above from cloud.google.com/bigquery/…. Why to access bigtable I need a permission which contains word bigquery ? – Flasher

At the top of the graph, the first question is: is your data structured? When the answer is yes, you can find a path to select the BigTable service, but BigTable is intended for Non-Structured data. Why we can arrive at this option even when data is structured? – Handstand

@ltaljuk If my understanding is correct, one could write structured data as "key-value" pairs matching the storage model of Bigtable: cloud.google.com/bigtable/docs/overview#storage-model – Pincushion

Answer 3

BigQuery and Cloud Bigtable are not the same. Bigtable is a Hadoop based NoSQL database whereas BigQuery is a SQL based datawarehouse. They have specific usage scenarios.

In very short and simple terms;

• If you don’t require support for ACID transactions or if your data is not highly structured, consider Cloud Bigtable.
• If you need interactive querying in an online analytical processing (OLAP) system, consider BigQuery.

Dentistry

Bigtable is not a Hadoop-based NoSQL database, it's in fact an opposite - based on Bigtable white paper HBase (Hadoop-based NoSQL) was build. Because of this they share similar (same?) API though. – Hispanicism