Human-in-the-loop Data Integration Guoliang Li Department of - - PowerPoint PPT Presentation

Human-in-the-loop Data Integration

Guoliang Li

Department of Computer Science, Tsinghua University, China http://dbgroup.cs.tsinghua.edu.cn/ligl

Acknowledgement

2 Jianhua Feng @Tsinghua Lizhu Zhou @Tsinghua Chen Li @UCI Beng Chin Ooi @NUS

Acknowledgement

3

Jiannan Wang aP@SFU Dong Deng PostDoc@MIT On Job Market! Yudian Zheng @HKU Ju Fan AP@RUC

Thank everyone who support me! ü Collaborators ü Students ü Friends ü ......

Data Integration (DI)

Combine data in different sources and provide users with a unified view

4

Brand Product Region Price Apple iPhone6S Beijing 4000 Apple iPhone6SP Beijing 5000 Samsung Galaxy S7 Beijing 3500 Name Loc Sales 6S 4.7’ Bei Jing 40K 6S 5.5’ Bei Jing 30K S7 Bei Jing 35K

Brand Product Loc Price Sales Apple iPhone6S Beijing 4000 40K Apple iPhone6SP Beijing 5000 30K Samsung Galaxy S7 Beijing 3500 35K

Data Analysis Data Integration Is there any correlation between location and revenue?

Data Science Pipeline: Data Integration à Data Analysis

Data Integration (DI)

pData Integration is important and challenging

– New York Times

• 80% of a data science project is to clean and integrate

the data, while 20% is actual data analysis – Mark Schreiber of Merck

• data scientists spend 98% of
• n “grunt work” and
• only one hour per week
• n “useful work”

pIn many communities

– DB, AI, KDD, Web

5

Entity Matching in DI

pDate Integration

– data acquisition, extraction, cleaning, schema matching, entity matching, etc.

pEntity Matching (EM): A core problem

– Find pairs of records referring to the same entity

6

Brand Product Region Price Apple iPhone6S Beijing 4000 Apple iPhone6SP Beijing 5000 Samsung Galaxy S7 Beijing 3500 Name Loc Sales Apple 6S 4.7’ Bei Jing 40K Apple 6S 5.5’ Bei Jing 30K Samsung S7 Bei Jing 35K

• Data are full of errors

and inconsistencies.

Hybrid Human-Machine EM

DIMA System

pSimilarity-based processing system pEntity Matching pEase to use

7

CDB System

pCrowd-powered SQL pNew optimization Model pEntity Matching pCost, Latency, Quality

Name Model Size 6S iPhone 5.7 S7 Samsung 5.7 Product Type Color iPhone6 6th red Samsung 7gen white

R S

Rule-based Candidate Generation

Rule Generation Signature-based Filtering Verification Rules

Candidate pairs

Crowd-based Refinement

Graph Model Tuple-Level Cost Control Round-based

Latency Control

Unified Quality Control

Matching pairs

Hybrid Human-Machine EM

8

iPhone6S iPhone6SP Galaxy S7 iPhone 6S 4.7’ iPhone 6S 5.5’ Samsung S7 iPhone 6S iPhone 6S 4.7’ 0.75 iPhone 6S iPhone 6S 5.5’ 0.75 iPhone 6SP iPhone 6S 4.7’ 0.72 iPhone 6SP iPhone 6S 5.5’ 0.72 Galaxy S7 Samsung S7 0.5 iPhone6S Samsung S7 0.1 iPhone6S Samsung S7 0.1 Galaxy S7 iPhone 6S 4.7’ 0.1 Galaxy S7 iPhone 6S 5.5’ 0.1

Machine-Based Algorithms

Pruning dissimilar pairs

Crowd-Based Algorithms

Asking crowd to label some pairs

iPhone 6S iPhone 6S 4.7’ iPhone 6SP iPhone 6S 5.5’ Galaxy S7 Samsung S7

Pruning 4 dissimilar pairs Removing 2 non-matched pairs

Two tables of records candidate record pairs matching record pairs

Jaccard Edit distance Semantics

Dima: Distributed In-memory

Similarity-based System

9

Query interface ü Extended SQL ü Easy to use Distributed In-memory Processing Engine ü Indexing ü Similarity Operations ü Optimizer Support similarity-based query processing

Ji Sun, Zeyuan Shang, Guoliang Li, Dong Deng, Zhifeng Bao. Dima: A Distributed In-Memory Similarity-Based Query Processing System. VLDB 2017

RDBMS HDFS Native RDD Spark Local Indexing Global Indexing Similarity-based Query Optimizer simSQL Parser DataFrame API CLI JDBC Scala Program Similarity-based Query Operations

CDB: A Crowd-powered Database

10

MetaData

Task Worker

Crowdsourcing Platforms Relational Database

Graph-Based Query Model

Assignment

CQL Parser Result Collection

Query Optimization

Cost Control Latency Control Quality Control

!"#$ %##&'()*(+ !,-+. /(0*,*(1*

Crowd UI Designer Statistics

Guoliang Li, Chengliang Chai, Ju Fan, Jian Li, Yudian Zheng. CDB: A Crowd- Powered Database. SIGMOD 2017.

Fine-grained Tuple-level Graph model Multi-goal

• ptimization

EaseCrowd ChinaCrowd

DIMA: Rule-Based Matching

Jaccard(Name, Brand) ≥0.8 ∧ ED(Storage, Capacity)<2

11

Brand Capacity Price Apple iPhone6S 64 4000 Apple iPhone6SP 128G 5000 Samsung Galaxy S7 64G 3500 Name Storage Sales Apple 6S 4.7’ 64GB 40K Apple 6S 5.5’ 128GB 30K Samsung S7 64GB 35K

Jiannan Wang, Guoliang Li, Jeffrey Xu Yu, Jianhua Feng: Entity Matching: How Similar Is

• Similar. VLDB, 2011:622-633.

Name is similar to Brand Storage is similar to Capacity

DIMA: Rule-Based Matching

p Challenges

– How to obtain the rules?

• High-quality rules
• Explainable, Programmable

– How to apply the rules?

• Avoid Cartesian product
• Fast and Scalable

12 Threshold Similarity Functions Attribute pairs

Quantifying Rules

Objective function: |MΨ∩M|- |MΨ∩N| Goal: Find a rule set Ψ to maximize |MΨ∩M|-|MΨ∩N|

13

M: positive examples N: negative examples Mφ : record pairs that satisfy a rule φ MΨ: record pairs that satisfy a rule set Ψ={φ}

MΨ∩M MΨ∩N

Venue

Computer Science Chemical Sciences

…

Chemical Sciences (general)

…

Database

…

VLDB SIGMOD

…

RSC Advances

…

System ICPADS

…

Rule Generation

14

Threshold Similarity Functions Attribute pairs

a,b; Attributes NP-hard

Effective Algorithm

F

String: edit distance Set: Jaccard Knowledge

δ: [0,1]

Infinite à Finite ü F(a,b) on attribute pairs

Rule-Based Matching

p Challenges

– How to obtain the rules?

• High-quality rules
• Explainable, Programmable

– How to apply the rules?

• Avoid Cartesian product
• Fast and Scalable

15 Threshold Similarity Functions Attribute pairs

Applying Rules

16

It is expensive to enumerate every record pairs! ü 10million*10million Signature-based Method ü If two records do not share a common signature, they cannot be matching

1 2 3 1 2 1 3 1 2 3 1

Guoliang Li, Dong Deng, Jiannan Wang, Jianhua Feng: Pass-Join: A Partition-based Method for Similarity Joins. VLDB, 2012:253-264.

A Rule: Name is similar to Brand

Dima - Signature

17

RDBMS HDFS Native RDD Spark Local Indexing Global Indexing EM Query Optimizer EMSQL Parser DataFrame API CLI JDBC Scala Program EM Query Operations Balance-Aware Signature Generation

Signature-based Method ü If two records do not share a common signature, they cannot be matching Balance-Aware Signature ü The signatures are selectable ü Balance the workload

1 2 3 1 2 1 3 2 3 1 4

Dong Deng, Guoliang Li, He Wen, Jianhua Feng. An Efficient Partition Based Method for Exact Set Similarity Joins. VLDB, 2016

Dima: Load Balance

18

Challenges pHow to generate signatures?

– Partition-based

pHow to select the signatures?

– Dynamic programming

pHow to balance the workload?

– NP-hard – Greedy algorithms

Wj =

ηl

X

i=1

⇣ bi X

g2pSig+

s,i,l&P(g)=j

F [g]+ ci X

g2pSig−

s,i,l&P(g)=j

• F +[g] +

X

g2pSig+

s,i,l&P(g)=j

F [g] + F +[g] ⌘ bi = ( 1 Z[i] = 1 Z[i] 6= 1 ci = ( 1 Z[i] = 2 Z[i] 6= 2 s.t.

ηl

X

i=1

Z[i] θ|s|,l.

RDBMS HDFS Native RDD Spark Local Indexing Global Indexing EM Query Optimizer EMSQL Parser DataFrame API CLI JDBC Scala Program EM Query Operations Balance-Aware Signature Generation

Dima: Indexing

19

RDBMS HDFS Native RDD Spark Local Indexing Global Indexing EM Query Optimizer EMSQL Parser DataFrame API CLI JDBC Scala Program EM Query Operations Balance-Aware Signature Generation

Signature-based partition ü Local join ü Avoid join on different nodes Global Indexing ü Signatureà nodes Local Indexing ü Signature à records

LocalIndex DataRDD

• <signature>

<signature> <signature> IndexRDD

• Global

Mapping P(sig) segment/deletion sig=(seg/del,i,l) … … …

1 n 1 n

FrequencyTable F (sig), F (sig) +

• Index HashMap

sig L [pos] sig L [pos]

• +

Data Array record record 1 2 record … record n

Dima: Query Processing

20

RDBMS HDFS Native RDD Spark Local Indexing Global Indexing EM Query Optimizer EMSQL Parser DataFrame API CLI JDBC Scala Program EM Query Operations Balance-Aware Signature Generation

EM Operation ü Selection ü Join ü Topk EM Query Processing ü Global

ü ZipPartition; Balance-aware

ü Local

ü Avoid Duplicates

},1,5):2

ProbeRDD IndexRDD 1 n FrequencyTable F (sig), F (sig) +

• DataRDD
• <signature>

<signature> <signature> Global Mapping P(sig) segment/deletion (seg,i,l) … …

1 n

• ZipPartition

1

… … ; ; ;

R e. he n. rts et

r4 r5

L4

root

an do ef en er er

CA CS EE Stanford UCLA

r4 r5 r3 r4 r5 r1 r2 r5

L8 L2 L3 L6 L7

r2 r5

L10

r2 r4

L12

r2 r4

L15

r3

L17 h1 h2

r1

L5

r1

L16

r1 r4

L9

r1

L14

Berkeley

r3

L1

r3

L11

r2 r3 r4

L13

CA CS EE Stanford UCLA Berkeley

R

Dima: Query Optimization

21

RDBMS HDFS Native RDD Spark Local Indexing Global Indexing EM Query Optimizer EMSQL Parser DataFrame API CLI JDBC Scala Program EM Query Operations Balance-Aware Signature Generation

EM Optimizer for multiple attributes ü Join order ü Selection Order ü Cost Estimation ü Size Estimation ü #Partitions

Guoliang Li, Jian He, Deng Dong, Jian Li, Jianhua Feng. Efficient Similarity Search and Join on Multi-Attribute Data. SIGMOD 2015.

φ= ∧λ where λ: F(a,b) ≥ δ

Hybrid Human-Machine EM

DIMA System

pSimilarity-based processing system pEntity Matching pEase to use

22

CDB System

pCrowd-powered SQL pNew optimization Model pEntity Matching pCost, Latency, Quality

Name Model Size 6S iPhone 5.7 S7 Samsung 5.7 Product Type Color iPhone6 6th red Samsung 7gen white

R S

Rule-based Candidate Generation

Rule Generation Signature-based Filtering Verification Rules

Candidate pairs

Crowd-based Refinement

Graph Model Tuple-Level Cost Control Round-based

Latency Control

Unified Quality Control

Matching pairs

CDB: Selection-Inference-Refine

23

Question Selection Answer Refinement Result Inference

Challenge 1: How to select? Cost/Latency Challenge 3: How to tolerate errors? Quality Challenge 2: How to infer? Cost Candidate questions Selected questions Crowd answers Deduced answers Refined answers Next round

Crowd

Inference - Transitivity

p Challenges

– Labeling order

• A=B, B≠C à A≠C
• B≠C, A≠C à A?B

– Cost

• Optimal Order

– Latency

• Parallel crowdsourcing

– Quality

• Transitivity Errors
• If workers give B=C, then deduce A=C

24

iPad2 iPhone5 iPad Two ≠ iPad2 iPhone5 iPad Two ≠

?

Inference - Partial Order

pCandidate pairs pij pPartial order

– pij>pi’j’ if sij >= si’j’

25

p67 p45 p12 p13 p23 p27 p26 p10,11 p89 p37 p24 p34 p25 p35 p47 p57 p46 p56

Chengliang Chai, Guoliang Li, Jian Li, Dong Deng. Cost-Effective Crowdsourced Entity Resolution: A Partial-Order Approach. SIGMOD 2016.

✔ ✔ ✖ ✖

k k

A B

Selection-Inference-Refine Framework

26

p67 p45 p12 p13 p23 p27 p26 p10,11 p89 p37 p24 p34 p25 p35 p47 p57 p46 p56

p67 p45 p12 p13 p23 p27 p26 p10,11 p89 p37 p24 p34 p25 p35 p47 p57 p46 p56 p67 p45 p12 p13 p23 p26 p10,11 p89 p37 p24 p34 p25 p35 p47 p57 p46 p56 p27 p67 p45 p12 p13 p23 p26 p10,11 p89 p37 p24 p34 p25 p35 p47 p57 p46 p56 p27

pSelection-Inference-Refine Framework

– Selection: minimize the number of questions – Inference: infer the answers of no-asked questions – Refine: tolerate errors of partial order and crowd

Question Selection

27

pSerial Algorithms: Ask one question in each iteration

– Comparable Vertices

• O(log|P|), |P| is length of path

– Incomparable Vertices

• O(Blog|V|), B is path number
• |V| is vertex number

g9 g2 g1 g8 g3 g4 g5 g7 g6

g9 g2 g1 g8 g3 g4 g5 g7 g6 g9 g2 g1 g8 g3 g4 g5 g7 g6

g9 g2 g1 g8 g3 g4 g5 g7 g6 g9 g2 g1 g8 g3 g4 g5 g7 g6

Question Selection

28 Parallel Algorithm

Multi-Path Algorithm

g9 g2 g1 g8 g3 g4 g5 g7 g6

g9 g2 g1 g8 g3 g4 g5 g7 g6 g9 g2 g1 g8 g3 g4 g5 g7 g6

g9 g2 g1 g8 g3 g4 g5 g7 g6

Topology-Sorting-Based Algorithm

L1 L2 L3 L4

g9 g2 g1 g8 g3 g4 g5 g7 g6

L5 L1 L2

g9 g2 g1 g8 g3 g4 g5 g7 g6

L1

g9 g2 g1 g8 g3 g4 g5 g7 g6 g9 g2 g1 g8 g3 g4 g5 g7 g6

Select multiple vertices and ask them together in each iteration

Refinement

29 Equi-depth histograms Overall weighted similarities of pairs

Results

30

Cost 100✖ Latency 10✖ Quality 5%

Crowd-based Method - CDB

31

pA crowd-powered database system

– Users require to write code to utilize crowdsourcing platforms – CDB encapsulates the complexities of interacting with the crowd

pLimitations of existing systems

– Coarse-grained optimization - Tree model – Table Level – Single-goal optimization - Cost

pHighlights of CDB

– Fine-grained optimization - Graph Model – Tuple Level – Multi-goal optimization – Cost, Latency, Quality

Table 1 Table 2 Table 3 Table 4

Tuples

Tree Model vs Graph Model

pFind connected paths with 3 solid edges

32

Conf

VLDB

Conf Name

SIGMOD Guoliang LI

Name Affiliation

G.L. Li Tsinghua University

University

Tsinghua

=

≠

=

Table 1 Table 2 Table 3 Table 4

Tuples

Tree Model vs Graph Model

pFind connected paths with 3 solid edges

33

Optimal Tree Model: 9+5+1=15 tasks Graph Model: 3 tasks

Table 1 Table 2 Table 3 Table 4

Tuples

Tree Model vs Graph Model

pFind connected paths with 3 solid edges

34

Optimal Tree Model: 9+5+1=15 tasks Graph Model: 3 tasks

CDB: Graph Model

35

pGraph Model

– Vertices

• Tuples

– Edges

• Join predicate

– Weight

• Similarity

u1 u2 u3 u4 u5 u6 u7

u11

r1 r2 r3 r4 r5 r6

r7 r11

p1 p2 p3 p7 c1 c2 c3 c4 c5 c9 c10 c11

0.63 0.61 0.70 0.63 0.65 0.74 0.63 0.42 0.41 0.83 0.30 0.79 0.40 . 4 . 3 7 0.88 0.33 0.33 0.50 0.43 0.64 0.31 u12 r12

p8 c12

0.50 0.53 0.89 0.53 0.50 0.70

u8 u9

u10

r8 r9

r10

p4 p5 p6 c6 c7 c8

0.65 0.61 0.35 0.75 0.83 0.70 0.71 0.91 0.89 0.40 0.46 0.40

SELECT * FROM Paper, Researcher, Citation, University WHERE Paper.Author CROWDEQUAL Researcher.Name AND Paper.Title CROWDEQUAL Citation.Title AND Researcher.Affiliation CROWDEQUAL University.Name

Chengliang Chai, Guoliang Li, Jian Li, Dong Deng. Cost-Effective Crowdsourced Entity Resolution: A Partial-Order Approach . SIGMOD 2016.

MetaData

Task Worker

Crowdsourcing Platforms Relational Database

Graph-Based Query Model

Assignment

CQL Parser Result Collection

Query Optimization

Cost Control Latency Control Quality Control

!"#$ %##&'()*(+ !,-+. /(0*,*(1*

Crowd UI Designer Statistics

u1 u2 u3 u r1 r2 r3 p1 p2 c1 c2 c3

0.63 0.61 . 4 2 0.41 0.83 0.30 . 3 7 0.88 0.33 0.33 . 5 3 0.50 0.70

CDB: Cost Control

36

pMinimize Cost

– Find all the results with the minimal cost

pBudget (pay as you go)

– Find the most results with a given budget (B tasks)

pExpectation-based Method

– Pruning ability to cut the graph

E(t, t0) = Qx

i=1(1 − ω(t, ti))

x α + Qy

i=1(1 − ω(ti, t0))

y β.

u6 u7 r r6

r7

p3 c c9

0.65 0.74 0.79 0.40 0.40 0.50 . 4 3

u8 u9 r8 r9 p4 p5 p6 c6 c7 c8

0.65 0.61 0.75 0.83 0.70 0.71 0.91 0.89

MetaData

Task Worker

Crowdsourcing Platforms Relational Database

Graph-Based Query Model

Assignment

CQL Parser Result Collection

Query Optimization

Cost Control Latency Control Quality Control

!"#$ %##&'()*(+ !,-+. /(0*,*(1*

Crowd UI Designer Statistics

CDB: Latency Control

37

pWhich tasks can be asked in parallel

– Tasks have correlations – Tradeoff: cost and latency – Minimize the number of rounds without increasing the cost

pTask batching

– Connected components – Edges containing tuples from the same table

u1 u2 u3 u r1 r2 r3 p1 p2 c1 c2 c3

0.63 0.61 . 4 2 0.41 0.83 0.30 . 3 7 0.88 0.33 0.33 . 5 3 0.50 0.70

MetaData

Task Worker

Crowdsourcing Platforms Relational Database

Graph-Based Query Model

Assignment

CQL Parser Result Collection

Query Optimization

Cost Control Latency Control Quality Control

!"#$ %##&'()*(+ !,-+. /(0*,*(1*

Crowd UI Designer Statistics u2 u3 u4 u5 u6 u7 r2 r3 r4 r5 r6

r7

p1 p2 p3 c2 c3 c4 c5

0.61 0.70 0.63 0.65 0.74 . 4 2 0.41 0.83 0.30 0.79 . 4 . 4 . 3 7 0.88 0.33 0.33 0.50 0.53 0.50 0.70

p4 c6 c7

0.75 0.71 0.91 . 4

CDB: Quality Control

38

pTruth Inference

– A unified inference model

• Single choice
• Multiple choice
• Fill/Collection

pOnline Task Assignment

– Worker Model – Task Model – Worker answer prediction

pi = Q

(w,a)2Vt (qw)1{i=a} · ( 1qw `1 )1{i6=a}

P`

j=1

Q

(w,a)2Vt (qw)1{j=a} · ( 1qw `1 )1{j6=a}

I

I(t) = H(~ p) − X`

i=1

⇥ pi · qw + (1 − pi) · 1 − qw ` − 1 ⇤ · H(~ p0).

C(t) = X

{(w,a)2Vt}^{(w0,a0)2Vt}^{w6=w0}

sim(a, a0) |Vt|

2

• .

MetaData

Task Worker

Crowdsourcing Platforms Relational Database

Graph-Based Query Model

Assignment

CQL Parser Result Collection

Query Optimization

Cost Control Latency Control Quality Control

!"#$ %##&'()*(+ !,-+. /(0*,*(1*

Crowd UI Designer Statistics

Data Integration As A Service

39

Entity Matching Data Schema Matching Data Cleaning User Interface Declarative Language API Crowd As A Service Crowd Modeling Crowd Scheduling Crowd Control Data Extraction Workflow Management Hybrid Machine-Human Computing Rule-based Reasoning Machine Learning

Lessons Learned

pHuman is important in data integration pMachine step – Rules are important – Require high-quality examples

pCrowd step

– Crowd is double-edged sword

• high quality for easy tasks
• low quality for hard tasks

– Inference is important

• Can reduce the cost significantly
• But may sacrifice quality

40

Thank You!

All the codes are open-sourced at https://github.com/TsinghuaDatabaseGroup/