PHP MYSQL ETL。 我应该使用ETL工具，存储过程还是php脚本？

Question

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.

Answer 1

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.