Me gustaría haber podido indagar bajo el capó de teradataR, el paquete de R desarrollado por Teradata que permite que R realice lo que llaman por ahí in database analytics utilizando dicha plataforma propietaria.

Ya lo probé hace un tiempo con resultados bastante desiguales y que distaban muy mucho de mis expectativas originales, habida cuenta de las muchas bondades del gestor relacional. Durante mucho tiempo he tenido la intención de desentrañar los secretos del paquete, pero me contuvieron los términos desacostumbradamente restrictivos de la licencia:

License Restrictions: No license rights to the Software will be implied. You are responsible for the installation of the Software, as well as for providing parallel and backup operations. You will not sell, copy, rent, loan, modify, transfer, disclose, embed, sublicense, distribute, or create derivative works of the Software, in whole or in part, without Teradata’s prior written consent. You will not disclose the results of any testing or evaluation, including any benchmarks, performed by you insofar as it relates to the Software without Teradata’s prior written consent. You will not reverse-assemble, reverse compile or reverse-engineer the Software for purposes of illegally obtaining the Software’s source code. The Software, which includes all copies thereof whether in whole or in part, is and remains the exclusive property of Teradata and its licensors.

El paquete contiene muchas llamadas a funciones (median, mean, summary, etc.) que calculan determinados estadísticos (y test estadísticos) sobre tablas de Teradata directamente, sin bajarlas a R. Pero nunca supe cómo: ¿existirían sofisticadas funciones en Teradata para realizar tales cómputos? Nunca lo sabría porque yo soy sumamente licencioso —que me tomo muy en serio las licencias creo que significa eso—.

Pero un día tuve la buena suerte de que mi gato se paseó por encima del teclado y hete aquí lo que se ve que la bestia dio por componer:

summary.td.data.frame
teradataR:::.td.genmedian 

¡Ahí se descubrió el bollo! Porque, efectivamente, apareció en pantalla el código de ambas funciones. La primera, se conoce, calcula determinados estadísticos sobre cada una de las columnas de una tabla. La segunda, utilizada junto a muchas otras análogas por la primera, es una función no exportada del paquete que genera una consulta SQL para calcular la mediana de una columna (numérica) determinada.

La consulta que genera td.genmedian es algo así como:

SELECT "median"
FROM (
  SELECT
    SUM(CAST(1 AS DECIMAL(18,0)))
      OVER( ORDER BY col ROWS UNBOUNDED PRECEDING)
      AS "xrnk",
    SUM(CAST(1 AS DECIMAL(18,0)))
      OVER( ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
      AS "gcnt",
    SUM(CAST(col AS FLOAT))
      OVER( ORDER BY col ROWS 1 PRECEDING) AS "xsum2",
    CASE WHEN "gcnt" MOD 2 = 0 THEN "xsum2" / 2 ELSE col END
      AS "median"
  FROM mitabla WHERE col IS NOT NULL
) T1
WHERE ("xrnk" + "xrnk" = "gcnt" + 2 AND "gcnt" MOD 2 = 0) OR
("xrnk" + "xrnk" = "gcnt" + 1 AND "gcnt" MOD 2 = 1) 

La consulta se entiende mejor si se ejecuta la subconsulta T1 sobre una tabla chiquita. Así, para una tabla con un número par de registros construye una tabla con el mismo número de registros y un aspecto similar a

mientras que para tablas con un número impar de registros, construye, análogamente

Gracias pues a la deambulación de mi gato pude por tanto averiguar por qué teradataR era incapaz de satisfacer mis expectativas iniciales, es decir, que con su concurso pudiera calcular estadísticos relevantes en tablas inmensas: se limita a lanzar series de consultas SQL sobre las tablas y estas, además, están no lo suficientemente bien diseñadas.

En efecto, la consulta anterior falla sobre una tabla con unos cuantos cientos de millones de registros. Y con razón: gran parte del coste computacional se consume en dirimir si el número de registros de la tabla es o no es par y aplicar una u otra versión de la mediana según el caso.

Y, seamos serios, señores de Teradata: una vez que el tamaño de las tablas excede cierto umbral —precisamente el umbral a partir del cual estaríamos interesados en utilizar su producto— realmente da igual que su número de registros sea par o impar.

La verdad sea dicha, con datos tan grandes no hay una manera canónica —por no decir única— de calcular la mediana: depende de si en la columna tiene muchos o pocos valores repetidos, etc. Y en cada caso, la consulta adecuada sería otra.

Además, tengo la sospecha de que la estrategia adecuada para extender los conceptos estadísticos a entornos big data va a tener más que ver por el uso inteligente del muestreo y técnicas análogas que en el de la fuerza bruta.

En fin, que espero que no demanden a mi gato…

Codd desarrolló el modelo relacional —la base de casi todos los actuales sistemas de bases de datos— a finales de los años sesenta. El modelo relacional, basado en la lógica proposicional, suponía una ventaja sustancial con respecto a los métodos anteriores de almacenar información y bien implementado permite resolver una serie de problemas que afectaban a los sistemas anteriores:

• Evita la redundancia de los datos.
• Minimiza los problemas de actualización de los datos en las tablas.
• Protege la integridad de los datos.
• Etc.

Sin embargo, hay motivos por los que dicho esquema no es enteramente válido en contextos en los que se manejan datos grandes (para una definición sensata sobre lo que son “datos grandes”, léase este artículo).

La solución al problema de almacenar, procesar y acceder a conjuntos de datos grandes —implementada en diversas plataformas, tanto libres como propietarias— pasa por partirlos y distribuirlos en diversas máquinas y discos.

Pero partir plantea el problema adicional de decidir cuál es el criterio para distribuir la información entre las distintas máquinas. Los criterios a tener en cuenta son los siguientes:

1. Uniformidad: la distribución de la información por máquina debería ser (idealmente) uniforme: el uso de los recursos (CPU, disco, etc.) de cada máquina debería ser aproximadamente parejo. Piénsese que una operación distribuida de escritura o lectura no termina hasta que no lo hace la tarea asignada al último nodo, por lo que un nodo sobrecargado ralentizaría todo el sistema. Y que el espacio libre en disco se agota cuando se llena cualquiera de los discos.
2. Localidad: Por otro lado, información que se procese junta debería, idealmente, estar próxima, ser local. Si suelo sumar las ventas de todos mis establecimientos durante un mes, debería segmentar la información por mes. Si me interesa comparar la evolución de las ventas por establecimiento a lo largo del tiempo, segmentar por establecimiento. Las operaciones intranodo son mucho más económicas que las operaciones que involucran tráfico de datos entre nodos. De ahí que un criterio fundamental de diseño sea el de minimizar el tráfico de información entre nodos.

Existen segmentaciones naturales de datos: por punto de venta, por cliente, por persona, por periodo, etc. que, teóricamente, deberían satisfacer el criterio de localidad. Sin embargo, en los datos reales existen, se manifiestan, las llamadas leyes de potencias. Por motivos sobre los cuales estadísticos, sociólogos, físicos y muchos otros han escrito toneladas de papel, muchas distribuciones reales siguen las llamadas leyes de potencias (término que funciona en ocasiones como metáfora de la metáfora de cola larga o de cola gruesa).

Por ejemplo, la distribución del número de SMS enviados por persona: habrá quien mande diez al mes; otros mandarán treinta; algunos, ciento; pero seguro que hay números desde los que se mandan —posiblemente de manera automatizada— mil, diez mil o incluso, un millón. Igual pasa con el número de operaciones en bolsa, el número de billetes de avión adquiridos por cliente (¡los habrá corporativos!), número de visitas a la página por IP, etc.

En estas situaciones, la distribución que resulta ideal para la mayoría de los casos —los pequeños, los normales— es inadecuada en los atípicos: estos crean picos, saturan máquinas y discos, dan lugar a cuellos de botella.

Tradicionalmente, quienes diseñan bases de datos adoptan una visión vertical de las tablas: las columnas tienen su función, son clave primaria, forman parte de un índice secundario, son atributos o clave externa de otra tabla. Y esas propiedades se extienden a todos los registros de la tabla.

Estoy convencido de que los datos grandes exigen una visión no únicamente vertical sino también horizontal, más próxima al contenido, a los datos, que tenga en cuenta el número, significado y estructura de las filas olvidando soluciones de talla única.

Teniendo ese principio en mente, una solución no enteramente ortodoxa —bajo cierta definición de ortodoxia— al problema de las tablas distribuidas con datos de cola pesada puede pasar por

• descomponer la tabla-relación (en nomenclatura, valga la redundancia, relacional) en dos o más tablas-objeto (no necesariamente relacionales)
• distribuir cada uno de ellos de acuerdo de la manera más indicada a su contenido y
• emplazar las filas de la tabla-relación original en las tablas-objeto más adecuadas a su naturaleza.

Como parte de mis atribuciones dentro del comité organizador de las III Jornadas de Usuarios de R estoy tratando de conseguir la participación (y tal vez la financiación) de empresas e instituciones. Me ha parecido oportuno invitar a tomar parte en ellas a Teradata, empresa que, según la Wikipedia,

[está] especializada en herramientas de data warehousing y herramientas analíticas empresariales.

Teradata no se postula como un vendedor de herramientas de almacenamiento: quiere ir más allá. Su mercado es el de las empresas que aspiran a algo más que a que sus datos permanezcan varados en discos duros esperando, como mucho, a ser exportados a aplicaciones externas. Teradata dice ser capaz de realizar el análisis estadístico de los datos dentro de su propio sistema, eso que se ha dado en llamar in database analytics.

Con ese objetivo, a su implementación de SQL ha añadido una serie de funciones estadísticas que permiten realizar operaciones que exceden las relativamente limitadas del primero. Teradata las pone a disposición de sus usuarios a través de un API y —ya vamos entrando en materia— ha creado un paquete de R propietario (aunque gratuito) denominado TeradataR. Pueden encontrarse detalles sobre dicho paquete y enlaces para descargarlo en este artículo.

Dado, además, el entusiasmo que tiene Teradata por R —y retomo con eso el hilo del artículo— entendí que la empresa estaría encantada de dar a conocer su tecnología a la comunidad de usuarios de R. Así que contacté con las oficinas españolas de Teradata con el fin de ofrecerles la posibilidad de hacernos una demostración práctica e ilustrarnos en sus sus posibles usos en el seno de las Jornadas.

El primer contacto, por correo electrónico, fue poco fructífero: pasaron días sin recibir respuesta. Al final, tras unas cuantas llamadas telefónicas, pude contactar con la persona que me indicaron se encarga de ésas cosas en la empresa. Le expliqué quiénes éramos, qué estábamos organizando, cuál era nuestro interés y le pregunté por la posibilidad de que alguno de sus consultores nos ilustrase en vivo las bondades y aplicaciones del paquete que han creado frente a una audiencia que, me consta, tiene el máximo interés por este tipo de asuntos. Y en la que, todo hay que decirlo, hay representanes de organizaciones que son clientas de sus productos.

¿Creerán mis lectores que se excusó con cuatro palabras? ¡Dizque habían cerrado el año y que no pensaban participar en eventos que no fuesen propios! ¡Cuán brusco y poco delicado remate para un esfuerzo que sin duda ameritaba un final más amable!

Así que como quedó claro que tan falto está Teradata de recursos para dar publicidad a sus propios productos; como es gente que, a pesar de su brusquedad, en el fondo, me cae simpática por la promoción que hacen del software libre a través de iniciativas tales como TeradataR y dado que tan ocupados parecen prever estar de acá a noviembre, voy a asumir en mis hombros desinteresadamente en esta entrada la tarea de ilustrar con ejemplos el uso de su tecnología puntera.

Descargué primero el paquete de la página arriba indicada —para lo que hube de registrarme previamente en su portal. La instalación, todo ha de decirse, fue inmediata y sin problemas. El paquete apenas ocupa, en gran medida porque lo aligera lo magro de su documentación. Ha de notarse que depende del paquete RODBC, del que ya disponía.

Y comencé el análisis de TeratataR:

library( teradataR )
#Loading required package: RODBC
tdConnect( "prod" )           # servidor de producción
                              # dos líneas y todo bien de momento
 
# apunto a midb.borrar_cjgb
tdf <- td.data.frame("borrar_cjgb", "midb")
# el objeto resultante no reside memoria
 
dim( tdf )
#[1] 585592      9         # estupendo
 
head( tdf )
#Teradata table "midb"."borrar_cjgb"
 
#[1] "COD_GESTION" "FECHA"   "COD_USU"     "COD_OFICINA" "DES_NOMBRE" 
#[6] "DES_APELLIDO"
 
#585592 rows                   # exacto! qué bien!

Pero a partir de este punto comenzaron mis infortunios. Aunque pude ejecutar

summary( tdf )

sin más problemas que el de una excesiva demora para obtener una salida equivalente a summary sobre un dataframe habitual, el mismo comando con una tabla algo sustancialmente más teradatesca (es decir, grande) devolvió

summary( tdf )
#Error in quan[[3]] : subscript out of bounds

después de media hora de ejecución. Por otra parte, hist( tdf ) sobre la tabla más pequeña creó una cosa que no era un histograma sino un amazacotado gráfico de puntos en el que no se sacaba nada en claro. Además, en la tabla grande, al tratar de calcular la mediana de una columna numérica, obtuve

median( tdf["IMPORTE_TOTAL"] )
# [1] NA NA
# Warning message:
# In median.td.data.frame(tdf["IMPORTE_TOTAL"]) : NAs introduced by coercion

¡Nulos después de un buen rato! Claro, no pude calcular la media por culpa de los nulos… pero al comprobar los valores en la tabla observé que no había ninguno. O eso me pareció porque, digo yo, una aplicación tan sólida como Teradata ha de ser necesariamente más de fiar que los miopes ojos de uno.

El paseo por estas elementales funciones de TeradataR resultó atrozmente frustrante y me hizo reflexionar amargamente en los motivos de esa torpeza mía que me impedía disfrutar de ese paraíso analítico que Teradata ponía a mi disposición y ponderar si es que me vuelvo viejo o si, más bien, la desafortunada negativa de Teradata a participar en las Jornadas habría hecho mella en mi ecuanimidad. Sí, porque ¿quién que no esté armado de la mediana (sepa o no hacerla andar), la función cuantil, el algoritmo de k-medias, la función sigmoidal, el test de Wilcoxon no se siente con ánimos como de invadir, como poco, un país mediano? Teradata regala a quien pueda costearse sus licencias la entera totalidad de la veintena de funciones analíticas que serían la envidia de la calculadora programable de un bachiller.

Termino acá mi paseo por TeradataR con la decepción de no haber podido prestar mejor servicio a la comunidad de usuarios de Teradata y R y con el propósito de enmendarme y aprender lo que fuere necesario por no dejar malparada la reputación de unos y otros.

¡Ojalá pudiésemos contar en las Jornadas de R con un verdadero consultor de Teratada que nos guiase más certeramente que yo por los arcanos del paquete!

Quiero compartir con mis lectores dos enlaces relacionados. Puede que a alguno le interese su sustancia misma. A mí no tanto. A mí me interesan en cuanto que ilustran la emergencia de R y el papel protagónico que está asumiendo en el universo de las cosas analíticas. Tan protagónico que hasta dos viejos dinosaurios pasan voluntariamente por su aro.

Tradicionalmente, para analizar grandes bases de datos empresariales, se realizaba en primer lugar una extracción masiva de datos. Luego se procesaban con herramientas específicas (SAS, por ejemplo). En muchas ocasiones los resultados eran volcados nuevamente en el sistema de partida.

El proceso es manifiestamente mejorable: ¿qué necesidad existe de realizar tantas extracciones e importaciones de datos? ¿No podría realizarse el análisis en el mismo entorno? Además, en tal caso, los desarrolladores de sistemas de bases de datos podrían depredar el lucrativo nicho de las empresas que ofrecen soluciones de análisis de datos.

A este voluntarista proyecto le dieron incluso un nombre (en inglés, claro): in database analytics.

De hecho, por eso estuvieron tan cerca SAS y Teradata —que vende unos gestores de bases de datos ridículamente caros a empresas cuyos directivos se preocupan más de dejarse invitar a congresos chachiguays que de sus accionistas— de fusionarse. Y puede que también por eso lo desestimasen.

Lo que es cierto es que Oracle, Teradata y otras empresas del ramo han desarrollado sus propios algoritmos de minería de datos. Y ahora —ahora llegan mis dos anunciados enlaces— nos brindan una interfaz a dichos algoritmos desde R:

• Así, Teradata ha desarrollado el paquete paquete TeradataR.
• Y Oracle, que lleva años desarrollando sus propios algoritmos de minería de datos —desarrollados a partir del trabajo original de Thinking Machines— ha desarrollado y liberado RODM.

A pesar del escepticismo que muestra el empresariado patrio frente a la emergencia de R, el que dos dinosaurios corporativos hayan pelado sus barbas puede ser el indicio de que telefónicas, santanderes, bebeuveás y demás tienen que ir poniendo las suyas a remojar.

Yo creo que es un bug, vamos. Y tengo tres motivos para creerlo:

1. Teradata no hace lo que se espera que haga.
2. No he encontrado por ahí motivo técnico alguno que proscriba razonadamente lo que intento hacer.
3. He hablado con un señor empleado de Teradata, le he enviado el ejemplo y en lugar de explicarme mi error (de haberlo) ha hecho el avestruz (ya hablé de lo que pasa cuando uno encuentra bugs en software propietario).

He aquí cómo reproducir el bug. Primero creo una tabla muy simple e inserto una única fila en ella.

create table borrar_cjgb (
    a char(3)
);

insert into borrar_cjgb values(  'P21' );

Selecciono el prefijo, “P”, del valor que he insertado:

select
    cast( substr( ltrim( rtrim(a) ), 1,1 ) AS CHAR(3) ) as prefijo
    from borrar_cjgb
;

Sin embargo, por peregrinas razones, ¡Teradata no me deja encapsular mi consulta en una vista! La creación de la vista

replace view borrar_cjgb_v as (
    select
        cast( substr( ltrim( rtrim(a) ), 1,1 ) AS CHAR(3) ) as prefijo
        from borrar_cjgb
);

falla con error

3706: Syntax error: Data Type "rtrim" does not match a Defined Type name.

No sé muy bien para qué cuento esto acá hoy. Supongo que es porque el señor empleado de Teradata no atiende mi petición de luz y guía peculiar punto. Y también, claro está, para ilustrar a mis lectores con un ejemplo más de lo reacios que se muestran los altivos valedores del software propietario en aceptar razonadísimos informes de bugs.

El otro día, al hablar de la compra de Netezza por parte de IBM, hice referencia a un comentario del blog que es casi el flotador al que me asgo cuando quiero averiguar la verdad de las cosas que se me tuercen (últimamente). Dediqué en mi entrada una única línea para referirme a un único párrafo de la otra. Una visión tan parcial y puntual puede haber generado malinterpretaciones que me apresuro a enmendar con la profusión que el tema merita.

No voy tanto a valorar lo expreasado por mi cobloguero como a exponer con mayor abundamiento los motivos que me hacen pensar que IBM no va a hacer dormir a Netezza el sueño de los justos. Y eso que me refiero a IBM, una compañía que lleva 30 años dejándose escapar de la punta de los dedos negocio tras negocio (desde el PC hasta lo de Lenovo, pasando por OS/2 y los PowerPC), que siempre baila con la más fea (p.e., AIX, Lotus Notes y el no tan Intelligent Miner) y que ahora, en la feria de las adquisiciones del mundo del BI, se queda con el cada vez más periclitado SPSS.

Existen también otros muchos antecedentes de lo que teme mi colega: los que cita y, estoy seguro, un millón más. Unos, por error de cálculo. Otros, por mala intención. SAS y Microsoft son famosos por haber adquirido competidores potenciales para, poco más o menos, meterlos a la trituradora de papel. Muchas veces, porque el comprador está más interesado en la tecnología, el capital humano o los clientes de la compañía comprada que por sus mismos productos.

Pasa también que A (p.e. Oracle) compra a B (digamos, Sun), que B tiene mil productos (que pudieran llamarse OpenSolaris, OpenOffice, MySQL, Java, VirtualBox, etc.) y que a A sólo le interese realmente un porcentaje de ellos y congele el desarrollo del resto.

Pero esta vez, y en aras de la feroz competencia de los peces gordos que haga que bailemos los que somos más chicos, espero no estar equivocado con lo de la compra de Netezza y que pronto veamos a IBM cantando las virtudes de su nuevo DBMS por doquier. Y para tal vez racionalizar esta suposición, alego lo siguiente:

• Netezza, esencialmente, es una compañía uniproducto. Si IBM no lo quiere, ¿qué quiere pues? (¿tal vez la tecnología para netezzar DB2?)
• Netezza no vende sólo cedés sino máquinas en las que corre su software (a razón como de 20.000 dólares por TB, aparentemente, que viene a ser lo que cuesta un TB de RAM); y estas máquinas son desde no hace mucho blades de IBM (podría extenderme aquí con alguna frase en la que utilizar la palabra sinergia).
• Que Teradata tiene acuerdos técnicos (y dicen las malas lenguas que pudieron haber llegado a ser más que técnicos) con SAS (ya se sabe, in-database analytics). Que Netezza ofrece la posibilidad de crear una capa analítica sobre su DBMS usando SAS o R. Y que el flamante SPSS/Clementine del portafolio de IBM podría quedar fuera de esta novedosa moda.
• Que no sabemos cómo le habrá ido a IBM con sus escarceos en el mercado de Teradata y Netezza, pero sospecho que bien pueden haberle segado la hierba por debajo de los pies.

En fin, que IBM tenga suerte, que abra el mercado, que baje los precios y que cuando tenga que hacer correr R sobre la nueva plataforma, me deje un correo.

El primero tiene que ver con coches. En el ascensor, en las conversaciones que oigo en el ascensor, que es donde pulso los intereses de mis cotidianos coadláteres, soy mudo testigo de multitud de conversaciones. Las más tratan de coches. Es increíble cómo la gente está al día de marcas, modelos, motores y potencias. Aunque luego les preguntas por lo de su oficio y te das cuenta de que, sorprendentemente, no saben por dónde les pega el aire. Así, nuestro teórico máximo sabedor sobre la base de datos con la que trabajamos ni siquiera estaba al corriente de que existía una cosa llamada Postgres. (Le tuve que deletrear el nombre, lo apuntó en un papel y me dijo que lo buscaría en internet; cualquier día le pregunto hasta dónde lo ha llevado su afán de saber).

Escuchándolos, cualquiera diría que no existe otro DBMS que Oracle.  Y Teradata, en nuestro peculiar contexto.

No me gustaría que los lectores de mi blog estuviesen tan intelectualmente desarmados como tanto consultor advenedizo y renuente y, para ello, les voy a regalar una mínima taxonomía de los DBMSs que abra su apetito, despierte su curiosidad y los anime a visitar la Wikipedia para consultar los detalles.

Del año 68 para acá impera —salvo un número sorprendentemente elevado de mentes retrógradas— el llamado modelo relacional. Los más de los DBMS modernos siguen tal esquema. Aunque existe un reciente interés por paradigmas noSQL, por no divagar, sobre ellos no añadiré cosa alguna más allá de unas frases copiadas de aquí:

Google, Facebook, Amazon, Digg and other vested web properties didn’t turn to classic enterprise technology (such as RDBMs) to address their non-classical challenges of availability and scalability. Instead, they turned towards the core of the problem, and invented novel theories, concepts and solutions to cope with their enormous growth and subsequent demand. These solutions are now becoming available in the software commons, such as column-oriented databases, messaging queues and highly scalable infrastructure management tools.

Por simplificar, los DBMS relacionales, se encuadran en cuatro grandes grupos:

• Los tradicionales, con una arquitectura diseñada hace treinta años o más y que funcionan decentemente en contextos más o menos típicos (conjuntos de datos pequeños o medianos). Entre ellos se cuentan los más conocidos de los diletantes:
  • MySQL —y sus forks, como MariaDB— que es el motor de, entre otros, este blog.
  • SQL Server, para los amigos de los productos de Microsoft.
  • Oracle, que se administra tan fácilmente como se resuelve un sudoku ninja. Y que no es particularmente barato.
  • Postgres y sus derivados, como EnterpriseDB, que podrían sustituir a Oracle en el 95% de las instalaciones generando únicamente el 10% de los dolores de cabeza que produce este último. Y gratis, claro.
  • Otros como DB2 y similares, que tienen un tufillo a sobaco de dinosaurio que echa para atrás.
• Los orientados a columnas. Porque todos los anteriores almacenan información en tablas y en dichas tablas, los contenidos de una fila se almacenan de modo contiguo. Los orientados a columnas guardan de manera contigua los contenidos de las columnas. Es prácticamente como si almacenasen cada columna en un fichero distinto. Esta orientación a columnas les permite beneficiarse de una serie importante de ventajas técnicas: pueden leer únicamente un porcentaje de los datos en cada consulta (las columnas involucradas) obviando el resto, pueden incorporar mecanismos de compresión más avanzados y simples, etc. He visto alguna que otra comparación entre DBMS de ambos paradigmas (almacenamiento por filas y por columnas) y los primeros suelen ser más rápidos en operaciones de carga masiva, mientras que los segundos son mucho más rápidos a la hora de resolver consultas.
• Los DBMS MPP (massively parallel processing), aptos para instalaciones grandes. Entre ellos, se cuentan:
  • Teradata, que me hace sentir como todo un máster del universo cuando completa en dos minutos consultas que en otras instalaciones llevarían una tarde (o se caerían); o cuando ejecuta en unas horas 300.000 consultas que le lanzo —programáticamente, claro está— cuando me aburro y quiero jugar al gato y al ratón con los administradores.
  • Netezza, similar pero, aparentemente, más barata y recientemente adquirida por IBM.
• Finalmente, MPP y orientada a columnas, como Vertica, el DBMS emergente del que nunca han oído hablar nuestros carlossainz de ascensor.

En este contexto se explica bien el interés que puede tener IBM en adquirir Netezza. No es, como se lee en otros sitios, que IBM haya de alguna manera traicionado a sus antiguos clientes de DB2 o que haya particular riesgo de que le haga dormir el sueño de los justos. Es, más bien, que hay tantos DBMS como problemas de negocio y que IBM quiere un trozo del pastel que ahora tan rica (en varias acepciones) y monopolísticamente está disfrutando Teradata en este país nuestro en el que reina, más que Juan Carlos I, además de una irritante pereza intelectual, una enervante inapetencia por lo nuevo.

Aunque el concepto de minería de datos esté casi indisolublemente asociado al de bases de datos enormes, en la práctica, el análisis y desarrollo de los modelos se realizan sobre muestras pequeñas.

Esencialmente, para lo que nos ocupa, es pequeño un conjunto de datos que cabe en la RAM de un PC. Actualmente son habituales las máquinas con 1 GB. A modo de comparación, la base de datos de clientes de una de las mayores compañías españolas y en la que trabajé hace un tiempo venía a ocupar 5 GB.

De acuerdo con la ley de Moore, dentro de [18 * log( 5 / 1 ) =] 41,8 meses ya podrá considerarse pequeña.

Pero durante 41,8 meses todavía tendremos que seguir muestreando datos.
Por eso, en este artículo se discuten procedimientos para realizar extracciones aleatorias mediante el muestreo aleatorio simple –la más básica de tales técnicas– de algunos de los gestores de bases de datos con los que es más probable encontrarse:

• DB2:

  select * from t tablesample bernuilly (p);

• Mysql:

  select * from t order by rand() limit n;

• Oracle:

  select * from t sample(p);

• PostgreSQL:

  select * from t order by random() limit n;

• SQL Server:

  select top n * from t order by newid();

En las queries anteriores, p representa un porcentaje –un valor entre 0 y 100– y n, un determinado número de filas.
El interesado en realizar muestreos de dicha manera, deberían considerar la información anterior como punto de partida para investigar, según proceda:

• las opciones adicionales de las que disponen DB2 u Oracle
• la validez estadística de los muestreos realizados con newid(), rand() o random()
• tener en cuenta que, a partir de la versión 2005, SQL Server soporta el comando tablesample.

De todos modos, los que no podemos salir de casa sin Python en el pendrive siempre tenemos la opción de volcar las tablas a un fichero de texto y recurrir a una versión del siguiente script:

import random
from sys import argv, exit
import os
if len(argv) == 1:
  print("Argumentos: path fichero umbral")
  exit(1)
random.seed()
path = argv[1]
nom_f_entrada = argv[2]
umbral = float(argv[3])
nom_f_salida = "muest_" + nom_f_entrada
f_entrada = open(os.path.join(path, nom_f_entrada), "r")
f_salida  = open(os.path.join(path, nom_f_salida), "w")
f_salida.write(f_entrada.readline())
l = f_entrada.readline()
while l:
	if (random.random() < umbral):
	f_salida.write(l)
	l = f_entrada.readline()
	f_entrada.close()
f_salida.close()

Nota: esta entrada forma parte de las que aparecían en un antiguo blog mío ya extinto cuyo contenido trato ahora de recuperar. Algo de cuanto en ella se lee, por lo tanto, puede que huela a rancio (como la que se refiere al tamaño de la memoria habitual en los ordenadores). Pero lo más debería todavía poder sostenerse en pie varios años después.

Hoy aprovecho que pasan dos pájaros por el cielo para pegar un tiro que, seguro, es del interés de mis lectores: voy a utilizar un modelo lineal mixto para estudiar los factores que afectan al rendimiento de una familia de queries de SQL complejas.

El objetivo final es contar con criterios empíricos para la optimización de ciertas queries (siento decir optimización de queries: me obliga a ello la voluntad de que los buscadores me indexen donde más búsquedas se vayan a realizar; por una vez, renegaré del talibán ortográfico que llevo dentro) e, indirectamente, ilustrar con datos distintos de los habituales esta técnica estadística.

La query tiene este aspecto:

select * from ( un carajal de tablas y subconsultas ) where
fecha = :fecha: and
unidad = :unidad: and
cuenta = :cuenta:
;

Los dos factores que se consideran potencialmente críticos para el rendimiento de la query son el número de filas correspondientes a una unidad dada (unit.size en lo sucesivo) en una de las tablas subyacentes y el número de filas correspondientes a una cuenta determinada en otra de dichas tablas (account.size).

Además, se observa que la carga de la base de datos varía mucho en función de factores externos (¿otros usuarios accediendo a ella?) fuera de nuestro control.

Por tanto, analizaremos los tiempos de ejecución de la query en función de unit.size, account.size y, finalmente, de los factores no controlados. Para ello, se selecionamos 200 queries, es decir, 200 combinaciones de unidad, cuenta y fecha. Cada una de las queries se ejecuta 5 veces.

n <- 20
unidades <- sample( unidades, n )
epigrafes <- sample( epigrafes, n )
fechas <- sample( fechas, n, replace = TRUE )

Además, se aleatoriza con respecto al tiempo. Esto se hace para evitar que el efecto de las fluctuaciones de carga del servidor se confundan con el efecto de las distintas queries. Distribuyendo aleatoriamente en el tiempo el momento de la ejecución de una misma query se reduce la posibilidad de que todas las iteraciones de una de ellas se realicen en periodos de carga anormalmente alta o baja (los puristas del diseño experimental nos aplaudirían en este punto sólo a medias).

n.rep <- 5
iter.order <- sample( rep( 1:n, n.rep ) )

Finalmente, se construye el conjunto de datos mediante

salida <- do.call( rbind, sapply( iter.order, foo.sample, simplify = F ) )

donde foo.sample es la función que ejecuta la query contra la base de datos, cuenta el número de registros, etc.

Los datos resultantes pueden descargarse aquí. Se trata de una tabla con cuatro columnas: el identificador de la query, el tiempo de ejecución, unit.size y account.size. (Debería ser ocioso decir aquí que el identificador está asociado al enunciado de la query y no su ejecución puesto que cada una de ellas se repite 5 veces).

Análisis estadístico

Descargamos y normalizamos en primer lugar los datos:

dat <- read.table( url(
   "http://www.datanalytics.com/uploads/query_time_analysis.csv" ),
   header = T ) 

normalize <- function( x ) ( x - mean( x ) ) / sd( x )

dat$unit.size <- normalize( dat$unit.size )
dat$account.size <- normalize( dat$account.size )
dat$id <- factor( dat$id )

(La normalización se hace especialmente para facilitar la interpretación del modelo que se plantea más abajo). A continuación, creamos un objeto de la clase groupedData,

library( nlme )
dat <- groupedData( query.time ~ unit.size + account.size  | id, data = dat )

que viene a ser un data.frame con información sobre cómo ciertas filas están asociadas entre sí. De forma que si uno hace

plot( groupedData( query.time ~ 1 | id, data = dat ) )

se obtiene el siguiente gráfico:

Se aprecia en él cómo la varianza de los tiempos de ejecución crece con éstos. Además, por consideraciones relativas a la construcción de los datos —un cruce de varias tablas de cada una de las cuales se extrae un número variable de filas— hay razones para intuir una estructura multiplicativa en los datos. Eso nos hace considerar el uso de logaritmos. De hecho,

plot( groupedData( log(query.time) ~ 1 | id, data = dat ) )

que produce la gráfica

que tiene mejor aspecto. No es todo lo bueno que uno quisiera, pero tiene mejor aspecto. Nótese además, cómo el que los datos aparezcan ordenados en la figura puede hacer sobreestimar el efecto de la dispersión de la varianza. En realidad, más adelante, se plantea como ejercicio verificar de una manera más canónica cómo el tomar logaritmos no deja de tener sentido.

Finalmente, planteamos el modelo mixto usando la función lme del paquete nlme:

modelo <- lme( log2( query.time ) ~ unit.size + account.size,
   random = ~1 | id , data = dat )

El modelo consta términos fijos (unit.size y account.size) y de una parte aleatoria, ~1 | id. La parte aleatoria, de acuerdo con el consejo recurrente de mi colega Oliver Núñez en r-help-es, comprende aquellos términos del modelo que variarían de realizarse de nuevo el experimento. Y, efectivamente, la próxima vez que se ejecute una query, es improbable que ésta sea una de las 200 seleccionadas más arriba. El enunciado particular de la query es, por lo tanto, variable (o aleatorio, en nuestro contexto).

Alternativamente, usando modelos no mixtos, podría plantearse el modelo equivalente

modelo.lm <- lm( log2( query.time ) ~
   id + unit.size + account.size, data = dat )

que haría aparecer 200 (técnicamente, 199 porque no se ha eliminado el término independiente del modelo) coeficientes nuevos que representarían la variación en los tiempos de ejecución atribuida a cada enunciado de query en particular (variación que puede deberse a la distinta distribución de la filas correspondientes a distintas unidades o cuentas en el disco, etc.). Obviamente, estos coeficientes no son de mayor interés en sí mismos. A lo más, interesa saber si existen variaciones sustanciales entre las distintas queries que pudieran ser indicativas de algún fenómenono tenido en cuenta.

Mediante

summary( modelo )

se obtiene:

Linear mixed-effects model fit by REML
Data: dat
AIC    BIC  logLik
3259.9 3284.4 -1625.0

Random effects:
Formula: ~1 | id
(Intercept) Residual
StdDev:  0.00029632   1.2220

Fixed effects: log2(query.time) ~ unit.size + account.size
Value Std.Error  DF t-value p-value
(Intercept)  0.70249  0.038644 800 18.1785  0.0000
unit.size    0.19907  0.038665 197  5.1486  0.0000
account.size 0.11006  0.038665 197  2.8465  0.0049
Correlation:
(Intr) unt.sz
unit.size    0.000
account.size 0.000  0.008

Standardized Within-Group Residuals:
Min       Q1      Med       Q3      Max
-1.82818 -0.82791 -0.23734  0.70098  3.63443

Number of Observations: 1000
Number of Groups: 200

De la salida anterior interesan varios valores:

• En la sección correspondiente a los efectos aleatorios, la desviación estándar del término independiente, 0.00029632, que parece indicar que apenas hay variación entre las distintas queries.
• En la misma sección, el valor relativamente elevado, 1.2220, del residuo. Eso indica que existe una variación importante entre ejecuciones distintas de la misma query debidas, probablemente, a las distintas condiciones de carga del servidor en el momento de la ejecución. ¡Muy sintomático!
• En la sección de los términos fijos, el término independiente, de valor 0.7 y altamente significativo, debido a la peculiar normalización de los datos, indicaba un tiempo medio de ejecución de 2^0.7 = 1.62 segundos.
• En dicha sección los coeficientes de unit.size y de account.size, también altamente significativos, auguraban un rendimiento desigual de las consultas, posiblemente inaceptablemente desigual.

Finalmente, puede verse un gráfico de diagnóstico del modelo haciendo

plot( modelo )

que produce

y que muestra cómo la varianza de los residuos no parece variar apreciablemente con el tamaño predicho del tiempo de ejecución.

Colofón

En primer lugar, quiero dejar planteados varios ejercicios que serán sin duda de sumo provecho para los más inquietos e interesados de mis lectores:

• Generar y analizar el gráfico de diagnóstico para el modelo análogo en el que no se toman logaritmos de la variable objetivo.
• Calcular la varianza de los coeficientes asociados a cada una de las queries del modelo no mixto modelo.lm y compararla con la del término independiente de la parte aleatoria del modelo mixto.
• Reconstruir el modelo sin normalizar las variables previamente y analizar (comparativamente) los resultados.

Finalmente, doy respuesta a lo que más de uno se estará preguntando: todo esto, ¿para qué? Puede parecer, y alguno así me lo ha manifestado, un ejercicio ocioso. Pero, la verdad, los números muestran varios indicios fundamentales:

• El primero, que parece necesario identificar ventanas temporales en las que el servidor esté desocupado para realizar pruebas de rendimiento: dos desviaciones estándar de ruido intra-query multiplican los tiempos de ejecución en un factor de 5.4264 (=2^(2*1.22) ). Es algo que se intuía pero no se cuantificaba. Y que ponía en entredicho pruebas de rendimiento realizadas anteriormente.
• El segundo, que las dependencias del tiempo de ejecución con respecto al tamaño de las subqueries no son, como se suponía un tanto cándidamente, O(1). Más bien, son O(n*m). Y esto sugirió alterar el orden de los cruces de las tablas para lograr un plan de ejecución alternativo más rápido y robusto (que, de hecho, se encontró).

Hace no mucho escribí una entrada en este blog sobre, bromas aparte, cómo no escribir código SAS. Habría respondido in situ a uno de los comentarios que hicieron mis lectores pero, abusando de mi condición de dueño del blog, lo voy a hacer desde más encumbrado púlpito: una entrada ad hoc. Conste que escribo para discrepar. Pero conste también que lo hago desde la más genuina cordialidad y con la esperanza de generar un debate que a todos nos enriquezca.

El comentario venía a ser una crítica al uso de SQL dentro de SAS motivada parcialmente por el hecho de que quienes abusan de tal procedimiento de SAS son, precisamente, quienes menos SAS conocen (opinión con la que estoy bastante de acuero).

Dicho lo cual, manifestaré que cuando programo en SAS suelo utilizar SQL siempre que me es posible por las razones que enumero a continuación.

Las tres primeras son anecdóticas:

• SQL es parte integrante de SAS Base tanto como los pasos data. No es como SAS IML, SAS SCM u otros lenguajes de dominio que pueda vender SAS Institute.
• SAS utiliza SQL profusamente: por ejemplo, prácticamente todo el código que genera su herramienta de ETL es SQL (con algún paso data circunstancial para usar hashes y poco más). ¡Algún motivo tendrá!
• SAS, de alguna manera, promueve el uso de SQL. Véase si no este artículo que plantea 10 motivos (no los más poderosos) para usar SQL.

Consideraciones de legibilidad

La legibilidad es condición imprescindible para que el código sea bueno. Dejando aparte el hecho de que un código legible suele ser más eficiente que otro que no lo es, la legibilidad es condición indispensable para la mantenibilidad. Y comparemos dos pedazos de código aparecidos en un artículo que ya conocemos. El primero es el código feo y malo:

proc sort data=sales;
by region;
proc summary data=sales nway;
by region;
var saleprce;
output out=stats
mean=meansale;
data report;
merge stats sales;
by region;
if saleprce gt meansale;

El segundo es una reescritura del primero usando SQL:

proc sql;
create table report as
select * from sales
having saleprce gt mean(saleprce)
group by region;

Menos de la mitad de caracteres, muy expresivo.

Condiciones de interoperabilidad

El código bueno tiene que ser interoperable. Tiene que poder ser analizado y modificado por diversos usuarios. Y muchos conocen SQL (todos si yo fuese ministro de educación) pero sólo una minoría conoce la enrevesada sintaxis de un lenguaje sesentero.

Además, código que utiliza SQL es más fácil de migrar a otros sistemas y entornos fuera de SAS. Quien hubo de trabajar en uno de tales proyectos sabrá muy bien a qué me refiero.

Condiciones de rendimiento

Una de las pegas que los programadores de SAS de la vieja escuela ponen al uso de SQL en sus programas es el rendimiento. Gustan alegar que merge es mucho más eficiente que un join de SQL. Y eso siempre me parece síntoma de que la Wikipedia se lee menos de lo que se debiera. Existen varios algoritmos para cruzar tablas, uno de los cuales, el llamado merge join, es:

• Uno de los menos eficientes (con las salvedades que apunto debajo).
• El que usa merge en SAS Base.

Un merge join ordena las tablas que se quieren cruzar primero y luego lee filas una a una buscando coincidencias. Es el mejor algoritmo de cruce sólo cuando las tablas iniciales estás ordenadas precisamente por los campos de cruce. En el resto de los casos, es el algoritmo menos recomendado.

No sé qué técnica de cruce utiliza PROC SQL. Pero en el peor de los casos puede optar por un merge join y tener, cuando menos, el mismo rendimiento que merge. Si utiliza otro (o, mejor aún, si utiliza el que el optimizador considera más adecuado a la vista de los datos), sólo puede ser mejor.

Puede que quienes programaron SQL en SAS sean mucho más tontos que el resto de los programadores de la empresa y que, por lo tanto, el rendimiento de SQL sea inferior al que cabría esperar. Pero también es cierto que si un día los despiden y contratan a otros más avispados, el rendimiento de las aplicaciones podría mejorar substancialmente sin cambiar una sola línea de código.

SQL, ¿siempre?

No, hombre, no. No seamos como el proverbial tonto del martillo. Hay motivos poderosos para usar SQL, pero hay cosas que se hacen mejor programáticamente. Por ejemplo, cuando se usa first o last, cuando hay que echar la vista atrás para comparar la línea actual con alguna previa (operaciones que, merece la pena advertir, forman parte de las nuevas especificaciones del estándar de SQL). Pero fuera de eso, honestamente, no.