compsci516 data intensive computing systems

CompSci 516DataIntensiveComputingSystems

Lecture21Datalog

Instructor:Sudeepa Roy

1DukeCS,Fall2016 CompSci516:DataIntensiveComputingSystems

Announcement

• HW3duenextWednesday:11/16

DukeCS,Fall2016 CompSci516:DataIntensiveComputingSystems 2

• Datalog– forrecursion indatabasequeries

• AquicklookatIncrementalViewMaintenance(IVM)

ReadingMaterial:DatalogOptional:1. Thedatalog chaptersinthe“AliceBook”FoundationsofDatabasesAbiteboul-Hull-VianuAvailableonline:http://webdam.inria.fr/Alice/

2.Datalog tutorialSIGMOD2011“Datalog andEmergingApplications:AnInteractiveTutorial”

BriefHistoryofDatalog

• MotivatedbyProlog– startedbackin1970-80’s– thenquietforalongtime

• AlongargumentintheDatabasecommunitywhetherrecursionshouldbesupportedinquerylanguages– “Nopracticalapplicationsofrecursivequerytheory...havebeenfoundto

date”—MichaelStonebraker,1998ReadingsinDatabaseSystems,3rdEditionStonebraker andHellerstein,eds.

– RecentworkbyHellerstein etal.onDatalog-extensionstobuildnetworkingprotocolsanddistributedsystems.[Link]

Datalog isresurging!

• NumberofpapersandtutorialsinDBconferences

• Applicationsin– dataintegration,declarativenetworking,programanalysis,information

extraction,networkmonitoring,security,andcloudcomputing

• Systemssupportingdatalog inbothacademiaandindustry:– Lixto (informationextraction)– LogicBlox (enterprisedecisionautomation)– Semmle (programanalysis)– BOOM/Dedalus (Berlekey)– Coral– LDL++

RecallourdrinkerexampleinRC(Lecture4)

Find drinkers that frequent some bar that serves some beer they like.

Q(x) = $y. $z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z)

Likes(drinker, beer)Frequents(drinker, bar)Serves(bar, beer)

7CompSci516:DataIntensiveComputingSystems

DukeCS,Fall2016

DrinkerexampleisfromslidesbyProfs.Balazinska andSuciuandthe[GUW]book

WriteitasaDatalog RuleFind drinkers that frequent some bar that serves some beer they like.

DukeCS,Fall2016

RC:Q(x) = $y. $z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z)

Datalog:Q(x) :- Frequents(x, y), Serves(y,z), Likes(x,z)

WriteitasaDatalog RuleFind drinkers that frequent some bar that serves some beer they like.

DukeCS,Fall2016

Datalog:Q(x) :- Frequents(x, y), Serves(y,z), Likes(x,z)

RC:Q(x) = $y. $z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z)

• Quickdifferences:– Uses“:-”not=– noneedfor$ (assumedbydefault)– Use“,”ontherighthandside(RHS)– AnythingonRHStheof:- isassumedtobecombinedwith∧ bydefault– ",Þ,notallowed– theyneedtousenegation¬– Standard“Datalog”doesnotallownegation– Negationallowedindatalog withnegation

• Howtospecifydisjunction(OR/⋁)?

Example:ORinDatalogFind drinkers that (a) either frequent some bar that serves some beer they like, (b) or like beer “BestBeer”

DukeCS,Fall2016

Datalog:Q(x) :- Frequents(x, y), Serves(y,z), Likes(x,z)Q(x) :- Likes(x, “BestBeer”)

RC:Q(x)=[$y.$z.Frequents(x,y)∧Serves(y,z)∧Likes(x,z)]⋁ [Likes(x,“BestBeer”)]

Example:ORinDatalogFind drinkers that (a) either frequent some bar that serves some beer they like, (b) or like beer “BestBeer”, (c) or, frequent bars that “Joe” frequents

DukeCS,Fall2016

Datalog:JoeFrequents(w):- Frequents(“Joe”,w)Q(x):- Frequents(x,y),Serves(y,z),Likes(x,z)Q(x):- Likes(x,“BestBeer”)Q(x):- Frequents(x,w),JoeFrequents(w)

RC:Q(x)=[$y.$z.Frequents(x,y)∧Serves(y,z)∧Likes(x,z)]⋁ [Likes(x,“BestBeer”)]

⋁ [$w Frequents(x,w)∧ Frequents(“Joe”,w)]

• Tospecify“OR”,writemultipleruleswiththesame“Head”• Next:terminologyforDatalog

• Each rule is of the form Head :- Body

• Each variable in the head of each rule must appear in the body of the rule

DukeCS,Fall2016

JoeFrequents(w):- Frequents(“Joe”,w)Q(x):- Frequents(x,y),Serves(y,z),Likes(x,z)Q(x):- Likes(x,“BestBeer”)Q(x):- Frequents(x,w),JoeFrequents(w)

Fourrules

BodyHead

Datalog Rules

Variable

EDBsandIDBs

DukeCS,Fall2016 CompSci516:DataIntensiveComputingSystems

• Intensional DataBases (IDBs)– Relationsthatarederived– Canbeintermediateorfinaloutputtables– e.g.JoeFrequents,Q– CanbeontheLHSorRHS(e.g.JoeFrequents)

• ExtensionalDataBases (EDBs)– Inputrelationnames– e.g.Likes,Frequents,Serves– canonlybeontheRHSofarule

JoeFrequents(w):- Frequents(“Joe”,w)Q(x):- Frequents(x,y),Serves(y,z),Likes(x,z)Q(x):- Likes(x,“BestBeer”)Q(x):- Frequents(x,w),JoeFrequents(w)

TupleinanEDBoranIDB:aFACT

eitherbelongstoagivenEDBrelation,orisderivedinanIDB relation

GraphExample

E(edgerelation)

Example1

E(edgerelation)

WriteaDatalog programtofindpathsoflengthtwo(outputstartandfinishvertices)

Example1

E(edgerelation)

P2(x,y):- E(x,z),E(z,y)

Example1:Execution

E(edgerelation)

P2(x,y):- E(x,z),E(z,y)

a db cb ec ac ed c

sameasE⨝E.V2=E.V1 E

Example2

E(edgerelation)

WriteaDatalog programtofindallpairsofvertices(u,v)suchthatvisreachablefromu

• CanyouwriteaSQL/RA/RCqueryforreachability?

Example2

E(edgerelation)

• CanyouwriteaSQL/RA/RCqueryforreachability?• NO- SQL/RA/RCcannotexpressreachability

d e WriteaDatalog programtofindallpairsofvertices(u,v)suchthatvisreachablefromu

Example2

E(edgerelation)

R(x,y):- E(x,y)R(x,y):- E(x,z),R(z,y)

Option1

Example2

E(edgerelation)

Option1 R(x,y):- E(x,y)R(x,y):- R(x,z),E(z,y)

Option2

R(x,y):- E(x,y)R(x,y):- R(x,z),R(z,y)

Option3

linear

non-linear

LinearDatalog

• Linearrule– atmostoneatominthebodythatisrecursivewiththeheadoftherule

– e.g.R(x,y):- E(x,z),R(z,y)• Lineardatalog program

– ifallrulesarelinear– likelinearrecursion

• Top-downandbottom-upevaluationarepossible– wewillfocusonbottom-up

Example2:Execution

Option1

a cb ab dc dd ad e

Iteration1 R=E

(verticesreachablein1-hopbyadirectedge)

Example2:Execution

Option1

a cb ab dc dd ad ea db cb ec ac ed c

RIteration2

(verticesreachablein2-hops)

Example2:Execution

Option1

a cb ab dc dd ad ea db cb ec ac ed ca ea ac cd d

RIteration3

(verticesreachablein3-hops)

Example2:Execution

Option1

RIteration4

Runchanged- stop

Examples3and4

E(edgerelation)

WriteaDatalog programtofindallverticesreachablefromb

R(x,y):- E(x,y)R(x,y):- E(x,z),R(z,y)QB(y):- R(b,y)

WriteaDatalog programtofindallverticesureachablefromthemselvesR(u,u)

R(x,y):- E(x,y)R(x,y):- E(x,z),R(z,y)Q(x):- R(x,x)

TerminationofaDatalog Program

Q. ADatalog programalwaysterminates– why?

TerminationofaDatalog Program

Q. ADatalog programalwaysterminates– why?

• Becausethevaluesofthevariablesarecomingfromthe“activedomain”intheinputrelations(EDBs)

• Activedomain=(finite)valuesfromthe(possiblyinfinite)domainappearingintheinstanceofadatabase

– e.g.agecanbeanyinteger(infinite),butactivedomainisonlyfinitelymanyinR(id,name,age)

• ThereforethenumberofpossiblevaluesineachoftheIDBsisfinite

• e.g.inthereachabilityexampleR(x,y),thevaluesofxandycomefrom{a,b,c,d,e}

– atmost5x5=25tuplespossibleintheIDBR(x,y)– inanyiteration,atleastonenewtupleisaddedinatleastoneIDB– Muststopafterfinitesteps– e.g.themaximumnumberofiterationinthereachabilityexampleforanygraphwith

fiveverticesis25(itwasonly4inourexample)

Bottom-upEvaluationofaDatalog Program

• Naïveevaluation

• Semi-naïveevaluation

Naïveevaluation- 1

a cb ab dc dd ad e

Iteration1:R=E=R1(say)

Inallsubsequentiteration,checkifanyoftherulescanbeapplied

DounionofalltheruleswiththesameheadIDB

Naïveevaluation- 2

a cb ab dc dd ad e

Iteration2:R=E∪

E⨝ R1=R2(say)

R1≠R2socontinue

Naïveevaluation- 3

a cb ab dc dd ad e

Iteration2:R=E∪

E⨝ R1=R2(say)

R1≠R2socontinue

Iteration3:R=E∪

E⨝ R2=R3(say)

R2≠R3socontinue

Naïveevaluation- 4

a cb ab dc dd ad e

Iteration2:R=E∪

E⨝ R1=R2(say)

R1≠R2socontinue

Iteration3:R=E∪

E⨝ R2=R3(say)

R2≠R3socontinue

Iteration4:R=E∪

E⨝ R3=R4(say)

R3=R4soSTOP

ProblemwithNaïveEvaluation

• ThesameIDBfactsarediscoveredagainandagain– e.g.ineachiterationalledgesinEareincludedinR– Inthe2nd-4th iterations,thefirstsixtuplesinRarecomputed

repeatedly

• Solution:Semi-NaïveEvaluation

• Workonlywiththenewtuplesgeneratedinthepreviousiteration

Semi-Naïveevaluation- 1

DukeCS,Fall2016CompSci 516:DataIntensiveComputing

Systems 36

a cb ab dc dd ad e

Iteration1:R=E=R1(say)ΔR1=R1

Initially:R=Φ

Systems 37

a cb ab dc dd ad e

Iteration2:R=R1∪

E⨝ ΔR1=R2(say)

ΔR2=R2– R1

ΔR2≠Φsocontinue

Initially:R=Φ

Systems 38

a cb ab dc dd ad e

Iteration2:R=R1∪

E⨝ ΔR1=R2(say)

ΔR2=R2– R1

ΔR2≠Φsocontinue

Iteration3:R=R2∪

E⨝ ΔR2=R3(say)

ΔR3=R3– R2

ΔR3≠Φsocontinue

Initially:R=Φ

Systems 39

a cb ab dc dd ad e

Iteration2:R=R1∪

E⨝ ΔR1=R2(say)

ΔR2=R2– R1

ΔR2≠Φsocontinue

Iteration3:R=R2∪

E⨝ ΔR2=R3(say)

ΔR3=R3– R2

ΔR3≠Φsocontinue

Iteration4:R=R3∪E⨝ ΔR3=R4(say)

ΔR4=R4– R3ΔR=Φ(CHECKJ)soSTOP

Initially:R=Φ

IncrementalViewMaintenance(IVM)

• Whydidthesemi-naïvealgorithmwork?• BecauseofthegenerictechniqueofIncrementalView

Maintenance(IVM)

• Supposeyouhave– adatabaseD=(R1,R2,R3)– aqueryQthatgivesanswerQ(D)– D=(R1,R2,R3)getsupdatedtoD’=(R1’,R2’,R3’)– e.g.R1’=R1∪ ΔR1(insertion),R2’=R2- ΔR1(deletion)etc.

IncrementalViewMaintenance(IVM)

• Whydidthesemi-naïvealgorithmwork?• BecauseofthegenerictechniqueofIncrementalView

Maintenance(IVM)

• Supposeyouhave– adatabaseD=(R1,R2,R3)– aqueryQthatgivesanswerQ(D)– D=(R1,R2,R3)getsupdatedtoD’=(R1’,R2’,R3’)– e.g.R1’=R1∪ ΔR1(insertion),R2’=R2- ΔR1(deletion)etc.

• IVM: CanyoucomputeQ(D’)usingQ(D)andΔR1,ΔR2,ΔR3withoutcomputingitfromscratch(i.e.donotrerunthequeryQ)?

IVM Example:Selection

a cb ad ac d

a cb ad ac db dd e

σV1=bR

R’=R∪ ΔR

σV1=bR’

b ab d

σV1=bR σV1=bΔR

• σV1=b(R∪ ΔR)=σV1=bR∪ σV1=bΔR• Itsufficestoapplytheselectionconditiononly onΔR

– andincludewiththeoriginalsolution

IVM Example:Projection

• πV1(R∪ ΔR)=πV1R∪ πV1ΔR• Itsufficestoapplytheprojectionconditiononly onΔR

– andincludewiththeoriginalsolution

a cb ad ad e

a cb ad ad eb dc d

R’=R∪ ΔR

πV1R’

πV1ΔR

IVM Example:Join

(R∪ ΔR)⨝ (S∪ ΔS)=(R⨝ S)∪ (R⨝ ΔS)∪ (ΔR⨝ S)∪ (ΔR⨝ ΔS)

a1 b1a2 b2a3 b1

b1 c1b2 c2

S’=S∪ ΔSR’=R∪ ΔR

ΔRΔS

a1 b1B C

b1 c1A B C

a1 b1 c1⨝

a1 b1B C

b1 c1⨝

a1 b1⨝

a2 b2a3 b1

⨝B C

a2 b2a3 b1

b2 c2⨝

a1 b1 c1a3 b1 c1a2 b2 c2

IVM forLinearDatalog Rule

(R∪ ΔR)⨝ (S∪ ΔS)=(R⨝ S)∪ (R⨝ ΔS)∪ (ΔR⨝ S)∪ (ΔR⨝ ΔS)

a1 b1a2 b2a3 b1

S’=S

R’=R∪ ΔR

a1 b1B C

b1 c1A B C

a1 b1 c1⨝

a1 b1 c1a3 b1 c1

• R(x,y):- E(x,z),R(z,y)– i.e.Rnew =E⨝ R

• ButEisEDB– ΔE=Φ

• Therefore,E⨝ (R∪ ΔR)=(E⨝ R)∪ (E⨝ ΔR)• Itsufficestojoinwiththedifference

ΔRandincludeintheresultinthepreviousroundE⨝ R

• Advantageofhaving“linearrule”

(Non-recursive)Datalog withNegation

• RecursionandnegationtogethermakeDatalog executioncomplicated– thereisanotioncalled“stratifiedsemantic”forthispurpose– computeIDBrelationsinstrata/layersbeforetakinganegation– notcoveredinthisclass

• Wewillonlydonegationfornon-recursiveDatalog

Unsafe/SafeDatalog Rules

• Whatistheproblemwiththisrule?• Whatshouldthisrulereturn?

– namesofalldrinkersintheworld?– namesofalldrinkersintheUSA?– namesofalldrinkersinDurham?

Find drinkers who like beer “BestBeer” Q(x) :- Likes(x, “BestBeer”)

Find drinkers who DO NOT like beer “BestBeer”

Q(x) :- ¬Likes(x, “BestBeer”)

ProblemwithNegationinDatalog Rules

• Whatistheproblemwiththisrule?• Dependenton“domain”ofdrinkers

– domain-dependent– infiniteanswerspossibletoo..

• keepgenerating“names”

• Solution:• Restrictto“activedomain”ofdrinkersfromtheinputLikes (orFrequents)relation– “domain-independence”– samefiniteansweralways

• Becomesa“saferule”

ProblemwithNegationinDatalog Rules

Q(x) :- Likes(x, y), ¬Likes(x, “BestBeer”)

Q(x) = $y. Likes(x, y)∧"z.(Serves(z,y) Þ Frequents(x,z))

Query: Find drinkers that like some beer (so much) that they frequent all bars that serve it

CompSci516:DataIntensiveComputingSystems

DukeCS,Fall2016

RC→Datalog withnegation→ SQL(1/8)

Ack:slidesbyProfs.Balazinska andSuciu

RevisitexamplefromLecture4

Query: Find drinkers that like some beer so much that they frequent all bars that serve it

Step 1: Replace " with $ using de Morgan’s Laws

Q(x) = $y. Likes(x, y)∧ ¬$z.(Serves(z,y) ∧ ¬Frequents(x,z))

"x P(x) same as¬$x ¬P(x)

¬(¬P∨Q) same asP∧ ¬ Q

º Q(x) = $y. Likes(x, y)∧"z.(¬ Serves(z,y) ∨ Frequents(x,z))

DukeCS,Fall2016

RC→Datalog withnegation→SQL(2/8)

P => Q same as ¬P∨Q

Query: Find drinkers that like some beer so much that they frequent all bars that serve it

Step 1: Replace " with $ using de Morgan’s Laws

Q(x) = $y. Likes(x, y)∧ ¬$z.(Serves(z,y) ∧ ¬Frequents(x,z))

(new) Step 2: Make all subqueries domain independent

Q(x) = $y. Likes(x, y) ∧ ¬$z.(Likes(x,y)∧Serves(z,y)∧¬Frequents(x,z))

DukeCS,Fall2016

(new) Step 3: Create a datalog rule for some subexpressions of the form$x $y…. R(….)∧ S(….)∧ T(….)∧….

Q(x) = $y. Likes(x, y) ∧¬ $z.(Likes(x,y)∧Serves(z,y)∧¬Frequents(x,z))

H(x,y) :- Likes(x,y),Serves(z,y), not Frequents(x,z)Q(x) :- Likes(x,y), not H(x,y)

H(x,y)

DukeCS,Fall2016

Step 4: Write it in SQL

SELECT DISTINCT L.drinker FROM Likes LWHERE ……

SELECT DISTINCT L.drinker FROM Likes LWHERE not exists

(SELECT * FROM Likes L2, Serves SWHERE … …)

DukeCS,Fall2016

(SELECT * FROM Likes L2, Serves SWHERE L2.drinker=L.drinker and L2.beer=L.beer

and L2.beer=S.beerand not exists (SELECT * FROM Frequents F

WHERE F.drinker=L2.drinkerand F.bar=S.bar))

Sometimes can simplify the SQL query by using an unsafe datalog ruleCorrectness ensured by safe outermost rule

(SELECT * FROM Serves SWHERE L.beer=S.beer

and not exists (SELECT * FROM Frequents FWHERE F.drinker=L.drinker

and F.bar=S.bar))

H(x,y) :- Likes(x,y),Serves(z,y), not Frequents(x,z)Q(x) :- Likes(x,y), not H(x,y) Unsafe rule

DukeCS,Fall2016

RC→Datalog withnegation→SQL(8/8)

AnOverviewofDataProvenancewithAnnotations

Selected/adaptedslidesfromthekeynotebyProf.ValTannen,EDBT2010

(optionalmaterial:fullslidedeckisavailableonVal’swebpage)

Optional/additionalslides

Lineage

• [Cui-Widom-Wiener’00]• Lineage:

– Givenadataitemintheview– Determinethesourcedatathatproducedit– Theprocessbywhichitwasproduced

optionalslide

Applications

• OLAP/OLAM(mining)– originofanomalousdatatoverifyreliability

• ScientificDatabases– howanswerwasproducedfromrawdata

• OnlineNetworkmonitoringandDiagnosissystem– identifyfaultysensorfromnetworkmonitors

optionalslide

Applications

• Cleanseddatafeedback– Cleanrawdataandsendreporttosources

• Materializedviewschemaevolution– ifviewschemaischanged(newcolumnadded),recomputation maynotbenecessary

• Viewupdate– translateviewupdatestobasedataupdates

optionalslide

SlidebyValTannen,EDBT2010

optionalslide

HasAsthma

Friend

Ann Joe

Ann Tom

Bob Tom

Smoker

Booleanquery Q():- HasAsthma (x),Friend(x,y),Smoker(y)

• x,y,z∈ {0,1}• 1=present• 0 =absent• Therearethreealternativewaystoderivethe“true”answer

y1y2y3

ProvenanceExample

ProvenanceFQ,D =x1y1z1 +x1y2z2 +x2y3z2

optionalslide

HasAsthma

Friend

Ann Joe

Ann Tom

Bob Tom

Smoker

Booleanquery Q:$ x$ yHasAsthma (x)Ù Friend(x,y)Ù Smoker(y)

• x,y,z∈ {0,1}

• WhathappensifAnnisdeleted?– Doestheanswerchangetofalsefromtrue?

y1y2y3

Applicationto“DeletionPropagation”

ProvenanceFQ,D =x1y1z1 +x1y2z2 +x2y3z2 [Greenetal.’07]

optionalslide

HasAsthma

Friend

Ann Joe

Ann Tom

Bob Tom

Smoker

Booleanquery Q:$ x$ yHasAsthma (x)Ù Friend(x,y)Ù Smoker(y)

• x,y,z∈ {0,1}

• WhathappensifAnnisdeleted?– Doestheanswerchangetofalsefromtrue?

• Noneedtore-evaluatethequery– justpluginx1=0andevaluate

y1y2y3

Applicationto“DeletionPropagation”

ProvenanceFQ,D =x1y1z1 +x1y2z2 +x2y3z2

[Greenetal.’07]

optionalslide

compsci516 data intensive computing systems - cs.duke.edu · duke cs, fall 2016 compsci 516: data...

Documents

d -intensive computing paradigms for big data

petascale data intensive computing for escience

compsci516 data intensive computing systems ·...

data intensive computing information based computing digital...

data-intensive scientific computing in astronomy

compsci516 data intensive computing systems lecture 6a...

eece 571r: data-intensive computing systems

condor compatible tools for data intensive computing

cyber analytics applications for data-intensive computing

data intensive computing frameworks

cps 516 : data-intensive computing systems

compsci516 data intensive computing systems ·...

data-intensive computing systems · 2016-12-06 ·...

on data intensive computing and exascale

compsci516 database systems - duke university€¦ ·...

integrating data-intensive computing systems with

map reduce for data-intensive computing

compsci516 data intensive computing systemscompsci516 data...

compsci516 data intensive computing systems lecture 7...