data integration zachary g. ives university of pennsylvania cis 550 – database & information...
TRANSCRIPT
![Page 1: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/1.jpg)
Data Integration
Zachary G. IvesUniversity of Pennsylvania
CIS 550 – Database & Information Systems
April 21, 2023
LSD Slides courtesy AnHai Doan
![Page 2: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/2.jpg)
2
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
![Page 3: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/3.jpg)
3
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
![Page 4: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/4.jpg)
4
Data Integration System / Mediator
Typical Data Integration Components
Mediated Schema
Wrapper Wrapper Wrapper
SourceRelations
Mappingsin Catalog
SourceCatalog
Query Results
![Page 5: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/5.jpg)
5
Typical Data Integration Architecture
Reformulator
QueryProcessor
SourceCatalog
Wrapper Wrapper Wrapper
Query
Query over sources
SourceDescrs.
Queries +bindings Data in mediated format
Results
![Page 6: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/6.jpg)
6
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
![Page 7: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/7.jpg)
7
Different Aspects to Mapping
Schema matching / ontology alignmentHow 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…
![Page 8: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/8.jpg)
8
Standard Schema Matcher Architecture(Established by LSD System)
Suppose user wants to integrate 100 data sources1. 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
![Page 9: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/9.jpg)
9
listed-price $250,000 $110,000 ...
address price agent-phone description
Example
location Miami, FL Boston, MA ...
phone(305) 729 0831(617) 253 1429 ...
commentsFantastic houseGreat location ...
realestate.com
location listed-price phone comments
Schema of realestate.com
If “fantastic” & “great”
occur frequently in data values =>
description
Learned hypotheses
price $550,000 $320,000 ...
contact-phone(278) 345 7215(617) 335 2315 ...
extra-infoBeautiful yardGreat beach ...
homes.com
If “phone” occurs in the name =>
agent-phone
Mediated schema
![Page 10: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/10.jpg)
10
Learning from Multiple SourcesUse 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
![Page 11: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/11.jpg)
11
<location> Boston, MA </> <listed-price> $110,000</> <phone> (617) 253 1429</> <comments> Great location </>
<location> Miami, FL </> <listed-price> $250,000</> <phone> (305) 729 0831</> <comments> Fantastic house </>
Training the Learners
Naive Bayes Learner
(location, address)(listed-price, price)(phone, agent-phone)(comments, description) ...
(“Miami, FL”, address)(“$ 250,000”, price)(“(305) 729 0831”, agent-phone)(“Fantastic house”, description) ...
realestate.com
Name Learner
address price agent-phone description
Schema of realestate.com
Mediated schema
location listed-price phone comments
![Page 12: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/12.jpg)
12
<extra-info>Beautiful yard</><extra-info>Great beach</><extra-info>Close to Seattle</>
<day-phone>(278) 345 7215</><day-phone>(617) 335 2315</><day-phone>(512) 427 1115</>
<area>Seattle, WA</><area>Kent, WA</><area>Austin, TX</>
Applying the Learners
Name LearnerNaive Bayes
Meta-Learner
(address,0.8), (description,0.2)(address,0.6), (description,0.4)(address,0.7), (description,0.3)
(address,0.6), (description,0.4)
Meta-LearnerName LearnerNaive Bayes
(address,0.7), (description,0.3)
(agent-phone,0.9), (description,0.1)
address price agent-phone description
Schema of homes.com Mediated schema
area day-phone extra-info
![Page 13: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/13.jpg)
13
Putting It All Together: LSD Schema Matching System
L1 L2 Lk
Mediated schema
Source schemas
Data listings
Training datafor base learners Constraint Handler
Mapping Combination
User Feedback
Domain Constraints
Matching PhaseTraining Phase
![Page 14: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/14.jpg)
14
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
![Page 15: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/15.jpg)
15
A Few Mapping Examples
Movie(Title, Year, Director, Editor, Star1, Star2)
Movie(Title, Year, Director, Editor, Star1, Star2)
PieceOfArt(ID, Artist, Subject, Title, TypeOfArt)
MotionPicture(ID, Title, Year)Participant(ID, Name, Role)
CustID
CustName
1234 Smith, J.
PennID
EmpName
46732 John Smith
PieceOfArt(I, A, S, T, “Movie”) :- Movie(T, Y, A, _, S1, S2),ID = T || Y, S = S1 || S2
Movie(T, Y, D, E, S1, S2) :- MotionPicture(I, T, Y), Participant(I, D, “Dir”), Participant(I, E, “Editor”), Participant(I, S1, “Star1”), Participant(I, S2, “Star2”)
T1 T2
Need a concordance table from CustIDs to PennIDs
![Page 16: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/16.jpg)
16
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/K2 @ 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
![Page 17: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/17.jpg)
17
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
![Page 18: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/18.jpg)
18
Some Simple Data<book> <author>Bernstein</author> <author>Newcomer</author> <title>Principles of TP</title></book>
<book> <author>Chamberlin</author> <title>DB2 UDB</title></book>
![Page 19: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/19.jpg)
19
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 AllData()/bookwhere $b/title/text() = “DB2 UDB” and $b/author/text() = “Chamberlin”return $b
![Page 20: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/20.jpg)
20
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
![Page 21: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/21.jpg)
21
A Wrapper Definition in MSL
Wrappers have templates and binding patterns ($X) in MSL:
B :- B: <book {<author $X>}> // $$ = “select * from book where author=“ $X //
This reformats a SQL query over Book(author, year, title)
In XQuery, this might look like:define function GetBook($x AS xsd:string) as book {
for $b in sql(“Amazon.DB”, “select * from book where author=‘” + $x
+”’”)return <book>{$b/title}<author>$x</author></book>
}
book
title author
… …
…
The union of GetBook’s results is unioned with others to form the view Mediator()
![Page 22: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/22.jpg)
22
How to Answer the Query
Given our query:for $b in Mediator()/bookwhere $b/title/text() = “DB2 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!
![Page 23: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/23.jpg)
23
Query Composition with Views
We find all views that define book with author and title, and we compose the query with each:
define function GetBook($x AS xsd:string) as book {for $b in
sql(“Amazon.DB”, “select * from book where author=‘” + $x + “’”)
return <book> {$b/title} <author>{$x}</author></book>}for $b in Mediator()/book
where $b/title/text() = “DB2 UDB” and $b/author/text() = “Chamberlin”return $b
book
title author
… …
![Page 24: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/24.jpg)
24
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 GetBook($x AS xsd:string) as book {
for $b in sql(“Amazon.DB”, “select * from book where author=‘” + $x +
“’”)return <book>{ $b/title } <author>{$x}</author></book>
}
let $x := “Chamberlin”for $b in GetBook($x)/bookwhere $b/title/text() = “DB2 UDB” return $b
book
title author
… …
![Page 25: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/25.jpg)
25
The Final Step: Unfolding
let $x := “Chamberlin”for $b in (
for $b’ in sql(“Amazon.com”,
“select * from book where author=‘” + $x + “’”) return <book>{ $b/title }<author>{$x}</author></book> )/bookwhere $b/title/text() = “DB2 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() = “DB2 UDB” return $b
![Page 26: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/26.jpg)
26
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
![Page 27: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/27.jpg)
27
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
![Page 28: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/28.jpg)
28
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:ComputerBooks(…) Books(Title, …, Subj), Subj =
“Computers”
![Page 29: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/29.jpg)
29
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
![Page 30: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/30.jpg)
30
Query Answering
Assumption: conjunctive queries, set semanticsSuppose we have a mediated schema:
author(aID, isbn, year), book(isbn, title, publisher)Suppose we have the query:
q(a, t) :- author(a, i, _), book(i, t, p), t = “DB2 UDB”
and sources:s1(a,t) author(a, i, _), book(i, t, p), t = “123”…s5(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 s1, s5 is an answer to the query!
![Page 31: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/31.jpg)
31
Answering Queries Using Views
Numerous recently-developed algorithms for these Inverse rules [Duschka et al.]
Bucket algorithm [Levy et al.]
MiniCon [Pottinger & Halevy]
Also related: “chase and backchase” [Popa, Tannen, Deutsch]
Requires conjunctive queries
![Page 32: Data Integration Zachary G. Ives University of Pennsylvania CIS 550 – Database & Information Systems October 16, 2015 LSD Slides courtesy AnHai Doan](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ec55503460f94bd0dd3/html5/thumbnails/32.jpg)
32
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 WebSphere Integrator, numerous packages from middleware companies