xquery to sql by xml algebra tree
Download
Skip this Video
Download Presentation
XQuery to SQL by XML Algebra Tree

Loading in 2 Seconds...

play fullscreen
1 / 43

XQuery to SQL by XML Algebra Tree - PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on

XQuery to SQL by XML Algebra Tree. Brad Pielech, Brian Murphy Thanks: Xin. Outline. Overview of Rainbow System Process of translating XQuery -> SQL XML Operators Partial translation walkthrough with running example. Rainbow System. Complete XML <-> SQL system

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' XQuery to SQL by XML Algebra Tree' - halla-dillon


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
xquery to sql by xml algebra tree

XQuery to SQL by XML Algebra Tree

Brad Pielech, Brian Murphy

Thanks:

Xin

outline
Outline
  • Overview of Rainbow System
  • Process of translating XQuery -> SQL
  • XML Operators
  • Partial translation walkthrough with running example
rainbow system
Rainbow System
  • Complete XML <-> SQL system
  • Uses some ideas from XPERANTO, Niagara, and other systems
  • Several main subsystems:
    • Document Shredder
    • View Generator
    • Query Translation, Query Rewrite
    • Result Generation
  • Work in progress
steps in translation
Steps in Translation
  • User inputs XQuery query
  • User Query is converted into an XML Algebra Tree (XAT)
  • Database Mapping Query’s XAT generated
  • Queries are Decorrelated
  • Trees are merged, unnecessary branches cut
steps continued
Steps Continued
  • Computation Pushdown (presentation concludes here)
  • SQL Generation
  • Query Execution
  • Tagging of Results
what is the difference between the two queries
What is the difference between the two queries?
  • The user query is executed over a view of the XML document and specifies what to return and how to return it
  • The mapping query specifies how the view the user is querying “maps” to the database
  • Therefore, combining the two queries into one is necessary in order to correctly process the user’s request
xat operators
XAT Operators
  • Each XAT is comprised of XAT Operators.
  • Similar in concepts to Relational Algebra
  • Operator set is combination between Niagara and Xperanto papers
set of operators
Set of Operators
  • SQL like (9):
    • Project, Select, Join (Theta, Outer, Semi), Groupby, Orderby, Union (Node, Outer), Cartesian Product.
  • XML like (4):
    • Tagger, Navigate, is(Element, Text), Aggregate.
  • Special:
    • SQL, Function, Source, NameColumn, FOR
sports xml document
Sports XML Document

<sports>

<organization>

<team> Boston Red Sox </team>

<stadium sname = "Fenway Park"/>

<starPlayer>

<pname> Nomar </pname>

<position> Shortstop </position>

</starPlayer>

<starPlayer>

<pname> Pedro </pname>

<position> Pitcher </position>

</starPlayer>

<starPlayer>

<pname> Manny </pname>

<position> Outfield </position>

</starPlayer>

</organization>

<organization>

</organization>

<stadium>

<sname> Fenway Park </sname>

<capacity> 33,000 </capacity>

<yearBuilt> 1912 </yearBuilt>

<ticket_high rate = "55"/>

<ticket_low rate = "18"/>

</stadium>

<stadium>

<stadium>

<player name="Pedro" number="45"

rookieYear = "1991" />

<player name="Nomar" number="5"

rookieYear = "1997" />

<player name="Manny" number="24"

rookieYear = "1993" />

</sports>

example xquery
Example XQuery

<bestPlayers>

{

For $p in document("sports.xml")/sports/organization

Let $a = $p/team/text()

Where $a = "Boston Red Sox"

Return

<playerName>

$p/starPlayer/pname/text()

<playerName>

}

</bestPlayers>

List all of the star players’ names on the Boston Red Sox

xat tree for example query

Tagger(<bestPlayers> V1 </bestPlayers>

$pname = Navigate($p, starPlayer/pname/text())

V1 := Aggregate

Tagger(<playerName> $pname </playerName>

$a := Navigate($p, team/text())

$p := Navigate(“/”, sports/organization)

Select($a = "Boston Red Sox")

Source(“sports.xml”)

XAT Tree for Example Query
rdbms tables of sports info
RDBMS Tables of Sports Info

PlayerInfo

Organization

Stadium

StarPlayer

partial default xml view
Partial Default XML View

<Organization>

<row>

<organizationId> 1 </organizationId>

<teamName> Boston Red Sox </teamName>

<stadiumName> Fenway Park </stadiumName>

</row>

</Organization>

<Stadium>

<row>

<stadiumID> 1 </stadiumID>

<sname> Fenway Park </sname>

</row>

</Stadium>

challenge question i
Challenge Question I

What is the XQuery that converts the document on the left (default XML view) to the document on the right (user view)?

<Organization>

<row>

<organizationId> 1 </organizationId>

<teamName> Boston Red Sox </teamName>

<stadiumName> Fenway Park </stadiumName>

</row>

</Organization>

<StarPlayer>

<row>

<starPlayerName> Nomar </starPlayerName>

<starPlayerPosition> shortstop</starPlayerPosition>

<organizationId> 1 </organizationId>

</row>

</StarPlayer>

<organization>

<team> Boston Red Sox </team>

<stadium sname = "Fenway Park"/>

<starPlayer>

<pname> Nomar </pname>

<position> Shortstop </position>

</starPlayer>

<starPlayer>

<pname> Pedro </pname>

<position> Pitcher </position>

</starPlayer>

<starPlayer>

<pname> Manny </pname>

<position> Outfield </position>

</starPlayer>

</organization>

mapping query part i
Mapping Query Part I

B2

Create view invoice as (

<sports>

FOR

$organization IN view ("default") /Organization/row

RETURN

<organization>

<team> $organization/teamName/text() </team>

<stadium sname = $organization/stadiumName/text() />

FOR$starPlayer IN view ("default") /StarPlayer/row

WHERE$starPlayer/organizationID = $organization/organizationID

RETURN

<starPlayer>

<pname> $starPlayer/starPlayerName/text() </pname>

<position> $starPlayer/starPlayerPosition/text() </position>

</starPlayer>

</organization>

B1

mapping query part ii
Mapping Query Part II

FOR$stadium IN view ("default") /Stadium/row

RETURN

<stadium>

<sname> $stadium/sname/text() </sname>

<capacity> $stadium/capacity/text() </capacity>

<yearBuilt> $stadium/yearBuilt/text() </yearBuilt>

<ticket_high rate = $stadium/ticket_high_rate/text() />

<ticket_low rate = $stadium/ticket_low_rate/text() />

</stadium>

FOR$player IN view ("default") /PlayerInfo/row

RETURN

<player name = $player/playerName/text() number = $player/playerNumber/text() rookieYear = $player/rookieYear/text() />

</sports>

)

B3

B4

cutting mapping query
Cutting Mapping Query
  • The mapping query has data that is unused by the user query, so we can get rid of it
    • B3 and B4 are completely removed
    • Remove stadium from B1
    • Remove position from B2
mapping query xat general form
Mapping Query XAT General Form

B1

FOR $organization

Some Stuff

$organization := Navigate("/",Organization/row)

FOR $starPlayer

$starPlayer :=

Navigate("/", StarPlayer/row)

Source(“default.xml”)

More Stuff

Source(“default.xml”)

B2

  • Some Stuff will be shown in Part I
  • More Stuff in Part II
mapping query xat part i

O := Tagger(<sports> All </sports)

All = Aggregate

Tagger(<organization> V0 </organization)

V0 := Aggregate

Tagger (<team>$tname </team> )

Mapping Query XAT Part I

B1

FOR $organization

$tname := Navigate($organization, teamName/text())

FOR $starPlayer

$starPlayer :=

Navigate("/", StarPlayer/row)

To: Part II

Source("default.xml")

Some Stuff

mapping query xat part ii

Select($starPlayerID = $ID)

$sname := Navigate($starPlayer, starPlayerName)

$starPlayerID := Navigate($starPlayer, OrganizationID)

$ID := Navigate($organization, organizationID)

Mapping Query XAT Part II

More Stuff

To: Part I

Aggregate

Tagger(<starPlayer>

<pname> $sname </pname> </starPlayer)

B2

decorrelated mapping xat part i
Decorrelated Mapping XAT Part I

<sports>

<organization>

<team> Boston Red Sox </team>

<starPlayer>

<pname> Nomar </pname>

</starPlayer>

<starPlayer>

<pname>Pedro </pname>

</starPlayer>

<starPlayer>

<pname> Manny </pname>

</starPlayer>

</organization>

</sports>

O:= Tagger(<sports> All </sports>)

All = Aggregate

Tagger(<organization> V0 </organization)

V0 := Aggregate

Tagger (<team>$tname </team>

$tname := Navigate($organization, teamName/text())

From Part II

decorrelated mapping xat part ii

$ID := Navigate($organization, organizationID)

Select($starPlayerID = $ID)

Cartesian Product

$sname := Navigate($starPlayer, starPlayerName)

$starPlayerID := Navigate($starPlayer, organizationID)

Decorrelated Mapping XAT Part II

To Part I

Aggregate

Tagger(<starPlayer>

<pname> $sname </pname> </starPlayer)

$starPlayer := (Navigate"/", StarPlayer/row)

$organization = Navigate("/", Organization/row)

Source("default.xml")

Source("default.xml")

progress report
Progress Report
  • User inputs XQuery query
  • User Query is converted into an XML Algebra Tree (XAT)
  • Database Mapping Query’s XAT generated
  • Queries are Decorrelated
  • Trees are merged, unnecessary branches cut
xat merging
XAT merging
  • Input:
    • User Query XAT + Mapping Query XAT
  • Output:
    • Simplified composite XAT
  • Approach:
    • The Tagger from the top of the Mapping Query is linked to the bottom of the User Query.
    • The Source Operator at the bottom of the User Query is deleted
    • Pushdown Navigation
      • By using the commutative rules
    • Cancel out the navigation operators
      • By using the composition rules
combined xat

O:= Tagger(<sports> All </sports>)

All = Aggregate

Tagger(<organization> V0 </organization)

V0 := Aggregate

Tagger (<team>$tname </team>

$tname := Navigate($organization, teamName/text())

Combined XAT

Top of Mapping Query

Tagger(<bestPlayers> V1 </bestPlayers>

V1 := Aggregate

Tagger(<playerName> $pname </playerName>

Select($a = "Boston Red Sox")

$a := Navigate($p, team/text())

User Query

$pname = Navigate($p, starPlayer/pname/text())

$p := Navigate(O, sports/organization)

Rest of Mapping Query

computation pushdown part i
Computation Pushdown Part I

What is PushDown?

  • After merging the 2 XATs, there may be redundancies in the larger tree.
    • Ex: The user query and mapping query may navigate to the same thing
  • The decorrelated query tree may be unorganized and inefficient
  • Pushdown aims to eliminate these problems
computation pushdown part ii
Computation Pushdown Part II
  • XPERANTO mentions pushdown as a means of pushing computation to relational engine
  • Niagara defines equivalence rules and specifies several different heuristics for using the rules
xat pushdown example part i
XAT Pushdown Example Part I

Top of Mapping Query

Tagger(<bestPlayers> V1 </bestPlayers>

V1 := Aggregate

O:= Tagger(<sports> All </sports>)

Tagger(<playerName> $pname </playerName>

All = Aggregate

Tagger(<organization> V0 </organization)

Select($a = "Boston Red Sox")

V0 := Aggregate

$a := Navigate($p, team/text())

Tagger (<team>$tname </team>

User Query

$pname = Navigate($p, starPlayer/pname/text())

$tname := Navigate($organization, teamName/text())

$p := Navigate(O, sports/organization)

Rest of Mapping Query

xat pushdown example part ii
XAT Pushdown Example Part II

Top of Mapping Query

Tagger(<bestPlayers> V1 </bestPlayers>

V1 := Aggregate

O:= Tagger(<sports> All </sports>)

Tagger(<playerName> $pname </playerName>

All = Aggregate

Tagger(<organization> V0 </organization)

Select($a = "Boston Red Sox")

V0 := Aggregate

$a := Navigate($p, team/text())

Tagger (<team>$tname </team>

User Query

$pname = Navigate($p, starPlayer/pname/text())

$tname := Navigate($organization, teamName/text())

$p := Navigate(O, sports/organization)

Rest of Mapping Query

xat pushdown example part iii
XAT Pushdown Example Part III

Tagger(<starPlayer>

<pname> $sname </pname> </starPlayer)

Tagger(<bestPlayers> V1 </bestPlayers>

Select($starPlayerID = $ID)

V1 := Aggregate

$sname := Navigate($starPlayer, starPlayerName)

Tagger(<playerName> $pname </playerName>

$starPlayerID := Navigate($starPlayer, organizationID)

Select($a = "Boston Red Sox")

$ID := Navigate($organization, organizationID)

Cartesian Product

$a := Navigate($p, team/text())

$pname = Navigate($p, starPlayer/pname/text())

$starPlayer := (Navigate"/", StarPlayer/row)

$organization = Navigate("/", Organization/row)

User Query

$p := Navigate(O, sports/organization)

Source("default.xml")

Source("default.xml")

xat pushdown example part iv
XAT Pushdown Example Part IV

Tagger(<starPlayer>

<pname> $sname </pname> </starPlayer)

Tagger(<bestPlayers> V1 </bestPlayers>

Select($starPlayerID = $ID)

V1 := Aggregate

$sname := Navigate($starPlayer, starPlayerName)

Tagger(<playerName> $pname </playerName>

$starPlayerID := Navigate($starPlayer, organizationID)

Select($a = "Boston Red Sox")

$ID := Navigate($organization, organizationID)

Cartesian Product

$a := Navigate($p, team/text())

$pname = Navigate($p, starPlayer/pname/text())

$starPlayer := (Navigate"/", StarPlayer/row)

$organization = Navigate("/", Organization/row)

User Query

$p := Navigate(O, sports/organization)

Source("default.xml")

Source("default.xml")

challenge questions ii iii
Challenge Questions II & III
  • What are some of the heuristics we could use during Pushdown?
    • What can / should we try to accomplish?
    • What should the tree look like afterwards?
  • How could we go about pushing things down?
    • What would the algorithm be?
    • How do we know if an operator can be pushed down?
    • When do we stop pushing an operator down?
computation pushdown part iii
Computation Pushdown Part III
  • Goal: Tagger + SQL operators + XML operators
    • Use Equivalence rules repository to swap operators
  • Step 1: Navigation Pushdown.
    • Cancel Mapping Query Taggers and corresponding Aggregates
    • Delete redundant Navigates from User Query
    • Rename columns in Mapping Query
  • Step 2: SQL Computation Pushdown.
    • By commutative and composition rules.
equivalence rules
Equivalence Rules
  • Pair-wise rules that determine if one operator (parent) may be pushed through another (child)
    • Navigate / Navigate rule: If the parent depends on the child, they may not be swapped
    • Navigate / Join: Navigate is pushed to the side of the join that its entry point comes from
    • And many, many more
pushdown results
Pushdown Results
  • Push Navigates to the correct side of Cartesian Product
  • Create a NameColumn operator that renames $tname into $a
  • Create a 2nd NameColumn operator that renames $pname into $sname
  • Get rid of all Taggers and Aggregates from Mapping Query and Navigates that were crossed out from User Query
  • Merge Select($starPlayerID = $ID) and Cartesian into a Join
xat after computation pushdown part i

Tagger(<bestPlayers> V1 </bestPlayers>

V1 := Aggregate

Tagger(<playerName> $pname </playerName>

Select($a = "Boston Red Sox")

NameColumn( $a = $tname)

NameColumn( $pname = $sname)

XAT After Computation PushDown Part I

From Part II

xat after computation pushdown part ii

$tname := Navigate($organization, teamName/text())

$starPlayerID := Navigate($starPlayer, OrganizationID)

$ID := Navigate($organization, organizationID)

$sname := Navigate($starPlayer, starPlayerName)

$organization := Navigate("/",Organization/row)

$starPlayer :=

Navigate("/", StarPlayer/row)

Source("default.xml")

Source("default.xml")

XAT After Computation PushDown Part II

To Part I

Join on ($ID = $starPlayerID)

rest of the process
Rest of the Process
  • Take the Combined XAT from the previous slide and generate a single SQL query.
  • Execute query on local RDBMS
  • Format result tuples according to Tagger
  • Return XML document to user
summary
Summary
  • Created XAT of the user query
  • Created XAT for mapping query
    • Cut information unused by user query
    • Decorrelated Mapping query
  • Merged two queries into 1 larger XAT
  • Identified weaknesses in combined tree
  • Walked through pushdown steps
  • Displayed final, optimized tree
ad