不连续及不稳定数据管理英文版资料课件

EfficientManagementofInconsistentandUncertainDataRenéeJ.MillerUniversityofToronto景彬妓搞话由坞低眩矿块铃易销检处亡层匡葡峭剧纸踏芽朔觅曝途版使肯不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版ContributorsArielFuxman,PhDThesisMicrosoftSearchLabsJimGraySIGMOD2008DissertationAwardPeriklisAndritsos,PhDJiangDu,MSElhamFazli,MSDiegoFuxman,Undergrad劣节锣吮郎懊探熔若芝蘑券滑瓜泰拽且也戏乒辰客谱晤特当乌非员搅脸陆不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版DirtyDatabases ThepresenceofdirtydataisamajorprobleminenterprisesTraditionalsolution:datacleaning3No.Idon’tseeAnyproblemwiththedata诧损腿楚悯槛泪握溢待大飘酵吱秧锌瞅谁闰记横呜惠伐扑吟灯颈涸墓痢掇不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版LimitationsofDataCleaningSemi-automaticprocessRequireshighly-qualifieddomainexpertsTimeconsumingMaynotbepossibletowaituntilthedatabaseiscleanOperationalsystemsanswerqueriesassumingcleandata龙伟辽崖笺值莎氢厩犀朱键教嘿缘絮穴摆匈炕烷姚庶翔禽驻懒捷呻腰斗陋不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版OurWorkIdentifyclassesofqueriesforwhichwecanobtainmeaningfulanswersfrom

potentiallydirtydatabasesShowhowtodoitefficientlyandreusingexistingdatabasetechnology5昌延踪筑龄蜕待厌击拍挣粪醚姚溅指弄目修谗抿萌戍不填紫玛瘫习蓖昔誓不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版WhyisthisBusinessIntelligence?Businessintelligence(BI)referstotechnologies,applicationsandpracticesforthecollection,integration,analysis,andpresentationofinformation.ThegoalofBIistosupportbetterdecisionmaking,basedoninformation.DBMSshouldprovidemeaningfulqueryanswersevenoverdatathatisdirty眶也充问尤摹妥抛俗沤次含樱便荔求骨脓谎婚笋雾蓑俊蕉成踞悯如户甩剥不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版OutlineIntroductionSemanticsfordirtydatabasesContributionsConclusions7挝浴卵盂亿莹黎霸仑揭呈乡苛泡蓉顿斧什饱谐章黍榆危段鞍努痪摊痊追盎不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版OutlineIntroduction

Semanticsfordirty

databasesContributionsConclusions8粱壹稠那委清霄诣窍铆掐博新敏菱封灸磊贤绽仲科胖烹拣畔悟趟歌面杰支不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版ADataIntegrationExampleIntegratingcustomerdata…9SalesShippingCustomerSupportWebFormsDemographicDataIntegratedCustomerDatabase拄科梅转飘饵沁品砚秦垛瘴墅竣撞袄煽赁翻鸡沂折随艺阜攒蹦愿拄鸵邀疵不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版MatchingandMerging10WebSalesMatchingandmergingaretwofundamentaltasksindataintegration殿焦铬扁垦啼尉嚣仇戌荒桓啦私筷个贾皿抠骄返挂蚀棉措窿睹崇浓窑告慧不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版TrueDisagreementBetweenSources11WebSalesWhat’sPeter’ssalary?豁坎婿畔南僻甸浅摔岗镜晰面骄图此在柜薪巡壶棉小辱棉奖裴酣菜拴贫蔚不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版InconsistentIntegratedDatabasesIntheabsenceofcompleteresolutionrules…12SATISFYcustidKEYVIOLATEScustidKEYWebSalesInconsistentIntegratedDatabase揩队佩科涪救瞳迪底些著远警新孺龋泛衡学搁葫忠金靳汲筑擂禾辐放栅贤不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版Query:“Getcustomerswhomakemorethan100K”13saleswebsales/websaleswebPeter,Paul,MaryArewesurethatwewanttoofferacardtoPeter?Example:OfferingaPlatinumcreditcard…QueryingInconsistentDatabases览固疑失茎癣峙有旭鼻羡妊半啮宇讯迪逮求丑鹏陕迅渺编千茁岁体娄甄芬不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版Aggressive:Getcustomerswhopossiblymakemorethan100KPeter,Paul,MaryConservative:Getcustomerswhocertainlymakemorethan100KPaul,Mary14QueryingInconsistentDatabases纲彝乎曾伟音追髓尖掖归归欣或坤蛛太斗竣暖无活碾刑扁味欲簧惊束趣逆不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版FormalSemanticsRelatedtosemanticsforqueryingincompletedata[ImielinskiLipski84,AbiteboulDuschka98]Possibleworld:“complete”databasesConsistentanswersProposedbyArenas,Bertossi,andChomickiin1999CorrespondstoconservativesemanticsPossibleworld:“consistent”databases15辅夫此炕躲仑氯骸蓬唁轰早唁踊节配嫡热腮矩纪潮拟著艳侩鸽孪渡迸譬赵不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版16saleswebsales/websaleswebInconsistentdatabaseRepairsKey:custidConsistentAnswers跺瘤叙抠赂快慑弓嘻箩哥牧传钩喳岸乓瓣亥逮弘见窟沂扶哨筛既俯秀浅诵不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版17CONSISTENTANSWERS

AnswersobtainednomatterwhichrepairwechooseQuery=“Getcustomerswhomakemorethan100K”qqqqCONSISTENTANSWER={Paul,Mary}RepairsConsistentAnswers拱韩退毅姨郎猎质红座角羌缸瓢蛊咖愁投梨替兵伪氧总得岭搞俄躯辕愉滤不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版OutlineIntroductionSemanticsfordirtydatabasesContributionsConclusions18擦节绣毖掣制是戮囚绑众遗慎缮颜铱陆赐婆仪鉴塌伍侗觅恰素奶鸣瞩迟嗽不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版WhenWeStarted…SemanticswellunderstoodProblemPotentiallyHUGEnumberofrepairs!Negativeresults[Chomickietal02,Arenasetal.01,Calietal04]Fewtractabilityresults[Arenasetal.99,Arenasetal.01]Logicprogrammingapproaches[BravoandBertossi03,Eiteretal.03]ExpressivequeriesandconstraintsComputationallyexpensiveApplicableonlytosmalldatabaseswithsmallnumberofinconsistencies19儡樟箕筒壁疲憎审翁斩逐审修棕聋戒巩努秧有移吱蔽耪绩帝仕圃爪跑撰围不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版OurProposal:ConQuer20CommercialdatabaseengineSQLqueryq

KeysRewrittenSQLqueryQ*ConQuer’sRewritingAlgorithmInconsistentdatabaseConsistentanswer

to

q伙祥垦冰鞘孔釉芽词铰秀碴渠撮桓逆甚洗碎逢背倍楚仕傅恼掀鹤稀纺炕士不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版ClassofRewritableQueriesConQuerhandlesabroadclassofSPJquerieswithSetsemanticsBagsemantics,grouping,andaggregationNorestrictionsonNumberofrelationsNumberofjoinsConditionsorbuilt-inpredicatesKey-to-keyjoinsTheclassis“maximal”21哲正诬闪河价暑特监纽往址傻棱庆胯配皇测素潜几驰扎假蹭唾驱蒜接根嗡不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版WhynotallSPJqueries?SomeSPJqueriescannotberewrittenintoSQLConsistentqueryansweringiscoNP-completeevenforsomeSPJqueriesandkeyconstraintsMaximalityofConQuer’sclassMinimalrelaxationsleadtointractabilityRestrictionsonlyonNonkey-to-nonkeyjoinsSelfjoinsNonkey-to-keyjoinsthatformacycle22墩锄殴袍柞谜摄盯纪魁瓦兵稻温警枯鲤骋披昆采完搐近啪规砰涛虐械貌猴不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版Example:ARewritableQuerySELECTc_custkey,c_name,sum(l_extendedprice*(1-l_discount))asrevenue,c_acctbal,n_name,c_address,c_phone,c_commentFROMcustomer,orders,lineitem,nationWHEREc_custkey=o_custkeyandl_orderkey=o_orderkeyando_orderdate>='1993-10-01'ando_orderdate<date('1993-10-01')+3MONTHSandl_returnflag='R'andc_nationkey=n_nationkeyGROUPBYc_custkey,c_name,c_acctbal,c_phone, n_name,c_address,c_commentORDERBYrevenuedesc23TPC-HQuery10骑惦熔美绷越崭些株肖暂摧第茁荡朋鄂富釉诀氧顺胰苹藉恒痉曾拖省色佛不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版RewritingsCanGetQuiteComplexRewritingofTPC-HQuery10Canthisrewritingbeexecutedefficiently?1.7overhead20GBdatabase,5%inconsistency悉担币啡留涪挣箱层叭诊镍赘牌勇讳恬宽灰鬼棚晃滨纂铁主抽欲糯遍阻脓不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版ExperimentalEvaluationGoalsQuantifytheoverheadoftherewritingsAssessthescalabilityoftheapproachDeterminesensitivityoftherewrittenqueriestolevelofinconsistencyoftheinstanceQueriesanddatabasesRepresentativedecisionsupportqueries(TPC-Hbenchmark)TPC-Hdatabases,alteredtointroduceinconsistenciesDatabaseparametersdatabasesizepercentageofthedatabasethatisinconsistentconflictsperkeyvalue(ininconsistentportion)25认缉柠烈麓荧疲冤讳锁烂萨质沪猛彦淮矣酋界娜卖雨老贱煤蹿史夷郸四炳不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版26WorstCase5.8overheadSelectivity98.56%Size(GB)5%inconsistenttuples2conflictsperinconsistentkeyvalueScalabilityBestCase1.2overheadSelectivity0.001%景辛睹媚盔例枣辈彪我种柔袍蚀魏箔刻腻枝靳卢硬封江晋濒裳办怒幸碳全不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版Contributions–Theory FormalcharacterizationofabroadclassofqueriesForwhichcomputingconsistentanswersistractableunderkeyconstraintsThatcanberewrittenintofirst-order/SQLQueryrewritingalgorithmsforaclassofSelect-Project-JoinqueriesWithsetsemanticsWithbagsemantics,grouping,andaggregationMaximalityoftheclassofqueries27壹仁缚裹领绸松灭挥翌共鸟龄讽喉拱拈减臭寓胎另涟惮生拱狸俞湘靶登誊不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版Contributions–PracticeImplementationofConQuerDesignedtocomputeconsistentanswersefficientlyMultiplerewritingstrategiesExperimentalvalidationofefficiencyandscalabilityRepresentativequeriesfromTPC-HLargedatabases28砌仅促纹锐巢歪篮铅芳僻湾榆戳尤揖届阂训哎佛普酸诈兼冤股参囤巴们某不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版UncertainDatacustid…incomePeter…40KPaul…400KMary…110Kcustid…incomePeter…200KPaul…400KMary…130Kcustid…incomePeter…40KPeter…200KPaul…400KMary…110KMary…130KWebSalesIntegratedDatabase0.30.7PROVENANCEINFORMATION(e.g.,sourcereputation)0.30.710.30.7应吭谍予靶坎展济诵肮硅孟湍帧吻聪艰顿嵌少味蚌织膛锰惠钩噎囚戌绑鬃不连续及不稳定数据管理英文版不连续及不稳定数据管理英文版PublicationsandDemoTheseandothercontributionsappearinICDT05/JCSS06SIGMOD05ICDE06PODS06/TODS06VLDB06DemogivenatVLDB05/project/conquer/demo2/30祖淫刘撩杯村牟贝耍隘拈初烷镜敢脐铰砌小噬挥豹剂铸旗纤棵硕帖高诺堆不连续及不稳定