Data Integration Zachary G Ives University of Pennsylvania

Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems LSD Slides courtesy An. Hai Doan

A Problem § We’ve seen that even with normalization and the same needs, different people will arrive at different schemas § In fact, most people also have different needs! § Often people build databases in isolation, then want to share their data § § Different systems within an enterprise Different information brokers on the Web Scientific collaborators Researchers who want to publish their data for others to use § This is the goal of data integration : tie together different sources, controlled by many people, under a common schema 2

Building a Data Integration System Create a middleware “mediator” or “data integration system” over the sources § Can be warehoused (a data warehouse) or virtual § Presents a uniform query interface and schema § Abstracts away multitude of sources; consults them for relevant data Unifies different source data formats (and possibly schemas) Sources are generally autonomous , not designed to be integrated § Sources may be local DBs or remote web sources/services § Sources may require certain input to return output (e. g. , web forms): “binding patterns ” describe these 3

Typical Data Integration Components Query Results Data Integration System / Mediator Mediated Schema Source Catalog Mappings in Catalog Wrapper Source Relations 4

Typical Data Integration Architecture Query Reformulator Query over sources Query Processor Queries + bindings Wrapper Source Descrs. Source Catalog Results Data in mediated format Wrapper 5

Challenges of Mapping Schemas In a perfect world, it would be easy to match up items from one schema with another § Every table would have a similar table in the other schema § Every attribute would have an identical attribute in the other schema § Every value would clearly map to a value in the other schema Real world: as with human languages, things don’t map clearly! § § May have different numbers of tables – different decompositions Metadata in one relation may be data in another Values may not exactly correspond It may be unclear whether a value is the same 6

Different Aspects to Mapping Schema matching / ontology alignment How do we find correspondences between attributes? Entity matching / deduplication / record linking / etc. How do we know when two records refer to the same thing? Mapping definition § How do we specify the constraints or transformations that let us reason about when to create an entry in one schema, given an entry in another schema? Let’s see one influential approach to schema matching… 7

Standard Schema Matcher Architecture (Established by LSD System) Suppose user wants to integrate 100 data sources 1. User: § § manually creates mappings for a few sources, say 3 shows schema matcher these mappings 2. Schema matcher learns from the mappings § § “Multi-strategy” learning incorporates many types of info in a general way Knowledge of constraints further helps 3. Matcher proposes mappings for remaining 97 sources 8

Example address location Mediated schema price agent-phone listed-price phone description comments Schema of realestate. com location listed-price phone comments realestate. com Miami, FL $250, 000 (305) 729 0831 Fantastic house Boston, MA $110, 000 (617) 253 1429 Great location. . . homes. com price contact-phone extra-info $550, 000 (278) 345 7215 Beautiful yard $320, 000 (617) 335 2315 Great beach. . Learned hypotheses If “phone” occurs in the name => agent-phone If “fantastic” & “great” occur frequently in data values => description 9

Learning from Multiple Sources Use a set of base matchers § Each exploits well certain types of information: Name learner looks at words in the attribute names Naïve Bayes learner looks at patterns in the data values Etc. Match schema elements of a new source § Apply the base learners Each returns a score For different attributes one learner is more useful than another § Combine their predictions using a combiner / meta-learner Combiner / meta-learner § Uses training sources to measure base learner accuracy § Weighs each learner based on its accuracy 10

Training the Learners Mediated schema address location price agent-phone listed-price phone description comments Schema of realestate. com Name Learner realestate. com Miami, FL $250, 000 (305) 729 0831 Fantastic house Boston, MA $110, 000 (617) 253 1429 Great location (location, address) (listed-price, price) (phone, agent-phone) (comments, description). . . Naive Bayes Learner (“Miami, FL”, address) (“$ 250, 000”, price) (“(305) 729 0831”, agent-phone) (“Fantastic house”, description) 11. . .

Applying the Learners Mediated schema Schema of homes. com area day-phone extra-info Seattle, WA Kent, WA Austin, TX address Name Learner Naive Bayes (278) 345 7215 (617) 335 2315 (512) 427 1115 Beautiful yard Great beach Close to Seattle price agent-phone Meta-Learner description (address, 0. 8), (description, 0. 2) (address, 0. 6), (description, 0. 4) (address, 0. 7), (description, 0. 3) (agent-phone, 0. 9), (description, 0. 1) (address, 0. 6), (description, 0. 4) 12

Putting It All Together: LSD Schema Matching System Training Phase Matching Phase Mediated schema Source schemas Data listings Training data for base learners L 1 L 2 Lk Domain Constraints User Feedback Constraint Handler Mapping Combination 13

Mappings between Schemas LSD provides attribute correspondences, but not complete mappings Many similar systems: COMA, COMA++, Falcon-AO, … Mappings generally are posed as views: define relations in one schema (typically either the mediated schema or the source schema), given data in the other schema § This allows us to “restructure” or “recompose + decompose” our data in a new way We can also define mappings between values in a view § We use an intermediate table defining correspondences – a “concordance table” § It can be filled in using some type of code, and corrected by hand 14

A Few Mapping Examples § Movie(Title, Year, Director, § Piece. Of. Art(ID, Artist, Subject, Editor, Star 1, Star 2) Title, Type. Of. Art) Piece. Of. Art(I, A, S, T, “Movie”) : - Movie(T, Y, A, _, S 1, S 2), ID = T || Y, S = S 1 || S 2 § Movie(Title, Year, Director, Editor, Star 1, Star 2) § Motion. Picture(ID, Title, Year) Participant(ID, Name, Role) Movie(T, Y, D, E, S 1, S 2) : - Motion. Picture(I, T, Y), Participant(I, D, “Dir”), Participant(I, E, “Editor”), Participant(I, S 1, “Star 1”), Participant(I, S 2, “Star 2”) T 1 Cust. ID Cust. Name 1234 Smith, J. T 2 Penn. ID Emp. Name 46732 John Smith Need a concordance table from Cust. IDs to Penn. IDs 15

Two Important Approaches § TSIMMIS [Garcia-Molina+97] – Stanford § Focus: semistructured data (OEM), OQL-based language (Lorel) § Creates a mediated schema as a view over the sources § Spawned a UCSD project called MIX, which led to a company now owned by BEA Systems § Other important systems of this vein: Kleisli/K 2 @ Penn § Information Manifold [Levy+96] – AT&T Research § Focus: local-as-view mappings, relational model § Sources defined as views over mediated schema Requires a special § Led to peer-to-peer integration approaches (Piazza, etc. ) § Focus: Web-based queriable sources 16

TSIMMIS § One of the first systems to support semi-structured data, which predated XML by several years: “OEM” § An instance of a “global-as-view” mediation system § We define our global schema as views over the sources § We’ll use XQuery + XML to illustrate the principles 17

Some Simple Data Bernstein Newcomer Chamberlin 18

Queries in TSIMMIS Specified in OQL-style language called Lorel § OQL was an object-oriented query language that looks like SQL § Lorel is, in many ways, a predecessor to XQuery Example in XQuery: for $b in All. Data()/book where $b/title/text() = “DB 2 UDB” and $b/author/text() = “Chamberlin” return $b 19

Query Answering in TSIMMIS Basically, it’s view unfolding , i. e. , composing a query with a view § The query is the one being asked § The views are the MSL templates for the wrappers § Some of the views may actually require parameters, e. g. , an author name, before they’ll return answers Common for web forms (see Amazon, Google, …) XQuery functions (XQuery’s version of views) support parameters as well, so we’ll see these in action 20

A Wrapper Definition in MSL Wrappers have templates and binding patterns ($X) in MSL: B : - B: }> // $$ = “select * from book where author=“ $X // § This reformats a SQL query over Book(author, year, title) In XQuery, this might look like: define function Get. Book($x AS xsd: string) as book { for $b in title sql(“Amazon. DB”, “select * from book where author=‘” + $x +”’”) return {$b/title}$x } … The union of Get. Book’s results is unioned with others to form the view Mediator() … book author … 21

How to Answer the Query Given our query: for $b in Mediator()/book where $b/title/text() = “DB 2 UDB” and $b/author/text() = “Chamberlin” return $b Find all wrapper definitions that: § Contain output enough “structure” to match the conditions of the query § Or have already tested the conditions for us! 22

Query Composition with Views We find all views that define book with author and title, and we compose the query with each: book define function Get. Book($x AS xsd: string) as book { for $b in sql(“Amazon. DB”, author title “select * from book where author=‘” + $x + “’”) return {$b/title} {$x} } … … for $b in Mediator()/book where $b/title/text() = “DB 2 UDB” and $b/author/text() = “Chamberlin” return $b 23

Matching View Output to Our Query’s Conditions § Determine that $b/book/author/text() $x by matching the pattern on the function’s output: define function Get. Book($x AS xsd: string) as book { for $b in sql(“Amazon. DB”, “select * from book where author=‘” + $x + “’”) return { $b/title } {$x} } book title let $x : = “Chamberlin” for $b in Get. Book($x)/book where $b/title/text() = “DB 2 UDB” return $b … author … 24

The Final Step: Unfolding let $x : = “Chamberlin” for $b in ( for $b’ in sql(“Amazon. com”, “select * from book where author=‘” + $x + “’”) return { $b/title }{$x} )/book where $b/title/text() = “DB 2 UDB” return $b § How do we simplify further to get to here? for $b in sql(“Amazon. com”, “select * from book where author=‘Chamberlin’”) where $b/title/text() = “DB 2 UDB” return $b 25

Virtues of TSIMMIS § Early adopter of semistructured data, greatly predating XML § Can support data from many different kinds of sources § Obviously, doesn’t fully solve heterogeneity problem § Presents a mediated schema that is the union of multiple views § Query answering based on view unfolding § Easily composed in a hierarchy of mediators 26

Limitations of TSIMMIS’ Approach Some data sources may contain data with certain ranges or properties § “Books by Aho”, “Students at UPenn”, … § If we ask a query for students at Columbia, don’t want to bother querying students at Penn… § How do we express these? Mediated schema is basically the union of the various MSL templates – as they change, so may the mediated schema 27

An Alternate Approach: The Information Manifold (Levy et al. ) When you integrate something, you have some conceptual model of the integrated domain § Define that as a basic frame of reference, everything else as a view over it § “Local as View” May have overlapping/incomplete sources § Define each source as the subset of a query over the mediated schema § We can use selection or join predicates to specify that a source contains a range of values: Computer. Books(…) Books(Title, …, Subj), Subj = “Computers” 28

The Local-as-View Model The basic model is the following: § “Local” sources are views over the mediated schema § Sources have the data – mediated schema is virtual § Sources may not have all the data from the domain – “open-world assumption” The system must use the sources (views) to answer queries over the mediated schema 29

Query Answering Assumption: conjunctive queries, set semantics Suppose we have a mediated schema: author(a. ID, isbn, year), book(isbn, title, publisher) Suppose we have the query: q(a, t) : - author(a, i, _), book(i, t, p), t = “DB 2 UDB” and sources: s 1(a, t) author(a, i, _), book(i, t, p), t = “ 123” … s 5(a, t, p) author(a, i, _), book(i, t), p = “SAMS” We want to compose the query with the source mappings – but they’re in the wrong direction! Ø Yet: everything in s 1, s 5 is an answer to the query! 30

Answering Queries Using Views Numerous recently-developed algorithms for these § Inverse rules [Duschka et al. ] § Bucket algorithm [Levy et al. ] § Mini. Con [Pottinger & Halevy] § Also related: “chase and backchase” [Popa, Tannen, Deutsch] Requires conjunctive queries 31

Summary of Data Integration Local-as-view integration has replaced global-as-view as the standard § More robust way of defining mediated schemas and sources § Mediated schema is clearly defined, less likely to change § Sources can be more accurately described Methods exist for query reformulation, including inverse rules Integration requires standardization on a single schema § Can be hard to get consensus § Today we have peer-to-peer data integration, e. g. , Piazza [Halevy et al. ], Orchestra [Ives et al. ], Hyperion [Miller et al. ] Data integration capabilities in commercial products: Oracle Fusion, IBM’s Web. Sphere Integrator, numerous packages from middleware companies 32