Relational Foundations for Functorial Data Migration

Relational Foundations for Functorial

Data Migration

David Spivak, Ryan Wisnesky Department of Mathematics Massachusetts Institute of Technology

{dspivak, wisnesky}@math.mit.edu

DBPL October 27, 2015

Introduction

§ _{In this talk I will describe an equivalence between a fragment of the}

relational data model (SPCU queries) and a fragment of the (extended) functorial data model (FQL queries):

SP CU –F QL

§ _{The functorial data model (my name) originated with Rosebrugh et al.}

in the late 1990s.

§ _{Schemas are categories, instances are set-valued functors.} § _{Spivak extends it to solve information integration problems.}

§ _{Sponsored by:}

§ _{ONR grant N000141310260}

Category Theory

§ _{Apresentationof a category is a reflexive,directed,labelled,} multi-graphand a set ofpath equations:

N ‚ g ( ( h 6 6 f M ‚ N.f.f.g = N.f.h

§ _{Aset-valued functorassigns a set to each node and a function to}

each edge, such that the equations holds.

N “N M “ tbillu fpxq “x`1 gpxq “hpxq “bill @xPN § _{Category theory was instrumental in the development of two}

extensions to the relational model, both of which inform work on language-integrated query (LINQ):

§ The nested relational model generalizes sets to nested collections

and is inspired bymonads.

§ _{Algebraic datatypesimplement nested collections using recursion and}

The Functorial Data Model

Emp ‚ works // manager first last Dept ‚ secretary o o name z z Dom ‚

Emp.manager.works“Emp.works Dept.secretary.works“Dept

Emp

ID mgr works first last

101 103 q10 Al Akin 102 102 x02 Bob Bo 103 103 q10 Carl Cork Dept ID sec name q10 102 CS x02 101 Math Dom ID Al Akin Bob Bo Carl Cork CS Math

Convention

§ _{Omit Dom table, and draw edges‚ Ñf ‚Dom as ‚ ´ ˝f :}

Emp ‚ works // manager first last Dept ‚ secretary o o name z z Dom ‚ “ Emp ‚ works // manager Dept ‚ secretary o o first ˝ last˝ name˝

The Functorial Data Model (abbreviated)

Emp ‚ works // manager Dept ‚ secretary o o first ˝ last˝ name˝

Emp.manager.works“Emp.works Dept.secretary.works“Dept

Emp

ID mgr works first last

101 103 q10 Al Akin 102 102 x02 Bob Bo 103 103 q10 Carl Cork Dept ID sec name q10 102 CS x02 101 Math

Functorial Data Migration

§ A functorF:S_ÑT is a constraint-respecting mapping:

nodespSq ÑnodespTq edgespSq ÑpathspTq and it induces three adjoint data migration functors:

§ ∆_F: T-inst _ÑS-inst (like project) S F //

∆FpIq:“I˝F

6

6

T I //Set

§ _{ΠF:S-inst ÑT-inst (like join)}

∆F %ΠF

§ _{ΣF:S-instÑT-inst (like outer disjoint union then quotient)}

∆

(Project)

Name ˝ Salary ˝ N1 ‚ N2‚ Age ˝ F ÝÝÝÑ Name ˝ Salary ˝ N ‚ Age ˝ N1 ID Name Salary 1 Alice $100 2 Bob $250 3 Sue $300 N2 ID Age 4 20 5 20 6 30 ∆F ÐÝÝ N

ID Name Salary Age

a Alice $100 20

b Bob $250 20

Π

(Join)

Name ˝ Salary ˝ N1 ‚ N2‚ Age ˝ F ÝÝÝÑ Name ˝ Salary ˝ N ‚ Age ˝ N1 ID Name Salary 1 Alice $100 2 Bob $250 3 Sue $300 N2 ID Age 4 20 5 20 6 30 ΠF ÝÝÑ N

ID Name Salary Age

a Alice $100 20 b Alice $100 20 c Alice $100 30 d Bob $250 20 e Bob $250 20 f Bob $250 30 g Sue $300 20 h Sue $300 20 i Sue $300 30

Σ

(Union)

Name ˝ Salary ˝ N1 ‚ N2‚ Age ˝ F ÝÝÝÑ Name ˝ Salary ˝ N ‚ Age ˝ N1 ID Name Salary 1 Alice $100 2 Bob $250 3 Sue $300 N2 ID Age 4 20 5 20 6 30 ΣF ÝÝÑ N

ID Name Salary Age

a Alice $100 null1 b Bob $250 null2 c Sue $300 null3 d null4 null5 20 e null6 null7 20 f null8 null9 30

Foreign keys

Name ˝ Salary ˝ N1 ‚ f //N2‚ Age ˝ F ÝÝÝÑ Name ˝ Salary ˝ N ‚ Age ˝ N1 ID Name Salary f 1 Alice $100 4 2 Bob $250 5 3 Sue $300 6 N2 ID Age 4 20 5 20 6 30 ∆F ÐÝÝ ΠF,ΣF ÝÝÝÝÝÑ N

ID Name Salary Age

a Alice $100 20

b Bob $250 20

Evaluation of the functorial data model

§ _Positives:

§ _{The category of categories is bi-cartesian closed (model of the STLC).} § _{For each categoryC, the categoryC-inst is a topos (model of HOL).} § _{Data integrity constraints (path equations) are built-in to schemas.} § _{Data migration functors transform entire instances.}

§ _{The FDM is expressive enough for many information integration tasks.} § _{Easy to pivot.}

§ _Negatives:

§ _{Data integrity constraints (in schemas) are limited to path equalities.} § _{Data migrations lack analog of set-difference.}

§ _{No aggregation.}

§ _{Data migration functors are hard to program directly.}

§ _{Instance isomorphism is too coarse for many integration tasks.}

§ _{Many problems about finitely-presented categories are semi-computable:} § Path equivalence

The Attribute Problem

N

ID Name Age Salary

1 Alice 20 $100

2 Bob 20 $250

3 Sue 30 $300

–(good) N

ID Name Age Salary

4 Alice 20 $100

5 Bob 20 $250 6 Sue 30 $300

–(bad) N

ID Name Age Salary

1 Amy 20 $100

2 Bill 20 $250

Solving the Attribute Problem

§ _{Mark certain edges to leaf nodes as “attributes”.}

§ _{In this extension, a schema is a category C, a discrete categoryC0, and} a functor C0ÑC. Instances and migrations also generalize.

§ _{Schemas become special ER (entity-relationship) diagrams.} § _{The FDM takesC0to be empty.}

§ _{The example schema below, which was an abbreviation in the FDM, is a} bona-fide schema in this extension: attributes are first, last, and name.

Emp ‚ works // manager _Dept ‚ secretary o o first ˝ last˝ name˝

Solved Attribute Problem

N

ID Name Age Salary

1 Alice 20 $100

2 Bob 20 $250

3 Sue 30 $300

–(good) N

ID Name Age Salary

4 Alice 20 $100

5 Bob 20 $250 6 Sue 30 $300

fl(good) N

ID Name Age Salary

1 Amy 20 $100

2 Bill 20 $250

Functorial Data Migration as SPCU

§ Theorem: migrations of the form

ΣF ˝ΠG˝∆H

§ _{F is a discrete op-fibration (ensures union compatibility).} § _{Gis a surjection on attributes (ensures domain independence).} § _{all categories are finite (ensures computability).}

§ _{can be implemented using SPCU (select, project, cartesian product,}

union) and keygen, under set semantics.

∆

using SPCU

GivenF :SÑT andI PT–Inst, define ∆FpIq PS–Inst as: § _{for each nodeN in S, the table∆FpNqis IpFpNqq.} § for each attributeA in S, the table∆_FpAq isIpFpAqq. § _{for each edgee:XÑY inS mapping to a path}

Fpeq:FpXq ÑFpYqin T, compose IpFpeqqto obtain ∆Fpeq.

S F //

∆FpIq:“I˝F

6

6

Σ

using SPCU

Gven F :S ÑT a discrete op-fibration, a S-instanceI, we define ΣFpIq PT–Inst as

§ _{for each nodeN in T, the tableΣFpNq is the union of the node}

tables in I thatF maps to N.

§ _{for each attributeA in T, the tableΣFpAq is the union of the}

attribute tables inI that F maps to A.

§ _{Lete:X ÑY be an edge in T. We know that for eachcPF}´1_pXq

there is at least one pathpc inS such that Fppcq –e. Composepcto

a single binary table, and defineΣFpeqto be the union over all such c.

Discrete Op-Fibrations / Union Compatibility

b1 ‚ oo g1 a‚1 h1 //c‚1 b2 ‚ oo g2 a‚2 h2 //c‚2 a3 ‚ g3 h h h3 ( ( c3 ‚ c4 ‚ ÓF B ‚ oo G A‚ H //C‚

Π

using SPCU

GivenF :SÑT with S finite and aS-instance I, we define ΠFpIq PT–Instas:

§ _{too difficult to describe in a presentation.} § Intuitively,Πis a “join all”

SPCU as Functorial Data Migration

§ Theorem : SPCU (bags) can be implemented using∆,Σ,Π. § Theorem : SPCU (sets) can be implemented using∆,Σ,Π, dedup,

wherededupT :T–InstÑT–Instequates IDs which cannot be

distinguished along any attribute path.

§ _{We must encode relational schemas, for example,Rpc1, . . . , cnq and}

R1_pc1 1, . . . , c1n1qbecomes: R ‚ c1 + + ¨¨¨ cn R1 ‚ c1 n1 ¨¨¨ c1 1 s s D ‚ A ˝

Project using

∆

We expressπi1,...,ikR as∆F: πR ‚ i1 ¨¨¨ ik D ‚ F ÝÝÝÑ R ‚ c1 ¨¨¨ cn D ‚

Select using

∆

,

Π

We expressσa“bR as∆F ˝ΠF: R ‚ a * * c1 ¨¨¨ cn b t t D ‚ F ÝÝÝÑ σR ‚ x & & c1 ¨¨¨ cn D ‚ HereFpaq “Fpbq “x and Fpciq “ci for1ďiďn.

Product using

Π

We expressRˆR1 _{as Π} F: R ‚ c1 + + ¨¨¨ cn R1 ‚ c1 n1 ¨¨¨ c1 1 s s D ‚ F ÝÝÝÑ RˆR1 ‚ c1 * * ¨¨¨ cn ¨¨¨ c1 1 ¨¨¨ c1 n1 t t D ‚

Union using

Σ

We expressR`R1 <sub>as Σ</sub> F: R ‚ c1 + + ¨¨¨ cn R1 ‚ c1 n ¨¨¨ c1 1 s s D ‚ F ÝÝÝÑ R`R1 ‚ c1 ¨¨¨ cn D ‚

FQL - A Functorial Query Language

§ _{The open-source, graphical FQL IDE available at}

categoricaldata.net/fql.html implements functorial data migration (with attributes) in software. FQL translates migrations of the form

ΣF ˝ΠG˝∆H

into SQL and vice versa.

FQL evaluation

§ _Positives:

§ _Attributes.

§ _{Running on SQL enables interoperability and execution speed.} § _{Better Σsemantics than TGD-only systems (e.g., Clio).}

§ _Negatives:

§ _{No selection by constants.} § _{Relies on fresh ID generation.}

§ _{Cannot change type of data during migration.} § _{Attributes not nullable.}

Conclusion

§ _{I described the functorial data model and data migration functors,} § _{how to extend the functorial data model to have attributes,} § an equivalence

SP CU –F QL

whereF QLis a fragment of the data migration functors