Este artículo se basa en una traducción (y transcripción) de un podcast de PHP Abstract, el cual explica de manera sencilla y a través de un ejemplo, como migrar o importar datos de una supuesta table de empleados a otra tabla existente de clientes, la cual viene con otro formato distinto.
Esta es la tabla de empleados (Employee), el origen de datos:
+-----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | YES | | NULL | |
| Dept | varchar(255) | YES | | NULL | |
| Join_Date | varchar(50) | YES | | NULL | |
| Email | varchar(255) | YES | | NULL | |
| Comments | text | YES | | NULL | |
+-----------+-----------------------+------+-----+---------+----------------+
Mientras que el sistema al que se quiere migrar es la siguiente tabla, más una tabla auxiliar para facilitar las búsquedas de los departamentos:
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| employee_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| department_id | int(11) | YES | | NULL | |
| join_date | datetime | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| comments | text | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| department_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
De la tabla original, debemos hacer las siguientes transformaciones:
- El id se transforma en employee_id y el tipo de dato cambia ligeramente
- El Name se seàra en dos, first_name y last_name
- El campo Dept se necesita sacar fuera a una tabla de búsqueda y se crea el department_id en esta tabla
- El campo Join_date tiene que cambiar su tipo de dato
- Y los campos Email and Comments permanecen inmutables
Convertir el tipo de dato - Employee ID
Es bastante sencillo, simplemente se añade una nueva columna dentro de la tabla Employees y luego se cargan los datos
alter table Employees add column employee_id int;
update Employees set employee_id = id;
Separando Strings - El campo Name
Por suerte, los datos que se almacenan en este campo tienen el nombre y el apellido separados por un espacio. Para separar el nombre y el apellido empezaremos con la siguiente sentencia select:
select Name , substring_index(Name,' ',1) as first_name,
substring_index(substring_index(Name,' ',2),' ',-1) as last_name
from Employees limit 10;
+--------------------+------------+-------------+
| Name | first_name | last_name |
+--------------------+------------+-------------+
| Anika Lott | Anika | Lott |
| Jenette Gibson | Jenette | Gibson |
| Lacy Barton | Lacy | Barton |
| Noelani Peck | Noelani | Peck |
| Lacy Prince | Lacy | Prince |
| Glenna Rowland | Glenna | Rowland |
| Isabella Hall | Isabella | Hall |
| Hadley Fitzpatrick | Hadley | Fitzpatrick |
| Elaine Steele | Elaine | Steele |
| Kathleen Davidson | Kathleen | Davidson |
+--------------------+------------+-------------+
Ahora nos toca modificar la tabla con los nuevos campos e ir poniendo los datos en su correspondiente lugar:
alter table Employees add column first_name varchar(50);
alter table Employees add column last_name varchar(50);
update Employees set first_name = substring_index(Name,' ',1),
last_name = substring_index(substring_index(Name,' ',2),' ',-1)
Del tipo string para las Fechas al tipo Fecha real
Tenemos una columna llamada join_date en nuestra tabla Employee, por lo que crearemos otra columna con otro nombre:
alter table Employees add column join_date_formatted datetime;
De nuevo, una sentencia Select primero, y a continuación la conversión con una sentencia Update:
select join_date , str_to_date(join_date, '%m-%d-%y') as machine_date
from Employees limit 10;
update Employees set join_date_formatted = str_to_date(join_date, '%m-%d-%y');
MySQL por defecto pondrá la hora a la medianoche del día seleccionado.
Datos enumerados (o casi enumerados) pasan a la tabla de búsqueda
En un principio, vamos a echar un vistazo a los datos que contiene la tabla de origen:
select distinct Dept from Employees;
+--------------------------+
| Dept |
+--------------------------+
| Human Resources |
| Research and Development |
| Accounts |
| Finances |
| Quality Assurance |
| Accounting |
| Tech Support |
| LegalPayroll |
| QA |
| Sales and Marketing |
| Customer Service |
| Advertising |
+--------------------------+
Observando los datos, hay unas cuantas cosas que tenemos que tener en cuenta, como por ejemplo “LegalPayroll” debería ser “Legal and Payroll”, y tenemos también “Accounts” y “Accounting” además de “Quality Assurance” y “QA”, que son de hecho el mismo departamente. Como el conjunto de datos es realmente pequeño, vamos a pasar los datos manualmente a la tabla de búsqueda, junto con un índice.
+---------------+--------------------------+
| department_id | name |
+---------------+--------------------------+
| 1 | Human Resources |
| 2 | Research and Development |
| 3 | Accounting |
| 4 | Finances |
| 5 | Quality Assurance |
| 6 | Tech Support |
| 7 | Legal and Payroll |
| 8 | Sales and Marketing |
| 9 | Customer Service |
| 10 | Advertising |
+---------------+--------------------------+
A continuación, creamos la nueva columna en la vieja tabla de Employees y vamos dandole las entradas “fáciles”.
alter table Employees add column department_id int;
update Employees set department_id = (select department.department_id
from department where Employees.Dept = department.name);
Echando un vistazo a los registros que no se actualizaron, nos queda todavía cosas para corregir:
select Name, Dept from Employees where department_id is null;
+--------------------+--------------+
| Name | Dept |
+--------------------+--------------+
| Lacy Barton | Accounts |
| Isabella Hall | Accounts |
| Kathleen Davidson | Accounts |
| Chiquita Rodriguez | LegalPayroll |
| Emi Ochoa | LegalPayroll |
| Wanda Sullivan | QA |
| Yen Wilder | QA |
| Echo Hopkins | LegalPayroll |
| Germane Stout | Accounts |
| Evangeline Oconnor | Accounts |
| Hadley Ochoa | QA |
| Carly Riley | LegalPayroll |
| Destiny Brock | QA |
| Mariam Baldwin | Accounts |
+--------------------+--------------+
update Employees set department_id = 3 where Dept = "Accounts";
update Employees set department_id = 5 where Dept = "QA";
update Employees set department_id = 7 where Dept = "LegalPayroll";
El paso final
Finalmente insertamos en la tabla employee con todos los nuevos datos preparados y formateados:
insert into employee select id, first_name, last_name, department_id, join_date_formatted,
Email, Comments from Employees;
Artículo original: DevZone
Podcast: PHP Abstract
Tags: base de datos, datos, migracion, sql
