Preamble
As was already hinted in the comments, the new functionality is a much higher level. It is basically a compiler from entity query language to SQL, specialized to a given SQL dialect of the underlying database engine.
You can view the new functionality more or less as ORM for WL. It brings similar benefits as e.g. Django ORM brings to python web development. However, in our case, we were able to keep the full set of underlying SQL primitives exposed in some way, so if one wants, one can code almost exactly the thing one would obtain by writing raw SQL. In other words, our system contains both high-level and lower-level primitives.
Distinctive features of the new relational database functionality
Here are some key features that in our view as developers, make this valuable.
- This can be used by people who know WL but don't know SQL and don't have the desire or time to learn it. This is somewhat similar to the role
Compile plays for WL / C language pair.
- Query representation is fully symbolic, which allows one to programmatically compose them, and very easily build custom query builders for their own problem / domain, automating construction of the common query parts
- The queries are strongly typed, so a wide range of errors and mistakes will be caught by the compiler at query-building time (rather than at the database level at query run-time), and meaningful diagnostic messages are issued to hint at the cause of the error.
- In cases when a given database backend has semantics for certain operations that is different from WL semantics for the same operations, the compiler makes sure that the WL semantics is preserved on a given db backend, by generating appropriate SQL code.
- The type of composition and nesting for entity queries is functional composition typical for WL, which is very different from the SQL expression composition. One can keep thinking functionally when constructing the queries, whereas SQL requires a different way of thinking that may be unfamiliar to many (related to my first point).
- It is possible to do meaningful work with individual entities. This is useful on a number of levels, from better understanding of the data to quick prototyping and also debugging of complex queries.
- Transparent treatment of subqueries, including correlated subqueries - which get translated into nested
EntityFunction constructs.
- There are certain primitives such as relations, which are very powerful and allow concise queries built in a natural way, which would save the time and mental overhead even for people well-familiar with SQL.
Some examples
Let's consider a few examples to back the points I just made. Some of the examples here are borrowed from the tutorial, which will be very soon added to the official documentation and has been also written by the author of this post.
Initialization
Titanic dataset:
titanicDB = RelationalDatabase[URLDownload[CloudObject[
"https://www.wolframcloud.com/objects/documentation/titanic.sqlite"
]]];
Quiet @ EntityRegister @ EntityStore @ titanicDB
(* {"titanic"} *)
Classic models database:
rdb = RelationalDatabase[FindFile["ExampleData/ecommerce-database.sqlite"]];
EntityRegister[EntityStore[rdb]]
(*
{"productlines", "payments", "offices", "products",
"customers", "employees", "orderdetails", "orders"}
*)
The query syntax and building on familiarity with WL
Consider the titanic dataset. Here is a simple query that selects all surviving passengers in first class with age <= 15:
EntityValue[
FilteredEntityClass[
"titanic",
EntityFunction[
p,
p["class"] == "1st" && p["age"] <= 15 && p["survived"] == 1
]
],
{"class", "age", "sex", "survived"},
"PropertyAssociation"
]
(*
{
<|"class" -> "1st", "age" -> 1, "sex" -> "male", "survived" -> 1|>,
<|"class" -> "1st", "age" -> 11, "sex" -> "male","survived" -> 1|>,
<|"class" -> "1st", "age" -> 14, "sex" -> "female", "survived" -> 1|>,
<|"class" -> "1st", "age" -> 4, "sex" -> "male", "survived" -> 1|>,
<|"class" -> "1st", "age" -> 15, "sex" -> "female", "survived" -> 1|>,
<|"class" -> "1st", "age" -> 13, "sex" -> "male", "survived" -> 1|>,
<|"class" -> "1st", "age" -> 6, "sex" -> "male", "survived" -> 1|>
}
*)
and in this case, the SQL query is pretty simple too:
SELECT class, age, sex, survived
FROM titanic
WHERE class = '1st' AND age <= 15 AND survived = 1
But for those who are not familiar with SQL, the WL query should be very easy to understand, since it follows very closely what one would've done in WL:
KeyTake[
Select[
EntityValue["titanic", {"class", "age", "sex", "survived"}, "PropertyAssociation"],
Function[
p,
p["class"] == "1st" && p["age"] <= 15 && p["survived"] == 1
]
],
{"class", "age", "sex", "survived"}
]
where EntityValue call is only used to provide initial data to WL, loading in the entire table.
Here is a more complex example: split all passengers into age classes 0-20, 20-40, 40-60, 60-80, etc. years old, and compute the fraction of surviving passengers for each passenger class, age group and sex, then sort the results by that fraction in descending order.
Here is the WL query that does it:
EntityValue[
SortedEntityClass[
AggregatedEntityClass[
ExtendedEntityClass[
"titanic",
"ageGroup" -> EntityFunction[t, Ceiling[t["age"] / 20]]
],
"surviverFraction" -> EntityFunction[t, Mean[t["survived"]]],
{"class", "ageGroup", "sex"}
],
"surviverFraction" -> "Descending"
],
{"class", "ageGroup", "sex", "surviverFraction" }
] // Short[#, 5] &
(*
{
{1st,Missing[NotAvailable],female,1.}, {3rd,4,female,1.},
{1st,3,female,0.977273},{1st,2,female,0.970149},{2nd,1,female,0.962963},
<<20>>,{1st,4,male,0.0666667},{3rd,3,male,0.0645161},
{2nd,3,male,0.0357143},{3rd,4,male,0.}
}
*)
and in this case, the SQL is not that simple any more. I will show the generated SQL, but the hand-written one will not be that different:
SELECT "T235".class, "T235"."ageGroup", "T235".sex, "T235"."surviverFraction"
FROM (
SELECT
"T234"."ageGroup" AS "ageGroup",
"T234".class AS class,
"T234".sex AS sex,
avg("T234".survived) AS "surviverFraction"
FROM (
SELECT
CAST(ceil(CAST("titanic_T232".age AS REAL) / CAST(20 AS REAL)) AS INTEGER) AS "ageGroup",
"titanic_T232".class AS class,
"titanic_T232".sex AS sex,
"titanic_T232".survived AS survived
FROM titanic AS "titanic_T232"
) AS "T234"
GROUP BY "T234".class, "T234"."ageGroup", "T234".sex
) AS "T235"
ORDER BY "T235"."surviverFraction" DESC
In this case, I would argue that WL query is way more accessible and clear.
Symbolic nature of the WL queries, and query composition
This and other sections will be using the classic models db.
Consider the query that aggregates over the payments and produces total payments for all customers:
totalPayments = AggregatedEntityClass[
"payments",
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
]
This can be used on its own right, for example to find the five top-paying customers:
EntityValue[
SampledEntityClass[
SortedEntityClass[
totalPayments,
"totalPaid" -> "Descending"
],
5
],
{"customerNumber", "totalPaid"}
]
(*
{{141, 715739.}, {124, 584188.}, {114, 180585.}, {151, 177914.}, {148, 156251.}}
*)
However, it can also be used to, for example, get total payments of all customers served by a specific employee:
EntityValue[
AggregatedEntityClass[
CombinedEntityClass["customers", totalPayments, "customerNumber"],
"customersTotalForEmployee" -> EntityFunction[c, Total[c["totalPaid"]]],
"salesRepEmployeeNumber"
],
{"salesRepEmployeeNumber", "customersTotalForEmployee"}
] // Short
(* {{1165,989907.},{1166,347533.},<<11>>,{1621,457110.},{1702,387477.}} *)
So, one can build the queries from pieces, holding those pieces in variables or generating them with function calls. This is pretty powerful.
Symbolic query transformations
Programmatic access to queries and their symbolic nature can also be useful for certain more advanced scenarios, such as nontrivial query transformations or generation.
As an example, consider operator forms for common query building blocks. At present, for various reasons there is no direct support for those, for the core Entity framework query building blocks. Yet users often prefer this style of query writing, and it can in some cases make queries more readable by reducing the amount of nesting in the query.
Below is a simple implementation of an alternative syntax that would support operator forms.
ClearAll[filter, sample, aggregate, combine, extend, sortby]
$translations = {
filter -> FilteredEntityClass,
sample -> SampledEntityClass,
aggregate -> AggregatedEntityClass,
combine -> CombinedEntityClass,
extend -> ExtendedEntityClass,
sortby -> SortedEntityClass,
ev -> EntityValue
};
entityClassQ[_String?EntityFramework`EntityTypeExistsQ] := True
entityClassQ[ (Alternatives @@ Keys[ $translations])[___]] := True
entityClassQ[_] := False
compileQuery[q_?entityClassQ] := ReplaceAll[q, $translations ]
Scan[
Function[p, p[args___][c_?entityClassQ] := p[c, args]],
Keys[ $translations]
]
The following example query is written in operator style and does the following: selects the five top-paying customers and returns their customer number, customer name and total amount they have paid, sorted in the order of decreasing total amount:
compileQuery[
"payments" // RightComposition[
aggregate[
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
combine["customers", "customerNumber"],
sortby["totalPaid" -> "Descending"],
sample[5],
ev[{
"customerName",
EntityProperty["customers", "customerNumber"],
"totalPaid"
}]
]
]
(*
{
{"Euro+ Shopping Channel", 141, 715739.},
{"Mini Gifts Distributors Ltd.", 124, 584188.},
{"Australian Collectors, Co.", 114, 180585.},
{"Muscle Machine Inc", 151, 177914.},
{"Dragon Souveniers, Ltd.", 148, 156251.}
}
*)
which is arguably much closer to so much desired by many Dataset queries. I will leave it as an exercise to the adventurous to allow the use of Function (including slot-based ones) in such queries in place of EntityFunction, as well as to extend this code to support subqueries.
Note how easy it was to create your own DSL / syntactic sugar on top of existing functionality. This wouldn't have been so had the framework not exposed purely symbolic queries. It will be just as easy to build your own custom query builders, specialized to your domain.
Subqueries
Subqueries normally are parts of the larger query, which in the WL context can be expressed using EntityValue. On the SQL side, most of the time this corresponds to the inner SELECT statement with a single field, typically returning a scalar (either because there is a single row or because aggregation is being performed), but sometimes also returning a column (to be used in the IN clause, which would correspond to the use of MemberQ on the Entity framework side).
The following example, selecting all products with the MSRP price within the top 10% of most expensive products, in terms of the price range, represents a simple, uncorrelated subquery:
EntityValue[
FilteredEntityClass[
"products",
EntityFunction[
p,
p["MSRP"] >= 0.9 * EntityValue["products", "MSRP", Max]
]
],
{"productName", "MSRP"}
]
(*
{
{"1952 Alpine Renault 1300", 214.3},
{"2003 Harley-Davidson Eagle Drag Bike", 193.66},
{"1968 Ford Mustang", 194.57},
{"2001 Ferrari Enzo", 207.8}
}
*)
To my mind, this is pretty easy to understand, also for people who are not familiar with SQL.
Correlated subqueries are also supported.
The following more complex version of the previous query extends each product with the number of products that are within a window of $15 from the current product, in terms of MSRP:
EntityValue[
SortedEntityClass[
ExtendedEntityClass[
"products",
"closelyPricedProductsCount" -> EntityFunction[
p,
EntityValue[
FilteredEntityClass[
"products",
EntityFunction[pr, Abs[pr["MSRP"] - p["MSRP"]] <= 15]
],
"productCode",
Length
]
]
],
{"closelyPricedProductsCount" -> "Descending", "MSRP" -> "Descending"}
],
{"productName", "MSRP", "closelyPricedProductsCount"}
] // Short
(* {{1949 Jaguar XK 120,90.87,33},<<108>>,{1952 Alpine Renault 1300,214.3,2}} *)
Where the subquery inside EntityFunction that defines the "closelyPricedProductsCount" extended property is a correlated subquery, since the filtering of products now depends on the price of the current product, which has to be referenced from that filtering/aggregation subquery.
Relations and single entities
Relations provide a high-level way to perform lookups for properties in entity classes/types related to a given one (related database tables), without performing explicit joins. Their internal implementation can utilize different tools to achieve this goal, such as subqueries and/or joins, but these are hidden from the user.
Relations are entity-valued and entity-class-valued entity properties, generated by the Entity framework for related entity types (database tables). In addition to the properties that correspond to existing table columns, new properties are created by the Entity framework for tables that are related to other tables. They constitute the mechanism to provide a higher-level (w.r.t. explicit joins or subqueries) way to use data from multiple related entity types (database tables) in queries.
Consider properties for type "employees":
EntityProperties["employees"]
(*
{
EntityProperty["employees", "employeeNumber"],
EntityProperty["employees", "lastName"],
EntityProperty["employees", "firstName"],
EntityProperty["employees", "extension"],
EntityProperty["employees", "email"],
EntityProperty["employees", "officeCode"],
EntityProperty["employees", "reportsTo"],
EntityProperty["employees", "jobTitle"],
EntityProperty["employees", "employees-reportsTo"],
EntityProperty["employees", "employees-reverse"],
EntityProperty["employees", "offices"],
EntityProperty["employees", "customers"]
}
*)
There are four properties that do not correspond to existing database columns in this example: "offices", "customers", "employees-reportsTo" and "employees-reverse".
One of the simplest ways to understand relations is in the context of single entities.
For a simple example, consider some particular employee:
emp = Part[EntityList["employees"], 10]
(* Entity["employees", 1216] *)
The properties in question correspond respectively to customers served by this employee, office for which this employee works, the manager of this employee and all employees for whom this employee is the manager:
emp[EntityProperty["employees", "customers"]]
emp[EntityProperty["employees", "offices"]]
emp[EntityProperty["employees", "employees-reportsTo"]]
emp[EntityProperty["employees", "employees-reverse"]]
(*
EntityClass["customers", {"salesRepEmployeeNumber" -> 1216}]
Entity["offices", "2"]
Entity["employees", 1143]
EntityClass["employees", {"reportsTo" -> 1216}]
*)
Relations can be entity valued or entity-class valued.
The following query finds an entity class of all coworkers for the given employee:
emp[EntityProperty["employees", "offices"]]["employees"]
(* EntityClass["employees", {"officeCode" -> "2"}] *)
The following is a more interesting query that returns all employees who work in the same office and report to the same manager as the given employee:
EntityList @ FilteredEntityClass[
emp[EntityProperty["employees", "offices"]]["employees"],
EntityFunction[e, e["reportsTo"] == emp["reportsTo"]]
]
(* {Entity["employees", 1188], Entity["employees", 1216]} *)
One can use relations to economically formulate queries. Here is how one can use relations to compute the number of employees for each office:
EntityValue[
"offices",
{
"officeCode",
EntityFunction[o, EntityValue[o["employees"], "employeeNumber", Length]]
}
]
(* {{"1", 6}, {"2", 2}, {"3", 2}, {"4", 5}, {"5", 2}, {"6", 4}, {"7", 2}} *)
Note that relations allow one to use data from different related tables, while staying on a higher level of abstraction w.r.t. explicit joins or subqueries.
For a more involved example, consider the following query that computes, for each office, the maximal number of customers that a single employee in that office deals with.
This is how it can be written using relations:
EntityValue[
ExtendedEntityClass[
"offices",
"maxEmployeeCustomerCount" -> EntityFunction[
o,
EntityValue[
ExtendedEntityClass[
o["employees"],
"customerCount" -> EntityFunction[
e,
EntityValue[e["customers"], "customerNumber", Length]
]
],
"customerCount",
Max
]
]
],
{"officeCode", "maxEmployeeCustomerCount"}
]
(* {{"1", 6}, {"2", 6}, {"3", 8}, {"4", 10}, {"5", 5}, {"6", 5}, {"7", 9}} *)
The logic here is pretty transparent: we start with the offices, and for each one we compute the maximal number of customers served by any employee in that office, just as requested. To do that, we add the new "customerCount" property to all employees in a given office. For the office bound to o, the entity class of all employees in that office is given by the relation o["employees"]. Since it is an entity class, we can use it anywhere where entity classes are allowed, in the query - in particular, in ExtendedEntityClass. For each employee (say, biud to e variable), all customers served by that employee, form an entity class given by the relation e["customers"]. The two nested EntityFunction-s represent correlated subqueries, performing the aggregations.
This may look a bit complex, but this is not an easy query. Those who are familiar with SQL, may try their hand at it, and will likely observe that the resulting SQL will not be as simple to understand as the above WL query.
Current shortcomings
Speed / performance
We did not particularly emphasize the performance in this first version of the project. However, this is something we will definitely work on to improve in the very near future. The current performance differences w.r.t. DatabaseLink will hopefully be removed or may be even turned around soon.
Read-only
Currently read-only workflows are supported. Supporting writing to databases is one of the objectives for the next version.
SQL support
There are a number of constructs we don't yet support. We hope to be able to extend the support in the future. A lot will depend on the user feedback - if there is a strong support / multiple requests from the users to add extended support for certain SQL features, we are more likely to get the development time allocated for that.
Alternative query syntaxes
There have been requests to support Dataset syntax, which might make it on our roadmap. One of the design issues here is that Dataset allows a much wider set of operations and generally repersents a hierarchical rather than tabular structure. Which means that relational database - backed Dataset would have to fail on a wide variety of operations not available for relational databases. A better option would've been to design a special tabular type of Dataset with restricted query language, but this will take some work to keep it consistent with the main Dataset functionality.
Summary
I tried to summarize the distinctive features of the new relational database support functionality, which (in our view) make it powerful and attractive to use, particularly for the users who are well familiar with WL, but not so much with SQL.
A lot in the future directions of development of this functionality may depend on how actively the users will try to use it and provide their feedback.
Entityfunctionality at the last WTC - several registered their concerns. I've also raised some issues with WRI. Development seems to be driven more by needs and trades off WL's orthogonality to achieve it. For example, why is there anSortedEntityClasswhen sorting is just a state? There's no[[SortedList]]or[[SortedAssociation]]. Doesn't seem like a clean design, though I've not used it. Seems to me if one wanted all this type clutter one could use Python. $\endgroup$