Using the Dataset Query Language to Query Relational Databases

Leonid Shifrin, Wolfram Research

(RowDefault[, (Spacer1), , (Spacer1), ])  #WolframTechConf

ABSTRACT

Dataset has become a widely used tool to process and manipulate data in the Wolfram Language, but its scope has been limited to the data stored in kernel’s memory. In this talk, I will discuss the general strategy to extend the scope of Dataset’s query language to query other types of data sources and illustrate general ideas with examples of using Dataset’s query language to query a relational database (SQLite).

Summary and goals

Connect Dataset  to 2D data formats

Back WL Datasets by 2D data (relational, but not only)

Import existing tabular / relational data as nested Dataset

Run Dataset queries against various backends

This project builds on:

Dataset

Databases

Main problems to overcome

Impedance mismatch between hierarchical and tabular data models

More restricted nature of flat / relational data manipulations / SQL

Certain specific semantics of Dataset query language

Potential workflows

Back WL-side Dataset by flat data

Convert Dataset’s nested data to flattened version, and possibly persist

Could persist to external database

Could also use DataContainer as a backend

Run translated Dataset queries against flattened version

For relational databases,  queries will be translated in SQL

For backends in DataContainer, queries will translate into a set of API calls, supported by a specific backend (Apache Arrow, etc.)

Convert result back to nested version

This is for presentation / visualization purposes

Also to execute WL-side part of the query (things like Histogram etc.)

Import existing external data as nested Datasets

Connect to external data source

Relational DB

DataContainer (Apache Arrow, etc.)

Mongo

etc.

Create a nested data representation / Dataset

Use original data / metadata, plus “nesting spec” provided by the user

In most cases, data will not actually be imported, except possibly a small portion for formatting / presentational purposes

Translate and run Dataset queries against external backends

Dataset queries will be compiled into different target query languages / API calls

Get result back in nested format

The underlying data will generally remain flat

Dataset type system - a refresher

Simple example

planetMountains  = Dataset @ \[LeftAssociation]"Earth"->{\[LeftAssociation]"Name"->"Mount Everest","Elevation"->(Quantity[8.848`, "km", kilometers, "Kilometers"])\[RightAssociation],\[LeftAssociation]"Name"->"K2","Elevation"->(Quantity[8.611`, "km", kilometers, "Kilometers"])\[RightAssociation],\[LeftAssociation]"Name"->"Kangchenjunga","Elevation"->(Quantity[8.586`, "km", kilometers, "Kilometers"])\[RightAssociation]},"Mars"->{\[LeftAssociation]"Name"->"Olympus Mons","Elevation"->(Quantity[21.287`, "km", kilometers, "Kilometers"])\[RightAssociation],\[LeftAssociation]"Name"->"Ascraeus Mons","Elevation"->(Quantity[18.209`, "km", kilometers, "Kilometers"])\[RightAssociation],\[LeftAssociation]"Name"->"Arsia Mons","Elevation"->(Quantity[17.779`, "km", kilometers, "Kilometers"])\[RightAssociation],\[LeftAssociation]"Name"->"Pavonis Mons","Elevation"->(Quantity[14.307`, "km", kilometers, "Kilometers"])\[RightAssociation]}\[RightAssociation]

Dataset`GetType @ planetMountains
%//fmt

Atomic types

$AtomTypePattern//fmt

Composite types

There are two dimensions that can help to categorize the main composite types of the Dataset type system. Below is the visualization:

Style[Grid[{{"", "List", "Association"}, {"Homogeneous", "Vector", "Assoc"}, {"Inhomogeneous", "Tuple", "Struct"}}, Dividers->{{False,True,False},{False,True}}],
FontSize->20
]

Vector

Dataset @ Range[10]
%//Dataset`GetType

Assoc

assoc = <|"a" -> 1, "b" -> 2, "c" -> 3|>
%//Dataset
%//Dataset`GetType

Struct

struct = <|"int" -> 1, "real" -> 2.5, "string" -> "abc"|>
%//Dataset
%//Dataset`GetType

Tuple

tuple = {1, 1.5, "abc"}
%//Dataset
%//Dataset`GetType

Special types

AnyType

UnknownType

More complex example

planets = ExampleData[{"Dataset", "Planets"}]
%//Dataset`GetType
%//fmt

Hierarchical to flat data mapping

Example: simple in-memory dataset

Dataset

md = Normal @ planetMountains
planetMountains
%// Dataset`GetType
%//fmt

Schema

mdSchema = TabularDatasetSchema[planetMountains]
%["FormattedExpression"]

Field mappings

Direct mapping

mdSchema["FieldsMapping"]
%//fmt

Inverse mapping

mdSchema["ReverseFieldsMapping"]
%//fmt

Abstract flattener

mdSchema["AbstractFlattener"]
%//fmt

Concrete (compiled) flattener

fl = compileDatasetFlattener @ mdSchema["AbstractFlattener"]

Flattening the data

fldt = fl @ md
%//Dataset

Abstract unflattener

mdSchema["AbstractUnflattener"]
%//fmt

Concrete (compiled) unflattener

unfl = compileDatasetUnflattener @ mdSchema["AbstractUnflattener"]

Unflattening the data

unfl @ fldt

Example: importing relational database as Dataset

Connecting to db

schema = RelationalDatabase @ DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]
es = EntityStore[schema]
Quiet @ EntityRegister[es]

(* Databases`Database`$DBQueryLogger = Echo *)

Sample database schema

DatabasesUtilities`DatabaseSchemaPlot[schema ,ImageSize->{1000,1000}]

Creating RelationalDataset

Note: this will not import any data, essentially just create the Dataset schema

ds = 
        RelationalDataset[
            schema,
            "employees" -> 
                {                    
                    "employeeNumber", 
                    "lastName", 
                    "firstName", 
                    "customers" -> 
                        {                            
                            "customerNumber", 
                            "customerName", 
                            "country", 
                            "creditLimit", 
                            "payments" -> {"checkNumber", "paymentDate", "amount"}, 
                            "orders" -> {"orderNumber", "orderDate", "status"}
                        }
                }
        ]

One can explore the schema. It is very similar to the case of in-memory data, but now contains parts for relational database (tables).

ds["Schema"]

Getting the data into Wolfram Language

withSQL[Normal @ ds // Dataset]

This does extract the data, as above, but also performs an additional unflattening step:

Dataset @ ds

General notes on the compilation and data extraction procedure

The dataset schema plays the role of an intermediate representation for the compilation process

The process of compiling Dataset query here is that of producing a new schema for the new Dataset, which is the result of executing the query. No actual query runs against the database in this process, of course.

In general, one or more levels in a Dataset correspond to one database table

One can view different database tables (or, in general, queries) as different data sources for one or more levels in the Dataset

A typical setup is that for a given db table used in a Dataset, there are

A Struct-type level that corresponds  a single row (database columns for it are fields of that Struct)

A parent (to Struct) Assoc level, which corresponds to all rows, with an assoc key being a Primary Key for that table. In some cases,  Assoc level may be absent.

Assoc level may be absent in some- for example for many to one relationship

There may be yet higher levels connected to the same table / query. In particular, GroupBy would be one thing that generates them

Database tables in the schema

Are represented in the form of symbolic SQL queries, expressed in one of the intermediate symbolic representations internal to Databases project

When a Dataset query is compiled, the original queries standing for db tables, are replaced with queries where relevant operations on those symbolic representations of tables are performed

Operations such as Part do collapse Dataset levels.  If a collapsed level has a different db table / model, than its parent, the child’s model is (symbolically) JOIN-ed to the parent model when the level gets collapsed.

RelationalDataset is a purely symbolic / inert object.

To actually run the resulting SQL query and get back the result, one has to apply Normal or Dataset to it

Normal returns the underlying flat data with internal field / column names

Dataset also unflattens the returned data and returns the hierarchical Dataset that one would expect to see

Data import

At the moment, Dataset @ RelationalDataset[...] imports all the data of that RelationalDataset[...] into the kernel.

In the future, we may have a more lazy form of action here, where only the data needed for Dataset visualization, is imported (typically only some smaller number of the rows coming first)

SQL generation

One of the general principles we follow is to JOIN tables / queries as late as possible (even symbolically)

The actual SQL is only generated when the data is requested by applying Normal[] or Dataset. The SQL generation is performed using the internal symbolic pipeline of Databases project.

Example queries

Picking specific customers

This will compile the query to produce the new RelationalDataset / new schema.

ds[1165, "customers", 124]

withSQL @ Dataset[ ds[1165, "customers", 124]]

Nested query

Here is a nested Dataset query, which selects only employees with short enough “full name”, picks only the “customers” field for each employee, and then within those customers selects only those who come from France:

res2 = ds[
Select[StringLength[#1["firstName"]<>"_"<>#1["lastName"]]<10&], 
    "customers", 
    Select[#1["country"]==="France"&]
]

To actually execute it and get the result, wrap it in Dataset:

withSQL[res2//Dataset]

Some internals

General notes

You can use this section to manually go through the steps which have been automated and collected together by the RelationalDataset wrapper implementation.

While code for compiling to SQL and executing the result is not yet in the system, you should be able to repeat / execute most steps even without it. The final symbolic result for the generated DB query is provided and can be executed (see below), same is true for the concrete unflattener function used to bring the data back to the hierarchical form.

Getting the dataset schema

ds["Schema"]

Symbolic db queries for the original Dataset levels

This shows how symbolic queries look, for “employees” and “customers” database tables. The paths used in Part calls, extract the relevant Dataset levels:

employeeStruct = ds["Schema"][["Inner"]]
First[employeeStruct]["RelationalMetadata", "Model"]
%//fmt

customerStruct = ds["Schema"][["Inner", "customers", "Inner"]]
First[customerStruct]["RelationalMetadata", "Model"]
%//fmt

Compiled WL-side Dataset query

This shows the compiled form of Dataset query, where compilation is done by Dataset internally, and the result is still a Wolfram Language query:

Query[    
    Select[StringLength[#1["firstName"]<>"_"<>#1["lastName"]]<10&], 
    "customers", 
    Select[#1["country"]==="France"&]
] // Normal

Compiling to IR / getting new schema

This shows the internal function to compile that query. The result is a new TabularDatasetSchema[] object:

qres = compileToRDB[
	RightComposition[
		Select[StringLength[#1["firstName"]<>"_"<>#1["lastName"]]<20&],
		Map[
			RightComposition[
				Slice["customers"], 
				Select[#1["country"]==="France"&]
			]
		]
	]
][ds["Schema"]]

Symbolic db query for the result

This shows the symbolic DB query for the interesting part of the resulting Dataset. Since the “customers” level has been collapsed by the “customers” part, the two db tables / models were automatically joined, so the “customers” Struct level now has now a joined table “customers” + “employees” as its new data source. An alternative possibility would’ve been to move the “employees” model up to the top-most Assoc level for “employees”, I might actually make that change in the future.

dbQuery = First[qres[["Inner", "Inner"]]]["RelationalMetadata", "Model"]
%//fmt

For those who are really curious: executing the symbolic query

Since some of the code demonstrated can’t be yet executed standalone, here is a way to grab the above symbolic query and actually execute it on SQLite, using existing Databases functionality.

First, repeat the query, pasting the above result (well, not a full one, since we leave out tables “orders” and “payments” here for simplicity - but the most relevant part is here):

dbQuery =DatabaseWhere[DatabaseJoin[DatabaseQueryMakeAlias[DatabaseSelectFields[DatabaseAnnotate[DatabaseModel["customers"],\[LeftAssociation]"customerNumber_59"->DatabaseFunction[e,e["customerNumber"]],"customerName_60"->DatabaseFunction[e,e["customerName"]],"country_61"->DatabaseFunction[e,e["country"]],"creditLimit_62"->DatabaseFunction[e,e["creditLimit"]],"salesRepEmployeeNumber_63"->DatabaseFunction[e,e["salesRepEmployeeNumber"]]\[RightAssociation]],{"customerNumber_59","customerName_60","country_61","creditLimit_62","salesRepEmployeeNumber_63"}],"_"],DatabaseWhere[DatabaseQueryMakeAlias[DatabaseSelectFields[DatabaseAnnotate[DatabaseModel["employees"],\[LeftAssociation]"employeeNumber_54"->DatabaseFunction[e,e["employeeNumber"]],"lastName_55"->DatabaseFunction[e,e["lastName"]],"firstName_56"->DatabaseFunction[e,e["firstName"]],"reportsTo_57"->DatabaseFunction[e,e["reportsTo"]],"officeCode_58"->DatabaseFunction[e,e["officeCode"]]\[RightAssociation]],{"employeeNumber_54","lastName_55","firstName_56","reportsTo_57","officeCode_58"}],"_"],DatabaseFunction[row,StringLength[row["firstName_56"]<>"_"<>row["lastName_55"]]<20]],{"salesRepEmployeeNumber_63"->"employeeNumber_54"},"Left"],DatabaseFunction[row,row["country_61"]==="France"]]

The following  produces two internal Databases objects:

qbo = DBQueryToQueryBuilderObject[dbQuery,schema]
qo = DBQueryObject[qbo]

We can now run this against the SQLite database (need to be connected, per “Connecting to db” section above)

DBRunQuery[qo]

If one only needs the generated SQL, then:

DBRunQuery[qo, "SQLString"]

Extracting the data

This is an internal function to extract the data, in the form of Dataset of flat data. This is the moment when SQL query gets generated and executed:

extracted = extractDatasetData[schema, qres]

Unflattening the data

We first get rid of the “_.” prefix (which has been introduced for purely technical reasons) for all the column names:

rawData = Normal @ extracted;
rawData = Map[KeyMap[Replace[key_String :> StringDrop[key, 2]]], rawData]

This is the abstract unflattener, extracted from the query:

absUnfl = qres["AbstractUnflattener"]
%//fmt

This is a concrete unflattener for the in-memory data, compiled from the abstract one:

unfl = compileDatasetUnflattener[absUnfl]

Finally, we unflattend the data by simply applying the concrete unflattener to the raw tabular data, and wrap the resulting nested data in Dataset:

Dataset @ unfl @ rawData

Progress with relational backing

Query compilation model

Need better understanding of how main primitives work together

Map, MapAt, ApplyThrough

Composition, RightComposition

Select / KeySelect

Part / Slice

GroupBy

Values / Catenate

SortBy / Reverse / KeySortBy / TakeLargestBy / MaximalBy

Aggregations: Min, Max, Mean, Total, Length

Expressions

Basic arithmetics (Plus, Times, Power, etc.)

Logical operations (Not / And / Or)

String operations

Date operations

Have an intermediate representation suitable for query code generation

Works for nested queries

Supports aggregation

Targeting SQL

Figuring out the mapping between Dataset and SQL data / query models

In general, Dataset represents a collection of relational tables

Query[] generally maps initial collection of tables to final collection of tables

Problems with the single flat tablular representation

Unnecessary / premature JOINs

We may often have to first JOIN, and then make another effort to “unjoin”, where actually no JOIN was necessary in the first place

In general, Query[] may map into several SQL queries rather than a single one

Single query is always possible, but not always good, due to unnecessary JOINs

Interplay between Dataset schema (nesting) and relational schema (primary and foreign keys, connections between tables)

Impedance mistmatch

Dataset GroupBy[] is more lazy than SQL GROUP BY

For Dataset, grouped items remain a collection

For SQL, GROUP BY immediately aggregates

Many-To-Many relations are poorly representable in Dataset

Data (de)normalization and relations to entities

Strategies for SQL code generation

Granularity / LOD notion for fields

Algebra of fields (like in Tableau)

Joining strategy

Thank you!

Init

Dataset

<<Dataset`
<<TypeSystem`
<<GeneralUtilities`
Dataset`$DatasetTargetRowCount = 10

DB

<<Databases`;
<<EntityFramework`
$ContextPath = Join[$ContextPath, {
	"Databases`SQL`",
	"Databases`Common`",
	"Databases`Database`",
	"Databases`Entity`",
	"Databases`Schema`",
	"DatabasesUtilities`"
}]

ClearAll[withSQL]
withSQL = Function[code, Block[{Databases`Database`$DBQueryLogger = Echo}, code], HoldAll]

Data

planets = ExampleData[{"Dataset","Planets"}];

samplePlanetsData = <| 
	"Earth" -> <|
		"Mass" -> Quantity[5.9721986*10^24, "Kilograms"],
		"Radius"->Quantity[6367.4447, "Kilometers"],
		"Moons"-> <|
			"Moon"-><| 
				"Mass"->Quantity[7.3459*10^22, "Kilograms"],
				"Radius"->Quantity[1737.5, "Kilometers"]
			|>
		|>,
		"Mountains" -> {
			<| "Name" -> "Mount Everest", "Elevation" -> Quantity[8.848, "Kilometers"]|>,
			<| "Name" -> "K2", "Elevation" -> Quantity[8.611, "Kilometers"]|>,
			<| "Name" -> "Kangchenjunga", "Elevation" -> Quantity[8.586, "Kilometers"]|>
		}
		
	|>
	,
	"Mars" -> <|
		"Mass"-> Quantity[6.41693*10^23, "Kilograms"],
		"Radius"-> Quantity[3386., "Kilometers"],
		"Moons" -> <|
			"Phobos"-><|
		  		"Mass"-> Quantity[1.072*10^16, "Kilograms"],
		  		"Radius"-> Quantity[11.1, "Kilometers"]
		  	|>,
		  	"Deimos"-><|
		  		"Mass"-> Quantity[1.5*10^15, "Kilograms"],
		  		"Radius"-> Quantity[6.2, "Kilometers"]
		  	|>
		|>
		,
		"Mountains" -> {
			<| "Name" -> "Olympus Mons", "Elevation" -> Quantity[21.287, "Kilometers"]|>,
			<| "Name" -> "Ascraeus Mons", "Elevation" -> Quantity[18.209, "Kilometers"]|>,
			<| "Name" -> "Arsia Mons", "Elevation" -> Quantity[17.779, "Kilometers"]|>,
			<| "Name" -> "Pavonis Mons", "Elevation" -> Quantity[14.307, "Kilometers"]|>
		}
	|>
|>;

planetMountains  = Dataset @ \[LeftAssociation]        
    "Earth" -> 
    {        
        \[LeftAssociation]"Name"->"Mount Everest", "Elevation"->Quantity[8.848`, "Kilometers"]\[RightAssociation], 
        \[LeftAssociation]"Name"->"K2", "Elevation"->Quantity[8.611`, "Kilometers"]\[RightAssociation], 
        \[LeftAssociation]"Name"->"Kangchenjunga", "Elevation"->Quantity[8.586`, "Kilometers"]\[RightAssociation]
    },     
    "Mars" -> 
    {        
        \[LeftAssociation]"Name"->"Olympus Mons", "Elevation"->Quantity[21.287`, "Kilometers"]\[RightAssociation], 
        \[LeftAssociation]"Name"->"Ascraeus Mons", "Elevation"->Quantity[18.209`, "Kilometers"]\[RightAssociation], 
        \[LeftAssociation]"Name"->"Arsia Mons", "Elevation"->Quantity[17.779`, "Kilometers"]\[RightAssociation], 
        \[LeftAssociation]"Name"->"Pavonis Mons", "Elevation"->Quantity[14.307`, "Kilometers"]\[RightAssociation]
    }
\[RightAssociation];