Phenobarbital con Soda

En muchos casos el proceso de migración de datos de una base de datos a otra se convierte en un tedioso proceso de convertir cosas de un punto a otro (ejemplo: crear archivos separados por coma o dumps SQL que debemos modificar) y muchas veces la gente no explota las capacidades de mySQL como “agente de migración” rápido y efectivo.

MySQL goza de una capacidad interesante para “emular” el comportamiento de otras bases de datos, esto se logra via la variable sql_mode; a nivel de la herramienta de migración mysqldump es a través del parámetro –compatible; con esta capacidad, podremos generar “volcados” (dumps) de tablas completas de una manera “compatible” con otras bases de datos, incluyendo postgreSQL.

La situación de ejemplo:

Tengo dos bases de datos, una en mysql llamada dpt_venezuela (División Político Territorial Venezolana), la obtuve a partir de interesantes transformaciones de la data que entrega el INE (Instituto Nacional de Estadística) que es una vulgar hoja de excel a la cual hay que hacerle muchas transformaciones en los datos (y que gracias a LOAD DATA INFILE en mySQL eso es un paseo); tengo las 4 tablas (entidades, municipios, parroquias y ciudades) en un schema “dpt” dentro de una DB en postgreSQL para un proyecto (iut); la idea ahora es llenar esas tablas sin tener que hacerlo a mano ni tener que generar un DUMP para trabajar con COPY, sino hacerlo directo y “en vivo”.

Describe de la tabla dpt_entidades (mySQL)

CREATE TABLE `dpt_entidades` (
  `id_entidad` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `entidad` varchar(50) DEFAULT NULL,
  `cod_entidad` char(3) DEFAULT NULL,
  `capital` varchar(128) DEFAULT NULL,
  `mostrar` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_entidad`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

Tabla dpt.entidades (postgreSQL):

CREATE TABLE dpt.entidades
(
  id_entidad int4 NOT NULL,
  entidad varchar(65) NOT NULL,
  cod_entidad char(3) NOT NULL,
  capital varchar(128),
  mostrar int2 NOT NULL DEFAULT 1,
  CONSTRAINT dpt_entidades_pk PRIMARY KEY (id_entidad)
)
WITHOUT OIDS;
ALTER TABLE dpt.entidades OWNER TO postgres;

Entendiendo a mysqldump:

mysqldump puede generar archivos separados por coma, xml o en su defecto (que usaremos) sentencias INSERT INTO; como hay diferencias sintácticas entre mySQL y postgreSQL usaremos un parámetro de mysqldump conocido como “–compatible” y que recibe como valor la palabra “postgresql”, el resto de parámetros son útilitarios de mysqldump (–skip-opt elimina comentarios y opciones “solo mysql” y -n me permite obviar las sentencias de CREATE TABLE de la tabla respectiva, –tables me permite generar una lista de tablas que serán exportadas).

Nota: debemos agregar –default-character-set=utf8 ya que por defecto, mysql hará el dump en latin1 y postgreSQL es por defecto utf8.

Inicialmente, la sentencia de mysqldump quedaría asi:

mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades

El único problema que tenemos ahora, es que la sentencia INSERT INTO generada:

INSERT INTO "dpt_entidades" VALUES (1,'Distrito Capital','DCA','Caracas',1);

No es aún compatible con postgresql, ya que como verán, la relación public.dpt_entidades no existe, sino que es dpt.entidades (schema: dpt, tabla: entidades); pero no hay nada que el comando linux sed no pueda arreglar.

Arreglando la salida con sed:

sed es en resumidas cuentas un comando editor; nos permite hacer sustituciones y reemplazos sobre archivos o sobre la salida std de nuestra consola; por ende, podemos pasar toda la salida del comando mysqldump a través de sed y modificar lo que queramos, en nuestro caso, un reemplazo de “dpt_entidades” > dpt.entidades.

Para ello, adicionalmente usamos pipe ( | ) un “concatenador” de la consola Unix, que nos permite indicar que la salida de un comando irá integramente hacia la entrada del comando siguiente; de esta manera, nuestra sentencia mysqldump ahora dice:

mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades | sed -e "s|\"dpt_entidades\"|dpt.entidades|"

Ahora si vemos, nuestras sentencias INSERT tienen la forma que postgreSQL necesita:

INSERT INTO dpt.entidades VALUES (1,'Distrito Capital','DCA','Caracas',1);

Ahora solo falta entregarle estas sentencias a psql (el comando de entrada de postgreSQL).

psql y entrando datos a postgreSQL:

Usar psql es bastante simple, solamente convocamos la DB (iut) usando el usuario owner de la misma (jesuslara) y pidiendo password (-W), en nuestro caso, recibirá datos de entrada usando PIPE ( | ) que viene desde el comando mysqldump+sed anteriormente ejecutado:

psql -U jesuslara -W -h localhost -d iut

y la cosa al final queda:

mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades | sed -e "s|\"dpt_entidades\"|dpt.entidades|" | psql -U jesuslara -W -h localhost -d iut

Entrando los datos correctamente hasta la tabla dpt.entidades de la base de datos iut.

Repetimos esto para municipios, parroquias o ciudades (o de plano, hacemos un dump de todas las tablas, agrandando acorde el sed para transformar todas las salidas a la vez) y tendremos migrados nuestras tablas desde mysql a postgresql sin necesidad de archivos intermedios.

Usando mySQL como gestor de migraciones:

sql_mode y –compatible son grandes aliados a la hora de migrar datos; LOAD DATA INFILE es mucho más poderoso que COPY y por ejemplo, hay muchas cosas allá afuera en MS Access o MS Fox Pro que ameritan ser migradas y mySQL ayuda muchisimo para cargar (además de normalizar y transformar) la data que venga de orígenes heterogéneos y llevarla limpia y sin problemas a postgreSQL.

Que disfruten este truco de la abuela!.

Nota: recuerden que sed es un comando muy poderoso y pueden por ejemplo, aplicar expresiones regulares de reemplazo para la transformación de los datos y los metadatos.

This entry was posted on Tuesday, June 17th, 2008 at 1:03 am and is filed under Cultura Libre, Databases, PlanetaLinux, trucos de la abuela. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.