Hace unos días publiqué la primera parte acerca del proceso de creación de una base de datos con la información histórica de precios de acciones en los mercados financieros. Nos quedamos justo en la creación de las tablas y sus relaciones.

A la estructura que ya hemos creado hace falta agregar una tabla de detalle que registrará N cantidad de registros de precios asociados a un símbolo específico.

CREATE TABLE `stock_price` (
    `id`                INT(11) AUTO_INCREMENT NOT NULL,
    `stock`             INT(11) NOT NULL,
    `date`              DATE NOT NULL,
    `open`              DECIMAL(15, 3) NOT NULL,
    `close`             DECIMAL(15, 3) NOT NULL,
    `adjusted_close`    DECIMAL(15, 3) NULL,
    `low`               DECIMAL(15, 3) NULL,
    `high`              DECIMAL(15, 3) NULL,
    `volume`            INT(11) NULL,
    `created`           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
    PRIMARY KEY (`id`),
    FOREIGN KEY (`stock`)
        REFERENCES `stock` (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    UNIQUE KEY `stock_price_unique` (`id`)
)ENGINE=InnoDB;

Esta estructura no es más que la descripción que encontramos en nuestro archivo CSV de orígen con la única diferencia que acá en vez de guardar el símbolo estamos haciendo referencia a un registro en la tabla stock que creamos anteriormente.

CSV histórico de precios

Para poder hacer una inserción en nuestra nueva tabla de precios es por lo tanto necesario tomar una línea de nuestro archivo CSV y dejar que sea la base de datos quien haga el mapeo necesario. Este es un proceso que si bien podríamos hacer unos cuantos inserts de forma manual, hacerlo para todo el dataset nos llevaría probablemente varios años de trabajo. Es por ello que recurrimos a un stored procedure que se encargará de ubicar las variables necesarias para realizar el insert a partir del símbolo de la acción. Este stored procedure se ejecutará literalmente millones de veces durante este proceso de carga.

DROP PROCEDURE IF EXISTS `add_stock_price`;
DELIMITER $$
USE `[basededatos]` $$
CREATE PROCEDURE `[basededatos]`.`add_stock_price` (
    IN pTicker VARCHAR(10), 
    IN pDate VARCHAR(50),
    IN pOpen VARCHAR(50),
    IN pClose VARCHAR(50),
    IN pAdjustedClose VARCHAR(50),
    IN pLow VARCHAR(50),
    IN pHigh VARCHAR(50),
    IN pVolume VARCHAR(50)
)
BEGIN
    DECLARE vStock            INT(11) DEFAULT NULL;
    DECLARE vDate             DATE DEFAULT NULL;
    DECLARE vOpen             DECIMAL(15, 3) DEFAULT NULL;
    DECLARE vClose            DECIMAL(15, 3) DEFAULT NULL;
    DECLARE vAdjustedClose    DECIMAL(15, 3) DEFAULT NULL;
    DECLARE vLow              DECIMAL(15, 3) DEFAULT NULL;
    DECLARE vHigh             DECIMAL(15, 3) DEFAULT NULL;
    DECLARE vVolume           INT(11) DEFAULT NULL;
	
    SELECT `stock`.`id` INTO vStock
      FROM `stock`
     WHERE BINARY `stock`.`ticker` = pTicker;
  	 
    IF vStock IS NOT NULL THEN
        IF pDate <> '' THEN
            SET vDate = STR_TO_DATE(pDate, '%c/%d/%Y');
            IF pOpen <> '' THEN 
                SET vOpen = CONVERT(pOpen, DECIMAL(15, 3));
                IF pClose <> '' THEN
                    SET vClose = CONVERT(pClose, DECIMAL(15, 3));
                    
                    IF pAdjustedClose <> '' THEN
                        SET vAdjustedClose = CONVERT(pAdjustedClose, DECIMAL(15, 3));
                    END IF;
	
                    IF pLow <> '' THEN
                        SET vLow = CONVERT(pLow, DECIMAL(15, 3));
                    END IF;

                    IF pHigh <> '' THEN 
                        SET vHigh = CONVERT(pHigh, DECIMAL(15, 3));
                    END IF;

                    IF pVolume <> '' THEN
                        SET vVolume = CONVERT(pVolume, INT);
                    END IF;
					
                    INSERT INTO `stock_price` 
                    (`stock`, `date`, `open`, `close`, `adjusted_close`, `low`, `high`, `volume`) 
                    VALUES
                    (vStock, vDate, vOpen, vClose, vAdjustedClose, vLow, vHigh, vVolume);
                END IF;
            END IF;
        END IF;
    END IF;
END
$$

Nuestro stored procedure es bastante rudimentario pero cumple con su función. Todos los parámetros los he declarado de tipo VARCHAR y hago una conversión a su tipo de dato correcto debido a que estamos importando toda esta información a partir de un archivo que puede contener inconsistencidas. De esta forma es que la forma más básica de validar si se ha suministrado algún parámetro es validar si es diferente de una cadena de texto vacía. El único parámetro obligatorio es que venga el símbolo (ticker), si no se suministra dicho valor el stored procedure no ejecuta ninguna instrucción adicional. El resto de parámetros únicamente se setean a un valor nulo en caso de no suministrarse.

En versiones anteriores de este stored procedure intenté desplegar mensajes de advertencia cuando hacen falta parámetros o se encuentra alguna inconsistencia pero como veremos a continuación, cuando este proceso se ejecuta de manera concurrente millones de veces lo último que hay es tiempo de revisar mensajes de advertencia que parpadean en pantalla por fracciones de segundo.

Lo último que nos queda es entonces convertir el contenido de nuestro CSV en llamadas a nuestro stored procedure. La forma más fácil de hacerlo es usar las funciones de concatenación de Excel para construir una llamada a nuestro stored procedure. Acá es donde se pierde un poco la elegancia, por tratarse de un archivo gigantesco Excel se rehusa a abrirlo de manera íntegra:

Si bien se cargará un poco más de un millón de registros, esto no representa ni el 10% de nuestro dataset. El archivo de Excel resultante no nos es funcional por tratarse de una versión truncada de nuestra información. Aún con 32GB de memoria RAM no fue posible cargar el archivo completo, estoy convencido que se trata de una limitación de Excel y no un error en el archivo original.

Este archivo no puede ser cargado de forma íntegra en Excel 365

Quien no tiene ningún problema en cargar el archivo íntegro es Notepad++. El contenido del archivo no es más que una línea de encabezado y luego millones de líneas con valores separados por comas. El problema con Excel claramente no es la complejidad del archivo sino sencillamente el volúmen de información que contiene.

El contenido de nuestro archivo CSV en todo su esplendor

Y es acá donde empieza el verdadero problema de cargar toda esta información a nuestra base de datos. Es necesario utilizar Excel para concatenar este texto de una forma que nos permita invocar nuestro stored procedure pero tendremos que hacerlo entonces en bloques. Terminé copiando un millón de registros a la vez a un archivo CSV temporal para que Excel pudiera por lo tanto realizar su función.

Un millón de registros a la vez. La última columna contiene una función que concatena el texto de tal forma que arma una llamada a nuestro stored procedure de inserción
=CONCATENATE("CALL add_stock_price(", CHAR(34), A2, CHAR(34), ", ", CHAR(34), TEXT(H2, "mm/dd/yyyy"), CHAR(34), ", ", CHAR(34), B2, CHAR(34), ", ", CHAR(34), C2, CHAR(34), ", ", CHAR(34), D2, CHAR(34), ", ", CHAR(34), E2, CHAR(34), ", ", CHAR(34), F2, CHAR(34), ", ", CHAR(34), G2, CHAR(34),");")

Podemos finalmente copiar la columna con el resultado de esta función y pegarla en un archivo independiente. El resultado es un millón de llamadas a nuestro stored procedure.

El contenido de este archivo puede ser copiado y pegado a un editor como HeidiSQL y ser ejecutado directamente o bien utilizar la línea de comando de MySQL / MariaDB para importar el archivo directamente (personalmente mi forma preferida de hacerlo).

Si bien el stored procedure se tarda milisegundos en ejecutarse de inicio a fin, aún así tardaremos unas 2 horas en ejecutar el bloque completo de un millón de registros. Si hacemos las cuentas, 25 millones de registros por 2 horas por millón nos dará una idea del tiempo aproximado que tardaremos en cargar toda la información a nuestra base de datos.

Una consulta básica a nuestra tabla de precios ya con información cargada

No hemos hecho más que cargar una cantidad considerable de información en una estructura relacional que nos permitirá hacer uso de la misma de formas interesantes, sin embargo no es el final del camino. Acá por el volúmen de información que estamos manejando nos iremos dando cuenta que cualquier consulta hacia la tabla histórica de precios implicará un considerable retardo en ejecutarse. En una siguiente ocasión nos adentraremos en la optimización de consultas hacia una tabla de esta naturaleza mediante creación de índices, segmentación y otros trucos que se prestan para estos escenarios.