Transcript Powerpoint

Efficiently Publishing Relational
Data as XML Documents
Jayavel Shanmugasundaram
University of Wisconsin-Madison/
IBM Almaden Research Center
Joint work with:
Rimon Barr
Michael Carey
Bruce Lindsay
Hamid Pirahesh
Berthold Reinwald
Eugene Shekita
Outline
•
•
•
•
Why?
How?
Which?
Hence
XML Example
<department name=“Purchasing”>
<emplist>
<employee> John </employee>
<employee> Mary </employee>
</emplist>
<projlist>
<project> Internet </project>
<project> Recycling </project>
</projlist>
</department>
What is the big deal about XML?
• Elegantly models complex, hierarchical/
graph-structured data
• Domain-specific tags (unlike HTML)
• Simple!

Fast emerging as dominant standard
for data exchange on the WWW
Why Relational Data?
• Most business data stored in relational
databases
• Unlikely to change in the near future
– Scalability, Reliability, Performance, Tools

Need efficient means to publish
relational data as XML documents
Usage Scenario
Application/User Query to
produce XML Documents
XML Result (processed or
displayed in browser)
The Internet
Existing Database System
(RDBMS)
Example Relational Schema
Employee
Department
DeptId
DeptName
10
Purchasing
EmpId
101
DeptId
10
91
10
EmpName Salary
John
50K
Mary
70K
Project
ProjId
888
DeptId
10
ProjName
Internet
795
10
Recycling
XML Representation
<department name=“Purchasing”>
<emplist>
<employee> John </employee>
<employee> Mary </employee>
</emplist>
<projlist>
<project> Internet </project>
<project> Recycling </project>
</projlist>
</department>
Main Issues
• Relational data is flat, XML is a tagged graph
• How do we specify translation from flat
model to a graph model?
– A query language to map from relations to XML
• How do we transform flat representations to
tagged nested representations?
– Efficient implementation strategies
Outline
• Why?
• How?
– Language?
– Mechanism?
• Which?
• Hence
Transformation Languages
• Two obvious choices:
– XML Query Language
– SQL
Example Relational Schema
Employee
Department
DeptId
DeptName
10
Purchasing
EmpId
101
DeptId
10
91
10
EmpName Salary
John
50K
Mary
70K
Project
ProjId
888
DeptId
10
ProjName
Internet
795
10
Recycling
XMLQL: Default XML View
<defaultview>
<department>
<row> <deptid>10</> <deptname>Purchasing</> </row>
</department>
<employee>
<row> <empid>101</> <deptid>10</> <empname>John</> <salary>50K</> </row>
<row> <empid>91</> <deptid>10</> <empname>Mary</> <salary>70K</> </row>
</employee>
<project>
<row> <projid>888</> <deptid>10</> <projname>Internet</> </row>
<row> <projid>795</> <deptid>10</> <projname>Recycling</> </row>
</project>
</defaultview>
XMLQL: Query Over Default View
WHERE <defaultview.department.row>
<deptid> $did </> <deptname> $dname </>
</> IN DefaultView
CONSTRUCT <department name=$dname>
<emplist>
{ WHERE <defaultview.employee.row>
<deptid> $did </>
<empname> $ename </>
</> IN DefaultView
CONSTRUCT <employee> $ename </> }
</emplist>
<projlist>
{ WHERE <defaultview.project.row>
<deptid> $did </>
<projname> $pname </>
</> IN DefaultView
CONSTRUCT <project> $pname </> }
</projlist>
</>
XMLQL: Query Result
<department name=“Purchasing”>
<emplist>
<employee> John </employee>
<employee> Mary </employee>
</emplist>
<projlist>
<project> Internet </project>
<project> Recycling </project>
</projlist>
</department>
XMLQL: Pros and Cons
• Pros:
– Natural for XML users
– Infrastructure to build hierarchies of XML views
– One query language for XML and relational data
• Cons:
– Ignores existing API (JDBC), tools, support
– Need to mature new query language (aggregates etc.)
SQL: Key Ideas
• Sub-queries to specify nesting
• Scalar functions to specify tags/attributes
– XML Constructors
• Aggregate functions to group child elements
SQL: Query to publish XML
Select DEPT(d.name,
<subquery to produce emplist>,
<subquery to produce projlist>
)
From Department d
SQL: XML Constructor
Define XML Constructor DEPT(dname: varchar(20),
emplist: xml,
projlist: xml) As (
<department name=$dname>
<emplist> $emplist </emplist>
<projlist> $projlist </projlist>
</department>
)
SQL: Query to publish XML
Select DEPT(d.name,
<subquery to produce emplist>,
<subquery to produce projlist>
)
From Department d
SQL: Query to publish XML
Select DEPT(d.name,
(Select XMLAGG(EMP(e.name))
From Employee e
Where e.deptno = d.deptno),
<subquery to produce projlist>
)
From Department d
SQL: XML Constructor
Define XML Constructor EMP(ename: varchar(20)) As (
<employee>
<name> $ename </name>
</employee>
)
SQL: Query to publish XML
Select DEPT(d.name,
(Select XMLAGG(EMP(e.name))
From Employee e
Where e.deptno = d.deptno),
<subquery to produce projlist>
)
From Department d
SQL: Query to publish XML
Select DEPT(d.name,
(Select XMLAGG(EMP(e.name))
From Employee e
Where e.deptno = d.deptno),
(Select XMLAGG(PROJ(p.name))
From Project p
Where p.deptno = d.deptno)
)
From Department d
Query Result
(<XML Result>)
<department name=“Purchasing”>
<emplist>
<employee> John </employee>
<employee> Mary </employee>
</emplist>
<projlist>
<project> Internet </project>
<project> Recycling </project>
</projlist>
</department>
SQL: Pros and Cons
• Pros:
– Reuses SQL infrastructure/API
– Natural for SQL users
– Efficient execution inside relational engine
• Cons:
– Limited support for XML View Composition
Outline
• Why?
• How?
– Language?
– Mechanism?
• Which?
• Hence
Relations to XML: Issues
• Two main differences:
– Nesting (structuring)
– Tagging
• Space of alternatives:
Early Tagging
Outside Engine
Late Tagging
Outside Engine
Early Structuring
Inside Engine
Inside Engine
Outside Engine
Late Structuring
Inside Engine
Early Tagging, Early Structuring, Outside Engine
Stored Procedure Approach
• Issue queries for sub-structures and tag them
• Could be a Stored Procedure
DBMS Engine
(10, Purchasing)
Department
Employee
(Internet)
(Recycling)
Project
• Problem: Too many SQL queries!
(John)
(Mary)
Early Tagging, Early Structuring, Inside Engine
Correlated CLOB Approach
Select DEPT(d.name,
(Select XMLAGG(EMP(e.name))
From Employee e
Where e.deptno = d.deptno),
(Select XMLAGG(PROJ(p.name))
From Project p
Where p.deptno = d.deptno)
)
From Department d
• Problem: Correlated execution of sub-queries
Early Tagging, Early Structuring, Inside Engine
De-Correlated CLOB Approach
With EmpStruct (deptname, empinfo) AS (
Select d.deptname,
XMLAGG(EMP(employee, e.empname))
From department d left join employee e
on d.deptid = e.deptid
Group By d.deptname)
With ProjStruct (deptname, projinfo) AS (
Select d.deptname,
XMLAGG(PROJ(employee, p.projname))
From department d left join project p
on d.deptid = e.deptid
Group By d.deptname)
Select DEPT(name, d1.empinfo, d2.projinfo))
From EmpStruct d1 full join ProjStruct d2
on d1.deptname = d2.deptname
• Problem: CLOBs during processing
Late Tagging, Late Structuring
• XML document content produced without
structure (in arbitrary order)
• Tagger enforces order as final step
Result XML Document
Tagging
Unstructured content
Relational Query
Processing
Late Tagging, Late Structuring
Redundant Relation Approach
• How do we represent nested content as relations?
(10, John)
(10, Mary)
(10, Purchasing)
(10, Internet)
(10, Recycling)
(Purchasing, John, Internet)
(Purchasing, John, Recycling)
(Purchasing, Mary, Internet)
(Purchasing, Mary, Recycling)
• Problem: Large relation due to data redundancy!
Late Tagging, Late Structuring
Outer Union Approach
• How do we represent nested content as relations?
Union
(Purchasing, null, Internet , 0)
(Purchasing, null, Recycling, 0)
(Purchasing, John, null
, 1)
(Purchasing, Mary, null
, 1)
Department
Employee
Employee
Project
Project
(Purchasing, John)
(Purchasing, Mary)
(Purchasing, Internet)
(Purchasing, Recycling)
Department
(10, Purchasing)
• Problem: Wide tuples (having many columns)
Late Tagging, Late Structuring
Hash-based Tagger
• Results not structured early
– In arbitrary order
• Tagger has to enforce order during tagging
– Hash-based approach
• Inside/Outside engine tagger
• Problem: Requires memory for entire document
Late Tagging, Early Structuring
• Structured XML document content produced
• Tagger just adds tags (constant space)
Result XML Document
Tagging
Structured content
Relational Query
Processing
Late Tagging, Early Structuring
Sorted Outer Union Approach
A
ABnDnnn
B
ABnnEnn
C
AnCnnFn
D
E
F
G
AnCnnnG
Sort By: Aid, Bid, Cid
• Problem: Only partial ordering required
Late Tagging, Late Structuring
Constant Space Tagger
• Detects changes in XML document hierarchy
• Adds appropriate opening/closing tags
• Inside/outside engine
Classification of Alternatives
Inside Engine
De-Correlated CLOB
Correlated CLOB
Late Tagging
Outside Engine
Early
Structuring
Outside Engine
Early Tagging
Late
Structuring
Outside Engine
Stored Procedure
Inside Engine
Sorted Outer Union
(Tagging inside)
Sorted Outer Union
(Tagging outside)
Inside Engine
Unsorted Outer Union
(Tagging inside)
Unsorted Outer Union
(Tagging outside)
Outline
• Why?
• How?
– Language?
– Mechanism?
• Which?
• Hence
Performance Evaluation
Database Size
TABLE0
TABLE00
TABLE000
TABLE001
Query Fan Out
TABLE01
TABLE010 TABLE011
Query Depth
Inside vs. Outside Engine
Time (in seconds)
60
Stored Proc
50
CLOB-Corr
40
CLOB-DeCorr
30
Redundant R
20
Unsorted OU (Out)
10
Unsorted OU (In)
0
2
3
Query Fan Out
4
Sorted OU (Out)
Sorted OU (In)
Time (in seconds)
Where Does Time Go?
35
30
25
20
15
10
5
0
S
ed
r
to
XML File
Tagging
Bind Out
Execution
P
c
o
r
B
O
L
C
-C
r
or
B
O
L
C
-D
r
or
eC
ns
U
d
tr e
o
U
O
ut
(O
ns
U
)
d
tr e
o
U
O
S
(In
)
d
tr e
o
U
O
)
t
u
O
(
S
d
tr e
o
U
O
)
n
(I
Time (in seconds)
Effect of Query Fan Out
15
10
CLOB-Corr
5
CLOB-DeCorr
0
Unsorted OU
2
3
Query Fan Out
4
Sorted OU
Time (in seconds)
Effect of Query Depth
60
40
CLOB-Corr
20
CLOB-DeCorr
0
Unsorted OU
2
3
Query Depth
4
Sorted OU
Memory Considerations
• Sorted outer union more robust
• Relational sort highly scalable!
Outline
• Why?
• How?
– Language?
– Mechanism?
• Which?
• Hence
Conclusion
• Publishing XML from relational sources important
in Internet
• Language alternatives:
– SQL based
– XML query language based
• Implementation Alternatives
– Inside engine >> Outside engine
– Unsorted Outer Union : sufficient main memory
– Sorted Outer Union : otherwise