Vorlesung Informaitonsintegration

Informationsintegration
Multidatenbanksprachen
Schema SQL: Konzept und Implementierung
Ulf Leser
Wissensmanagement in der
Bioinformatik
Enge vs. lose Kopplung
User
• Enge Kopplung
•
Integriertes/föderiertes Schema
• Lose Kopplung
•
Kein integriertes Schema
• Nutzer müssen Semantik der
Quellen kennen
•
•
Überbrückung von Heterogenität
erfolgt durch Anfragen
Mächtige Anfragesprache
notwendig
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
2
Schematische Heterogenität
maenner( Id, vorname, nachname)
frauen( Id, vorname, nachname)
Relation vs. Wert
Relation vs. Attribut
person( Id, vorname, nachname,
maennlich?, weiblich?)
person( Id, vorname, nachname,
geschlecht)
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
Attribut vs. Wert
3
SchemaSQL
[LSS96, LSS99, LSS01]
• Erweiterung von SQL
•
•
•
Zugriff auf Tabellen in verschiedenen Schemata
Daten und Metadaten werden gleich behandelt
Umstrukturierungen innerhalb der Anfrage
• Daten zu Metadaten und umgekehrt
•
Dynamische Sicht-Definition
• Ergebnisrelation hängt vom Zustand der Datenbank ab
•
Horizontale Aggregation
• Über mehrere Spalten hinweg
•
Überbrückung struktureller Heterogenität
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
4
Grundlegende Syntax SchemaSQL
• Grundlegende Syntax wie SQL
• SELECT … FROM … WHERE
• Deklaration in FROM Klausel durch <range> <var>
• Variablen über fünf verschiedene Wertbereichstypen
•
•
•
•
•
->
db->
db::rel->
db::rel
db::rel.attr
Alle
Alle
Alle
Alle
Alle
Datenbanknamen der Multidatenbank
Relationennamen einer Datenbank db
Attributnamen einer Relation rel in db
Tupel einer Relation rel in db
verschiedenen Werte von attr in rel in db
• Weiterer Unterschied: Geschachtelte Deklarationen
•
•
Spätere Deklarationen der FROM Klausel referenzieren frühere
Machen Queries nicht gerade sehr gut lesbar
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
5
Lösung
• Gesucht
•
Alle Abteilungen in univ-A, die Technikern
mehr zahlen als gleiche Abteilungen in univ-B
• SchemaSQL Anfrage
SELECT
FROM
WHERE
A.dept
univ-A::salInfo A,
univ-B::salInfo B,
univ-B::salInfo-> AttB
AttB <> `category`
A.dept = AttB AND
A.category = `Technician` AND
B.category = `Technician` AND
A.salFloor > B.AttB
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
6
Lösung
• Gesucht
•
Alle Abteilungen in univ-C, die Technikern mehr
zahlen als gleiche Abteilungen in univ-D
• SchemaSQL Anfrage
SELECT
FROM
WHERE
RelC
univ-C-> RelC,
univ-C::RelC C,
univ-D::salInfo D
RelC = D.dept AND
C.category = `Technician` AND
C.salFloor > D.Technician
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
7
Einschub: Zugriff auf Metadaten?
• Liegen auch im Data dictionary (Katalog)
•
Alle Attributnamen und –typen im Schema
SELECT attribute_name, attribute_type, table_name
FROM user_attributes;
•
Alle Tabellennamen, alle Integritätsconstraints, alle Berechtigungen, …
• Können in Anfragen verwendet werden
•
[Oracle] Alle Tabellen, die ein Attribut enthalten,
dass denselben Namen hat wie ein
person(
Wert in person.geschlecht
Id, vorname, nachname,
maennlich, weiblich)
person( Id, vorname, nachname,
SELECT t.table_name
geschlecht)
FROM user_tables t, person p
WHERE t.attribute_name = p.geschlecht;
• Aber nur Umwandlung Metadaten -> Daten
•
Kein Zugriff auf die Tabellen „hinter“ den Metadatennamen
• Kein Standard
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
8
Horizontale Aggregation 2
• Gesucht
•
Durchschnittliches Gehalt in
univ-C pro Kategorie für alle
Abteilungen
• Anforderungen
•
•
Durchschnittbildung über zwei
Spalten
Die liegen in verschiedenen
Tabellen
->
alle Datenbanknamen
db->
alle Relationen in db
db::rel-> alle Attribute in rel (in db)
db::rel
alle Tupel in rel (in db)
db::rel.attr alle Werte von Attribut attr
SELECT
FROM
T.category, avg(T.salFloor)
univ-C-> D,
univ-C::D T
GROUP BY T.category
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
9
Inhalt dieser Vorlesung
• Multidatenbanksprachen
• SchemaSQL
•
•
•
•
•
Grundlegende Syntax
Zugriff auf Metadaten
Horizontale Aggregation
Dynamische Umstrukturierung
Implementierung
• Ausklang
•
•
Andere Multidatenbanksprachen
Pivot-Operator
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
10
SchemaSQL – Umstrukturierung 1
• Gesucht
• Umstrukturierung der Daten aus univ-B in
das Schema von univ-A
• Anforderung
• Trennung
• Definition des Outputschemas
• Umstrukturierung der Daten
CREATE VIEW BtoA AS
SELECT T.category category,
D dept,
T.D salFloor
FROM
univ-B::salInfo-> D,
univ-B::salInfo T
WHERE D <> `category`
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
Attributnamen in
11
univ-B.salInfo
SchemaSQL – Umstrukturierung 2
• Gesucht
• Umgekehrt: Umstrukturierung der Daten
aus univ-A in das Schema von univ-B
• Anforderung
• Dynamische Schemaerzeugung
CREATE VIEW AtoB::salInfo(category, D) AS
SELECT A.category, A.salFloor
FROM
univ-A::salInfo A,
univ-A::A.dept D
Spezielle Semantik: Domainvariable in
Output-Schema-Definition eines Views
wird dynamisch expandiert zu D1,
D2,…, Dn
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
12
Erklärung
CREATE VIEW AtoB::salInfo(category, D) AS
SELECT A.category, A.salFloor
FROM
univ-A::salInfo A,
Prof, CS, 65.000
univ-A::A.dept D
AssocProf, CS, 50.000
Technician, CS, 45.000
Prof, Math, 60.000
D
AssocProf, Math, 55.000
Technician, Math, 45.000
CS
Math
CS
CS
CS
Math
Math
Math
• Magie
A
Prof, CS, 65.000
AssocProf, CS, 50.000
Technician, CS, 45.000
Prof, Math, 60.000
AssocProf, Math, 55.000
Technician, Math, 45.000
• Geschachtelte Deklaration
• Kein Cartesisches Produkt
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
13
Erklärung 2
CREATE VIEW AtoB::salInfo(category, D) AS
SELECT A.category, A.salFloor
FROM
univ-A::salInfo A,
Prof, CS, 65.000
univ-A::A.dept D
AssocProf, CS, 50.000
Technician, CS, 45.000
Prof, Math, 60.000
AssocProf, Math, 55.000
Technician, Math, 45.000
Prof, CS, 65.000
Prof, Math, 60.000
AssocProf, CS, 50.000
AssocProf, Math, 55.000
Technician, CS, 45.000
Technician, Math, 45.000
CS
Math
CS
Math
CS
Math
CS
CS
CS
Math
Math
Math
• Magie 2
• Gruppiere nach salInfo.dept
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
14
Erklärung 3
CREATE VIEW AtoB::salInfo(category, D) AS
SELECT A.category, A.salFloor
FROM
univ-A::salInfo A,
Prof, CS, 65.000
univ-A::A.dept D
Prof, Math, 60.000
AssocProf, CS, 50.000
AssocProf, Math, 55.000
Technician, CS, 45.000
Technician, Math, 45.000
Prof, CS, 65.000
Prof, Math, 60.000
AssocProf, CS, 50.000
AssocProf, Math, 55.000
Technician, CS, 45.000
Technician, Math, 45.000
Category
CS
CS
Math
CS
Math
CS
Math
CS
Math
CS
Math
CS
Math
• Magie 3
•
•
Merge macht aus jedem Wert von D
eine Spalte des Ausgabeschemas
Übernimmt Werte von salFloor
entsprechend Werten von
A.dept = D
Math
Prof
65.000
60.000
AssocProf
50.000
55.000
Ulf Leser:
Informationsintegration,
Wintersemester 2008/2009
Technician
45.000
45.000
15
Umstrukturierung & Aggregation 1
• Durchschnittliches Gehalt in
univ-D aller Angestellten pro
Fakultät und Kategorie
• Anforderung
faculty
dname
fname
Math
Arts and sciences
Physics
Arts and sciences
CS
Engineering
•
•
Aggregation über Block
Umstrukturierung
• Gewünschtes Outputschema
•
salInfo( faculty, Prof,
AssocProf, Technician)
CREATE VIEW average::salInfo(faculty, C) AS
SELECT
U.fname, AVG(T.C)
FROM
univ-D::salInfo-> C,
univ-D::salInfo T,
univ-D::faculty U
WHERE
C <> „dept“
AND
T.dept = U.dname
GROUP BY
U.fname
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
16
Erklärung
CREATE VIEW average::salInfo(faculty, C) AS
SELECT
U.fname, AVG(T.C)
FROM
univ-D::salInfo-> C,
univ-D::salInfo T,
univ-D::faculty U
WHERE
C <> „dept“
AND
T.dept = U.dname
GROUP BY
U.fname
T ⋈U
Eng,CS,75,60,40
A&S,Math,60,45,38
C
Faculty
Prof
AssocProf
Tech
Eng
75
60
40
A&S
60
45
38
Prof
AssocProf
Math
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
18
Inhalt dieser Vorlesung
• Multidatenbanksprachen
• SchemaSQL
•
•
•
•
•
Grundlegende Syntax
Zugriff auf Metadaten
Horizontale Aggregation
Dynamische Umstrukturierung
Implementierung
• Ausklang
•
•
Andere Multidatenbanksprachen
Pivot-Operator
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
19
Ziele einer Implementierung
• „Non-intrusive“
• Ausnutzung vorhandener RDBMS
• Übersetzung von Schema-SQL in Sequenz von (verteilten)
SQL Befehlen
• Optimierung
• Metadatenverwaltung
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
20
Ablauf
Metadaten
5
6
7
1
2
3
3
4
4
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
21
Anfragebearbeitung
• Phase 1
•
Variablen der FROM Klausel instantiieren
• VITs (Variable instantiation table)
•
Verwendung der Metadaten
• FST (Federation System Table)
• Schema
FST( dbname, relationname, attributename)
• Phase 2
•
•
SchemaSQL Anfrage umschreiben
Umgeschriebene Anfrage auf instantiierten Variablen ausführen
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
22
Beispielanfrage
Alle Abteilungen in univ-C, die Technikern mehr
zahlen als gleiche Abteilungen in univ-D
SELECT RelC, salFloor
FROM
univ-C-> RelC,
univ-C::RelC C,
univ-D::salInfo D
WHERE RelC = D.dept
AND
C.category = `Technician`
AND
C.salFloor > D.Technician
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
23
Phase 1
• VITRelC( RelC)
•
Anfrage an Metadaten
SELECT DISTINCT relname
FROM
FST
WHERE dbname = ‚univ-C‘;
• VITC( RelC, C.category, C.salFloor):
•
Bindings für ri:
SELECT RelC FROM VITRelC;
•
SELECT ‚r1‘ AS RelC,
category as Ccategory
salFloor AS CsalFloor
FROM
r1
UNION ... UNION
SELECT ‚rn‘ AS RelC ,
category as Ccategory,
salFloor AS CsalFloor
FROM
rn;
FST(dbname, relname, attname)
SELECT RelC, salFloor
FROM
univ-C-> RelC,
univ-C::RelC C,
univ-D::salInfo D
WHERE RelC = D.dept
•
AND
C.category = `Technician`
AND
C.salFloor > D.Technician
Alle Binding zu einer Query an univ-C
kompilieren:
VITD( D.dept, D.technician)
•
Anfrage direkt an univ-D
SELECT dept AS Ddept,
technician AS D
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
FROM
salInfo;
24
Optimiert: Selektionen pushen
• VITRelC( RelC)
•
Anfrage an Metadaten
SELECT DISTINCT relname
FROM
FST
WHERE dbname = ‚univ-C‘;
• VITC( RelC, C.salFloor):
•
Bindings für ri:
SELECT RelC FROM VITRelC;
•
SELECT ‚r1‘ AS RelC,
salFloor AS CsalFloor
FROM
r1
WHERE category=‚Technician‘
UNION ... UNION
SELECT ‚rn‘ AS RelC,
salFloor AS CsalFloor
FROM
rn
WHERE category=‚Technician‘;
FST(dbname, relname, attname)
SELECT RelC, salFloor
FROM
univ-C-> RelC,
univ-C::RelC C,
•
univ-D::salInfo D
WHERE RelC = D.dept
AND
C.category = `Technician`
AND
C.salFloor > D.Technician
Alle Binding zu einer Query an univ-C
kompilieren:
VITD( D.dept, D.technician)
•
Anfrage direkt an univ-D
SELECT dept AS Ddept,
technician AS D
FROM
salInfo;
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
25
Werte in den VIT
• VITRelC(RelC)
•
{(Math),
(CS)}
• VITC(RelC, C.salFloor)
•
{([CS], 42.000),
([Math], 46.000)}
• VITD(D.dept, D.technician)
•
{(CS, 40.000),
(Math, 38.000)}
SELECT RelC, salFloor
FROM
univ-C-> RelC,
univ-C::RelC C,
univ-D::salInfo D
WHERE RelC = D.dept
AND
C.category = `Technician`
AND
C.salFloor > D.Technician
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
26
Phase 2
• VITs sind in interner
Datenbank materialisiert
• SchemaSQL Anfrage so
umschreiben, dass das
Endergebnis lokal mittels
der VITs erzeugt werden
kann
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
27
Umgeschriebene Anfrage
• Zusätzlicher Natural Join über alle VITs
•
•
Bei geschachtelten Deklarationen
Damit Tupel der gleichen DB zusammenbleiben
CREATE
SELECT
FROM
WHERE
VIEW JVIT(RelC, CsalFloor, Ddept, Dtechnician) AS
VITRelC.RelC, VITC.CsalFloor, VITD.Ddept, VITD.Dtechnician
VITRelC, VITC, VITD
VITRelC.RelC = VITD.Ddept AND
SELECT RelC, salFloor
VITC.CsalFloor > VITD.Dtechnician AND
FROM
univ-C-> RelC,
VITRelC.RelC = VITC.RelC
univ-C::RelC C,
univ-D::salInfo D
WHERE RelC = D.dept
AND
C.category = `Technician`
AND
C.salFloor > D.Technician
Schon bei Erzeugung der VITs
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
28
Endergebnis
• Noch mal die umgeschriebene Anfrage
CREATE VIEW JVIT(RelC, CsalFloor, Ddept, Dtechnician) AS
SELECT VITRelC.RelC, VITC.CsalFloor,
VITD.Ddept, VITD.Dtechnician
FROM
VITRelC, VITC, VITD
WHERE
VITRelC.RelC = VITD.Ddept AND
VITC.CsalFloor > VITD.Dtechnician AND
VITRelC.RelC = VITC.RelC
• Endgültige Anfrage
•
Projektionen, Sortierungen, etc.
SELECT
FROM
RelC, CsalFloor
JVIT
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
29
Optimierungspotential
• Selektionen und Projektionen zu den Quellen pushen
• Kombinierte VIT
•
•
•
Wenn mehrere Variablen zu einer Datenbank gehören und die Verknüpfung
in SQL ausdrückbar ist („normaler“ Join)
Verknüpfte Anfrage an Quelle schicken
Das kombinierte Ergebnis in einer kombinierten VIT speichern
• Ergebnisse einer VIT in die Berechnung der späteren
pushen
•
Reihenfolgeproblem (Heuristik: Metadatenzugriffe zuerst)
• Latenzzeiten senken
•
Connection reuse, Connection Pools
• … (siehe VL Optimierung)
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
31
Inhalt dieser Vorlesung
• Multidatenbanksprachen
• SchemaSQL
•
•
•
•
•
Grundlegende Syntax
Zugriff auf Metadaten
Horizontale Aggregation
Dynamische Umstrukturierung
Implementierung
• Ausklang
•
•
Andere Multidatenbanksprachen
Pivot-Operator
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
32
Ausklang
• SchemaSQL hatte Vorläufer
•
•
•
•
F-Logic, SchemaLog, Pegasus, SQL/M, …
FLogic: Logische Sprache (wie Tupelkalkül) mit
Vererbung, strukturierten und mengenwertigen
Attributen, Tupel-ID, …
Zugriff auf Strukturdaten (Metadaten) in einer Anfrage
„Semantically First-Order, but syntactically second-order“
• Und Nachfolger: FISQL/FIRA
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
33
First-Order Queries
SELECT C.CS
FROM
salInfo C
WHERE C.category=‚Prof‘;
{Y | salInfo( X , Y , Z ) ∧ X =' Pr of '}
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
34
Syntactically second-order
SELECT B.C
FROM
univ-C-> C,
univ-B::salInfo B;
{X ? | ∃C : C (?) ∧ B.salInfo( X 1 ,.., X n ) ∧ X i = C}
?
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
35
… semantically first-order …
• Schemata sind endlich
• Zum Zeitpunkt der Anfrage stehen alle möglichen
Instanzen der Variablen fest
•
Bzw. während der Ausführung bei geschachtelten Deklarationen
• Umschreiben in UNIONs von First-Order Anfragen möglich
•
Genau diese Strategie implementiert SchemaSQL
get_values( univ-C->);
construct_query(
SELECT B.C
FROM
univ-C-> C,
univ-B::salInfo B;
SELECT
FROM
UNION
SELECT
FROM
);
…
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
B.CS
univ-B::salInfo B
B.Math
univ-B::salInfo B
36
Pivot-Operator
• Implementiert in kommerziellen
Systemen
•
Bekannt aus Excel, Access, …
• Vertauschung von Spalten/Zeilen
einer Tabelle
•
Pivot: „Ausklappen“ aller DISTINCT Werte
einer Spalte als eigene Spalte
•
•
Ergebnis hat mehr Spalten, aber weniger
Zeilen
Unpivot: „Einklappen“ aller Spalten als
zusätzliches Zeilenpräfix
•
Ergebnis hat weniger Spalten, aber mehr
Zeilen
• Voraussetzung: Eindeutigkeit
•
•
Pivot
auf Jahr
Nur ein Wert pro Monat/Jahr
Sonst „Data Collision“
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
Pivot
auf Monat
37
Pivot in SQL
• Ist in SQL ausdrückbar
•
•
Komplizierte Anfragen
Hängen von den Werten der
Tabelle ab
• Syntactically second-order
•
Schwierig zu optimieren
• Deshalb: Raum für eigene
Operatoren
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
38
Literatur
• SchemaSQL
•
•
•
[LSS01] Laks V. S. Lakshmanan, Fereidoon Sadri, Subbu N. Subramanian:
SchemaSQL: An extension to SQL for multidatabase interoperability. ACM
Trans. Database Syst. 26(4): 476-519 (2001)
[LSS96] Lakshaman, Sadri, Subramanian: SchemaSQL – A Language for
Interoperability in Relational Multidatabase Systems, in VLDB 1996
[LSS99] Lakshaman, Sadri, Subramanian: On Efficiently Implementing
SchemaSQL on a SQL Database System, in VLDB 1999
• Pivot
•
[CGGL04] C Cunningham, G Graefe, CA Galindo-Legaria: PIVOT and
UNPIVOT: Optimization and Execution Strategies in an RDBMS, in VLDB
2004
Ulf Leser: Informationsintegration, Wintersemester 2008/2009
39