'My use case: find and replace cities that have merged, glad to a list of events. What is the best way to follow? Is Spark GraphX useful for this case?
My use case is a common one: information is coming, associated with some cities, but the one who given them to us isn't aware that some of them have changed, do not exist anymore because they have merged into other ones to become bigger.
To detect this, I have a list whose each record ends with:
- the city that existed once
- the change that affected it, and what it became
+-----------+-----------+----------+----+--------------+------------------+------------------+------------------+------------------+----------+------------------+------------+----------------+--------------------+---------------------+-------------+------------------+-----------------+-------+------------------+---------+------------+----------------------+------------------------------+-----------------------------+------------------------+--------------+------------------+---------------+---------------------+--------------------------+-------------------------+-------------------------+-----------------------+----------------------------------+------------------------+-----------------------------------+-------------------------+-------------------------+-----------------------+----------------------------------+------------------------+-----------------------------------+
|typeCommune|codeCommune|codeRegion|CTCD|arrondissement|typeNomEtCharniere|nomMajuscules |nomCommune |LIBELLE |codeCanton|codeCommuneParente|sirenCommune|populationTotale|populationMunicipale|populationCompteApart|strateCommune|longitude |latitude |surface|sirenCommuneMembre|codeEPCI |nomEPCI |codeDepartementRetabli|codeCommuneChefLieuDepartement|typeNomEtCharniereDepartement|nomMajusculesDepartement|nomDepartement|libelleDepartement|codeDepartement|typeEvenementCommunal|dateEffetEvenementCommunal|typeCommuneAvantEvenement|codeCommuneAvantEvenement|typeDeNomAvantEvenement|nomCommuneMajusculesAvantEvenement|nomCommuneAvantEvenement|nomCommuneAvecArticleAvantEvenement|typeCommuneApresEvenement|codeCommuneApresEvenement|typeDeNomApresEvenement|nomCommuneMajusculesApresEvenement|nomCommuneApresEvenement|nomCommuneAvecArticleApresEvenement|
+-----------+-----------+----------+----+--------------+------------------+------------------+------------------+------------------+----------+------------------+------------+----------------+--------------------+---------------------+-------------+------------------+-----------------+-------+------------------+---------+------------+----------------------+------------------------------+-----------------------------+------------------------+--------------+------------------+---------------+---------------------+--------------------------+-------------------------+-------------------------+-----------------------+----------------------------------+------------------------+-----------------------------------+-------------------------+-------------------------+-----------------------+----------------------------------+------------------------+-----------------------------------+
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |33 |1974-11-01 |COM |01036 |0 |BELMONT |Belmont |Belmont |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |33 |1974-11-01 |COM |01226 |0 |LUTHEZIEU |Luthézieu |Luthézieu |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |34 |1997-12-01 |COMA |01226 |0 |LUTHEZIEU |Luthézieu |Luthézieu |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |34 |1997-12-01 |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |32 |2019-01-01 |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |COM |01036 |0 |VALROMEY SUR SERAN |Valromey-sur-Séran |Valromey-sur-Séran |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |32 |2019-01-01 |COM |01414 |0 |SUTRIEU |Sutrieu |Sutrieu |COM |01036 |0 |VALROMEY SUR SERAN |Valromey-sur-Séran |Valromey-sur-Séran |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |32 |2019-01-01 |COM |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |COMD |01036 |0 |BELMONT LUTHEZIEU |Belmont-Luthézieu |Belmont-Luthézieu |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |32 |2019-01-01 |COM |01442 |0 |VIEU |Vieu |Vieu |COM |01036 |0 |VALROMEY SUR SERAN |Valromey-sur-Séran |Valromey-sur-Séran |
|COM |01036 |84 |01D |011 |0 |VALROMEY SUR SERAN|Valromey-sur-Séran|Valromey-sur-Séran|0110 |null |200086411 |1339 |1320 |19 |3 |5.6568022515228575|45.91813833175709|5694.0 |200086411 |200040350|CC Bugey Sud|01 |01053 |5 |AIN |Ain |Ain |01 |32 |2019-01-01 |COM |01221 |0 |LOMPNIEU |Lompnieu |Lompnieu |COM |01036 |0 |VALROMEY SUR SERAN |Valromey-sur-Séran |Valromey-sur-Séran |
+-----------+-----------+----------+----+--------------+------------------+------------------+------------------+------------------+----------+------------------+------------+----------------+--------------------+---------------------+-------------+------------------+-----------------+-------+------------------+---------+------------+----------------------+------------------------------+-----------------------------+------------------------+--------------+------------------+---------------+---------------------+--------------------------+-------------------------+-------------------------+-----------------------+----------------------------------+------------------------+-----------------------------------+-------------------------+-------------------------+-----------------------+----------------------------------+------------------------+-----------------------------------+
These being treated a bit to be more manageable:
+-----------+------------------+------------------+---------------------+--------------------+-----------+---------+-----------------------------------------------------+
|codeCommune|dateEffetEvenement|nomCommune |nomCommuneAvecArticle|nomCommuneMajuscules|typeCommune|typeDeNom|typeEvenement |
+-----------+------------------+------------------+---------------------+--------------------+-----------+---------+-----------------------------------------------------+
|01036 |1974-11-01 |Belmont |Belmont |BELMONT |COM |0 |FUSION_ASSOCIATION |
|01036 |1974-11-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |FUSION_ASSOCIATION |
|01226 |1974-11-01 |Luthézieu |Luthézieu |LUTHEZIEU |COM |0 |FUSION_ASSOCIATION |
|01036 |1974-11-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |FUSION_ASSOCIATION |
|01036 |1997-12-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |TRANSFORMATION_DE_FUSION_ASSOCIATION_EN_FUSION_SIMPLE|
|01036 |1997-12-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |TRANSFORMATION_DE_FUSION_ASSOCIATION_EN_FUSION_SIMPLE|
|01226 |1997-12-01 |Luthézieu |Luthézieu |LUTHEZIEU |COMA |0 |TRANSFORMATION_DE_FUSION_ASSOCIATION_EN_FUSION_SIMPLE|
|01036 |1997-12-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |TRANSFORMATION_DE_FUSION_ASSOCIATION_EN_FUSION_SIMPLE|
|01036 |2019-01-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01036 |2019-01-01 |Valromey-sur-Séran|Valromey-sur-Séran |VALROMEY SUR SERAN |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01036 |2019-01-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01036 |2019-01-01 |Belmont-Luthézieu |Belmont-Luthézieu |BELMONT LUTHEZIEU |COMD |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01221 |2019-01-01 |Lompnieu |Lompnieu |LOMPNIEU |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01036 |2019-01-01 |Valromey-sur-Séran|Valromey-sur-Séran |VALROMEY SUR SERAN |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01414 |2019-01-01 |Sutrieu |Sutrieu |SUTRIEU |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01036 |2019-01-01 |Valromey-sur-Séran|Valromey-sur-Séran |VALROMEY SUR SERAN |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01442 |2019-01-01 |Vieu |Vieu |VIEU |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
|01036 |2019-01-01 |Valromey-sur-Séran|Valromey-sur-Séran |VALROMEY SUR SERAN |COM |0 |CREATION_DE_COMMUNE_NOUVELLE |
+-----------+------------------+------------------+---------------------+--------------------+-----------+---------+-----------------------------------------------------+
This has still rules to be understood and used, but the idea is that:
- If you lived in a city named Luthézieu, this city doesn't exist anymore and merged into Valromey-sur-Séran,
- if you lived in a city named Belmont, you experienced the merging of cities too, but only saw the name of your own city changing, during the years.
But one way or another, if someone comes with data related to either Luthézieu or Belmont, my goal is to substitute them with Valromey-sur-Séran, the real city that is existing now.
But here, with Spark, I'm stuck. I don't know what is the best practice to follow in order to solve my problem:
Given a source dataset containing:
{'01226', 'Luthézieu'},
{'01036' 'Belmont'}
return one containing:
{'01226', 'Luthézieu', '01036', 'Valromey-sur-Séran'},
{'01036' 'Belmont', '01036', 'Valromey-sur-Séran'}
Especially, I wonder if I'd better:
Use some classical
join,mapoperationsTry to create some kind of function, a dreamed
UDFfunction that cannot be created like that, but who would be aRow adjust(Row sourceCity)working internally with aDataset<Row>of many elements: the events. I would attempt to approach this function, whatever.What about Spark GraphX, I sometimes read about?
Is it made for this kind of purpose?
It would make sense to have these events information about cities as a graph, especially to detect special strange cases.
But what I saw about it is sparse, and related toRDD. So I'm wondering if it's a good choice or not.
What is the best way for me, in Spark, to figure out and prepare the work I am willing to do?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|