Query-Time Data Integration

195 Pages·2015·2.56 MB·English

Query-Time Data Integration
Dissertation
zur Erlangung des akademischen Grades
Doktoringenieur (Dr.-Ing.)
vorgelegtander
TechnischenUniversitätDresden
FakultätInformatik
eingereichtvon
Dipl. Medien-Inf. JulianEberius
geborenam28. September1987inDresden
Gutachter: Prof.Dr.-Ing.WolfgangLehner
TechnischeUniversitätDresden
FakultätInformatik,InstitutfürSystemarchitektur
LehrstuhlfürDatenbanken
01062Dresden
Prof.Dr.-Ing.ErhardRahm
UniversitätLeipzig
FakultätMathematikundInformatik,InstitutfürInformatik
AbteilungDatenbanken
04109Leipzig
TagderVerteidigung: 10. Dezember2015
Dresden,imOktober2015
ii
ABSTRACT
Today,dataiscollectedineverincreasingscaleandvariety,openingupenormouspotentialfor
newinsightsanddata-centricproducts.However,inmanycasesthevolumeandheterogeneity
of new data sources precludes up-front integration using traditional ETL processes and data
warehouses. Insomecases,itisevenunclearifandinwhatcontextthecollecteddatawillbe
utilized. Therefore,thereisaneedforagilemethodsthatdefertheeffortofintegrationuntil
theusagecontextisestablished.
ThisthesisintroducesQuery-TimeDataIntegrationasanalternativeconcepttotraditional
up-frontintegration. Itaimsatenablinguserstoissuead-hocqueriesontheirowndataasif
allpotentialotherdatasourceswerealreadyintegrated,withoutdeclaringspecificsourcesand
mappings to use. Automated data search and integration methods are then coupled directly
withqueryprocessingontheavailabledata.Theambiguityanduncertaintyintroducedthrough
fully automated retrieval and mapping methods is compensated by answering those queries
with ranked lists of alternative results. Each result is then based on different data sources or
queryinterpretations,allowinguserstopicktheresultmostsuitabletotheirinformationneed.
To this end, this thesis makes three main contributions. Firstly, we introduce a novel
method for Top-kEntityAugmentation, which is able to construct a top-k list of consistent in-
tegrationresultsfromalargecorpusofheterogeneousdatasources. Itimprovesonthestate-
of-the-artbyproducingasetofindividuallyconsistent,butmutuallydiverse,setofalternative
solutions, while minimizing the number of data sources used. Secondly, based on this novel
augmentation method, we introduce the DrillBeyond system, which is able to process Open
World SQL queries, i.e., queries referencing arbitrary attributes not defined in the queried
database. TheoriginaldatabaseisthenaugmentedatquerytimewithWebdatasourcespro-
vidingthoseattributes. Itshybridaugmentation/relationalqueryprocessingenablestheuseof
ad-hoc data search and integration in data analysis queries, and improves both performance
andqualitywhencomparedtousingseparatesystemsforthetwotasks.Finally,westudiedthe
managementoflarge-scaledatasetcorporasuchasdatalakesorOpenDataplatforms, which
areusedasdatasourcesforouraugmentationmethods. WeintroducePublish-timeDataInte-
gration as a new technique for data curation systems managing such corpora, which aims at
improvingtheindividualreusabilityofdatasetswithoutrequiringup-frontglobalintegration.
Thisisachievedbyautomaticallygeneratingmetadataandformatrecommendations,allowing
publisherstoenhancetheirdatasetswithminimaleffort.
Collectively,thesethreecontributionsarethefoundationofaQuery-timeDataIntegration
architecture,thatenablesad-hocdatasearchandintegrationqueriesoverlargeheterogeneous
datasetcollections.
iii
iv
ACKNOWLEDGEMENTS
Firstofall,IwanttothankmyadvisorProfessorWolfgangLehnerforgivingmetheopportunity
toworkinhisgroup,pursuemyresearchandwritethisthesis.Heencouragesandmaintainsan
atmosphereoftraditionalacademicfreedom,givingpeoplethelibertyandtimetothink,and
todeveloptheirwork. Thiscan, byallaccounts, notbetakenforgrantedintoday’sresearch
landscape.Atthesametime,hebringsdiscussion,contacts,andallkindsofopportunitiesinto
thegroup,andisalwaystheretosupporthisstudents.I’mgratefulthatIcouldspendfouryears
withthecolorfulgroupofindividualsheassembled.
Which brings me to the next point: my colleagues. I want to express my thanks to all of
them,startingwiththosethatIworkedmostcloselywith. FromourfirststepsinOpenData
anddo-it-yourselfmashupresearch, toourfinalbattleswiththeintricaciesofWebdatainte-
gration, the members of the original junior research group Edyra, Katrin Braunschweig and
MaikThiele, havecontributedthemosttomythesis. Thoughtheword“OpenData”quickly
becamethetargetofsomeridiculebythegroup’sjesters(butwhatwasnot?), wepersevered
andproducedrespectableresultsthatdonotneedtobehiddenfromanybody.ThankstoUlrike
Fischer and Benjamin Schlegel, with whom I shortly shared the best room of the building in
afriendlyandeasy-goingatmosphere. IwanttogivespecialthankstoMartinHahmann,with
whomIsharedanofficeformostofthesecondhalfofmystayinthegroup.Heiseasytolaugh
with but also available for more serious discussion about all aspects of life. Next, I want to
thankallothercolleaguesthatfrequentlyhadtoenduremyneedforcommunication(orrant-
ing),eventhoughtheyhadworkoftheirowntodo: ClaudioHartmann,HannesVoigt,Robert
Ulbricht, andTimKiefer. Ialsowanttothankallothercolleaguesthatatsomepointhelped
creatingthefunandcreativeatmosphereinthegroup:Ahmad,Annett,Bernd,Bernhard,Dirk,
Elena,Frank,Gunnar,Ines,Johannes,Juliana,Kai,Kasun,thetwoLarses,Laurynas,Matthias,
Patrick, Peter, Rihan, Steffen, Thomas, Till, Tobias, Tomas, Ulrike Schöbel, and Vasileios. I
wanttofurtherthankthestudentsthatcontributedtomydoctoralwork,ChristopherWerner,
PatrickDamme,MihailMaxacov,PaulPeschelandMarkusHentsch.
I am very grateful to Professor Erhard Rahm for co-refereeing this work. I also want to
thankoneofhisdoctoralstudents,EricPeukert,who,inhisfunctionasmyDiplomaadvisor,
introducedmetothescienceaspectsofcomputerscience,andsentmeonmywaytoadoctoral
work in this group. Special thanks also to Esther Schiwek for interdisciplinary co-working
whilewritingthisthesisandforprovidingmedicalbackgroundinformation. Iwanttothank
MaikThieleonemoretime, fortirelesslyandtimelyreviewingandproof-readingthisthesis,
offeringfeedbackandalwaysencouragingmewhenIhaddoubts.
Finally,Iwanttothankmyfamily,especiallymyparentandmybrothers,forgivingmethe
backgroundandtheopportunitiesthatledmetoundertakethisworkinthefirstplace,andfor
theircontinuedsupportthroughouttheyears.TheyneverhadanydoubtthatIwoulddoit,and
supportedmeinanywaytheycould. Iwanttothankmyownnewfamily, mychildrenMaia
andLorenzformakingmelaughandgivingmyworkpurpose,andmostimportantlymyfiancée
Katharina. Especiallyinthelastmonthsofwritingthisthesis,shekepteverythingaroundme
running,andbackedmeupineverywaypossibletoallowmetofinishthisthesis.
ForthatIwillalwaysbegrateful.
JulianEberius
October15,2015
v
vi
CONTENTS
1 INTRODUCTION 1
1.1 ChallengesinAgileDataIntegration . . . . . . . . . . . . . . . . . . . 2
1.2 Query-timeDataIntegration . . . . . . . . . . . . . . . . . . . . . . . 4
1.2.1 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2.2 QueryModel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.2.3 ArchitectureandThesisContributions . . . . . . . . . . . . . . . . . 9
1.2.4 Outline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
2 TOP-K ENTITY AUGMENTATION 13
2.1 MotivationandChallenges . . . . . . . . . . . . . . . . . . . . . . . . 15
2.2 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.3 Top-kConsistentEntityAugmentation . . . . . . . . . . . . . . . . . 20
2.3.1 EntityAugmentationQueries . . . . . . . . . . . . . . . . . . . . . . 20
2.3.2 RankedConsistentSetCovering . . . . . . . . . . . . . . . . . . . . 24
2.3.3 BasicGreedyAlgorithm . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.3.4 Extension:Greedy* . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.3.5 Extension:GeneticAlgorithm . . . . . . . . . . . . . . . . . . . . . 30
2.4 TheREASystem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
2.4.1 WebtablesRetrievalandMatching . . . . . . . . . . . . . . . . . . . 33
2.4.2 RelevanceScoring . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
2.4.3 WebtableSimilarity . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2.5 Evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2.5.1 ExperimentalSetup . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2.5.2 BaselineAlgorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
2.5.3 EvaluatingEntityCoverage . . . . . . . . . . . . . . . . . . . . . . . 39
2.5.4 EvaluatingSingleEntityPrecision . . . . . . . . . . . . . . . . . . . 41
vii
2.5.5 EvaluatingCoverQuality . . . . . . . . . . . . . . . . . . . . . . . . 42
2.5.6 ManualCoverQualityVerification . . . . . . . . . . . . . . . . . . . 45
2.5.7 EvaluatingRuntimePerformance . . . . . . . . . . . . . . . . . . . . 47
2.5.8 QualityofRelevanceandSimilarityFunctions . . . . . . . . . . . . . 49
2.6 RelatedWork . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2.6.1 WebTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2.6.2 EntityAugmentation . . . . . . . . . . . . . . . . . . . . . . . . . . 51
2.6.3 MashupsTools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
2.6.4 SetCovering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
2.6.5 DataFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
2.6.6 ResultDiversification . . . . . . . . . . . . . . . . . . . . . . . . . . 58
2.7 SummaryandDiscussion . . . . . . . . . . . . . . . . . . . . . . . . . 59
3 OPEN-WORLD SQLQUERIES 61
3.1 MotivationandChallenges . . . . . . . . . . . . . . . . . . . . . . . . 62
3.2 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
3.3 TheDrillBeyondSystem . . . . . . . . . . . . . . . . . . . . . . . . . . 67
3.3.1 SystemArchitecture . . . . . . . . . . . . . . . . . . . . . . . . . . 68
3.3.2 TheDrillBeyondOperator . . . . . . . . . . . . . . . . . . . . . . . 70
3.3.3 AugmentationGranularity . . . . . . . . . . . . . . . . . . . . . . . 73
3.3.4 Context-dependentResults . . . . . . . . . . . . . . . . . . . . . . . 73
3.3.5 PushingSQLquerycontext . . . . . . . . . . . . . . . . . . . . . . . 75
3.3.6 CostModel&InitialPlacementStrategy . . . . . . . . . . . . . . . . 76
3.4 ProcessingMulti-ResultQueries . . . . . . . . . . . . . . . . . . . . . 78
3.4.1 InvariantCaching . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
3.4.2 SeparatingAugmentationInputandOutput . . . . . . . . . . . . . . 80
3.4.3 SelectionPull-Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
3.4.4 ProjectionPull-up . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
3.4.5 PartialSelection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
3.4.6 RuntimeReoptimization . . . . . . . . . . . . . . . . . . . . . . . . 85
3.5 Evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
3.5.1 ExperimentalSetup . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
3.5.2 Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
3.5.3 AugmentationQuality . . . . . . . . . . . . . . . . . . . . . . . . . 92
viii CONTENTS
3.6 RelatedWork . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
3.6.1 Self-serviceBIusingWebData . . . . . . . . . . . . . . . . . . . . . 94
3.6.2 Open/ClosedWorldAssumptionandNullValueSemantics . . . . . . . 95
3.6.3 HybridDB/IRSystems . . . . . . . . . . . . . . . . . . . . . . . . . 96
3.6.4 FacilitatingSQLQueryFormulation . . . . . . . . . . . . . . . . . . 97
3.6.5 Multi-resultQueryProcessing . . . . . . . . . . . . . . . . . . . . . 98
3.6.6 UncertaintyandLineageinDatabases . . . . . . . . . . . . . . . . . 100
3.6.7 Adaptive-AndMulti-QueryOptimization . . . . . . . . . . . . . . . 101
3.7 SummaryandDiscussion . . . . . . . . . . . . . . . . . . . . . . . . . 102
4 PUBLISH-TIME DATA INTEGRATION 103
4.1 AStudyofExistingOpenDataPlatforms . . . . . . . . . . . . . . . . 105
4.1.1 StructureandMethodology . . . . . . . . . . . . . . . . . . . . . . . 106
4.1.2 PlatformClassificationandSurveyResults . . . . . . . . . . . . . . . 108
4.1.3 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
4.2 Publish-timeDataIntegration . . . . . . . . . . . . . . . . . . . . . . . 113
4.2.1 ImprovingReusabilityinDataCurationSystems . . . . . . . . . . . . 113
4.2.2 PrinciplesofPublish-timeDataIntegration . . . . . . . . . . . . . . . 115
4.2.3 Publish-timeDataIntegrationOperators . . . . . . . . . . . . . . . . 118
4.3 AttributeNameRecommendations . . . . . . . . . . . . . . . . . . . 119
4.3.1 SystemOverview . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
4.3.2 DomainsandDomain-Classification . . . . . . . . . . . . . . . . . . 121
4.3.3 NameRecommendationAlgorithms . . . . . . . . . . . . . . . . . . 122
4.3.4 ExperimentalEvaluation . . . . . . . . . . . . . . . . . . . . . . . . 126
4.3.5 Conclusions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
4.4 ExtractingDatafromPartiallyStructuredDocuments . . . . . . . . 132
4.4.1 SpreadsheetNormalization . . . . . . . . . . . . . . . . . . . . . . . 134
4.4.2 TheDeExcelerator . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
4.4.3 Evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
4.4.4 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
4.5 RelatedWork . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
4.5.1 SchemaMatching,EntityResolution,DataFusion . . . . . . . . . . . 147
4.5.2 IterativeandUser-drivenIntegrationandCleaning . . . . . . . . . . . 148
4.5.3 DataspaceSystemsandPay-as-you-goIntegration . . . . . . . . . . . . 149
CONTENTS ix
4.5.4 CrowdsourcedandCommunity-drivenDataIntegration . . . . . . . . 151
4.5.5 LinkedOpenData. . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
4.5.6 AttributeSynonymDiscovery . . . . . . . . . . . . . . . . . . . . . . 153
4.5.7 SpreadsheetDataManagement . . . . . . . . . . . . . . . . . . . . . 154
4.6 SummaryandDiscussion . . . . . . . . . . . . . . . . . . . . . . . . . 155
5 CONCLUSION AND FUTURE WORK 157
5.1 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
5.2 FutureWork . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
x CONTENTS