Linking in Spark¶
```python from pyspark import SparkConf, SparkContext from pyspark.sql import SparkSession
from splink.backends.spark import similarity_jar_location
conf = SparkConf()
This parallelism setting is only suitable for a small toy example¶
conf.set("spark.driver.memory", "12g") conf.set("spark.default.parallelism", "8") conf.set("spark.sql.codegen.wholeStage", "false")
Add custom similarity functions, which are bundled with Splink¶
documented here: https://github.com/moj-analytical-services/splink_scalaudfs¶
path = similarity_jar_location() conf.set("spark.jars", path)
sc = SparkContext.getOrCreate(conf=conf)
spark = SparkSession(sc) spark.sparkContext.setCheckpointDir("./tmp_checkpoints") ```
24/07/13 19:50:47 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
```python from splink import splink_datasets
pandas_df = splink_datasets.fake_1000
df = spark.createDataFrame(pandas_df) ```
```python import splink.comparison_library as cl from splink import Linker, SettingsCreator, SparkAPI, block_on
settings = SettingsCreator( link_type="dedupe_only", comparisons=[ cl.NameComparison("first_name"), cl.NameComparison("surname"), cl.LevenshteinAtThresholds( "dob" ), cl.ExactMatch("city").configure(term_frequency_adjustments=True), cl.EmailComparison("email"), ], blocking_rules_to_generate_predictions=[ block_on("first_name"), "l.surname = r.surname", # alternatively, you can write BRs in their SQL form ], retain_intermediate_calculation_columns=True, em_convergence=0.01, ) ```
```python linker = Linker(df, settings, db_api=SparkAPI(spark_session=spark)) deterministic_rules = [ "l.first_name = r.first_name and levenshtein(r.dob, l.dob) <= 1", "l.surname = r.surname and levenshtein(r.dob, l.dob) <= 1", "l.first_name = r.first_name and levenshtein(r.surname, l.surname) <= 2", "l.email = r.email", ]
linker.training.estimate_probability_two_random_records_match(deterministic_rules, recall=0.6) ```
Probability two random records match is estimated to be 0.0806.
This means that amongst all possible pairwise record comparisons, one in 12.41 are expected to match. With 499,500 total possible comparisons, we expect a total of around 40,246.67 matching pairs
python
linker.training.estimate_u_using_random_sampling(max_pairs=5e5)
----- Estimating u probabilities using random sampling -----
Estimated u probabilities using random sampling
Your model is not yet fully trained. Missing estimates for:
- first_name (no m values are trained).
- surname (no m values are trained).
- dob (no m values are trained).
- city (no m values are trained).
- email (no m values are trained).
```python training_blocking_rule = "l.first_name = r.first_name and l.surname = r.surname" training_session_fname_sname = ( linker.training.estimate_parameters_using_expectation_maximisation(training_blocking_rule) )
training_blocking_rule = "l.dob = r.dob" training_session_dob = linker.training.estimate_parameters_using_expectation_maximisation( training_blocking_rule ) ```
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
l.first_name = r.first_name and l.surname = r.surname
Parameter estimates will be made for the following comparison(s):
- dob
- city
- email
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- first_name
- surname
Iteration 1: Largest change in params was -0.709 in probability_two_random_records_match
Iteration 2: Largest change in params was 0.0573 in the m_probability of email, level `All other comparisons`
Iteration 3: Largest change in params was 0.0215 in the m_probability of email, level `All other comparisons`
Iteration 4: Largest change in params was 0.00888 in the m_probability of email, level `All other comparisons`
EM converged after 4 iterations
Your model is not yet fully trained. Missing estimates for:
- first_name (no m values are trained).
- surname (no m values are trained).
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
l.dob = r.dob
Parameter estimates will be made for the following comparison(s):
- first_name
- surname
- city
- email
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- dob
WARNING:
Level Jaro-Winkler >0.88 on username on comparison email not observed in dataset, unable to train m value
Iteration 1: Largest change in params was -0.548 in the m_probability of surname, level `Exact match on surname`
Iteration 2: Largest change in params was 0.129 in probability_two_random_records_match
Iteration 3: Largest change in params was 0.0313 in probability_two_random_records_match
Iteration 4: Largest change in params was 0.0128 in probability_two_random_records_match
Iteration 5: Largest change in params was 0.00651 in probability_two_random_records_match
EM converged after 5 iterations
m probability not trained for email - Jaro-Winkler >0.88 on username (comparison vector value: 1). This usually means the comparison level was never observed in the training data.
Your model is fully trained. All comparisons have at least one estimate for their m and u values
python
results = linker.inference.predict(threshold_match_probability=0.9)
Blocking time: 4.65 seconds
Predict time: 82.92 seconds
python
spark_df = results.as_spark_dataframe().show()
+------------------+------------------+-----------+-----------+------------+------------+----------------+---------------+---------------+------------------+--------------------+---------+---------+-------------+------------+------------+-------------------+------------------+----------+----------+---------+------------------+----------+----------+----------+---------+---------+------------------+------------------+--------------------+--------------------+-----------+----------+----------+-------------------+-------------------+---------+
| match_weight| match_probability|unique_id_l|unique_id_r|first_name_l|first_name_r|gamma_first_name|tf_first_name_l|tf_first_name_r| bf_first_name|bf_tf_adj_first_name|surname_l|surname_r|gamma_surname|tf_surname_l|tf_surname_r| bf_surname| bf_tf_adj_surname| dob_l| dob_r|gamma_dob| bf_dob| city_l| city_r|gamma_city|tf_city_l|tf_city_r| bf_city| bf_tf_adj_city| email_l| email_r|gamma_email|tf_email_l|tf_email_r| bf_email| bf_tf_adj_email|match_key|
+------------------+------------------+-----------+-----------+------------+------------+----------------+---------------+---------------+------------------+--------------------+---------+---------+-------------+------------+------------+-------------------+------------------+----------+----------+---------+------------------+----------+----------+----------+---------+---------+------------------+------------------+--------------------+--------------------+-----------+----------+----------+-------------------+-------------------+---------+
|15.131885475840011|0.9999721492762709| 51| 56| Jayden| Jayden| 4| 0.008| 0.008|11.371009132404957| 4.0525525525525525| Bennett| Bennett| 4| 0.006| 0.006| 9.113630950205666| 5.981981981981981|2017-01-11|2017-02-10| 1|14.373012181955707| Swansea| Swansea| 1| 0.013| 0.013|5.8704874944935215| 5.481481481481482| NaN| jb88@king.com| 0| 0.211| 0.004|0.35260600559686806| 1.0| 0|
| 7.86514930254232|0.9957293356289956| 575| 577| Jessica| Jessica| 4| 0.011| 0.011|11.371009132404957| 2.9473109473109473| Owen| NaN| 0| 0.006| 0.181|0.45554364195240765| 1.0|1974-11-17|1974-11-17| 3|220.92747883214062| NaN| NaN| 1| 0.187| 0.187|5.8704874944935215|0.3810655575361458| NaN|jessica.owen@elli...| 0| 0.211| 0.002|0.35260600559686806| 1.0| 0|
| 5.951711022429932|0.9841000517299358| 171| 174| NaN| Leah| 0| 0.169| 0.002|0.4452000905514796| 1.0| Russell| Russell| 4| 0.01| 0.01| 9.113630950205666| 3.589189189189189|2011-06-08|2012-07-09| 0|0.2607755750325071| London| London| 1| 0.173| 0.173|5.8704874944935215|0.4119032327124813|leahrussell@charl...|leahrussell@charl...| 4| 0.005| 0.005| 8.411105418567649| 9.143943943943944| 1|
|21.650093935297473|0.9999996961409438| 518| 519| Amelia| Amlelia| 2| 0.009| 0.001| 47.10808446952784| 1.0| Morgan| Morgan| 4| 0.012| 0.012| 9.113630950205666|2.9909909909909906|2011-05-26|2011-05-26| 3|220.92747883214062| Swindno| Swindon| 0| 0.001| 0.01|0.6263033203299755| 1.0|amelia.morgan92@d...|amelia.morgan92@d...| 3| 0.004| 0.001| 211.35554441198767| 1.0| 1|
|11.456207518049865|0.9996442185022277| 752| 754| Jaes| NaN| 0| 0.001| 0.169|0.4452000905514796| 1.0| NaN| NaN| 4| 0.181| 0.181| 9.113630950205666|0.1982977452590712|1972-07-20|1971-07-20| 2| 84.28155355946456| NaN| NaN| 1| 0.187| 0.187|5.8704874944935215|0.3810655575361458| j.c@white.org| j.c@whige.wort| 3| 0.002| 0.001| 211.35554441198767| 1.0| 1|
|24.387299048327478|0.9999999544286963| 760| 761| Henry| Henry| 4| 0.009| 0.009|11.371009132404957| 3.602268935602269| Day| Day| 4| 0.004| 0.004| 9.113630950205666| 8.972972972972972|2002-09-15|2002-08-18| 1|14.373012181955707| Leeds| Leeds| 1| 0.017| 0.017|5.8704874944935215| 4.191721132897603|hday48@thomas-car...|hday48@thomas-car...| 3| 0.003| 0.001| 211.35554441198767| 1.0| 0|
|12.076660303346712|0.9997685471829967| 920| 922| Evi| Evie| 3| 0.001| 0.007| 61.79623639995749| 1.0| Jones| Jones| 4| 0.023| 0.023| 9.113630950205666|1.5605170387779081|2012-06-19|2002-07-22| 0|0.2607755750325071| NaN| NaN| 1| 0.187| 0.187|5.8704874944935215|0.3810655575361458|eviejones@brewer-...|eviejones@brewer-...| 4| 0.004| 0.004| 8.411105418567649| 11.42992992992993| 1|
| 4.002786788974079|0.9412833223288347| 171| 175| NaN| Lheah| 0| 0.169| 0.001|0.4452000905514796| 1.0| Russell| Russell| 4| 0.01| 0.01| 9.113630950205666| 3.589189189189189|2011-06-08|2011-07-10| 0|0.2607755750325071| London| Londoon| 0| 0.173| 0.002|0.6263033203299755| 1.0|leahrussell@charl...|leahrussell@charl...| 4| 0.005| 0.005| 8.411105418567649| 9.143943943943944| 1|
|19.936162812706836|0.9999990031804153| 851| 853| Mhichael| Michael| 2| 0.001| 0.006| 47.10808446952784| 1.0| NaN| NaN| 4| 0.181| 0.181| 9.113630950205666|0.1982977452590712|2000-04-03|2000-04-03| 3|220.92747883214062| London| London| 1| 0.173| 0.173|5.8704874944935215|0.4119032327124813| m.w@cannon.com| m@w.cannon.com| 2| 0.002| 0.001| 251.69908796212906| 1.0| 1|
| 21.33290823458872|0.9999996214227064| 400| 402| James| James| 4| 0.013| 0.013|11.371009132404957| 2.4938784938784937| Dixon| Dixon| 4| 0.009| 0.009| 9.113630950205666| 3.987987987987988|1991-04-12|1991-04-12| 3|220.92747883214062| NaN| Loodnon| 0| 0.187| 0.001|0.6263033203299755| 1.0|james.d@merritot-...|james.d@merritt-s...| 3| 0.001| 0.005| 211.35554441198767| 1.0| 0|
|22.169132705637786|0.9999997879560012| 81| 84| Ryan| Ryan| 4| 0.005| 0.005|11.371009132404957| 6.484084084084084| Cole| Cole| 4| 0.005| 0.005| 9.113630950205666| 7.178378378378378|1987-05-27|1988-05-27| 2| 84.28155355946456| NaN| Bristol| 0| 0.187| 0.016|0.6263033203299755| 1.0|r.cole1@ramirez-a...|r.cole1@ramtrez-a...| 3| 0.005| 0.001| 211.35554441198767| 1.0| 0|
|6.1486678498977065|0.9861008615160808| 652| 654| NaN| NaN| 4| 0.169| 0.169|11.371009132404957| 0.19183680722142257| Roberts| NaN| 0| 0.006| 0.181|0.45554364195240765| 1.0|1990-10-26|1990-10-26| 3|220.92747883214062|Birmingham|Birmingham| 1| 0.04| 0.04|5.8704874944935215|1.7814814814814814| NaN|droberts73@taylor...| 0| 0.211| 0.003|0.35260600559686806| 1.0| 0|
|17.935398542824068|0.9999960106207738| 582| 584| ilivOa| Olivia| 1| 0.001| 0.014| 3.944098136204933| 1.0| Edwards| Edwards| 4| 0.008| 0.008| 9.113630950205666| 4.486486486486486|1988-12-27|1988-12-27| 3|220.92747883214062| Dudley| Duudley| 0| 0.006| 0.001|0.6263033203299755| 1.0| oe56@lopez.net| oe56@lopez.net| 4| 0.003| 0.003| 8.411105418567649| 15.239906573239907| 1|
|21.036204363210302|0.9999995349803662| 978| 981| Jessica| Jessica| 4| 0.011| 0.011|11.371009132404957| 2.9473109473109473| Miller| Miiller| 3| 0.004| 0.001| 82.56312210691897| 1.0|2001-05-23|2001-05-23| 3|220.92747883214062| NaN| Coventry| 0| 0.187| 0.021|0.6263033203299755| 1.0|jessica.miller@jo...|jessica.miller@jo...| 4| 0.006| 0.006| 8.411105418567649| 7.619953286619953| 0|
|13.095432674729635|0.9998857562788657| 684| 686| Rosie| Rosie| 4| 0.005| 0.005|11.371009132404957| 6.484084084084084| Johnstn| Johnston| 3| 0.001| 0.002| 82.56312210691897| 1.0|1979-12-23|1978-11-23| 1|14.373012181955707| NaN| Sheffield| 0| 0.187| 0.007|0.6263033203299755| 1.0| NaN| NaN| 4| 0.211| 0.211| 8.411105418567649|0.21668113611241574| 0|
|25.252698357543103|0.9999999749861632| 279| 280| Lola| Lola| 4| 0.008| 0.008|11.371009132404957| 4.0525525525525525| Taylor| Taylor| 4| 0.014| 0.014| 9.113630950205666|2.5637065637065635|2017-11-20|2016-11-20| 2| 84.28155355946456| Aberdeen| Aberdeen| 1| 0.016| 0.016|5.8704874944935215| 4.453703703703703|lolat86@bishop-gi...|lolat86@bishop-gi...| 4| 0.002| 0.002| 8.411105418567649| 22.85985985985986| 0|
| 9.711807138722323|0.9988089303569408| 42| 43| Theodore| Theodore| 4| 0.01| 0.01|11.371009132404957| 3.242042042042042| Morris| Morris| 4| 0.004| 0.004| 9.113630950205666| 8.972972972972972|1978-09-18|1978-08-19| 1|14.373012181955707|Birgmhniam|Birmingham| 0| 0.001| 0.04|0.6263033203299755| 1.0| NaN|t.m39@brooks-sawy...| 0| 0.211| 0.005|0.35260600559686806| 1.0| 0|
| 5.951711022429932|0.9841000517299358| 173| 174| NaN| Leah| 0| 0.169| 0.002|0.4452000905514796| 1.0| Russell| Russell| 4| 0.01| 0.01| 9.113630950205666| 3.589189189189189|2011-06-08|2012-07-09| 0|0.2607755750325071| London| London| 1| 0.173| 0.173|5.8704874944935215|0.4119032327124813|leahrussell@charl...|leahrussell@charl...| 4| 0.005| 0.005| 8.411105418567649| 9.143943943943944| 1|
| 23.43211696288854|0.9999999116452517| 88| 89| Lexi| Lexi| 4| 0.003| 0.003|11.371009132404957| 10.806806806806806| NaN| NaN| 4| 0.181| 0.181| 9.113630950205666|0.1982977452590712|1994-09-02|1994-09-02| 3|220.92747883214062|Birmingham|Birmingham| 1| 0.04| 0.04|5.8704874944935215|1.7814814814814814|l.gordon34cfren@h...|l.gordon34@french...| 2| 0.001| 0.002| 251.69908796212906| 1.0| 0|
|7.1659948250873144|0.9930847652376709| 391| 393| Isaac| Isaac| 4| 0.005| 0.005|11.371009132404957| 6.484084084084084| NaN| James| 0| 0.181| 0.007|0.45554364195240765| 1.0|1991-05-06|1991-05-06| 3|220.92747883214062| Lodon| London| 0| 0.008| 0.173|0.6263033203299755| 1.0|isaac.james@smich...| NaN| 0| 0.001| 0.211|0.35260600559686806| 1.0| 0|
+------------------+------------------+-----------+-----------+------------+------------+----------------+---------------+---------------+------------------+--------------------+---------+---------+-------------+------------+------------+-------------------+------------------+----------+----------+---------+------------------+----------+----------+----------+---------+---------+------------------+------------------+--------------------+--------------------+-----------+----------+----------+-------------------+-------------------+---------+
only showing top 20 rows