what's the practical difference between google datastore nosql and google bigquery sql?

Question

I want to know how to evaluate one tool over another.

My major concern is as following:

In google datastore, we define 'kind'. Each 'entities' has 'properties'.
Then the datastore backends use those properties to index data for future query. The query itself use almost the same idea in SQL, though different syntax, to filter data and find what we want.
If you index every property, the index metadata would be even bigger than real data.

Google bigquery uses it's dialect of SQL. And it's fully managed so users don't have to worry about the scaling problem.

So my question is, what's the purpose of using nosql datastore, spending so many computing cycles and storage for indexes so that we could just query it like we naturally could in bigquery SQL database?

Please share with me your evaluation process. Share with me what's the missing piece in my understandings. Thanks.

Answer 1

The two services solve different problems.

Datastore is designed to support transactional workloads, such as the backend for a web application. It's optimized for small transactions that read or write a limited number of rows per operation, with strong consistency guarantees.

BigQuery is designed for analytic workloads. It's append-only, and it's optimized for queries that scan/filter/aggregate entire tables of data to get answers out of your data.

So the real question is: what problem are you trying to solve? If you're building a web app and need a backend, use Datastore. If you have piles of data that you want to query to get answers about how your business is performing, use BigQuery.

Finally, if you want to do both, you can use Datastore for your app, and then export to BigQuery for analysis: https://cloud.google.com/bigquery/loading-data-cloud-datastore