A key concept every data engineer should know
One of the things to weigh up carefully when working on a project is the choice of database. Where will the information be stored? How is it structured? Will there be important transactions? How big will it be?
These, and many others, are questions we need to ask ourselves. Choosing the "wrong" database can cost a project its success, which is why at ESSE I we have specialists who take care of every single aspect of a project. For databases in particular, we asked our data engineers, who give us an overview of the choices they make every day.
SQL databases
The acronym SQL stands for Structured Query Language, a standard that helps run queries on relational databases.
For this reason, these databases are often also referred to as SQL databases.
The main advantage of databases over ordinary file storage systems is that they greatly reduce data redundancy, make it easier to share data between users and guarantee the security of data that can be immensely important to an organisation.
Each database contains several tables holding data in the form of rows and columns. Each table is related to a number of other tables within the database.
What is a NoSQL database?
NoSQL databases (the name stands for "not only SQL") arrived on the scene in the late 2000s. They are flexible, scalable, inexpensive and schema-less.
They were born from the need to manage the huge amounts of data generated in today's world – data produced at a high rate and in many different varieties.
Compared with SQL databases, NoSQL databases come in several types, each with its pros and cons:
- document-based
- key-value
- wide-column
- graph-based.
Let's look in depth at some of the main differences between SQL and NoSQL databases.
Differences between SQL and NoSQL databases
1. Design schema
SQL databases are relational databases that store data in several related tables. These tables are relations.
Each relation is organised in rows and columns. Each row is a tuple (i.e. a generic element of a relation with attributes in a relational database) and contains a record, and each column is an attribute for which each record usually holds a value. Tables in the database are related using SQL keys.
The columns in a table hold a certain type of data. If a record contains data of a different type, the database will raise an error.
In addition, a record must contain as many values as there are columns in the table, or must explicitly provide a NULL value. The most popular examples of SQL databases are MySQL, PostgreSQL, Oracle and SQL Server.
As mentioned, there are instead 4 types of NoSQL database. Let's look at them:
Document-based databases
These NoSQL databases store data in JSON-like documents. Each document has a key-value format, which means the data is semi-structured. Even if a value for a key is missing from a document, the database will not raise an error. A popular example is MongoDB.
Key-value databases
These NoSQL databases store data in key-value format. Both keys and values can be anything. Keys are stored in efficient index structures and can locate values quickly and uniquely. NoSQL databases of this type are ideal for applications that need fast data retrieval. Amazon DynamoDB is an example of such a database.
Wide-column databases
This kind of database stores data in records similar to any relational database, but it can store a very large number of dynamic columns.
This means the number of column values per row can vary in such databases. It groups columns logically into column families. Cassandra is a popular example of a NoSQL database of this type.
Graph-based databases
This type of database uses nodes to store data entities such as places, products and so on, and edges to store the relationships between them. There is no limit to the number and type of relationships a node can have.
Neo4j is an example of such a NoSQL database.
2. Data structure
Defining the structure or schema of the database before adding data is a prerequisite for SQL databases.
This means this type of database can store only structured data.
This makes handling real-world data generated at a high rate very rigid. Updating the schema takes a lot of time and effort and requires updating many relations.
NoSQL databases, on the other hand, have no fixed structure and can handle any kind of data: structured, semi-structured or unstructured. This means that even if incoming data has a different number of attributes, the database will handle it without errors. This makes NoSQL databases very popular, because you can easily change the schema without much disruption.
3. Speed
There is no real difference between the two in terms of speed. Both perform equally well in most scenarios. However, some differences can be seen when it comes to handling complex queries and large data sets.
SQL databases require data to be stored in normalised form to avoid redundancy. Although this reduces the amount of storage the database needs and makes records easy to update, it can have some effect when querying the database.
For example, running complex queries such as joins on a database with many tables can be quite laborious, especially when the size of the data becomes significant. NoSQL databases overcome this drawback.
NoSQL databases don't mind if data is duplicated, because storage is not a problem. Data in NoSQL databases is generally stored in a way that is optimised for queries. This means you can store data in the same form in which you would want it back after running a query. This removes the whole problem of joins and makes querying much faster.
For example, SQL databases require two separate tables for employee information and department information, linked by a foreign key, perhaps the department ID.
With NoSQL databases such as MongoDB, however, you can store the complete employee information, including the department information, in the same document, although you can nest some values if you wish.
4. Scaling
SQL databases run on traditional machines, meaning they run on a single server. If you exceed the server's current capacity, you need a more powerful CPU, more RAM, more storage and so on. This is what we call vertical scaling, and it can be quite expensive, especially when dealing with Big Data (in the order of GB, TB, PB and so on).
NoSQL databases, on the other hand, offer what we call horizontal scaling. This means that if you run out of capacity, you can simply add a machine to the cluster (a group of machines working together). These machines are generally much cheaper and are rightly referred to as commodity hardware. This capability of NoSQL databases has another important advantage: data distribution.
NoSQL databases usually run on a number of interconnected machines known as a cluster. Data is distributed among the machines in the cluster, each of which stores part of the data.
Why is this useful?
Data distribution gives us the ability to replicate data and offer fault tolerance. This means part of the data can be replicated and stored on several machines.
If a machine fails, the data on it will be present on another machine in the cluster and can be used without the user noticing the failure, providing fault tolerance. This is obviously not possible with SQL databases, because all the data is stored on the same machine.
5. Guiding principles
One of the main advantages of SQL databases is their ability to handle transactional processing – processes that change the contents of a database. The ACID properties of SQL databases govern:
Atomicity: transactions are carried out completely or not at all.
Consistency: this ensures the database is never left in a half-finished state. If an error occurs, it ensures the changes are rolled back.
Isolation: transactions take place independently. No transaction has access to any other transaction.
Durability: changes made to the database by completed transactions are saved in the database, and updates are not lost.
NoSQL databases, on the other hand, do not fully provide the ACID properties and are instead governed by what we call the CAP theorem:
Consistency: the user should be able to see the same data regardless of the node or machine they connect to in the system or cluster. So if a piece of data has been written to a node, it should be replicated to all its replicas.
Availability: every user request should get a response from the system. Whether the user wants to read or write, they should get a response even if the operation was not successful.
Partition Tolerance: a partition occurs when a node cannot receive messages from another node in the system. It could happen because of a network failure, a server crash or any other reason. Partition tolerance ensures the system can still work even if there is a partition in the system.
However, NoSQL databases have to make a trade-off between Consistency and Availability when a partition occurs. This is because, in a real-world system, partitions are bound to happen because of network failures or other reasons. So when a partition occurs, a NoSQL database must give up either consistency or availability. A distributed NoSQL database is therefore characterised as CP (Consistency / Partition Tolerance) or AP (Availability / Partition Tolerance).
Note: NoSQL databases are not, however, so rigid when it comes to CAP. Most of them now provide options to balance consistency and availability. So the choice is not always black and white.
Now we come to the last point of our discussion, usage – a very important point for anyone trying to choose between an SQL and a NoSQL database.
6. Usage
The ACID properties make SQL databases enormously valuable in fields where transactions are extremely important. For example in banking, where money transactions must be handled correctly, especially when a transfer fails, since a failure can cost a fortune.
Also, if the data will be structured and immutable, there is no reason to use NoSQL databases. You can always make the most of your SQL databases and, of course, your stellar knowledge of SQL!
However, if you need to work with a large volume of data without a set structure, NoSQL databases are the best choice! But NoSQL databases can also have wide-ranging use cases depending on their inherent structure and their preference among the properties of the CAP theorem.
While Elasticsearch stores log data, Cassandra is used by many social networking sites. Either way, at the end of the day all of this helps manage the volume, velocity and variety of Big Data!
