BigQuery vs Cloud SQL for dashboards backend

Question

I'm a bit confused about BQ vs Cloud SQL use cases. I've got few Gbs of row data in BQ and need to build web dashboards on that data (SQL with JOINs over several tables).

Is there a big downside of querying data right from BQ (e.g. latency or concurrency), and should I move data to Cloud SQL(pSQL) and query that?

The data isn't realtime. I expect at most few hundred concurrent requests.

I don't expect high cost on this, thus query speed and reliability is the main goal.

Thanks!

Answer 1

BigQuery is perfectly suited for storing and querying large data sets quickly.

Google Cloud SQL on the other hand, is mainly based on Relational Database Management System (RDBMS) concepts. It provides support for MySQL and PostgreSQL.

Big Query is however best suited for analytics but handling transactional data is possible as well. BigQuery is quite fast, certainly faster than Querying in CloudSQL because BigQuery is a Datawarehouse that has the ability to query absurdly large data sets to return results immediately. When you have to deal with really large data sets, then BigQuery will be the cheaper Database. Cloud SQL either MySQL or PostgreSQL can only handle up to 30,720 GB depending on the machine type for MySQL or depending on whether the instance has dedicated or shared vCPUs for PostgreSQL while BigQuery doesn’t have this kind of storage limitation. Please also refer to other quotas and limitation of BigQuery.

With that being said, the charges can rack up quickly on BigQuery if you don't construct your queries properly since it traverses too much data too frequently.

Cloud SQL and BigQuery are priced differently, please refer to the pricing page of Cloud SQL and BigQuery for more details.

I see this link that compares some of the features of BigQuery with CloudSQL. I think the details shared in the article may be helpful.

Generally, Cloud SQL is a relational database which is more intended for transactional purposes while BigQuery on the other hand is analytics data warehouse which is intended for analytics, data visualization, business intelligence and/or machine learning, etc. So, if your purpose is to store data for transactional purposes then Cloud SQL would be a choice, but, if you’re storing data for analytical purposes then BigQuery is the way to go.

Answer 2

The data isn't realtime. I expect at most few hundred concurrent requests.

It is worth noting the BQ quota limits, in this case the number of concurrent queries and number of API requests. With few hundred concurrent requests you might be hitting the limits.