Data Integration Helena Galhardas DEI IST (based on the slides of the course: CIS 550 – Database & Information Systems, Univ. Pennsylvania, Zachary Ives)
Agenda n n Overview of Data Integration Some systems: q q q The LSD System The TSIMMIS System Information Manifold
A Problem n n n 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 q q n 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
Motivating example (1) n n Full. Serve: company that provides internet access to homes, but also sells products to support the home computing infrastructure (ex: modems, wireless routers, etc) Full. Serve is a predominantly American company and decided to acquire Euro. Card, an European company that is mainly a credit card provider, but has recently started leveraging its customer base to enter the internet market
Motivating Example (2) Full. Serve databases: Employee Database Full. Time. Emp(ssn, emp. Id, first. Name, middle. Name, last. Name) Hire(emp. Id, hire. Date, recruiter) Temp. Employees(ssn, hire. Start, hire. End, name, hourly. Rate) Training Database Courses(course. ID, name, instructor) Enrollments(course. ID, emp. ID, date) Services Database Services(pack. Name, text. Description) Customers(name, id, zip. Code, street. Adr, phone) Contracts(cust. ID, pack. Name, start. Date) Sales Database Products(prod. Name, prod. Id) Sales(prod. Name, customer. Name, address) Resume Database Interview(interview. Date, name, recruiter, hire. Decision, hire. Date) CV(name, resume) Help. Line Database Calls(date, agent, cust. Id, text, action)
Motivating Example (3) Euro. Card databases: Employee Database Emp(ID, first. Name. Middle. Initial, last. Name) Hire(ID, hire. Date, recruiter) Credit. Card Database Customer(Cust. ID, card. Num, expiration, current. Balance) Cust. Detail(Cust. ID, name, address) Resume Database Interview(ID, date, location, recruiter) CV(name, resume) Help. Line Database Calls(date, agent, cust. Id, description, followup)
Motivating Example (4) Some queries employees or managers in Full. Serve may want to pose: n The Human Resources Department may want to be able to query for all of its employees whether in the US or in Europe q n Require access to 2 databases in the American side and 1 in the European side There is a single customer support hot-line, where customers can call about any service or product they obtain from the company. When a representative is on the phone with a customer, it´s important to see the entire set of services the customer is getting from Full. Serve (internet service, credit card or products purchased). Furthermore, it is useful to know that the customer is a big spender on its credit card. q Require access to 2 databases in the US side and 1 in the European side.
Another example: searching for a new job (1)
Another example: searching for a new job (2) n n Each form (site) asks for a slighly different set of attributes (ex: keywords describing job, location and job category or employer and job type) Ideally, would like to have a single web site to pose our queries and have that site integrating data from all relevant sites in the Web,
Goal of data integration n Offer uniform access to a set of data autonomous and heterogeneous data sources: q q Querying disparate sources Large number of sources Heterogeneous data sources (different systems, diff. Schemas, some structured others unstructured) Autonomous data sources: we may not have full access to the data or source may not be available all the time.
Why is it hard? n n n Systems reasons: even with the same HW and all relational sources, the SQL supported is not always the same Logical reasons: different schemas (e. g. Full. Serve and Euro. Card temporary employees), diff attributes (e. g. , ID), diff attribute names for the same knowledge (e. g. , text and action), diff. Representations of data (e. g. First-name, last name) also known as semantic heterogeneity Social and administrative reasons
Building a Data Integration System Create a middleware “mediator” or “data integration system” over the sources q q q 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 n n q q 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
Logical components of a virtual Is built for the data integration system Specify the properties of the sources the system needs to know to use their data. Main component are semantic mappings that specify how attributes in the sources correspond to attributes in the mediated schema. Other info is whether sources are complete integration application and contains only the aspects of the domain relevant to the application. Most probably will contain a subset of the attributes seen in sources Programs whose role is to send queries to a data source, receive answers and possibly apply some transformation to the answer
A data integration scenario
Components of a data integration system
Query reformulation (1) n n n Rewrite the user query that was posed in terms of the relations in the mediated schema, into queries referring to the schemas of data sources Result is called a logical query plan Ex: SELECT title, start. Time FROM Movie, Plays WHERE Movie. title = Plays. movie AND location=”New York” AND director=”Woody Allen”
Query reformulation (2) n n n Tuples for Movie can be obtained from source S 1 but attribute title needs to be reformulated to name Tuples for Plays can be obtained from S 2 or S 3. Since S 2 is complete for showings in NY, we choose it Since source S 3 requires the title of a movie as input and the title is not secified in the query, the query plan must first access S 1 and then feed the movie titles returned from S 1 as inputs to S 3.
Query optimization n Acepts a logical query plan as input and produces a physical query plan q Specifies the exact order in which sources are accessed, when results are combined, which algorithms are used for performing operations on the data
Query execution n Responsible for the execution of the physical query plan q n Dispatches the queries to the individual sources through the wrappers and combines the results as specified by the query plan. Also may ask the optimizer to reconsider its plan based on its monitoring of the plan’s progress (e. g. , if source S 3 is slow)
Challenges of Mapping Schemas In a perfect world, it would be easy to match up items from one schema with another q q q 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! q q 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
Different Aspects to Mapping Schema matching / ontology alignment How do we find correspondences between attributes? Entity matching / deduplication / record linkage / etc. How do we know when two records refer to the same thing? Mapping definition q 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?
Why Schema Matching is Important Enterprise 1 Data integration Data translation Data warehousing E-commerce World-Wide Web Ontology Matching Information agent Enterprise 2 Home users Application has more than one schema matching! Knowledge Base 2 Knowledge Base 1 need for
Why Schema Matching is Difficult n n No access to exact semantics of concepts q Semantics not documented in sufficient details q Schemas not adequately expressive to capture semantics Must rely on clues in schema & data q Using names, structures, types, data values, etc. Such clues can be unreliable q Synonyms: Different names => same entity: n area & address => location q Homonyms: Same names => different entities: n area => location or square-feet Done manually by domain experts q Expensive and time consuming
The LSD Project Reconciling Schemas of Disparate Da Sources: A Machine Learning Approach An. Hai Doan, Pedro Domingos, Alon Halevy University of Washington SIGMOD 2001
Find houses with 2 bedrooms priced under 300 K Charlie comes to town realestate. com homeseekers. com homes. com
Data Integration Find houses with 2 bedrooms priced under 300 K mediated schema source schema 1 source schema 2 source schema 3 wrapper realestate. com homeseekers. com homes. com
The LSD (Learning Source Descriptions) Approach Suppose user wants to integrate 100 data sources 1. User q q manually creates mappings for a few sources, say 3 shows LSD these mappings 2. LSD learns from the mappings 3. LSD proposes mappings for remaining 97 sources
Semantic Mappings between Schemas n Mediated & source schemas = XML DTDs house address contact-info agent-name num-baths agent-phone 1 -1 mapping non 1 -1 mapping house location contact name full-baths phone half-baths
Example Mediated schema address location 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
LSD Contributions 1. Use of multi-strategy learning q q well-suited to exploit multiple types of knowledge highly modular & extensible 2. Extend learning to incorporate constraints q handle a wide range of domain & user-specified constraints 3. Develop XML learner q exploit hierarchical nature of XML
Multi-Strategy Learning Use a set of base learners q Each exploits well certain types of information: n n n 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 q Apply the base learners n n q Each returns a score For different attributes one learner is more useful than another Combine their predictions using a meta-learner Meta-learner q q Uses training sources to measure base learner accuracy Weights each learner based on its accuracy
Base Learners n Input q q n Output q n schema information: name, proximity, structure, . . . data information: value, format, . . . prediction weighted by confidence score Examples q Name learner n q agent-name => (name, 0. 7), (phone, 0. 3) Naive Bayes learner n n “Kent, WA” “Great location” => (address, 0. 8), (name, 0. 2) => (description, 0. 9), (address, 0. 1)
The two phases of LSD Training Phase Matching Phase Mediated schema Source schemas Data listings Training data for base learners L 1 n n L 2 Lk Domain Constraints User Feedback Constraint Handler Mapping Combination Base learners: Name Learner, XML learner, Naive Bayes, Whirl learner Meta-learner q uses stacking [Ting&Witten 99, Wolpert 92] q returns linear weighted combination of base learners’ predictions
Training phase 1. 2. 3. 4. 5. Manually specify 1 -1 mappings for several sources Extract source data Create training data for each base learner Train the base learners Train the meta-learner
Training the Learners Mediated schema address location price agent-phone listed-price phone description comments Schema of realestate. com Name Learner realestate. com <location> Miami, FL </> <listed-price> $250, 000</> <phone> (305) 729 0831</> <comments> Fantastic house </> <location> Boston, MA </> <listed-price> $110, 000</> <phone> (617) 253 1429</> <comments> 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). . .
The matching phase 1. 2. 3. Extract and collect data Match each source-DTD tag Apply the constraint handler
Figure 5 - SIGMOD’ 01 paper
Applying the Learners Mediated schema Schema of homes. com area day-phone extra-info <area>Seattle, WA</> <area>Kent, WA</> <area>Austin, TX</> address Name Learner Naive Bayes <day-phone>(278) 345 7215</> <day-phone>(617) 335 2315</> <day-phone>(512) 427 1115</> <extra-info>Beautiful yard</> <extra-info>Great beach</> <extra-info>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)
Domain Constraints n Impose semantic regularities on sources q n Examples q q q n verified using schema or data a = address & b = address a=b a = house-id a is a key a = agent-info & b = agent-name b is nested in a Can be specified up front q q when creating mediated schema independent of any actual source schema
The Constraint Handler Predictions from Meta-Learner Domain Constraints area: (address, 0. 7), (description, 0. 3) contact-phone: (agent-phone, 0. 9), (description, 0. 1) extra-info: (address, 0. 6), (description, 0. 4) a = address & b = adderss area: address 0. 7 contact-phone: agent-phone 0. 9 extra-info: address 0. 6 0. 378 n n n area: address 0. 7 contact-phone: agent-phone 0. 9 extra-info: description 0. 4 0. 252 Can specify arbitrary constraints User feedback = domain constraint q ad-id = house-id Extended to handle domain heuristics q a = agent-phone & b = agent-name each other a & b are usually close to a=b 0. 3 0. 1 0. 4 0. 012
Exploiting Hierarchical Structure n Existing learners flatten out all structures <contact> <name> Gail Murphy </name> <firm> MAX Realtors </firm> </contact> n <description> Victorian house with a view. Name your price! To see it, contact Gail Murphy at MAX Realtors. </description> Developed XML learner q similar to the Naive Bayes learner n q input instance = bag of tokens differs in one crucial aspect n consider not only text tokens, but also structure tokens
Related Work n Rule-based approaches q q n Learner-based approaches q q n SEMINT [Li&Clifton 94], ILA [Perkowitz&Etzioni 95] employ a single learner, limited applicability Others q n TRANSCM [Milo&Zohar 98], ARTEMIS [Castano&Antonellis 99], [Palopoli et. al. 98], CUPID [Madhavan et. al. 01] utilize only schema information DELTA [Clifton et. al. 97], CLIO [Miller et. al. 00][Yan et. al. 01] Multi-strategy learning in other domains q q series of workshops [91, 93, 96, 98, 00] [Freitag 98], Proverb [Keim et. al. 99]
Summary n LSD project q n Main ideas & contributions q q q n applies machine learning to schema matching use of multi-strategy learning extend learning to handle domain & user-specified constraints develop XML learner System design: A contribution to generic schema-matching q q q highly modular & extensible handle multiple types of knowledge continuously improve over time
End LSD
Mappings between Schemas LSD provides attribute correspondences, but not complete mappings 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 q This allows us to “restructure” or “recompose + decompose” our data in a new way We can also define mappings between values in a view q q We use an intermediate table defining correspondences – a “concordance table” It can be filled in using some type of code, and corrected by hand
A Few Mapping Examples n Movie(Title, Year, Director, Editor, Star 1, Star 2) n Piece. Of. Art(ID, Artist, Subject, Title, Type. Of. Art) Piece. Of. Art(I, A, S, T, “Movie”) : - Movie(T, Y, A, _, S 1, S 2), I = T || Y, S = S 1 || S 2 n Movie(Title, Year, Director, Editor, Star 1, Star 2) n 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
![Two Important Approaches n TSIMMIS [Garcia-Molina+97] – Stanford q q n Information Manifold [Levy+96]](https://present5.com/presentation/baaa02306336db2b55a959d6727c96f8/image-47.jpg "Two Important Approaches n TSIMMIS [Garcia-Molina+97] – Stanford q q n Information Manifold [Levy+96]")
Two Important Approaches n TSIMMIS [Garcia-Molina+97] – Stanford q q n Information Manifold [Levy+96] – AT&T Research q q q n 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 Focus: local-as-view mappings, relational model Sources defined as views over mediated schema Led to peer-to-peer integration approaches (Piazza, etc. ) Focus: Web-based queriable sources
TSIMMIS n One of the first systems to support semistructured data, which predated XML by several years: “OEM” n An instance of a “global-as-view” mediation system q We define our global schema as views over the sources
XML vs. Object Exchange Model <book> <author>Bernstein</author> <author>Newcomer</author> <title>Principles of TP</title> </book> <author>Chamberlin</author> <title>DB 2 UDB</title> O 1: book { </book> O 2: author { Bernstein } O 3: author { Newcomer } O 4: title { Principles of TP } } O 5: book { O 6: author { Chamberlin } O 7: title { DB 2 UDB } }
Queries in TSIMMIS Specified in OQL-style language called Lorel q q OQL was an object-oriented query language that looks like SQL Lorel is, in many ways, a predecessor to XQuery Based on path expressions over OEM structures: select book where book. title = “DB 2 UDB” and book. author = “Chamberlin” This is basically like XQuery, which we’ll use in place of Lorel and the MSL template language. Previous query restated = for $b in All. Data()/book where $b/title/text() = “DB 2 UDB” and $b/author/text() = “Chamberlin” return $b
Query Answering in TSIMMIS Basically, it’s view unfolding, i. e. , composing a query with a view q q q 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 n n 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
Example: View Unfolding/Expansion n A view consisting of branches and their customers create view all_customer as (select branch_name, customer_name from depositor, account where depositor. account_number = account_number ) union (select branch_name, customer_name from borrower, loan where borrower. loan_number = loan_number ) n Find all customers of the Perryridge branch select customer_name from all_customer where branch_name = 'Perryridge'
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 // q 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 <book>{$b/title}<author>$x</author></book> … } … book author … The union of Get. Book’s results is unioned with others to form the view Mediator()
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: q q Contain enough “structure” to match the conditions of the query Or have already tested the conditions for us!
Query Composition with Views We find all views that define book with author and title, and we compose the query with each: define function Get. Book($x AS xsd: string) as book { for $b in sql(“Amazon. DB”, “select * from book where author=‘” + $x + “’”) book return <book> {$b/title} <author>{$x}</author></book> } author title for $b in Mediator()/book where $b/title/text() = “DB 2 UDB” and … $b/author/text() = “Chamberlin” … return $b
Matching View Output to Our Query’s Conditions n 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 + “’”) book return <book>{ $b/title } <author>{$x}</author></book> } author title let $x : = “Chamberlin” for $b in Get. Book($x)/book where $b/title/text() = “DB 2 UDB” return $b … …
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> )/book where $b/title/text() = “DB 2 UDB” return $b n 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
Virtues of TSIMMIS n Early adopter of semistructured data, greatly predating XML q q n Presents a mediated schema that is the union of multiple views q n Can support data from many different kinds of sources Obviously, doesn’t fully solve heterogeneity problem Query answering based on view unfolding Easily composed in a hierarchy of mediators
Limitations of TSIMMIS’ Approach Some data sources may contain data with certain ranges or properties q q q “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
Schema mapping languages n Schema mapping: set of expressions that describe a relationship between a set of schemata (typically two). In our case, mediator schema and the schema of the sources q q n Used to reformulate a query formulated in terms of the mediated schema into appropriate queries on the sources. Result is called logical query plan (refers only to the relations in the data sources) Schema mapping languages: global-as-view, local-as-view, global-local-as-view.
Components of a data integration system
Properties of mapping languages n n n Flexibility: the formalism should be able to express a wide variety of relationships between schemata. Efficient reformulation: reformulation algorithms should have well understood properties and be efficient Easy update: Must be easy to add and remove sources
An Alternate Approach: The Information Manifold (Levy et al. ) When you integrate something, you have some conceptual model of the integrated domain q q 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 q We can use selection or join predicates to specify that a source contains a range of values: Computer. Books(…) Books(Title, …, Subj), Subj = “Computers” q
The Local-as-View Model The basic model is the following: q q q “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
Query Answering Assumption: conjunctive queries, set semantics Suppose we have a mediated schema: author(a. ID, isbn, year), book(isbn, title, publisher) 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! Ø The idea is to determine which views may be relevant to each subgoal of the query in isolation
Answering Queries Using Views Numerous recently-developed algorithms for these q q 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
Advantages and Shortcomings of LAV n Enables expressing incomplete information More robust way of defining mediated schemas and sources Mediated schema is clearly defined, less likely to change Sources can be more accurately described n Computationally more expensive! n n n
Summary of Data Integration n Integration requires standardization on a single schema q Can be hard to get consensus q Today we have peer-to-peer data integration, e. g. , Piazza [Halevy et al. ], Orchestra [Ives et al. ], Hyperion [Miller et al. ] Some other aspects of integration were addressed in related papers q Overlap between sources; coverage of data at sources q Semi-automated creation of mappings and wrappers Data integration capabilities in commercial products: BEA’s Liquid Data, IBM’s DB 2 Information Integrator, numerous packages from middleware companies
Referências n n n Draft of the book on Data Integration by Alon Halevy (in preparation). An. Hai Doan, Pedro Domingos, Alon Halevy, “Reconciling Schemas of Disparate Data Sources: A Machine Learning Approach”, SIGMOD 2001. Hector Garcia-Molina et al, “The TSIMMIS Approach to Mediation: Data Models and Languages”, Journal of Intelligent Information Systems (JIIS) , 8 (2) : 117132 , 1997 http: //infolab. stanford. edu/tsimmis/ Alon Levy et al, “Querying Heterogeneous Information Sources using Source Descriptions”, VLDB 1996.
Скачать презентацию Data Integration Helena Galhardas DEI IST based on
baaa02306336db2b55a959d6727c96f8.ppt