9 Caso práctico de transformación de PostgreSQL a MongoDB

Para demostrar la validez de los algoritmos de migración planteados, voy a basarme en una base de datos con la estructura y los datos planteados en formato relacional, concretamente voy a partir de una base de datos en PostgreSQL.

Después transformaré el esquema y los datos, migrando a una base de datos en MongoDB.

Por último haré unas consultas de ejemplo tanto en PostgreSQL como en MongoDB para comparar los resultados obtenidos y la sintaxis empleada.

9.1 Descripción de la base de datos

Voy a utilizar una base de datos que ya tiene el esquema planteado y los datos cargados en formato SQL.

Esta base de datos: http://www.postgresqltutorial.com/postgresql-sample-database/ corresponde a un modelo de alquiler de DVD que típicamente se podría encontrar en un establecimiento en el que los clientes están registrados y pueden alquilar películas con un tiempo máximo de alquiler y su histórico de alquileres. Además también incluye datos sobre las películas disponibles, los empleados y las tiendas.

En cuanto a datos, tiene un total de 44820 registros con las siguientes tablas:

Relación de tablas y número de registros por tabla

Por otro lado, el diagrama entidad relación es el siguiente:

Se pueden diferenciar tres bloques de datos relacionados de la siguiente manera:

• Las películas que tienen una lista de categorías y actores, relacionados mediante una tabla auxiliar cada una, derivada de una relación binaria N a N. Además las películas tienen asociadas un lenguaje y un histórico de alquileres.

• Los clientes un histórico de alquileres con sus pagos. Tanto los clientes como los dependientes tienen asociada una dirección que se descompone en ciudad y país.

• Cada dependiente tiene una lista de pagos que se asocia a cada alquiler de película. Además está asociado a una tienda y la tienda a su vez tiene una dirección asociada.

En casi todas las tablas hay una columna fecha de actualización que no aporta información y no es relevante, de forma que voy a obviar este campo en la transformación a MongoDB

9.2 Creación del esquema y carga de datos en PostgreSQL

La base de datos sobre la que parto tiene un volcado de los datos que se puede restaurar directamente en PostgreSQL.

Para cargar tanto los datos como la estructura me he apoyado en la herramienta de gestión para PostgreSQL: pgadmin III. Con ella he creado una base de datos de nombre “dvdrental” y utilizando la opción “restore” sobre la misma, he importado los datos proporcionados.

Las sentencias de creación de tablas son las siguientes:

actor

CREATE TABLE actor
(
  actor_id integer NOT NULL DEFAULT nextval('actor_actor_id_seq'::regclass),
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
)

address

CREATE TABLE address
(
  address_id integer NOT NULL DEFAULT nextval('address_address_id_seq'::regclass),
  address character varying(50) NOT NULL,
  address2 character varying(50),
  district character varying(20) NOT NULL,
  city_id smallint NOT NULL,
  postal_code character varying(10),
  phone character varying(20) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT address_pkey PRIMARY KEY (address_id),
  CONSTRAINT fk_address_city FOREIGN KEY (city_id)
      REFERENCES city (city_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

category

CREATE TABLE category
(
  category_id integer NOT NULL DEFAULT nextval('category_category_id_seq'::regclass),
  name character varying(25) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT category_pkey PRIMARY KEY (category_id)
)

city

CREATE TABLE city
(
  city_id integer NOT NULL DEFAULT nextval('city_city_id_seq'::regclass),
  city character varying(50) NOT NULL,
  country_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT city_pkey PRIMARY KEY (city_id),
  CONSTRAINT fk_city FOREIGN KEY (country_id)
      REFERENCES country (country_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

country

CREATE TABLE country
(
  country_id integer NOT NULL DEFAULT nextval('country_country_id_seq'::regclass),
  country character varying(50) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT country_pkey PRIMARY KEY (country_id)
)

customer

CREATE TABLE customer
(
  customer_id integer NOT NULL DEFAULT nextval('customer_customer_id_seq'::regclass),
  store_id smallint NOT NULL,
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  email character varying(50),
  address_id smallint NOT NULL,
  activebool boolean NOT NULL DEFAULT true,
  create_date date NOT NULL DEFAULT ('now'::text)::date,
  last_update timestamp without time zone DEFAULT now(),
  active integer,
  CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
  CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (address_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

film

CREATE TABLE film
(
  film_id integer NOT NULL DEFAULT nextval('film_film_id_seq'::regclass),
  title character varying(255) NOT NULL,
  description text,
  release_year year,
  language_id smallint NOT NULL,
  rental_duration smallint NOT NULL DEFAULT 3,
  rental_rate numeric(4,2) NOT NULL DEFAULT 4.99,
  length smallint,
  replacement_cost numeric(5,2) NOT NULL DEFAULT 19.99,
  rating mpaa_rating DEFAULT 'G'::mpaa_rating,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  special_features text[],
  fulltext tsvector NOT NULL,
  CONSTRAINT film_pkey PRIMARY KEY (film_id),
  CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id)
      REFERENCES language (language_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

film_actor

CREATE TABLE film_actor
(
  actor_id smallint NOT NULL,
  film_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id),
  CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id)
      REFERENCES actor (actor_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id)
      REFERENCES film (film_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

film_category

CREATE TABLE film_category
(
  film_id smallint NOT NULL,
  category_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  category category_enum,
  CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id),
  CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id)
      REFERENCES category (category_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id)
      REFERENCES film (film_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

inventory

CREATE TABLE inventory
(
  inventory_id integer NOT NULL DEFAULT nextval('inventory_inventory_id_seq'::regclass),
  film_id smallint NOT NULL,
  store_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
  CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id)
      REFERENCES film (film_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

language

CREATE TABLE language
(
  language_id integer NOT NULL DEFAULT nextval('language_language_id_seq'::regclass),
  name character(20) NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT language_pkey PRIMARY KEY (language_id)
)

payment

CREATE TABLE payment
(
  payment_id integer NOT NULL DEFAULT nextval('payment_payment_id_seq'::regclass),
  customer_id smallint NOT NULL,
  staff_id smallint NOT NULL,
  rental_id integer NOT NULL,
  amount numeric(5,2) NOT NULL,
  payment_date timestamp without time zone NOT NULL,
  CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
  CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES customer (customer_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id)
      REFERENCES rental (rental_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id)
      REFERENCES staff (staff_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

rental

CREATE TABLE rental
(
  rental_id integer NOT NULL DEFAULT nextval('rental_rental_id_seq'::regclass),
  rental_date timestamp without time zone NOT NULL,
  inventory_id integer NOT NULL,
  customer_id smallint NOT NULL,
  return_date timestamp without time zone,
  staff_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
  CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES customer (customer_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id)
      REFERENCES inventory (inventory_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id)
      REFERENCES staff (staff_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

staff

CREATE TABLE staff
(
  staff_id integer NOT NULL DEFAULT nextval('staff_staff_id_seq'::regclass),
  first_name character varying(45) NOT NULL,
  last_name character varying(45) NOT NULL,
  address_id smallint NOT NULL,
  email character varying(50),
  store_id smallint NOT NULL,
  active boolean NOT NULL DEFAULT true,
  username character varying(16) NOT NULL,
  password character varying(40),
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  picture bytea,
  CONSTRAINT staff_pkey PRIMARY KEY (staff_id),
  CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (address_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

store

CREATE TABLE store
(
  store_id integer NOT NULL DEFAULT nextval('store_store_id_seq'::regclass),
  manager_staff_id smallint NOT NULL,
  address_id smallint NOT NULL,
  last_update timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT store_pkey PRIMARY KEY (store_id),
  CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (address_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id)
      REFERENCES staff (staff_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

9.3 Creación del esquema y carga de datos en MongoDB

Para poder hacer la transformación del esquema y el posterior migrado de los datos he partido de la base de datos ya creada en PostgreSQL y me he apoyado en el diagrama entidad relación que aquí se aporta.

Para cada una de las tablas y su relación con el resto voy a exponer cómo sería su transformación en colección para MongoDB.

9.3.1 Enumerados

Lo primero que se observa de esta base de datos es que se están usando tres tablas que solo tienen 3 atributos: su identificador, un campo de texto y un campo fecha. Estas tablas son enumerados tal y como se expone en las reglas de transformación, de forma que siguiendo con la regla 4.3 estas tablas no tendrán su propia colección.

Estas tablas son:

• Languaje

• Category

• Country

• City

9.3.2 Casos N a N

Estas tablas no tendrá una colección propia siguiendo con la regla 4.1. Estas tablas son:

• Film_category

  Mantiene la relación 0 a N con category y con film.

• Film_actor

  Mantiene la relación 0 a N con actor y con film.

• Inventory

  Mantiene la relación 0 a N con film y con store, además de que la tabla rental hace referencia a ésta.

9.3.3 Resto de casos

El resto de tablas que tienen cardinalidad máxima 1 a 1, 1 a N, 0 a 1 ó 0 a N.

• Actor. Tiene unicamente una relación con la tabla film_actor, de forma que una tabla film tendrá una lista de actor asociados y viceversa. Actor tendrá su propia colección siguiendo con la regla 3.3 y será la colección film la que tenga un array de índices siguiendo la regla 6.2.i, la tabla film tiene más columnas que la tabla actor.

• Film. Tiene relaciones con varias tablas. Por un lado tendrá un array de objetos JSON de category y language siguiendo con la regla 5. También tiene una relación con la tabla inventory que desaparece de forma que siguiendo la regla 6.2.i tendrá un array de índices a store. Además y por le mismo motivo, tendrá un array de índices a actor por la regla 6.2.i.
  Siguiendo la regla 3.3 esta tabla tendrá su propia colección.

• Rental. Esta tabla tiene relación con varias tablas, además de sus propios campos:

  • Tiene una relación máxima 1 a N con la tabla payment siendo ésta quien tiene la clave foránea. Como ambas tablas tienen más relaciones rental tendrá su propia colección siguiendo la regla 3.3 y un array de índices a payment siguiendo la regla 6.1.

  • Tiene una relación máxima 1 a N con customer y con staff, siendo rental la tabla con las claves foráneas. Cómo se ha expuesto la tabla rental tendrá su propia colección, de forma que customer y staff tendrán un array de índices a rental siguiendo con la regla 6.1.

  • Tiene una relación con la tabla inventory, que no tendrá su propia colección. Al ser inventory una tabla de tipo n-aria y no ser de tipo N:N:N como cardinalidad máxima, será tratada como una binaria, de forma que siguiendo con la regla 6.1 la colección rental tendrá un array de índices de film y de store.

• Payment. Esta tabla tiene relación con varias tablas, además de sus propios campos:

  • Tiene una relación máxima 1 a N con customer y staff, siendo payment la tabla con las claves foráneas. Cómo se ha expuesto, la tabla payment tendrá su propia colección, de forma que customer y staff tendrán un array de índices a payment siguiendo con la regla 6.1.

  • Tiene una relación máxima 1 a N con la tabla rental siendo payment quien tiene la clave foránea. Como ambas tablas tienen más relaciones, payment tendrá su propia colección siguiendo la regla 3.3 y el array de índices lo tendrá rental, siguiendo la regla 6.1.

• Address. Esta tabla tendrá su propia colección siguiendo la regla 3.3, además tendrá un array objetos JSON de city y country siguiendo la regla 5. Además de esto tiene una relación máxima 1 a N con las tablas staff, customer y store con la clave en esas tablas por lo que siguiendo la regla 6.1 tendra un array de los índices en su colección.

• Customer. Tiene las siguientes tablas relacionadas:

  • Como se ha mencionado antes, tiene un array de índices de la tabla rental y payment siguiendo la regla 6.1.

  • Tiene una relación máxima con address y con store 1 a N siendo customer la que tiene las claves foráneas, la tabla customer tendrá su propia colección siguiendo la regla 3.2, por lo que adress y store tendrán un array de índices a customer siguiendo con la regla 6.1.

• Staff. Tiene relación con las tablas de forma muy similar a customer:

  • Tiene un array de índices de la tabla rental y payment siguiendo la regla 6.1.

  • Tiene una relación máxima 1 a 1 con la tabla store, siendo store la que tiene la clave foránea, al tener la tabla staff su propia colección siguiendo la regla 3.2, staff tendrá un listado de índices a store.

  • Tiene una relación máxima con address y con store 1 a N siendo staff la que tiene las claves foráneas, por lo que address y store tendrán un array de índices a staff siguiendo con la regla 6.1.

• Store. Debido a la referencia circular que tiene con staff, store tendrá su propia colección siguiendo la regla 3.4. Además toene relación con la tabla customer y film, de forma que tendrá un array de índices de las tablas customer y staff siguiendo la regl 6.1. Además tiene la clave foránea de address y staff con una relación máxima 1 a N y 1 a 1 respectivamente. Siguiendo la regla 6.1 address y staff tendrán un array de índices de store.

9.3.4 Colecciones

Finalmente las colecciones que se derivan de la transformación, junto a su sentencia de creación son las siguientes:

• Actor db.createCollection("actor")

• Address db.createCollection("address")

• Customer db.createCollection("customer")

• Film db.createCollection("film")

• Payment db.createCollection("payment")

• Rental db.createCollection("rental")

• Staff db.createCollection("staff")

• Store db. createCollection("store")

El resto de tablas serán embebidas en las colecciones que le corresponden.

9.3.5 Transformación

Para migrar los datos y crear las colecciones voy a usar un script implementado en JavaScript y ejecutado con NodeJS versión 6.11.0 que realizará la extracción de los datos de la base de datos en PostgreSQL y los pasará a la base de datos en MongoDB.

Lo primero que hago es la conexión a ambas bases de datos. Si la conexión va bien:

• Se crean los esquemas de las colecciones

• Se realizan las funciones de transformación. Casa función tiene las siguientes sentencias:

  • Consulta a realizar en PostreSQL para la extracción de datos.

  • Se ejecuta la consulta en PostgreSQL. Si la extracción va bien:

    • Se recorren las filas extraídas de la consulta.

    • En caso de que la colección tenga índices u objetos JSON de otra tabla, se genera una nueva consulta en PostgreSQL para extraer sus datos y se cargan en un array.

    • Se crea la colección y se guarda en la base de datos de MongoDB.

La estructura de cada colección es la siguiente:

Actor

var actorSchema = new Schema({
    actor_id: Number,
    first_name: String,
    last_name: String
});

Address

var addressSchema = new Schema({
    address_id: Number,        
    address: String,
    address2: String,
    district: String,
    phone: String,
    postal_code: String,
    city: String,
    country: String,
    customers: Array,
    staffs: Array,
    stores: Array
});

Customer

var customerSchema = new Schema({
    customer_id: Number,
    first_name: String,
    last_name: String,
    email: String,
    active: Number,
    activebool: Boolean,
    create_date: Date,
    rentals: Array,
    payments: Array
});

Film

var filmSchema = new Schema({
    film_id: Number,
    title: String,
    description: String,
    release_year: Date,
    rental_duration: Number,
    rental_rate: Number,
    length: Number,
    replacement_cost: Number,
    rating: String,
    special_features: String,
    fulltext: String,        
    languages: Array,
    categories: Array,
    actors: Array, 
    stores: Array
});

Payment

var paymentSchema = new Schema({
    payment_id: Number,
    amount: Number,
    payment_date: Date
});

Rental

var rentalSchema = new Schema({
    rental_id: Number,
    rental_date: Date,
    return_date: Date,
    films: Array,
    stores: Array,
    payment: Array
});

Staff

var staffSchema = new Schema({
    staff_id: Number,
    first_name: String,
    last_name: String,
    email: String,
    active: Boolean,
    username: String,
    password: String,
    picture: String,
    rentals: Array,
    payments: Array,
    store: Array
});

Store

var storeSchema = new Schema({
    store_id: Number,
    manager_staff_id: Number,
    staffs: Array,
    customers: Array
});

9.4 Pruebas

Para comprobar que los datos han sido migrados correctamente y que la estructura de los mismos es correcta se realizan una seria de consultas sobre ambos sistemas.

Las consultas se han implementado en JavaScript y se han ejecutado en NodeJS al igual que la transformación del esquema.

Las consultas realizadas son las siguientes:

• Consultas simples. Sobre una sola tabla o colección.

• Subconsultas. Consultas uniendo varias tablas o colecciones.

Consultas simples

Extracción de datos sobre una sola tabla y sobre una única colección.

Film

Con un total de 1000 filas, las cosultas son:

• PostgreSQL. SELECT * from film
• MongoDB. Films.find()

Rental

Con un total de 16.044 filas, las consultas son:

• PostgreSQL. SELECT * from rental

• MongoDB. Rentals.find()

Subconsultas

Extracción de datos sobre varias tablas y colecciones, con una complejidad más alta y extrayendo datos más completos.

Listado de películas con su categoría y su lenguaje

En este caso la consulta sobre PostgreSQL requiere de hacer join con 3 tablas más, a diferencia de MongoDB que solo tiene que buscar sobre la colección films que ya tiene todos los datos. Las consultas son:

• PostgreSQL.

  Select f.film_id, f.title as titulo, 
  f.description as descripcion, 
  f.release_year as anioPublicacion, 
  f.rental_duration as duración, 
  c.name as categoria, l.name as idioma
  from film f
  join film_category fc on fc.film_id = f.film_id
  join category c on c.category_id = fc.category_id
  join language l on l.language_id = f.language_id
  order by f.film_id
  

• MongoDB.

  Films.find()

Listado de actores con el número de películas

La consulta sobre PostgreSQL requiere de hacer un join con dos tablas más y en este caso en MongoDB hay que hacer la extracción de actores y otra extracción más sobre películas para extraer el número que le corresponde. Las consultas son:

• PostgreSQL

  select COALESCE(a.first_name, ' '),
  COALESCE(a.last_name, ' '), 
  count(f.film_id) as numeroPeliculas
  from actor a
  join film_actor fa on fa.actor_id = a.actor_id
  join film f on f.film_id = fa.film_id
  group by  a.first_name, a.last_name
  order by  numeroPeliculas desc
  

• MongoDB

  var resultado = Actor.find();
  
  var peliculas = Film.find({'actors':resultado.actor.id});
  Actor.find({}, function(err, docs){
    var i = -1;
    var actors = docs;
    //Para cada actor se busca su numero de peliculas
    for ( var i = 0; i < actors.length; i++ ){
      Film.find({ actors: { "$in" : [actors[i].actor_id]} } , function(error, docs){
        console.log(actors[i], "peliculas", docs.length)
      });
    }
  });
  

9.5 Herramientas Software

La migración de los datos, la creación de las colecciones y la ejecución de consultas ha sido implementado en JavaScript y ejecutado con NodeJS versión 6.11.0 que realizará la extracción de los datos de la base de datos en PostgreSQL y los pasará a la base de datos en MongoDB.

Las librerías de apoyo que se han usado han sido:

• pg. Para la comunicación con la base de datos en PostgreSQL.

• mongoose. Para la comunicación con la base de datos de MongoDB.

• async. Para el recorrido síncrono de las filas extraídas y su tratamiento