Toon posts:

[MySQL] Data migreren naar nieuwe structuur

Pagina: 1
Acties:

Onderwerpen


  • Spooksel
  • Registratie: oktober 2000
  • Laatst online: 13:33
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:
1
2
3
| 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:
1
2
3
4
5
6
7
| 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
2
3
4
5
6
7
#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?

Bevalt mijn schrijfsel je niet? www.korrelatie.nl


  • u34186
  • Registratie: september 2001
  • Niet online
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. :P
Ik had de post niet volledig gelezen. O-)

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


  • The Eagle
  • Registratie: januari 2002
  • Laatst online: 21:48

The Eagle

I wear my sunglasses at night

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.

Al is het nieuws nog zo slecht, het wordt leuker als je het op zijn Brabants zegt :)


  • Spooksel
  • Registratie: oktober 2000
  • Laatst online: 13:33
Evanescent 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. :P
Ik had de post niet volledig gelezen. O-)
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 :P

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

Bevalt mijn schrijfsel je niet? www.korrelatie.nl


  • CurlyMo
  • Registratie: februari 2011
  • Laatst online: 21:49

CurlyMo

www.pilight.org

Ik zou net als @Evanescent 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.

geen vragen via PM die ook op het forum gesteld kunnen worden.


  • Spooksel
  • Registratie: oktober 2000
  • Laatst online: 13:33
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]

Bevalt mijn schrijfsel je niet? www.korrelatie.nl


  • u34186
  • Registratie: september 2001
  • Niet online
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 u34186 op 09-10-2019 14:14]


  • Banpei
  • Registratie: juli 2001
  • Laatst online: 17-03-2020

Banpei

Hachiroku on this touge?

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. B)

AE86 gevonden! | So what I thought I'd do was, I'd pretend to be one of those deaf-mutes.


  • Vloris
  • Registratie: december 2001
  • Laatst online: 22:13
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:
1
2
3
| 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:
1
2
3
4
5
6
7
| 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:
1
2
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
  • Registratie: oktober 2000
  • Laatst online: 13:33
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:
1
2
3
| 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:
1
2
3
4
5
6
7
8
| 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       |

Bevalt mijn schrijfsel je niet? www.korrelatie.nl


  • azteke
  • Registratie: september 2002
  • Laatst online: 29-11 23:58

azteke

King

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.

CMDR azteke || You never get a second chance to make a first impression...


  • Spooksel
  • Registratie: oktober 2000
  • Laatst online: 13:33
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 :)

Bevalt mijn schrijfsel je niet? www.korrelatie.nl


  • Jantje2000
  • Registratie: februari 2016
  • Laatst online: 04-12 16:27
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

De wet van Murphy: Alles wat fout kan gaan zal fout gaan.


  • GlowMouse
  • Registratie: november 2002
  • Niet online

GlowMouse

wees solidair

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?

geeft geen inhoudelijke reacties meer


  • Kalentum
  • Registratie: juni 2004
  • Laatst online: 22:00
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.

PVoutput


  • storeman
  • Registratie: april 2004
  • Laatst online: 03-12 21:43
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.

"Chaos kan niet uit de hand lopen"


  • Spooksel
  • Registratie: oktober 2000
  • Laatst online: 13:33
@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.

Bevalt mijn schrijfsel je niet? www.korrelatie.nl


  • storeman
  • Registratie: april 2004
  • Laatst online: 03-12 21:43
Op een beetje systeem hoeft dit geen half uur te duren. Maar vaak zit het toch tegen ;)

"Chaos kan niet uit de hand lopen"


  • gekkie
  • Registratie: april 2000
  • Laatst online: 20:38
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
  • Registratie: juli 2001
  • Laatst online: 17-03-2020

Banpei

Hachiroku on this touge?

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. :D

AE86 gevonden! | So what I thought I'd do was, I'd pretend to be one of those deaf-mutes.


  • scosec
  • Registratie: februari 2016
  • Laatst online: 17:23
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
  • Registratie: oktober 2000
  • Laatst online: 13:33
Inmiddels wat verder, oa bezig geweest me te verdiepen in stored functions :)

Ik heb bijvoorbeeld de volgende functie gemaakt:
SQL:
1
2
3
4
5
6
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:
1
2
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 :P

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:
1
2
3
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]

Bevalt mijn schrijfsel je niet? www.korrelatie.nl

Pagina: 1


Nintendo Switch (OLED model) Apple iPhone 13 LG G1 Google Pixel 6 Call of Duty: Vanguard Samsung Galaxy S21 5G Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True

Tweakers maakt gebruik van cookies

Bij het bezoeken van het forum plaatst Tweakers alleen functionele en analytische cookies voor optimalisatie en analyse om de website-ervaring te verbeteren. Op het forum worden geen trackingcookies geplaatst. Voor het bekijken van video's en grafieken van derden vragen we je toestemming, we gebruiken daarvoor externe tooling die mogelijk cookies kunnen plaatsen.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Forum cookie-instellingen

Bekijk de onderstaande instellingen en maak je keuze. Meer informatie vind je in ons cookiebeleid.

Functionele en analytische cookies

Deze cookies helpen de website zijn functies uit te voeren en zijn verplicht. Meer details

janee

    Cookies van derden

    Deze cookies kunnen geplaatst worden door derde partijen via ingesloten content en om de gebruikerservaring van de website te verbeteren. Meer details

    janee