[MySQL] Data migreren naar nieuwe structuur

[Spooksel]

Ik zit met een databasemigratie waarbij ik op zoek ben naar een goeie manier om data van het oude schema naar het nieuwe te krijgen, met name omdat de indeling in het nieuwe schema niet hetzefde is als de oude.

Hier een pseudo tabel 'Cursussen' zoals die in het oude scheme voor kan komen:

code:

| id | title               | start_date | price  |
| 1  | Some course title 1 | 2019-10-09 | 100.00 |
| 2  | Some course title 2 | 2019-10-09 | 100.00 |

En hier twee pseudo tabellen 'Cursussen' en 'Vertalingen' zoals die in het nieuwe schema voor kunnen komen:

code:

| id | title_tid | start_date | price  |
| 1  | 1         | 2019-10-09 | 10000 |
| 2  | 2         | 2019-10-09 | 10000 |

| id | content_en          | content_nl |
| 1  | Some course title 1 | null       |
| 2  | Some course title 2 | null       |

_{Ja, deze manier van vertalingen managen is superslecht... maar het is ff ter illustratie.}

Anyway, nu is mijn issue dus dat ik me afvraag hoe ik dergelijke migraties het beste aan kan pakken. Als het een kleinschalig database modelletje is dan zou je bijvoorbeeld een PHP script kunnen laten draaien wat in de kern het volgende doet:

code:

#1 Haal alle cursussen op uit het oude schema
foreach($courses as $course) {
   #2 Maak een vertaling aan en sla daarin de cursustitel op
   #3 Haal het ID van die vertaling op
   #4 Maak een nieuw cursusrecord aan en gebruik hierbij de vertaling ID als title_tid in het nieuwe schema
   #5 Sla het record in het nieuwe schema op
}

Dit gaat natuurlijk performancewijs nogal extreem problematisch worden wanneer je tabellen wat complexer worden, laat staan wanneer je het moet herhalen voor tientallen andere use cases met tabellen die in sommige gevallen miljoenen records bevatten.

Mijn vraag is dus: Hoe kan ik dit zo efficient mogelijk aanpakken?

Ik heb dus niet te maken met simpelweg 1:1 overzetten van data, d'r moet dus data naar verschillende tabellen verplaatst worden wat dan weer gekoppeld moet worden aan elkaar. Ook zullen er soms datatypes omgezet moeten worden, bijv van float/decimal naar integer.

Ben ik nu 'gedoemd' tot een custom conversiescript in iets als PHP (of wat werkt er beter voor zoiets?) of kan ik het ook in SQL zelf oplossen?

[Room42]

Je kunt misschien een query maken die de data als de gewenste output ophaalt en dat resultaat weer inserten in de database.

Je zou het zelfs in 1 query kunnen doen. Pseudocode:
INSERT INTO new (column1, column2, column3,...) VALUES ((SELECT a, b, c FROM old));

Maar als je er 'logica' op moet toepassen is PHP misschien makkelijker.
^{Ik had de post niet volledig gelezen.}

[ Voor 29% gewijzigd door Room42 op 09-10-2019 11:25 ]

[The Eagle]

Je zou het in SQL kunnen doen, maar zoals je zelf al bedacht had is dat behoorlijk omslachtig.
Je probleem is business en applicatierules. Iemand zal moeten zeggen hoe het nieuwe model er uit komt te zien, welke referentiele integriteitsregels er gelden, etc etc.

Ik denk dat je dit soort dingen het makkelijkste aan kunt vliegen met een ETL tool. Die zijn er voor gemaakt.

[Spooksel]

Room42 schreef op woensdag 9 oktober 2019 @ 11:22:
Je kunt misschien een query maken die de data als de gewenste output ophaalt en dat resultaat weer inserten in de database.

Je zou het zelfs in 1 query kunnen doen. Pseudocode:
INSERT INTO new (column1, column2, column3,...) VALUES ((SELECT a, b, c FROM old));

Maar als je er 'logica' op moet toepassen is PHP misschien makkelijker.
^{Ik had de post niet volledig gelezen.}

Dit is ben ik bang te straight forward, zeker wanneer je de inhoud van records op moet splitsen naar verschillende tabellen en daarbij de ID's weer terug moet koppelen om de boel bij elkaar te houden.

Edit: Eh, stiekem tussendoor de rest van de post lezen he

[ Voor 4% gewijzigd door Spooksel op 09-10-2019 11:28 ]

[CurlyMo]

Ik zou net als @Room42 dit zoveel mogelijk in de database zelf proberen te doen. Dat is meestal toch het goedkoopste.

Spooksel schreef op woensdag 9 oktober 2019 @ 11:26:
[...]

Dit is ben ik bang te straight forward, zeker wanneer je de inhoud van records op moet splitsen naar verschillende tabellen en daarbij de ID's weer terug moet koppelen om de boel bij elkaar te houden.

Dat is simpelweg een kwestie van de juiste volgorde van vullen aanhouden.

[Spooksel]

Maar waar ik bijvoorbeeld ook benieuwd naar ben, is het bijvoorbeeld handig wanneer er een migratie appje gemaakt zou worden met iets as GO/Golang?

PHP is tenslotte niet echt mega efficient wanneer je met grote datasets moet gaan werken. Nou is het natuurlijk onrealistisch om te denken dat dataprocessing van de ene naar de andere taal ineens 1000x sneller wordt, maar volgens mij zouden er toch een hoop snellere opties moeten zijn dan PHP.

[ Voor 29% gewijzigd door Spooksel op 09-10-2019 14:14 ]

[Room42]

Whatever floats your boat. Je kunt natuurlijk ook meerdere processen tegelijk draaien (geef elk proces een eigen deel van de dataset dan kun je meerdere PHP-processen tegelijk starten.

[ Voor 14% gewijzigd door Room42 op 09-10-2019 14:14 ]

[Banpei]

Persoonlijk zou ik het zelf proberen zoveel mogelijk op te lossen in de database: als je grote datasets gaat migreren moet je eerst alle rijen ophalen uit de database, modificeren en dan weer terug plaatsen in de database. Daarnaast is het splitsen van zo'n tabel een eitje in MySQL vergeleken met de hoeveelheid code die je in PHP moet schrijven om het te verplaatsen en de lookup te gaan doen.

Ik zou er zoiets in pseudo sql van maken:
1. start transactie
2. insert alle titles in de vertalingen
3. insert alle cursussen met een join op vertalingen (titel Engels)
4. commit

Tpe conversion is ook makkelijk te doen in MySQL en je zou zelfs triggers op de cursussen tabel in het oude schema kunnen zetten zodat nieuwe inserts automagisch naar het nieuwe schema gezet wordt.

[Vloris]

Spooksel schreef op woensdag 9 oktober 2019 @ 11:16:
Hier een pseudo tabel 'Cursussen_origineel' zoals die in het oude scheme voor kan komen:

code:

| id | title               | start_date | price  |
| 1  | Some course title 1 | 2019-10-09 | 100.00 |
| 2  | Some course title 2 | 2019-10-09 | 100.00 |

En hier twee pseudo tabellen 'Cursussen' en 'Vertalingen' zoals die in het nieuwe schema voor kunnen komen:

code:

| id | title_tid | start_date | price  |
| 1  | 1         | 2019-10-09 | 10000 |
| 2  | 2         | 2019-10-09 | 10000 |

| id | content_en          | content_nl |
| 1  | Some course title 1 | null       |
| 2  | Some course title 2 | null       |

Even dit letterlijke voorbeeld, volgens mij is dat best heel simpel in puur sql (ik heb je oude tabel even hernoemd naar Cursussen_origineel, zodat die naast de twee nieuwe tabellen kan blijven bestaan):

SQL:

INSERT INTO Vertalingen (id, content_en) SELECT id, title FROM Cursussen_origineel;
INSERT INTO Cursussen (id, title_tid, start_date, price) SELECT id, id, start_date, price FROM Cursussen_origineel;

[Spooksel]

Heh, maar dan ga je er wel vanuit dat je voor iedere cursus ook maar exact 1 vertaling aanmaakt, anders loopt het snel in de soep met de ID's

Maar wat als dit nou je situatie is?

code:

| id | title               | subtitle               | start_date | price  |
| 1  | Some course title 1 | Some course subtitle 1 | 2019-10-09 | 100.00 |
| 2  | Some course title 2 | null                   | 2019-10-09 | 100.00 |

En hier twee pseudo tabellen 'Cursussen' en 'Vertalingen' zoals die in het nieuwe schema voor kunnen komen:

code:

| id | title_tid | subtitle_tid | start_date | price  |
| 1  | 1         | 2            | 2019-10-09 | 10000 |
| 2  | 3         | null         | 2019-10-09 | 10000 |

| id | content_en             | content_nl |
| 1  | Some course title 1    | null       |
| 2  | Some course subtitle 1 | null       |
| 3  | Some course title 2    | null       |

[azteke]

Vloris ging er blijkbaar vanuit dat het ID van Vertallingen hetzelfde ID is van Cursussen (zie inserts). In theorie kan dat werken indien je maar 1 vertaling per Cursus hebt. Je voegt nu info toe die die logica kapot maakt

In feite heb je een analyse van de data nodig om te kunnen bepalen wat je nodig hebt om de migratie uit te voeren. ETL tools zijn voor dit soort use cases gemaakt maar daar heb ik niet veel ervaring mee. Met een zelf geschreven PHP / python / whatever script ben je het meest flexibel, maar dat is ook het langzaamst. Als het in SQL kan dan is dat het meest eenvoudig.

Wat wellicht een (minder elegante) oplossing kan zijn is eerst alle content in Vertalingen te zetten, en dan de insert in Cursussen doen waarbij je de ID van vertalingen opvraagt. Let wel op dat (als de titels dubbel voor kunnen komen) de applicatielaag er tegen moet kunnen dat bij een wijziging in naam er gecontroleerd moet worden of het record gemaakt of geupdate moet worden.

[Spooksel]

Mjah, mijn conclusie is inmiddels wel dat het waarschijnlijk niet 'gewoon' te doen is met een (heeeeeel uitgebreide) query. Omdat ik te maken ga krijgen met allerlei zaken als het uitsplitsen van data naar verschillende tabellen, het converteren van waardes (float -> int), etc etc moet ik dus wel een stukje software er tussenin gaan zetten om dat te managen.

So far dus een deel van mijn probleem.

Het tweede deel is dus dat ik op zoek ben naar een efficiente manier om dat te doen. ETL tools zijn hier dus een optie, maar ook het zelf schrijven van een script dat data ophaald en weer verwerkt op de gewenste manier. Hiervoor verschillen de opties echter ook!

Zo ben ik zelf bijvoorbeeld prima in staat om een PHP script te schrijven dat dit allemaal kan, maar is dat ook efficient? Wat als bijvoorbeeld, d'r vanuit gaande dat de beschikbare processing power hetzelfde is, een GOlang appje veeeeeeel sneller die verwerking kan doen dan PHP? Of.. anything else?

Daar zou ik ook graag wat inzichten in willen

[Jantje2000]

Ik weet niet tot welke applicaties je toegang hebt, maar zou SSIS in Visual Studio niets voor je zijn? Dan kun je een ETL proces bouwen, door middel van drag & drop, wat helemaal niet heel erg lastig is

[GlowMouse]

Het converteren van float naar int kan gewoon met SQL, dus ik snap niet zo goed waarom het met SQL niet lukt.

Ik verwacht dat je met PHP een prima migratiescript kunt schrijven, en dat je beter kunt focussen op de hoofdlijnen:
- lees je de data efficiënt in (bv. mbv joins of caching)?
- combineer je meerdere rijen in één INSERT query?
- zet je indices tijdelijk uit?
- doe je niet alles in losse transacties?

[Kalentum]

Ook wel handig om te weten:
- Over hoeveel data gaat het? Paar duizend records of miljoenen?
- Hoeveel van die data is stale, dus wordt niet meer gewijzigd (oude cursussen in jouw voorbeeld)
- Is downtime acceptable, desnoods op een rustig moment?

Er is namelijk geen standaardantwoord. IMHO maakt de taal niet zoveel uit (tenzij je voor elk record ingewikkelde transformaties moet doen), meeste tijd gaat als het goed is in de database zelf zitten. Ik zou gewoon die taal gebruiken die het makkelijkst voor je is.

En ook handig om rekening te houden met dat je de migratie moet afbreken (bv als er performance issues zijn) en dat je gewoon weer opnieuw kan starten.

[storeman]

Ik zie nog geen issues wat je niet in SQL op kan lossen. SQL is superkrachtig en bovendien hiervoor gemaakt. Denk er aan dat je ook met tijdelijke kolommen en tabellen kan werken.

Typecasts, kan in SQL.

Alles netjes in een transactie, alles gemigreerd of niets.

SQL ondersteunt ook functies, wel wat ingewikkelder, maar dan wordt het nog vele malen krachtiger.

[Spooksel]

@Kalentum Het gaat om een DB met ong 80 tabellen die gezamelijk nog net geen 5M records bevatten. 2,5GB aan data bij elkaar. Redelijk veel stale data en downtime -> check, dit is iets waarvoor het de toegang tot het systeem rustig een weekend plat gooien.

@storeman TIjdelijke kolommen zou idd ook een optie zijn om zo identifiers te geven aan records waardoor ze later bij het verbinden aan elkaar weer makkelijker terug te vinden zijn.

[storeman]

Op een beetje systeem hoeft dit geen half uur te duren. Maar vaak zit het toch tegen

[gekkie]

storeman schreef op zaterdag 12 oktober 2019 @ 00:02:
Op een beetje systeem hoeft dit geen half uur te duren. Maar vaak zit het toch tegen

Mwah je kan droog oefenen op een kopietje. En mocht het nog dramatisch verlopen dan is 2.5 GB terug zetten ook niet zo'n drama lijkt me.

[Banpei]

Een simpel migratie script zou naar mijn mening voldoende moeten zijn en probeer zoveel mogelijk in SQL te doen. Zoals gekkie al aangaf: even een kopie van de data op een (lokale) mysql server zetten zodat je goed kan oefenen en zien of na de migratie ook de nieuwe applicatie er mee kan werken.

ETL zou ik persoonlijk niet aan beginnen tenzij je van plan bent het vaker te gebruiken. Natuurlijk is ETL (extract-transform-load) bedoeld om data te verplaatsen en te modificeren. Maar elke ETL tool heeft een behoorlijke learning curve zodra je je buiten de standaard componenten begeeft. In het begin lijkt het allemaal rete overzichtelijk, maar zodra je bij tabel 80 bent is het waarschijnlijk ook een groot spinnenweb geworden of het je inmiddels 40 dependent subjobs gemaakt.

[scosec]

Dit lijkt zoals eerder al aangegeven wordt een prima klus voor SQL. Daarin kun je prima wat queries maken waarmee je dit vraagstuk kunt oplossen.

SSIS kan een oplossing voor je zijn. Je kunt er relatief eenvoudig data migraties mee uitvoeren. Deze tooling is gemaakt voor het verwerken van grote hoeveelheden data. Dit zou ik pas toepassen als je data vanaf meerdere bronsystemen wil laden.

Als je op dezelfde host blijft werken zou ik voor SQL kiezen. Daar kan geen programmeertaal tegen op.

[Spooksel]

Inmiddels wat verder, oa bezig geweest me te verdiepen in stored functions

Ik heb bijvoorbeeld de volgende functie gemaakt:

SQL:

CREATE FUNCTION store_translation (translationText blob)
    RETURNS int
BEGIN
    INSERT INTO `translations` (`content_en`) VALUES (translationText);
    RETURN LAST_INSERT_ID();
END;

Deze in SQL ingevoerd en daarna de volgende query gedraaid:

SQL:

INSERT INTO courses_new (title_tid, subtitle_tid, start_date, price)
SELECT store_translation(title), store_translation(subtitle), start_date, price FROM courses_old;

En dat gaf me een paar mooie records in de nieuwe tabel met vertalingen en alle respectievelijke ID's terug gekoppeld naar de nieuwe tabel voor cursussen.
_{Nee, die price is hierin nog niet geconverteerd}

Anyway, naar dit soort dingen ben ik dus op zoek. Dit gaat namelijk ontelbaar keer sneller dan wanneer de hele boel eerst via een PHP scriptje moet lopen bijv.

Edit:
Voor de vorm ook maar ff mn float -> int conversie:

SQL:

CREATE FUNCTION roundToUnits (amount float)
RETURNS int
RETURN amount / (1 / POWER(10,2));

Deze is dus om bedragen cursussen en betalingen enzo om te zetten van float naar int. Iets als 255,99 wordt dan 25599. Maar het gaat op deze manier om ervoor te zorgen dat 255 dan ook echt terug komt als 25500 bijv ipv 255, waardoor het na de conversie weer als 2,55 gezien zou worden.

[ Voor 19% gewijzigd door Spooksel op 14-10-2019 13:45 ]