As a beginner starting out in Data Analytics, I would like to know if they are similar (or different versions of the same thing), or if I have them confused for two entirely different concepts.

CodePudding user response：

Similarities

The similar thing between the 2 is that we can use SQL to query data stored in both MySQL and BigQuery

Differences

We can say that the 2 technologies have completely different use cases. So their philosophy, design, and internal architecture are different.

You can use MySQL to store data for a transactional system or OLTP. For example, if you have an ecommerce website then you can use a MySQL database to store data about users, orders, payments... You could have a lot of transactions/seconds but a transaction usually involves 1 or some lines in your database. MySQL and other relational database engines are good for that. They use some form of normalization to make write operation efficient and keep data consistent.

Now imagine you need to analyze the data of your Ecommerce website over the last 5 years. Your query now will involve all your entries (or rows), but usually for some columns only. And you don't have the same number of queries/second as in the previous situation. You can see the 2 conditions are different. And in this situation, MySQL isn't an optimal choice anymore but an OLAP system. BigQuery is an example of OLAP. With BigQuery, you store data for analysis, not for operational purposes.

Now you see that 2 technologies serve different purposes, you can understand the difference in their design and architecture. For example, with BigQuery, you're encouraged to denormalize data to avoid expensive JOIN operators. Internally, BigQuery stores data by columns and not by rows like MySQL. These decisions share a common goal, make analytic queries run efficiently.

You can research further about OLTP vs OLAP :).

CodePudding user response：

MySQL is a free RDBMS that runs everywhere, extremely popular, general purpose, is really well supported is extremely flexible.

BigQuery is a proprietary google-owned, Cassandra-like, expensive database that uses SQL but is more limited in features, but can be easier to scale for certain types of problems and is deeper embedded into the google ecosystem.

You should always default to MySQL or Postgres unless you have a specific reason to use something like BigQuery. If you don't know which one you should use, you should use MySQL or Postgres.