El Rincon del BI

Descubriendo el Business Intelligence…

13.4.1. Tabla Hechos Venta. Particionado en MySql.

Posted by Roberto Espinosa en 15 enero 2010


Antes de comenzar la implementación del proceso ETL para la carga de la tabla de Hechos de Ventas, vamos a realizar alguna consideración sobre el particionado de tablas.

Cuando estamos costruyendo un sistema de business intelligence con su correspondiente datawarehouse, uno de los objetivos (aparte de todas las ventajas de sistemas de este tipo: información homogenea, elaborada pensando en el analisis, dimensional, centralizada, estatica, historica, etc., etc.) es la velocidad a la hora de obtener información. Es decir, que las consultas se realicen con la suficiente rapidez y no tengamos los mismos problemas de rendimiento que suelen producirse en los sistemas operacionales (los informes incluso pueden tardar horas en elaborarse).

Para evitar este problema, hay diferentes técnicas que podemos aplicar a la hora de realizar el diseño fisico del DW. Una de las técnicas es el particionado.Pensar que estamos en un dw con millones de registros en una unica tabla y el gestor de la base de datos ha de mover toda la tabla. Ademas, seguramente habrá datos antiguos a los que ya no accederemos casi nunca (datos de varios años atras). Si somos capaces de tener la tabla “troceada” en segmentos mas pequeños seguramente aumentaremos el rendimiento y la velocidad del sistema.

El particionado nos permite distribuir porciones de una tabla individual en diferentes segmentos conforme a unas reglas establecidas por el usuario. Según quien realize la gestión del particionado, podemos distinguir dos tipos de particionado:

Manual: El particionado lo podriamos realizar nosotros en nuestra lógica de procesos de carga ETL (creando tablas para separar los datos, por ejemplo, tabla de ventas por año o por mes/año). Luego nuestro sistema de Business Intelligence tendrá que ser capaz de gestionar este particionado para saber de que tabla tiene que leer según los datos que le estemos pidiendo (tendra que tener un motor de generación de querys que sea capaz de construir las sentencias para leer de las diferentes tablas que incluyen los datos). Puede resultar complejo gestionar esto.

Automatico: Las diferentes porciones de la tabla podrán ser almacenadas en diferentes ubicaciones del sistema de forma automatica según nos permita el SGBDR que estemos utilizando.La gestión del particionado es automática y totalmente transparente para el usuario, que solo ve una tabla entera (la tabla “lógica” que estaria realmente partida en varias tablas “fisicas”). La gestión la realiza de forma automática el motor de base de datos tanto a la hora de insertar registros como a la hora de leerlos.

La partición de tablas se hace normalmente por razones de mantenimiento, rendimiento o gestión.

Lógica Particionado de tablas

Según la forma de realizar el particionado, podriamos distinguir:

Partición horizontal (por fila): consiste en repartir las filas de una tabla en diferentes particiones. Por ejemplo, los clientes de un pais estan incluidos en una determinada partición y el resto de clientes en otra. En cada partición se incluyen los registros completos de cada cliente.

Partición vertical( por columna): consiste en repartir determinadas columnas de un registro en una partición y otras columnas en otra (partimos la tabla verticalmente,). Por ejemplo, en una partición tenemos las columnas de datos de direcciones de los clientes, y en otra partición las columnas de datos bancarios.

Cada motor de base de datos implementa el particionado de forma diferente. Nosotros nos vamos a centrar en la forma de implementarlo utilizando Mysql, que es la base de datos que estamos utilizando para nuestro proyecto.

Particionado de tablas en MySql

MySql implementa el particionado horizontal. Basicamente, se pueden realizar cuatro tipos de particionado, que son:

  • RANGE: la asignación de los registros de la tabla a las diferentes particiones se realiza según un rango de valores definido sobre una determinada columna de la tabla o expresión (ver manual online de MySql aquí ). Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, el rango de valores que seran la condicion para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor del rango en la columna/expresion indicada, el registro se insertara en dicha partición.
  • LIST: la asignación de los registros de la tabla a las diferentes particiones se realiza según una lista de valores definida sobre una determinada columna de la tabla o expresión (ver manual online de MySql aquí ). Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, la lista de valores que seran la condicion para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor incluido en la lista de valores, el registro se insertara en dicha partición.
  • HASH: este tipo de partición esta pensado para repartir de forma equitativa los registros de la tabla entre las diferentes particiones. Mientras en los dos particionados anteriores eramos nosotros los que teniamos que decidir, según los valores indicados, a que partición llevamos los registros, en la partición HASH es MySql quien hace ese trabajo. Para definir este tipo de particionado, deberemos de indicarle una columna del tipo integer o una función de usuario que devuelva un integer (ver manual online de MySql aquí ). En este caso, aplicamos una función sobre un determinado campo que devolvera un valor entero. Según el valor, MySql insertará el registro en una partición distinta.
  • KEY: similar al HASH, pero la función para el particionado la proporciona MySql automáticamente (con la función MD5) (ver manual online de MySql aquí ). Se pueden indicar los campos para el particionado, pero siempre han de ser de la clave primaria de la tabla o de un indice único.
  • SUBPARTITIONS: Mysql permite ademas realizar subparticionado. Permite la división de cada partición en multiples subparticiones. (ver manual online de MySql aquí).

Ademas, hemos de tener en cuenta que la definición de particiones no es estática. Es decir, MySql tiene herramientas para poder cambiar la configuración del particionado a posteriori, para añadir o suprimir particiones existentes, fusionar particiones en otras, dividir una particion en varias, etc. (ver aquí ).

El particionado tiene sus limitaciones y sus restricciones, pues no se puede realizar sobre cualquier tipo de columna o expresión (ver restricciones al particionado aquí), tenemos un limite de particiones a definir y habrá que tener en cuenta algunas cosas para mejorar el rendimiento de las consultas y evitar que estas se recorran todas las particiones de una tabla (ver el artículo MySql Partitions in Practice, donde se nos explica con un ejemplo trabajando sobre una base de datos muy grande, como realizar particionado y que cosas tener en cuenta para optimizar los accesos a las consultas). Para entender como funciona el particionado, hemos replicado los ejemplos definidos en este articulo con una tabla de pruebas de 1 millón de registros (llenada, por cierto,con datos de prueba generados con Talend y el componente tRowGenerator).

Ejemplo componente tRowGenerator para producir datos de test

En concreto, hemos creado dos tablas iguales (con la misma estructura). Una con particionado por año en un campo de la clave del tipo fecha, y la segunda con la misma estructura sin particionado. En ambas tablas tenemos un millon de registros repartidos entre los años 2008 y 2017. Una vez creadas las tablas, utilizamos la sentencia de MySql  explain y explain partitions para analizar como se ejecutaran las sentencias sql (analisis de indices). Ademas, comprobamos tiempos de ejecución con diferentes tipos de sentencia SQL. Los resultados son mas que obvios:

Analisis tiempos ejecucion

En las mayoria de los casos se obtiene un mejor tiempo de respuesta de la tabla particionada, y en los casos en los que no, el tiempo de ejecución es practicamente igual al de la tabla no particionada (diferencias de milesimas de segundo). Observar cuando indicamos condiciones fuera del indice (ultima sentencia SQL), como los tiempos de respuesta son aun mas relevantes. Y siempre conforme vamos leyendo de mas particiones (por incluir mas años en la condición), el tiempo de respuesta de la consulta entre una y otra tabla se va acercando.

Particionado de la tabla de hechos de Ventas en nuestro DW

Para nuestro DW, hemos decidir implementar un particionado del tipo LIST. Como os habreis podido dar cuenta, seguramente los particionados por RANGE o por LIST son los mas adecuados para un sistema de Business Intelligence pues nos van a permitir de una forma facil reducir el tamaño de las casi siempre monstruosas tablas de hechos, de una forma fácil y automática.

Vamos a crear 10 particiones y repartiremos los diferentes años en cada una de las particiones, empezando por 2005 –> Particion 1, 2006 –> Particion 2, 2007 –> Particion 3, …, 2013 –> Particion 9, 2014 –> Partición 10. A partir de 2015, volvemos a asignar cada año a las particiones y así hasta el año 2024 (tiempo de sobra para lo que seguramente será la vida de nuestro DW).

Como el campo año no lo tenemos en el diseño físico de la tabla de hechos, aplicaremos sobre la columna fecha la funcion YEAR para realizar el particionado. La sentencia para la creación de la tabla de hechos quedaría algo parecido a esto:

CREATE  TABLE IF NOT EXISTS `enobi`.`dwh_ventas` (
`fecha_id` DATE NOT NULL ,
`material_id` INT(11) NOT NULL ,
`cliente_id` INT(11) NOT NULL ,
`centro_id` INT(11) NOT NULL ,
`promocion_id` INT(11) NOT NULL ,
`pedido_id` INT(11) NOT NULL ,
`unidades` FLOAT NULL DEFAULT NULL COMMENT 'Unidades Vendidas' ,
`litros` FLOAT NULL DEFAULT NULL COMMENT 'Equivalencia en litros de las unidades vendidas' ,
`precio` FLOAT NULL DEFAULT NULL COMMENT 'Precio Unitario' ,
`coste_unit` FLOAT NULL DEFAULT NULL COMMENT 'Coste Unitario del Producto')
PARTITION BY LIST(YEAR(fecha_id)) (
    PARTITION p1 VALUES IN (2005,2015),
    PARTITION p2 VALUES IN (2006,2016),
    ..................................
    PARTITION p9 VALUES IN (2013,2023),
    PARTITION p10 VALUES IN (2014,2024)
);

Con este forma de definir el particionado estamos sacando ademas partido de la optimización de lo que en MySql llaman “Partitioning Pruning” ( ver aqui ). El concepto es relativamente simple y puede describirse como “No recorras las particiones donde no puede haber valores que coincidan con lo que estamos buscando”. De esta forma, los procesos de lectura serán mucho mas rápidos.

A continuación, veremos en la siguiente entrada del Blog los ajustes de diseño de nuestro modelo físico en la tabla de hechos (teniendo en cuenta todo lo visto referente al particionado) y los procesos utilizando la ETL Talend para su llenado.

16 comentarios to “13.4.1. Tabla Hechos Venta. Particionado en MySql.”

  1. iim.vxk said

    graaacias!, excelente artículo super bien explicado, io había pensado manejar 2 tablas distintas para un sistema que maneja stock { activo “disponible para venta”, inactivo “vendido, usado como referencia al mostrar una venta” } pero que mejor que hacerlo de esta manera, particionando horizontal en base al campo “sale_id”,,,😉

    gracias pror tu infooo , measaiudado bastante ,)

  2. iim.vxk said

    oie bisexual, cheka este ejemplo, io particiono una tabla en modo HORIZONTAL del tipo LIST en base a los valores del campo numérico “sale_id” { si es “” o NULL en una partición, cualquier otro valor en otra }, pero que pasa si actualizo un registro de dicha tabla donde modifico el campo “sale_id” { le asigno algún valor numérico },,, se moverá de partición o no?

    saludoss !!

    • Hola:

      Si, tu cuando cambies el valor de una columna que se utilice para la definición de particiones, el registro se cambiara de sitio.

      Es importante que el valor NULL lo incluyas en la lista de valores cuando defines las particiones, tal y como ves en los siguientes ejemplos:

      mysql> CREATE TABLE ts2 (
      -> c1 INT,
      -> c2 VARCHAR(20)
      -> )
      -> PARTITION BY LIST(c1) (
      -> PARTITION p0 VALUES IN (0, 3, 6),
      -> PARTITION p1 VALUES IN (1, 4, 7),
      -> PARTITION p2 VALUES IN (2, 5, 8),
      -> PARTITION p3 VALUES IN (NULL)
      -> );
      Query OK, 0 rows affected (0.01 sec)

      mysql> CREATE TABLE ts3 (
      -> c1 INT,
      -> c2 VARCHAR(20)
      -> )
      -> PARTITION BY LIST(c1) (
      -> PARTITION p0 VALUES IN (0, 3, 6),
      -> PARTITION p1 VALUES IN (1, 4, 7, NULL),
      -> PARTITION p2 VALUES IN (2, 5, 8)
      -> );
      Query OK, 0 rows affected (0.01 sec)

      Si tienes dudas sobre el tratamiento de los NULL, te recomiendo que leas las consideraciones que hace Mysql a este respecto en el link: http://dev.mysql.com/doc/refman/5.4/en/partitioning-handling-nulls.html

      Según el rango de valores que vayas a tener en el campo sales_id, tambien podrías utilizar una partición del tipo RANGE. Ten en cuenta que los valores NULL, tal como indica Mysql, se almacen en la partición del rango de valores mas bajo.

      mysql> CREATE TABLE t2 (
      -> c1 INT,
      -> c2 VARCHAR(20)
      -> )
      -> PARTITION BY RANGE(c1) (
      -> PARTITION p0 VALUES LESS THAN (-5),
      -> PARTITION p1 VALUES LESS THAN (0),
      -> PARTITION p2 VALUES LESS THAN (10),
      -> PARTITION p3 VALUES LESS THAN MAXVALUE
      -> );

      Buena suerte!!!

  3. Miguel Angel Pérez Gómez said

    Hola,

    No estoy muy seguro de que sea el lugar más adecuado de todas las entradas de tu blog pero asi me lo ha parecido.

    Yo también utilizo talend (jasper etl) para los procesos de etl. Pero se me plantea un problema y es que tengo una tabla de “clientes” de unos 300.000 registros que deberé de pasar (filtrada) desde una bbdd oracle a otra mysql.

    El problema que tengo es que el pk de la tabla original oracle es un alfanúmerico que se genera (me imagino) mediante algún algoritmo hash de tal manera que siempre tiene la misma longitud y nunca se repite. El problema que se me plantea es que no se como filtrar la tabla de manera que solo cargue clientes nuevos, para mi sería un disparate el tener que “replicar” esta tabla todos los días.

    Pido perdón de antemano si la pregunta resulta un tanto tonta y confusa, pero es que no se por donde atacar esto. Tengo experiencia con replicaciones oracle-oracle, con dmp´s con archive log´s, etc. Pero oracle-mysql…me ha superado.

    Salu2 y gracias de antemano.

    • Si miras el ejemplo de la serie de Talend donde hablaba de la carga del maestro de clientes:

      https://churriwifi.wordpress.com/2010/01/12/13-3-etl-dimension-cliente/

      Ahi procesaba en el sistema origen todos los clientes y luego solo cargaba en el DW los que tenian alguna modificacion. Con el volumen de datos que tienes tu, no es lógico descargar los 300 mil registros.

      No se cual es tu sistema origen, pero, por ejemplo, en Sap, se guarda información de cuando se crea un registro de cliente o de cuando hay alguna modificación sobre el. De esta forma, en el proceso de extracción podrías decir sacame solo los clientes que se han creado o modificado desde X fecha (en el ejemplo del blog de la carga de materiales se hace algo parecido).

      En el caso de que no tengas un tipo de información asi, igual no te queda mas remedio que extraer toda la tabla completa y con Talend verificar que es lo que se ha cambiado y solo cargar esa parte en el DW.

      De todas formas, depende para que vayas a utilizar el sistema, pero puedes pensar que una carga diaria igual es excesiva (y pensar en periodos semanales para consolidar).

      Espero haberte sido de utilidad.

      Un saludo.

      • Miguel Angel Pérez Gómez said

        bufff que fallo, no se como se me escapó esa entrada del blog.

        La voy a leer bien y ya te cuento.

        Muchas gracias y animo con el blog que es de mucha calidad.

        Salu2

  4. iim.vxk said

    oHH gran BI, tu sabiduría me ilumina en el aventuroso camino hacia el código perfecto,,, mis mas sinceras REVERENCIAS hacia usted y su cerebro { inserte aquí de manera imaginativa cualquier emoticon alusivo a “reverencia” que usted guste }

    MUCHAS GRACIAS !! n.n

  5. […] } En una entrada anterior del blog vimos los conceptos básicos del particionado de tablas y como se podian llevar a la práctica utilizando MySql. Incluso hicimos una comparativa de tiempos […]

  6. combuilder said

    Una duda que me surge respecto a la partición de tablas: ¿no incluye claves foráneas en la definición?

    Muchas gracias de antemano!

  7. combuilder said

    Pues … a mi me da problemas. Obtengo el error siguiente:

    ERROR: Error 1506: Foreign key clause is not yet supported in conjunction with partitioning

    Te pongo el código completo de la tabla que quiero generar:
    — —————————————————–
    — Table `inventarios`.`emisiones`
    — —————————————————–
    CREATE TABLE IF NOT EXISTS `inventarios`.`emisiones` (
    `idemision` INT(10) NOT NULL AUTO_INCREMENT ,
    `idcontaminante` INT(10) NOT NULL ,
    `idincertidumbre` INT(10) NOT NULL ,
    `idmetodo` INT(10) NOT NULL ,
    `cantidad` DOUBLE NULL DEFAULT ‘0’ ,
    `AnnoInventario` YEAR NOT NULL DEFAULT ‘0’ ,
    `idproceso` INT(10) NOT NULL ,
    `idemisionanterior` INT(10) NULL DEFAULT NULL ,
    `EsDatoPlanCalidad` TINYINT(1) NOT NULL ,
    `fecha` YEAR NOT NULL DEFAULT ‘0’ ,
    PRIMARY KEY (`idemision`) ,
    INDEX `emisionesAnnoInventario` (`AnnoInventario` ASC) ,
    INDEX `idcontaminante` (`idcontaminante` ASC) ,
    INDEX `idemisionanterior` (`idemisionanterior` ASC) ,
    INDEX `idincertidumbre` (`idincertidumbre` ASC) ,
    INDEX `idmetodo` (`idmetodo` ASC) ,
    INDEX `idproceso` (`idproceso` ASC) ,
    INDEX `fk_emisiones_contaminantes1` (`idcontaminante` ASC) ,
    INDEX `fk_emisiones_metodos1` (`idmetodo` ASC) ,
    INDEX `fk_emisiones_incertidumbres1` (`idincertidumbre` ASC) ,
    INDEX `fk_emisiones_procesos1` (`idproceso` ASC) ,
    CONSTRAINT `fk_emisiones_contaminantes1`
    FOREIGN KEY (`idcontaminante` )
    REFERENCES `inventarios`.`contaminantes` (`idcontaminante` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_emisiones_metodos1`
    FOREIGN KEY (`idmetodo` )
    REFERENCES `inventarios`.`metodos` (`idmetodo` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_emisiones_incertidumbres1`
    FOREIGN KEY (`idincertidumbre` )
    REFERENCES `inventarios`.`incertidumbres` (`idincertidumbre` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_emisiones_procesos1`
    FOREIGN KEY (`idproceso` )
    REFERENCES `inventarios`.`procesos` (`idproceso` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    AUTO_INCREMENT = 3028070
    DEFAULT CHARACTER SET = latin1
    PARTITION BY LIST(YEAR(AnnoInventario)) PARTITIONS 10
    ( PARTITION part0 VALUES IN (2002,2012),
    PARTITION part1 VALUES IN (2003,2013),
    PARTITION part2 VALUES IN (2004,2014),
    PARTITION part3 VALUES IN (2005,2015),
    PARTITION part4 VALUES IN (2006,2016),
    PARTITION part5 VALUES IN (2007,2017),
    PARTITION part6 VALUES IN (2008,2018),
    PARTITION part7 VALUES IN (2009,2019),
    PARTITION part8 VALUES IN (2010,2020),
    PARTITION part9 VALUES IN (2001,2011))

    En fin … HELPPPPPPP!

    • Se que el particionado (tanto en MySql como en Oracle), tienen limitaciones. Supongo que esta es una de ellas.

      Siempre puedes, si realmente necesitas particionar (solo esta justificado con un numero muy grande de registros), es hacer la validación de las claves foraneas por programación en la lógica de tus aplicaciones.

      Un saludo.

  8. combuilder said

    El número es impostante … en torno a 700 MB actualmente sólo esa tabla!

    Muchas gracias!

  9. Luis said

    Fabuloso artículo Roberto, solamente me ha quedado una duda:

    ¿Las pruebas se han realizado teniendo indexados los campos fecha_id, num_id y cantidad en ambos casos?. En caso negativo, ¿no crees que afectaría en las diferencias de respuesta entre las dos opciones de forma significativa?.

    • No, solo existia el indice primario. Si, tienes razon, el tener indices puede favorecer los tiempos de respuesta. Aunque es un tema que hay que analizar en profundidad, sobre todo si estamos hablando de tablas con particionado.

      Un saludo.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

 
A %d blogueros les gusta esto: