We're restructuring our entire database. Said that, the database structure has changed drastically. Some table fields will be translated to table rows; there will be a lot of validations; some tables are broken up into multiple tables and other are combined. Basically we're changing the legacy database to 3NF.
New schema is ready and I am tasked to do ETL. How should I proceed? I am unfamiliar with any ETL tools so there is a learning curve there. I was thinking to write stored procedures, are they capable of dealing with all these complex stuff I am trying to achieve? I can write PHP scripts so I have better control over data validations but not sure if that is a way to go because of timeout and long scripts. The database size is about 6 GB and it has about 450 tables.
Suggestions please.
I would recommend creating a PHP migration script, which is run from the command line. PHP scripts that are run from the command line do not time out.
You could also add "set_time_limit(0)" at the top of your script:
<?php
set_time_limit(0);
//rest of the code goes here
I'm not sure stored procedures or commercial ETL tools are necessary. Just make sure to break the migration script code into appropriate functions / methods, and you should be fine.
I wouldn't create a home-grown system. The few I've seen tend be fragile and lacking. It will take you months, or even years to get to the point of a basic ETL tool.
More on the pros and cons of an ETL tool there.