Load data stored as "key value"

anormand — Tue, 27 Feb 2018 13:57:08 GMT

Hello community,

I am asked to study the possibility of loading data stored in base in a single column table in a "key value" format.

Here is an example of a data line.

"{""POP89036"":"""",""POP89037"":"""",""POP89034"":""PREX TMK E SO"",""Q1_NVE"":""2"",""POP89035"":""CD2"",""QUALIF"":""INT"",""POP89038"":""949854822214"",""T_CONT_LB"":""1er Trimestre 2018"",""POP89039"":""AC"",""Q10_NPS"":""7"",""POP89022_LB"":""9258427"",""ENT_DOMAINES_QO8_LB"":""Pas de motif 'domaines' détecté"",""CSEGM_LB"":""Professionnels"",""POP89043"":""4896524565216"",""POP89044"":""anonyme"",""POP89041"":"""",""ORIGINE_LB"":""XXXXXXXXXXXXX"",""MDATPOP89040_LB"":"""",""POP89025"":""CP"",""POP89026"":""P"",""CANAL_LB"":""Prestataire Externe"",""M_CONT_LB"":""Février 2018"",""TYPPLATEAU"":""3"",""POP89028"":""8965442487"",""ENT_VERBATIM_QO8"":""01854259454752461"",""ENT_VERBATIM_QO9"":"""",""Q3_LB"":""satisfait"",""T_REP"":""181"",""ENT_ALERTE_QO8"":""10"",""M_REP"":""19632"",""ENT_TONALITES_QO9"":"""",""ENT_TONALITES_QO8"":""1"",""ENT_ALERTE_QO9"":"""",""QO8"":""XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"",""MOTIF"":""AC"",""QO9"":"""",""S_CONT"":""156425"",""JSEM_REP_LB"":""Dimanche"",""POP89014"":"""",""POP89015"":"""",""VERBATIM_LB"":""Oui"",""POP89012"":""TL"",""S_REP"":""1805"",""ENT_VU_LB"":""Oui"",""ENT_THEMES_QO9_LB"":"""",""POP89018"":"""",""POP89019"":""INT"",""ENT_TONALITES_QO9_LB"":"""",""POP89016"":"""",""UFDA"":""PTRREEX"",""POP89017"":"""",""ENT_CONCURRENCE_QO8"":""0000"",""ENT_CONCURRENCE_QO9"":"""",""EXTRACT_LB"":""XXXXXXXXXX"",""Q2_LB"":""Oui"",""DELAIREPONSE_LB"":""1 jour"",""DOC_LB"":""XXXXXXXXX"",""Validite"":""1"",""S_CONT_LB"":""2018 SEM05"",""POP89021"":""YHNBGFDRTYHJ"",""POP89022"":""996747"",""POP89020"":""19654410"",""POP89003"":""O"",""POP89004"":""anonyme"",""POP89001"":""CSDFGHJKJ2"",""POP89002"":""2018-02-03"",""UFGHJ_LB"":""PRO PRESTA"",""POP89008"":""anonyme"",""ENT_THEMES_QO9"":"""",""POP89005"":""4892454654624762412"",""ENT_THEMES_QO8"":""006"",""POP89009"":""an"",""ENT_PRIORITE_QO8_LB"":""Non prioritaire"",""A_REP"":""18"",""S_REP_LB"":""2018 SEM05"",""ENT_PROCESSUS_QO9_LB"":"""",""TYPCENTR_LB"":""Centres Externes"",""H_REP"":""9"",""Q5_LB"":""satisfait"",""DOC"":""DT10"",""A_CONT_LB"":""2018"",""POP89010"":""GCM"",""M_CONT"":""1962"",""POP89078"":""Les Sélections Pro"",""ENT_PRIORITE_QO8"":""NP"",""ENT_PRIORITE_QO9"":"""",""ENT_CONCURRENCE_QO9_LB"":"""",""CSEGM"":""P"",""SUPPORT"":""TL"",""VERBATIM"":""1"",""ENT_PROCESSUS_QO8_LB"":""XXXXXXXXXXXXXXXXXXXXXXXXX"",""Q4_LB"":""satisfait"",""ENT_ACTEURS_QO9_LB"":"""",""TYPE_PRO_LB"":""Pro"",""A_CONT"":""18"",""C_POP89003"":""true"",""EXTRACT"":""P1"",""TYPCENTR"":""2"",""TYPE_PRO"":""Pro"",""ENT_ACTEURS_QO8"":""0000"",""ENT_VERBATIM_QO8_LB"":""XXXXXXXXXXXXXXXXXXXXXXXXX"",""ENT_ACTEURS_QO9"":"""",""Q7_LB"":""Claires"",""ENT_CONCEPTS_QO9_LB"":"""",""MOTIF_LB"":""XXXXXXXXXXXXXXXXXX"",""T_REP_LB"":""1er Trimestre 2018"",""POP89003_LB"":""PRO"",""H_REP_LB"":""entre 09h00 et 10h00"",""POP89072"":""07123263"",""VALIDITE_LB"":""Oui"",""DATCONT"":""2018-02-02"",""CONTACT"":""SDFGHJKL"",""POP89077"":""SPROFI"",""ORIGINE"":""LOIKUJUJUJEZ"",""POP89055_LB"":"""",""POP89075"":""j1005478562856477"",""POP89058"":"""",""POP89059"":"""",""POP89056"":""13430"",""Q2"":""1"",""Q3"":""1"",""POP89057"":"""",""Q4"":""1"",""Q5"":""1"",""Q6"":""1"",""Q1_NVE_LB"":""Satisfait"",""Q7"":""2"",""ENT_VERBATIM_QO9_LB"":"""",""Q6_LB"":""satisfait"",""ENT_CONCEPTS_QO8_LB"":""XXXXXXXXXXXXXXXXXXXXXXXXX"",""ENT_PRIORITE_QO9_LB"":"""",""ENT_DOMAINES_QO9"":"""",""ENT_DOMAINES_QO8"":""0000"",""ENT_CONCEPTS_QO9"":"""",""ENT_PROCESSUS_QO8"":""0000"",""ENT_PROCESSUS_QO9"":"""",""C_POP89003_LB"":"""",""ENT_CONCEPTS_QO8"":""0028"",""JSEM_REP"":""7"",""POP89060"":"""",""Q10_NPS_LB"":""7"",""POP89048"":"""",""POP89045"":"""",""POP89046"":"""",""JOUR_SI"":""2018-02-03"",""ENT_THEMES_QO8_LB"":""XXXXXXXXXXXXXXXXXX"",""ENT_VU"":""1"",""M_REP_LB"":""Février 2018"",""NBCONT"":""1.0"",""T_CONT"":""181"",""ENT_TONALITES_QO8_LB"":""XXXXXXXXXXXXXX"",""MDASDFGHJKLM0"":"""",""POP89025_LB"":""XXXXXXXXXXXXXXXXXXXXXXXXX"",""ENT_ACTEURS_QO8_LB"":""XXXXXXXXXXXXXXXXXXXXXXXXX"",""SUPPORT_LB"":""Téléphone"",""ENT_CONCURRENCE_QO8_LB"":""XXXXXXXXXXXXXXXXXX"",""CONTACT_LB"":""Proactif"",""ENT_DOMAINES_QO9_LB"":"""",""NBREP"":""1"",""A_REP_LB"":""2018"",""QUALIF_LB"":""Vente"",""DELAIREPONSE"":""1"",""TYPACTIV_LB"":""XXXXXXXXXXXXXXXXXXXXXXXXX"",""TYPPLATEAU_LB"":""Prestataire"",""CANAL"":""PREX"",""TYPACTIV"":""1"",""POP89055"":"""",""POP89052"":"""",""POP89053"":"""",""POP89036_LB"":""""}"

Daily, I will have to decode several hundred thousand of this kind of line, so millions of lines in all. I have a fairly classic production server 4 cores, 8 threads and a hundred GB of RAM.

Question 1: Do you think we can easily reconstitute a table dynamically whose columns would be the keys, and the lines the values of each line?

Example:

{ "" A "": "" 1 "", "" B "": "" 1 ""}

{ "" A "": "" 2 "", "" B "": "" 1 ""}

Give:

A | B

---------

1 | 1

2 | 1

Question 2: Do you think the performance will be good? (Currently I have this data in the form of CSV and I integrate them in less than a minute)

Question 3: How would you implement it?

Thank you in advance for your help !

Adams

topic Load data stored as "key value" in App Development

Load data stored as "key value"