[PHP/MYSQL] Import to EAV flow

[Gigazone]

Hallo,

In een eerder topic heb ik aangegeven dat ik als beginner aan het leren en proberen ben om zakelijke prijzen van meerdere leveranciers te importeren en in een later stadium ook deze prijzen ook weer te geven. Om de uitleg niet te complex te maken, het is in principe een prijsvergelijk.

In een eerder topic hebben verschillende mensen aangegeven dat in plaats van een hele brede (kolommen per leverancier) of hele lange (volledige rijen per product), het ook een mogelijkheid zou kunnen zijn om eens naar een EAV oplossing te kijken ( link naar topic )

In ieder geval heb ik nu een aantal tabellen in mijn database import_db:
products -> de "master" waarin unieke EAN komen met andere unieke kolommen zoals ISBN/UPC
product_attributes -> hierin moeten de product specificaties komen zoals kleur, afmetingen, afbeeldingen etc
product_prices -> prijzen per leverancier, voorraad, levertijd etc


Waar ik nu even tegenaanloop is hoe ik artikelen het beste kan importeren / kopiëren. Wat is de meest efficiente flow. Ik ga dus nog niets displayen, maar ik wil op het moment alleen nog maar producten toevoegen.

Wat ik nu doe (naar 1 tabel) is als onderstaand. De EAN komt maar 1 maal voor in de tabel:

code:

Download CSV van leverancier
Laad CSV in een 'tmp' database
Voeg nieuwe EAN uit de 'tmp' tabel toe aan 'products' tabel
update de overige kolommen voor iedere EAN/rij in 'products' met data uit 'tmp'

Bovenstaande werkt, maar of ik moet gebruik maken van meerdere kolommen per leverancier, de tabel kan dan heel breed worden. Of ik kies ervoor om per leverancier per ean een rij te maken, dan wordt het heel lang, en aagezien ik voor specificities ook kolommen nodig heb, zal het ook heel breed zijn.


Wat ik dus tracht te gaan doen is de informatie uit 'tmp' naar meerdere tabellen te kopieeren.
In principe match ik producten met EAN en leveranciers met Merchant_ID. EAN is een foreign key in product_prices en product_attributes.


mijn gedachte is:

als eerste de 'products' update, nieuwe producten aanmaken met universele basis info zoals EAN/UPC/ISBN:

code:

Download CSV van leverancier
Laad CSV in een 'tmp' database
Voeg nieuwe EAN uit de 'tmp' tabel toe aan 'products' tabel
update de overige kolommen voor iedere EAN/rij

Dus in principe wat ik al had, maar voor iets minder kolommen.

Daarna het updaten en/of toevoegen van prijzen en andere data voor de specifieke leverancier:

code:

Voeg nieuwe EAN uit de 'tmp' tabel toe aan 'product_prices tabel  als waar EAN and Merchant_ID niet bestaan .
update de prijzen, levertijd etc voor iedere EAN waar zowel de juiste EAN als Merchant_ID zijn

En als laatste voeg de attributen voor het product toe, kleuren, afmetingen, afbeeldingen etc:

code:

Voeg nieuwe EAN uit de 'tmp' toe aan 'product_prices' tabel  als waar EAN and Attribute 1 niet bestaan .
update the value van attribute 1 
Voeg nieuwe EAN uit de 'tmp' toe aan 'product_prices' tabel  als waar de combinatie EAN and Attribute 2 niet bestaan .
update the value van attribute 2 
herhaal tot alle attributes zijn toegevoegd

Ik heb het idee dat ik ergens wat stappen te veel doe, en dat de queries wellicht ook wel vrij zwaar kunnen gaan worden met groot aantal producten. Zie ik iets over het hoofd, denk ik te moeilijk? Of zit ik wel op de goede weg.

Alle pointers en suggesties zijn van harte welkom en worden zeer op prijs gesteld.

[Cascade]

Wat eerste gedachten:

- weet je 100% zeker dat de EAN uniek is en als primary key gebruikt kan worden? Ik denk: waarschijnlijk wel, maar er bestaan producten waar nog een extra specificatie aan vast kan zitten (bijv. tijdschriften, boeken, zie: https://en.wikipedia.org/wiki/International_Article_Number).

- waarom die tmp tabel? Zit daar een gedachte achter, wil je een soort buffer/queue hebben of is het alleen voor het gemak?

Waarom niet een rij uit de CSV alleen in memory gebruiken? Vorm het meteen om naar je basis (bijvoorbeeld: entiteit 'product' = product EAN + naam + manufacturer id, entiteit 'leverancier' = leverancier id + naam + adres + whatever, lijst van entiteit 'eigenschap' = id + product id + eigenschap soort + key + value) en verwerk het daarna pas verder naar je database zonder de tijdelijke tabel.

- goede ideeën hoe om te gaan met de prijs zijn in jouw vorige topics denk ik al aangedragen (zover ik vluchtig kan zien). Mijn ervaring is dat productdatabases van leveranciers/een groothandel vaak een prijs + een periode geven waarvoor die prijs geldig is (inclusief de toekomst). Je hebt dus leverancier id + product id (EAN) en een prijs + periode (mogelijk onbegrensd). Als je ooit nog een vorm van historie wil laten zien dan raad ik aan om dat ook zo op te slaan, en daarnaast een record bij te houden voor de meeste actuele prijs (voor snelheid).

- meerdere 'grote' queries hoeft helemaal geen probleem te zijn. Verdiep je eens in transactions en storage engines voor MySQL. Je kan meerdere queries batchen in een transaction, dit kan veel snelheidswinst opleveren. Bijvoorbeeld 1000 x insert op een InnoDB table in 1 enkele transaction is veel en veel sneller dan 1000 individuele inserts. Aan de andere kant zal een insert op een ISAM table ook snel zijn. Zou ik even mee experimenteren. Mijn persoonlijke voorkeur is om gewoon via PDO transactions te gebruiken, zowel voor snelheid en eigenlijk belangrijker voor een stuk robustheid (gaat het fout? Doe een rollback).

- kijk ook eens naar views en stored procedures voor de database. Je hoeft niet per se alles op te lossen aan de server scripting kant, er zijn mogelijkheden dichter bij de database server die een winst (snelheid en/of gemak) kunnen opleveren.

[Gigazone]

Bedankt voor je uitgebreide antwoord, er zijn een paar dingen die waarin ik me even moet inlezen en verdiepen.

Cascade schreef op dinsdag 30 april 2019 @ 21:19:
- waarom die tmp tabel? Zit daar een gedachte achter, wil je een soort buffer/queue hebben of is het alleen voor het gemak?

Het probleem is dat er geen uniforme structuur is voor csv file, iedere vendor geeft er zijn eigen draai aan. - - De ene gebruikt semicolons, de andere comma's .
- Bij de ene staat de EAN in kolom 1, bij de ander in kolom 6.
- Een bestand heeft 8 kolommen de ander 40
- Een bestand heeft 100 rijen, de andere 2.5 miljoen.
- sommige zijn gecomprimeerd

Ik heb een table gemaakt waarin ik de specificaties van een feed opsla. De technische layout en een beschrijving van waar de kolommmen met data die ik ga gebruiken zich bevind. Dus, bv delimiter = comma, EAN = colomn09

Daarna laad ik (een gedeelte) van de CSV dmv load infile in de tmp database, en gebruik een script op de tabellen (aan) te vullen. Daarna wordt er een volgend gedeelte van de CSV ingeladen om wederom de data in de andere tabellen aan te vullen.

Cascade schreef op dinsdag 30 april 2019 @ 21:19:


Waarom niet een rij uit de CSV alleen in memory gebruiken? Vorm het meteen om naar je basis (bijvoorbeeld: entiteit 'product' = product EAN + naam + manufacturer id, entiteit 'leverancier' = leverancier id + naam + adres + whatever, lijst van entiteit 'eigenschap' = id + product id + eigenschap soort + key + value) en verwerk het daarna pas verder naar je database zonder de tijdelijke tabel.

Idealiter zou ik mapping in Load Data infile willen doen, dan zit ik niet met die tmp database en het splitten van csv files Dat zou ik dan waarschijnlijk voor de drie tabellen moeten doen, maar soit.

Stel dat mijn mapping zo is, aangezien de data raw gedumpt wordt in de 'tmp' tabel zijn de kolommen dus gelijk aan de csv.:

products.ean = tmp.column1
products.title = tmp.column4
products.price = tmp.column5
product.fixed = 5
procucts.concat = tmp. column3 + tmp.column2

Wat dus ideaal zou zijn denk ik is iets als onderstaands

code:

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE products
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
products.ean  = tmp.column1,
products.title = tmp.column5,
product.fixed = 5,
ON DUPLICATE product.price = columns5
procucts.concat = tmp. column3 + tmp.column2
);

Maar een dergelijke vorm van mapping heb ik zelf nog niet kunnen ontdekken. En daarbij komt nog de vraag of ik bijvoorbeeld ook waarden ongemoeid kan laten bij duplicate zoals bijvoorbeeld titel, terwijl ik de prijs wel wil updaten bij duplicate.

Dus op het moment, om het beheersbaar en overzichtelijk (voor mezelf) te houden heb ik gekozen om gebruik te maken van een tmp tabel.

Daarnaast kan ik het aantal te verwerken rijen per loop instellen en aanpassen aan de resources. Als mijn POC werkt zou ik het op een zwaarder systeem kunnen draaien.

[Cascade]

Ik snap het probleem, ik zou dat persoonlijk iets anders aanpakken.

Wat ik niet zo mooi vind is die 'tmp' tabel. Het lijkt mij nu een verzameling generieke waarden, waar alleen een betekenis aan wordt gehangen bij het achteraf verwerken. Is elk veld hier een generieke VARCHAR? Wat doe je als een veld niet lang genoeg is? Wat doe je als er meer kolommen nodig zijn? Wat is de life cycle van die tabel, wordt dat regelmatig opgeruimd, leeggemaakt etc?

Het lijkt mij handiger om zo snel mogelijk binnenkomende data te normaliseren naar jouw eigen formaat en het daarna pas in de database te zetten. Kan allemaal prima met PHP.

Zoiets:
- laat PHP de CSV bestanden importeren volgens de leverancier specs (zie: fgetcsv).
- vorm de rauwe data (per regel) om naar jouw eigen data modellen. Dit heb je volledig in de hand hoe dit gebeurt; mapping, type conversie, concats, validatie, kan allemaal. Hoe die transformatie gebeurt kan ook vastgelegd worden in de leverancier specs.
- pas als de omgevormde data schoon, uniform (tot op zekere hoogte gezien EAV) en valide is, voeg het toe aan de database. Ook hier heb je de volledige controle; hoe te handelen bij duplicaten, etc.

Mijn ervaring is wel dat vooral met grote CSV bestanden er vroeg of (meestal heel erg) laat een regel tussen kan zitten die niet normaal verwerkt kan worden. Vertrouw niet 100% op correcte invoer en denk even na over robustheid: mag hetzelfde CSV bestand opnieuw ingelezen worden? Moet te achterhalen zijn of een regel al een keer verwerkt is? Of helemaal niet verwerkt kan worden?

[Gigazone]

Hoi Cacade,

Bedankt voor je moeite. Ik begrijp dat het niet het standaard pad is wat ik bewandel.

Als ik het goed heb, ga je met fgetcsv een file regel voor regel parsen. Ik heb wel eens gewerkt met onder andere WP All import en ik vond dat het verwerken van data vrij traag verliep. En ik heb gelezen dat andere parsers (zoals magic parser) met grote bestanden ook erg traag gaan worden.

Vandaar dat ik had bedacht als "projectje" om te kijken of er snellere manieren zijn om meerdere CSV files van verschillende leveranciers in een database te importeren..

Zodoende kwam ik bij LOAD DATA INFILE om data in' tmp' te laden, en dan met het idee om data van de 'tmp' naar een andere tabel te verplaatsen.

Cascade schreef op woensdag 1 mei 2019 @ 20:29:
Wat ik niet zo mooi vind is die 'tmp' tabel. Het lijkt mij nu een verzameling generieke waarden, waar alleen een betekenis aan wordt gehangen bij het achteraf verwerken. Is elk veld hier een generieke VARCHAR? Wat doe je als een veld niet lang genoeg is? Wat doe je als er meer kolommen nodig zijn? Wat is de life cycle van die tabel, wordt dat regelmatig opgeruimd, leeggemaakt etc?

Op het moment bestaat de 'tmp' tabel uit voldoende kolommen, ik heb gekeken naar de csv met de meeste kolommen, en er nog een paar extra toegevoegd, just in case.

Het zijn allemaal generieke kolommen VARCHAR (255) dat zou voldoende moeten zijn voor de meeste files, wellicht kan ik eventueel de kolommen die gebruikt worden voor beschrijving aanpassen.

De tabel wordt voor het inlezen van een nieuwe (deel-)csv leeg gemaakt, en de tmp tabel wordt verder ook niet gebuikt voor andere taken.

Cascade schreef op woensdag 1 mei 2019 @ 20:29:

Mijn ervaring is wel dat vooral met grote CSV bestanden er vroeg of (meestal heel erg) laat een regel tussen kan zitten die niet normaal verwerkt kan worden. Vertrouw niet 100% op correcte invoer en denk even na over robustheid: mag hetzelfde CSV bestand opnieuw ingelezen worden? Moet te achterhalen zijn of een regel al een keer verwerkt is? Of helemaal niet verwerkt kan worden?

Om prijzen te updaten zullen inderdaad CSV files vaker ingelezen worden.Ik wil wat fout controles toevoegen en alles wat niet voldoet automatisch verwijderen.

---

Wat ik nu doe is
1) downloaden van de CSV file van leverancier server
2) unpack indien nodig,
3) split in kleinere bestanden voor handelbaarheid
4) laad ieder deelbestand in tmp, process en laad het volgende.

Ik zit even hardop te denken, wellicht is het sneller en robuster om het volgende te doen
1) downloaden van de CSV file van leverancier server
2) unpack indien nodig,
3) LOAD DATA INFILE het hele bestand in tmp
4) process rij 1 ~ 1499, process rij 1500 ~ 2999 etc until done

ik denk dat het wellicht stabieler en sneller is om ineens in te lezen en delen te processen, dan deel voor deel in te lezen en te processen.


Ik kwan dit nog tegen, wellicht kan ik daar ook iets mee LINK alhoewel ze ook verwijzen naar een ander artikel (LINK) waar de flow lijkt op wat ik doe.
.
Ik moet uitvogelen hoe ik het snelst data van 'tmp' naar de andere tabellen krijg. Wellicht een combinatie van LOAD DATA voor statische data, INSERT/UPDATE voor dynamische data.

[reddevil]

Afhankelijk hoe groot de aangeleverde bestanden zijn, zou ik 't gewoon in memory laden... anders splitsen en vanuit memory werken. Waarom zou je het eerst in een 'tmp' table laden en dan weer gaan verwerken. In memory laden en verwerken (naar je eigen model omzetten zoals Cascade zegt) is veel sneller.

1) downloaden van de CSV file van leverancier server
2) unpack indien nodig,
3) load in memory (evt. gesplitst als het echt te groot wordt)
4) process lines per 500/1000 o.i.d.
5) verwerk data per 500/1000.

Een insert van 500 records tegelijk is stukken sneller dan 1 voor 1. Als het niet altijd een insert is, kan je dat helaas niet via een 'merge statement' oplossen, want die worden niet ondersteund in MySQL, maar je zou zoiets kunnen gebruiken: https://dev.mysql.com/doc.../insert-on-duplicate.html of de bestaande records in bulk eerst verwijderen en dan ineens inserten.
De meeste doorlooptijd van het importproces zal sowieso de inserts /updates zijn als je die 1 voor 1 doet.

[Gigazone]

Hallo Reddevil,,

Bedankt voor het meedenken.

De geleverde bestanden zitten tussen de 10MB en 1+GB ruwweg.

Ik snap nog niet helemaal hoe ik in het memory laadt, bewerk, en in de tabel plaats, zoals ik al aangaf ben ik beginnend, en ik zal waarschijnlijk nog wel wat vragen stellen die voor velen open deuren zijn.

Even afgezien van het verhaal of ik nu wel of niet een 'tmp' tabel gebruik. U schrijft dat een insert van 500 records sneller is dan 1 voor 1.

code:

$sql = "INSERT INTO products (EAN, UPC, ISBN10, ISBN13, ASIN, GTIN, brand, productname)\n"
    . "SELECT $EAN, $UPC, $ISBN10, $ISBN13, $ASIN, $GTIN, $BRAND, $PRODUCTNAME FROM tmp\n"

Is dit dan 1 voor 1?


Zoals ik al aagaf maak ik gebruik van een EAV model met 3 tabellen. Mocht ik toch de route bewandelen van het in memory laden. Moet ik het dan 3 maal serieel in het memory laden, of kan ik een maal laden, bewerken en zeggen : insert deze data in products, insert deze data in product_attributes, en insert/update deze data in 'product_prices'?

[reddevil]

Allereerst zou ik kijken naar wat 'behapbaar' is qua grootte. Bijv ~50MB files max per keer. Je zou je files kunnen dan ook eerst splitsen naar max 'x' regels. Even zoeken op google voor CSV files splitsen in PHP en genoeg standaard scriptjes.

Per file kan je die dan in memory laden, gewoon via 'file_get_contents' en parsen met 'str_getcsv'. Je kan dan aan de gang met de array. Ik zou dan die array manipuleren in PHP (lees: in memory) naar het nieuwe model dat je wilt en vanuit daar insert/update statements schrijven.


Dat select statement kopieert in de database alles uit 'tmp' ineens. Maar is de tabel een MySQL Temp Table (http://www.mysqltutorial.org/mysql-temporary-table/) of een eigen tabel. Als dat laatste, dan moet je ook elke keer ervoor zorgen dat je 't opschoont, etc.

Met '1 voor 1' inserten bedoel ik iets als een loop waarin je elke keer een enkel insert statement uitvoert, bijv.:

code:

INSERT INTO <table> VALUES (1,2,3,4)

Stel je doet dat 1000 keer (met verschillende values), dan moet de database 1000 keer een insert statement, commit, bijwerken table + index statistics doen. Beter is om dan iets te doen als:

code:

INSERT INTO <table> VALUES
 (1,2,3,4),
 (4,5,6,7),
 (8,9,10,11),
 (...)

Moet ik het dan 3 maal serieel in het memory laden, of kan ik een maal laden...

1 keer: nooit vaker inladen dan nodig ... en vanuit daar verder werken... je kan dan inderdaad de 3 statements per batch ineens uitvoeren.

[Matis]

Volgens mij betrek je te veel "problemen" in één keer.

Er zijn meerdere deel-problemen. Je zult per leverancier een Provider en Converter moeten schrijven welke de leverancier specifieke indeling van de CSV kan inlezen en omzetten naar een generiek model.

Dit model kun je dan weer in de database opslaan. Het is inderdaad aan te raden om dit in batches te doen.

Je zou ook een publicatie veld aan de tabel(len) toe kunnen voegen van wanneer en tot wanneer ze gepubliceerd mogen zijn.

Als laatste zou je ook kunnen kijken naar een andere soort database. Wij hebben een soortgelijk systeem ontwikkeld en wij slaan de attributen in MongoDB op. Zo zijn we veel flexibeler.
Daarnaast gebruiken we de EAN/ISBN niet als unieke waarde/key maar doen we dat op basis van een uuid.

[Gigazone]

Bedankt voor het meedenken!

reddevil schreef op vrijdag 3 mei 2019 @ 09:50:
Allereerst zou ik kijken naar wat 'behapbaar' is qua grootte. Bijv ~50MB files max per keer. Je zou je files kunnen dan ook eerst splitsen naar max 'x' regels. Even zoeken op google voor CSV files splitsen in PHP en genoeg standaard scriptjes.

Dit had ik al eerder geimplementeerd, bestanden worden gedownload, uitgepakt indien nodig en daarna gesplits naar x regels. Daarna laad ik de bestanden 1 voor 1 in de 'tmp' tabel en verplaats de data naar de products tabel.

reddevil schreef op vrijdag 3 mei 2019 @ 09:50:
Dat select statement kopieert in de database alles uit 'tmp' ineens. Maar is de tabel een MySQL Temp Table (http://www.mysqltutorial.org/mysql-temporary-table/) of een eigen tabel. Als dat laatste, dan moet je ook elke keer ervoor zorgen dat je 't opschoont, etc.

'tmp' is een tabel die alleen wordt gebruikt voor het tijdelijk opslaan van de split csv data, en nadat deze data is verplaatst naar 'products' wordt alle data uit de tabel verwijderd voordat er een nieuwe split csv wordt ingelezen.

Ik ga me verder verdiepen in het in het memory laden van de data, en het bulk insert.

reddevil schreef op vrijdag 3 mei 2019 @ 09:50:
Allereerst zou ik kijken naar wat 'behapbaar' is qua grootte. Bijv ~50MB files max per keer. Je zou je files kunnen dan ook eerst splitsen naar max 'x' regels. Even zoeken op google voor CSV files splitsen in PHP en genoeg standaard scriptjes.

Dit had ik al eerder geimplementeerd, bestanden worden gedownload, uitgepakt indien nodig en daarna gesplits naar x regels. Daarna laad ik de bestanden 1 voor 1 in de 'tmp' tabel en verplaats de data naar de products tabel.

Matis schreef op vrijdag 3 mei 2019 @ 13:19:
Volgens mij betrek je te veel "problemen" in één keer.

Er zijn meerdere deel-problemen. Je zult per leverancier een Provider en Converter moeten schrijven welke de leverancier specifieke indeling van de CSV kan inlezen en omzetten naar een generiek model.

Dit model kun je dan weer in de database opslaan. Het is inderdaad aan te raden om dit in batches te doen.

Je zou ook een publicatie veld aan de tabel(len) toe kunnen voegen van wanneer en tot wanneer ze gepubliceerd mogen zijn.

code:

str_replace ( 'problemen'  , 'uitdagingen')

Ik had al een tabel aangemaakt waar de feedspecifieke data wordt opgeslagen, de indeling, delimiter, enclosed by etc. Bij het verplaatsen van data van 'tmp' naar 'products' wordt er eerst naar die data gekeken zodat de data in de juiste kolommen terecht komt (mits de bron natuurlijk schoon is)

Matis schreef op vrijdag 3 mei 2019 @ 13:19:
Als laatste zou je ook kunnen kijken naar een andere soort database. Wij hebben een soortgelijk systeem ontwikkeld en wij slaan de attributen in MongoDB op. Zo zijn we veel flexibeler.
Daarnaast gebruiken we de EAN/ISBN niet als unieke waarde/key maar doen we dat op basis van een uuid.

uuid, is dat een interne id waardoor je meerdere producten per EAN zou kunnen hebben?

Ik heb ook gekeken naar MongoDB en ik snap de voordelen, maar, aangezien ik beginner ben en het een hobby project is, moet ik nog veel opzoeken. Over MySQL is meer te vinden, meer voorbeeld code etc.

Daarnaast heeft MariaDB blijkbaar Dynamic Columns waarbij je naar ik begreep per rij een set attributes in een dynamic column kunt plaatsen.


Sommige materie is nog wel abacadabra voor me, maar dat gaat goedkomen, mede dankzij jullie tips.


Mijn voornaamste uitdaging is op het moment om de data uit de csv snel te inserten vanuit 'tmp' of memory.
Ik zal ook wat moeten spelen met het aantal regels in de csv enzo om te zien wat werkt.

Wat ik op het moment ook lastig vind is om te zien wat er nu daadwerkelijk gebeurt. Ik lees bijvoorbeeld 10 deel csv in met ieder 10000 regels. Ik zie dat het script draait, maar wat het daadwerkelijk aan het doen is kan ik niet zien. Graag zou ik beter begrijpen wat er intern gebeurt, welke queries zijn traag? waar zitten de bottlenecks? etc. Zijn er (gratis) tools om zoiets inzichtelijk te maken, of is er een handleiding hoe ik het script continue data kan laten zien van wat er gebeurt?

[Matis]

Gigazone schreef op vrijdag 3 mei 2019 @ 16:10:

code:

str_replace ( 'problemen'  , 'uitdagingen')

Je mis een parameter

Ik had al een tabel aangemaakt waar de feedspecifieke data wordt opgeslagen, de indeling, delimiter, enclosed by etc. Bij het verplaatsen van data van 'tmp' naar 'products' wordt er eerst naar die data gekeken zodat de data in de juiste kolommen terecht komt (mits de bron natuurlijk schoon is)

Je moet inderdaad ergens de specifieke verschillen tussen de leveranciers opslaan. Wij maken gebruik van Symfony, dus bij ons staat het per leverancier opgeslagen in de yaml-files. Voor iedere leverancier wordt dezelfde Provider gebruikt, maar met aan aantal andere parameters.
De Provider leest de CSV file in, dit doet hij met 1000 regels tegelijkertijd. Daarna wordt deze buffer lijn voor lijn leeg gelezen door de Converter. Wanneer de buffer van de Provider leeg is, worden er weer 1000 lijnen ingelezen. De Converter converteert de lijn naar een model en worden in een buffer (in het geheugen) gezet. Wanneer de buffer 1000 modellen bevat, wordt deze in één keer geflushed naar de database. Ieder model wordt omgezet naar een kolom/value paar welke geconcateneerd wordt tot één grote insert query.

uuid, is dat een interne id waardoor je meerdere producten per EAN zou kunnen hebben?

Ja, we gebruiken een UUID zodat er meerdere EAN / ISBN mogelijk zijn. Wij hebben namelijk ons ERD nog verder uitgesplitst. Zo hebben we een collection products welke een UUID een EAN en een Type heeft (ISBN13 of plaintext). Daarnaast hebben we een collection welke alle attributen per leverancier voor dat specifieke product omschrijft. Die wordt dan gekoppeld aan het UUID van de product collection. Op die manier kunnen we een "oneindige" hoeveelheid leveranciers koppelen voor hetzelfde product. Al deze informatie zit in MongoDB. Omdat we hier de flexibiliteit willen van de verschillende documenten en hun velden. Zo zijn er bijvoorbeeld leveranciers die veel meer informatie aanleveren dan andere of in een ander format. MongoDB is daarvoor een stuk flexibeler.

Ik heb ook gekeken naar MongoDB en ik snap de voordelen, maar, aangezien ik beginner ben en het een hobby project is, moet ik nog veel opzoeken. Over MySQL is meer te vinden, meer voorbeeld code etc.

Daarnaast heeft MariaDB blijkbaar Dynamic Columns waarbij je naar ik begreep per rij een set attributes in een dynamic column kunt plaatsen.

Dynamic columns is praktisch hetzelfde als MongoDB, je plaatst de "variabele" ook in een JSON en slaat ze dan op in een BLOB veld. Ik snap dat er al genoeg uitdagingen zijn bij zo'n project en dat het leren van een nieuwe database extra uitdagingen toevoegt.

Sommige materie is nog wel abacadabra voor me, maar dat gaat goedkomen, mede dankzij jullie tips.


Mijn voornaamste uitdaging is op het moment om de data uit de csv snel te inserten vanuit 'tmp' of memory.
Ik zal ook wat moeten spelen met het aantal regels in de csv enzo om te zien wat werkt.

Wat ik op het moment ook lastig vind is om te zien wat er nu daadwerkelijk gebeurt. Ik lees bijvoorbeeld 10 deel csv in met ieder 10000 regels. Ik zie dat het script draait, maar wat het daadwerkelijk aan het doen is kan ik niet zien. Graag zou ik beter begrijpen wat er intern gebeurt, welke queries zijn traag? waar zitten de bottlenecks? etc. Zijn er (gratis) tools om zoiets inzichtelijk te maken, of is er een handleiding hoe ik het script continue data kan laten zien van wat er gebeurt?

Je kunt daarvoor een profiler (xdebug) gebruiken, die laat alle calls zien en de tijd die er tussen zit. Daarnaast kun je de queries ook zelf tegen de DB draaien en een EXPLAIN gebruiken.

Als je wilt weten hoe het script werkt, zul je moeten debuggen. Als je wilt weten waar de bottleneck zit, zul je moeten profilen en tweaken. Dat laatste kan heel eenvoudig door een aantal parameters in je systeem te zetten:

• Aantal regels per keer inlezen in het geheugen
• Aantal modellen bufferen alvorens weg te schrijven naar de database

Als je een redelijk grote testbench hebt, kun je experimenteren met de waardes en zo op zoek naar de sweetspot voor die parameters. Daarnaast moet je ook weten hoe de configuratie op de productie-omgeving is. Draai je een PHP met maar 128MB geheugen, dan kun je mogelijk niet eens 1000 regels per keer inlezen en/of 1000 modellen bufferen. Hetzelfde geldt voor de DB, hoe is die geconfigureerd, zijn er shards, hoe worden de replica's gemaakt, is er een binlog en relay etc. etc.? Ook zou je kunnen kijken naar een producer / consumer queue, RabbitMQ bijvoorbeeld. Aan de ene kant pomp je er data (een lijn) in en aan de andere kant trek je de data er weer uit en verwerk je het.

Niemand kan natuurlijk in de toekomst van een stuk software kijken, maar denk goed na over zaken als een database ontwerp en abstraheren van de logica voor het lezen, verwerken en wegschrijven van de data.
Ga niet blind beginnen met alleen de voor jou bekende technieken, maar probeer je echt in te lezen in de verschillende databases en technieken.

Zoals eerder gezegd maken we gebruik van Symfony en ook Doctrine. Die laatste heeft batch processing in zich. Daarmee neemt het framework een heel stuk werk uit handen. Het fijne van een ORM is dat je daarmee ook geen zorgen meer hoeft te maken over de daadwerkelijke SQL én het heeft fantastische debuggers / profilers.

[Gigazone]

Bedankt voor de tips en pointers, ik zal me er zeker in verdiepen. Iedere dag leer ik weer iets bij, en daar doen we het voor.


Maar op het moment ben ik nog aan het worstelen met syntax voor MySQL en PHP, en met hoe een database te ontwerpen, en hoe een database eigenlijk werkt. Ik ga eerst proberen om een 'proof of concept' te maken in PHP en MySQL en me verdiepen in het jargon, en daarna zou ik kunnen kijken hoe e.e.a. te optimaliseren d.mv. aanpassen van geheugen of implementeren van Symfony/Doctrine.

Zoals gezegd is het een hobbyproject ter lering, dus er is ruimte en tijd om dingen aan te passen, en het hoeft niet direct perfect te zijn. Al doende en door fouten leert men

[Xalephsis]

Over het database ontwerp, wat je kunt doen is de EAV iets verder opslitsen. Je hebt immers de entiteit Product, de entiteit Prijs, de entiteit Leverancier en je wilt dat alle Attributen gekoppeld kunnen worden.

Wat je kunt doen is de Attributen opslitsen in een type, dus opslaan als string, integer, datetime, text of double/float.

Wat je dan krijgt is bijvoorbeeld:

// de hoofd tabellen
suppliers
categories
products
prices
attributes
attribute_types
attribute_set

// de daadwerkelijke waarden van alle attributen
attribute_type_string
attribute_type_boolean
attribute_type_text
attribute_type_integer
attribute_type_double
attribute_type_datetime

// de relaties tussen de verschillende tabellen
categories_products
suppliers_prices
products_prices
products_attributes
products_suppliers
attribute_sets_categories
attributes_attribute_sets

Hierdoor kun je ook prima heel veel leveranciers koppelen aan heel veel producten, kun je ook de producten in een 'soort van' AttributeSet zetten, dit heeft als voordeel dat je, als je een import doet op de categorie "schoenen", je van te voren al weet welke attributen daartoe behoren. Vervolgens kun je je import daar door heen halen, alle waarden invullen die nog misten en als je het echt wilt, ook nog eventueel opslaan welke leverancier met welke informatie over dat product kwam, maar dat moet je zelf bepalen denk ik.


Het idee is hier, je standaard attributen definieer je van te voren, breedte, lengte, hoogte, diepte, gewicht etc kunnen allemaal opgeslagen worden als een AttributeType van float of double.

Veel attributen zoals name, short, size, shoe_size, kleur kunnen allemaal het AttributeType String krijgen omdat je ze zeer waarschijnlijk als tekst wilt opslaan.

Andere attributen zoals description etc wil je dan weer opslaan als text, omdat die langer kunnen zijn dan die maximum van 255 karakters.

De prijs zou ik wel apart opslaan, niet in je entiteiten schema, juist omdat dit waarschijnlijk veel vaker gewijzigd wordt. Zoals iemand anders hier al aangegeven heeft, wil je daar een min/max op kunnen zetten en ik zou het gewoon puur op inserts houden, die data in de tabel niet muteerbaar maken. Dan haal je alleen de meest recente prijzen voor de leveranciers op, heb je toch een historisch verloop van de prijzen van die leverancier.

Afijn, dit wordt een veel te lange post, als je hier denkt iets mee te kunnen en je hebt vragen, antwoord ik dan wel verder

[Gigazone]

Xalephsis schreef op woensdag 8 mei 2019 @ 17:15:
Over het database ontwerp, wat je kunt doen is de EAV iets verder opslitsen. Je hebt immers de entiteit Product, de entiteit Prijs, de entiteit Leverancier en je wilt dat alle Attributen gekoppeld kunnen worden.

Bedankt voor je uitgebreide antwoord!

Inderdaad zijn zowel de Attributes en de Categories nog even een dingetje
Ik ga uw suggesties even rustig laten bezinken en de volgende stap plannen.

[michel91]

Wanneer je alleen met CSV bestanden en niet met XML werkt, zou ik je graag willen wijzen op csvkit, de csvsql tool. Hiermee heb je in 1 regel een csv omgezet naar een nieuwe sql tabel.

Daarna kan je bijvoorbeeld via stored procedures de geïmporteerde tmp tabel querien en de data overzetten naar je nieuwe tabel.

Het voordeel hiervan, is dat je niet hoeft na te denken over geheugen verbruik. Ook heb je een csv met 500.000 records binnen 2 seconden naar jouw eigen tabel met aanbieders.

[Gigazone]

michel91 schreef op zondag 19 mei 2019 @ 09:03:
Wanneer je alleen met CSV bestanden en niet met XML werkt, zou ik je graag willen wijzen op csvkit, de csvsql tool. Hiermee heb je in 1 regel een csv omgezet naar een nieuwe sql tabel.

Daarna kan je bijvoorbeeld via stored procedures de geïmporteerde tmp tabel querien en de data overzetten naar je nieuwe tabel.

Het voordeel hiervan, is dat je niet hoeft na te denken over geheugen verbruik. Ook heb je een csv met 500.000 records binnen 2 seconden naar jouw eigen tabel met aanbieders.

Bedankt voor de feedback.

Is dat niet vergelijkbaar met LOAD DATA INFILE die ik nu gebruik om csv naar een tmp tabel importeren?

[michel91]

Met csvsql hoef je niet na te denken over hoe je tabel er uit ziet. Voor de rest is het hetzelfde.