Comparing Graql to SQL — Part 2/2
In this article, see part two of a comparison of Graql and SQL and learn how to read/write data and how we should model at a higher-level in Graql.
Join the DZone community and get the full member experience.Join For Free
This is part two of Comparing Graql to SQL. In the first part, we looked at the origin of the relational model and how to go about modeling in both Graql and SQL. In this part, we look at how to read/write data, and how we should model at a higher-level in Graql leveraging the Hypergraph and Automated Reasoning. If you haven’t read part 1, follow this link.
Let’s look at how we write and read data using relational operators. First, using the Northwind schema, let’s insert the following data: a new
product with name
Chocolate, product id
12, quantity per unit
421 and category name
In SQL, we do two queries. First, we fetch the ID of
Confections and then we insert the new row.
In Graql we do something different. We first match for the
Confections category, assign the result to the variable
$c, and then insert the new data.
The basic operational construct in SQL is the
SELECT - WHERE - FROM expression, which is used to derive new tables from existing ones.
The equivalent expression in Graql is the
Let’s look at some of the most commonly used operators in SQL and how these look in Graql. In doing so, we’re going to look at how to think about a query conceptually, instead of looking at the actual code. In this way, the difference between a Graql and a SQL query is that in Graql we think of queries at a conceptual level; the same way we semantically think about a question, rather than imposing a tabular structure over it.
In the figures below, on the left side, the tabular representation of the query in SQL is shown, and on the right the conceptual representation of the query in Graql (squares are entities, diamonds are relations and circles are attributes).
This SQL operator returns a table that contains all the rows that remain after specific columns have been removed. In Graql, we ask for an entity with specific attribute values.
An example is:
Return all product IDs and their unit prices.
This SQL operator gives us a table with rows from a specified table to a specific condition. In Graql, we ask for an entity and its attributes filtered by an attribute with specific values.
Return product IDs and product names, for products with a unit price higher than 12.5.
This SQL operator returns the rows of the tables that appear in either or both of the two specified tables. In Graql, we ask for entities and their attributes that are connected to one or two other entities through a relation.
Get all the different cities in which suppliers and customers are located.
This SQL operator returns a table with all rows that appear in both of two specified tables. In Graql, we ask for an entity that is connected to two specific entities through a relation.
Get all the cities in which suppliers and customers are simultaneously located.
The most famous SQL operator, a join returns a table containing all possible rows that are a combination of two rows, one from each of two specified tables, such that two rows contributing to any given result row have common values for the common attributes of the two tables. In Graql, we ask for the entities and their attributes that are connected through a specific relation. This means that we don’t need any join tables at all, not for 1–1 relations, 1-many relations, or many-many relations. The concept is no longer needed in Grakn.
Get all the different cities in which suppliers and customers are located.
Modeling at a Higher-Level in Graql: Hypergraph and Automated Reasoning
Compared to SQL, Graql allows us to model at a higher level of abstraction. This means that when we think about queries, we shouldn’t actually think in terms of relational operators, as we’ve just done in the previous section. Instead, we should rethink our model and leverage Graql’s expressivity. So, let’s revisit the Northwind dataset and think about how we can model it differently. In particular, let’s look at how we make use of Grakn’s hypergraph and reasoning capabilities.
Modeling in Graql
If we take the
customers tables from the Northwind dataset, we can model this in Graql by creating the following entities:
Product: We map this entity directly to the
productstable (using the singular term instead).
Order: This maps directly to the
Employee: This also maps directly to the
Company: As both the
customerstable refer to companies, we decide to model them as one entity type. We can define a company as a supplier or a customer with roles (see below).
We then create the following relations and corresponding roles:
Sale: We can model this relation as ternary (learn more about hypergraphs), as a sale occurs between an
customerrole) and an
Employee(playing the role of
Stocking: This relation relates to a
Product(playing the role of
stock) which is being stocked by another
Company, which plays the role of
Containing: We define this relation between the entity
Order(playing the role of
containing-product), because an order can contain multiple products. We also add two attributes to this relation:
This is what we’ve just modeled:
The model of the Northwind dataset in Graql.
Let’s see how this looks in Graql:
Writing a traversal query in SQL means we leverage the
JOIN operator. Below is a comparison between SQL and Graql for the following question:
Return all employee IDs who sold to a customer based in London, and has customer demographic "x"
Unlike in SQL, in Graql we have the capability to increase the expressivity of our model by creating a type hierarchy. For example, we can extend the Northwind dataset by adding non-profits, banks and pharmaceutical companies. Conceptually, this looks as follows:
And in Graql we define it as follows:
With Graql, we can also create rules (learn more here) to abstract and modularise our business logic. SQL does not support rules. For example, if we know that location
x is contained in
y, which in turn is contained in
z, we can create a rule that recursively infers that
x is also contained in
z. Writing this in Graql looks like this:
If we look at our Northwind dataset, we can see a transitivity exists between the
Region tables which means we can leverage the rule above in Graql. To illustrate this point, let’s look at this question:
Return all the employee IDs who are in a region with region-description "x".
In SQL, this is written as follows:
Having defined the
transitive-location rule in Graql, we can now directly relate the employee to the region (abstracting away the territory to region connection avoiding us having to make multiple joins):
If we also incorporate the organization type-hierarchy shown earlier, we can abstract away even more logic to Grakn. Let’s use this question as an example:
Return the IDs for customers that are companies and charities, that employees have sold to, and who are in a region with region-description "x".
In SQL, we write this query like this:
In Graql, we simply write:
In conclusion, we’ve seen how:
- Graql provides a higher-level abstraction for working with data. Graql makes it easier to model and query for complex data.
- Graql lets us create conceptual models giving us the physical independence of data. By implementing a concept level schema, Graql abstracts away the logical model. This means we no longer need to normalize our data.
- Grakn’s reasoning engine simplifies our queries. By using an automated reasoner, Grakn pushes down lower-level operations and enables us to work at a higher level of abstraction.
Grakn’s reasoning engine allows us to abstract away logic that otherwise happens in either our query or application layer. Pushing this logic down into Grakn allows us to write simpler queries at a higher-level of expressivity.
There is much more to Graql than what we’ve tried to show here. Hopefully this comparison has at least given the high-level similarities and differences between both languages.
Opinions expressed by DZone contributors are their own.