What's the difference between BigQuery and Bigtable?

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.

Solution 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.

Solution 2

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://stackoverflow.com/a/34845073/6785908. 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)

Comments

[dyeray]

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

[Elliott Brossard]

This past SO thread may help too: stackoverflow.com/questions/34437572/…

[The user with no hat]

@dyeray 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?

[Solomon Duskis]

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.

[Le Khiem]

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

[Mikhail Berlyant]

in second paragraph - you meant Use BigTable :o)

[Michael Manoochehri]

@MikhailBerlyant thanks, edited

[Igor Dvorzhak]

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.

[Maksood]

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.

[gstackoverflow]

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

[gstackoverflow]

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

[gstackoverflow]

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

[gstackoverflow]

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

[gstackoverflow]

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

[ljuk]

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?

[juferafo]

@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