Dataprix

El SQL recursivo es algo que muy pocos utilizan -sobre todo por mero y simple desconocimiento- pero que en determinadas ocasiones resulta ser una herramienta muy útil. Ya hemos visto ejemplos de ello en Oracle: calcular un factorial, convertir cadenas a tablas y tablas a cadenas, pero hay muchas otras tareas para las que el SQL recursivo puede ser el camino a seguir.

Como vimos, Oracle implementa la recursividad mediante la cláusula ‘CONNECT BY’, pero ¿existe algo así en Teradata? La respuesta es sí.

En Teradata la recursividad se implementa con una sintaxis que tiene por estructura algo así:

WITH RECURSIVE nombre_tabla (col1, col2, col3...) AS ( SELECT "RAIZ" UNION ALL SELECT "RECURSIVO" (que hace referencia al "RAIZ" por medio de nombre_tabla) ) SELECT "TOTAL" (sobre nombre_tabla)

El truco del asunto está en comprender que nombre_tabla es una tabla temporal. Dicha tabla se va llenando con los resultados del SELECT “RAIZ” PRIMERO, y luego con los del SELECT “RECURSIVO” en cada ejecución. Así, cada ejecución del SELECT “RECURSIVO” se realiza contra contra esta tabla temporal que tiene los resultados de la ejecución anterior. Esto se repite hasta que el SELECT “RECURSIVO” deja de retornar filas. Entonces se ejecuta el SELECT “TOTAL” contra la tabla temporal nombre_tabla.

Visto así es mucho más sencillo de lo que parecía al principio (¿o no?).

Para ver un ejemplo de cómo funciona, vamos a hacer algo parecido a lo que hicimos en Oracle para convertir filas a cadenas de caracteres, pero lo vamos a utilizar aquí para mostrar los índices de las tablas de una base de datos con las columnas que los componen en su orden de definición:

Teradata BTEQ 08.02.03.03 for WIN32. Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED. Enter your logon or BTEQ command: .LOGON DBNAME/yyyyyy .LOGON DDBNAME/yyyyyy Password: *** Logon successfully completed. *** Teradata Database Release is V2R.06.02.01.18 *** Teradata Database Version is 06.02.01.17 *** Transaction Semantics are BTET. *** Character Set Name is 'ASCII'. *** Total elapsed time was 3 seconds. WITH RECURSIVE JERARQUICA (DATABASENAME, TABLENAME, INDEXNAME, COLUMNNAME, COLUMNPOSITION) AS ( SELECT a.DATABASENAME, a.TABLENAME, COALESCE(a.INDEXNAME,'PI') INDEXNAME, CAST(TRIM(a.COLUMNNAME) AS VARCHAR(128)), a.COLUMNPOSITION FROM DBC.INDICES a WHERE a.DATABASENAME='DBNAME' AND a.COLUMNPOSITION = 1 UNION ALL SELECT a.DATABASENAME, a.TABLENAME, COALESCE(a.INDEXNAME,'PI') INDEXNAME, b.COLUMNNAME || ',' || TRIM(a.COLUMNNAME) COLUMNNAME, a.COLUMNPOSITION FROM DBC.INDICES a, JERARQUICA b WHERE a.DATABASENAME = b.DATABASENAME AND a.TABLENAME = b.TABLENAME AND COALESCE(a.INDEXNAME,'PI') = b.INDEXNAME AND a.COLUMNPOSITION = b.COLUMNPOSITION + 1 ) SELECT DATABASENAME, TABLENAME, INDEXNAME, MAX(COLUMNNAME) COLUMNAS FROM JERARQUICA GROUP BY DATABASENAME, TABLENAME, INDEXNAME ; DATABASENAME TABLENAME INDEXNAME COLUMNAS ------------ --------- --------- ----------------- DBNAME TABLE01 PI COL01 DBNAME TABLE02 PI COL01 DBNAME TABLE03 PI COL01,COL02,COL03 DBNAME TABLE04 PI COL01 DBNAME TABLE05 PI COL01 DBNAME TABLE06 PI COL01,COL02 DBNAME TABLE07 PI COL01,COL02,COL03 DBNAME TABLE08 PI COL01,COL02 DBNAME TABLE09 PI COL01,COL02,COL03 ...

Evidentemente los nombres de la base de datos, las tablas y las columnas se han ocultado, pero probad en vuestro entorno y veréis lo bien que funciona…

Saludos.

Carlos.

      

Sun ha sacado su contraparte a la HP Oracle Database Machine: Sun Data Warehouse Appliance, se trata de un “join venture” con Greenplum, un (no demasiado conocido, al menos por mí) software de base de datos de “Data Warehousing” que bebe, como Teradata, en los principios del “share nothing”.

Saludos.

Carlos.

      

Las herramientas de carga de Teradata ponen en una tabla filas con información de los registros (líneas) que han fallado (lo mismo hace Oracle con su sql*loader utilizando ficheros y ‘logs’).

En cualquier caso, muchas veces hay que ver qué esta fallando en la línea y, eventualmente, editarla para corregir los errores.

Esto se puede hacer fácilmente con un mero editor de texto… si el fichero no es muy grande, claro. Pero ocurre que actualmente estoy tabajando con herramientas Teradata (fastload, multiload) para cargar en la base de datos ficheros de 15 o 20 Gb.

Por supuesto, ni intentar abrir un monstruo de estos con un editor.

Otra posibilidad es hacer algo así:

$ head nombre_fichero -n numero_línea | tail -n 1

Pero intentad hacer:

$ head nombre_fichero -n 182358216 | tail -n 1

y veréis lo que pasa.

Así que pensando, pensando, se me ocurrió recurrir al temido awk (¡awk!¡Qué miedo!). Pero no es para tanto.
Un poco de lectura del ‘man’, documentación, etc, etc y terminé discurriendo esto:

$ awk 'NR == numero_línea {print}' nombre_fichero

Los resultados son mucho más rápidos que con head/tail. Así, para un fichero con 188660096 líneas que tenía cuatro ‘pochas’ hice :

$ awk 'NR == 182358216 {print}' fichero_grande.txt > fichero_grande_bad.txt $ awk 'NR == 103504305 {print}' fichero_grande.txt >> fichero_grande_bad.txt $ awk 'NR == 165070071 {print}' fichero_grande.txt >> fichero_grande_bad.txt $ awk 'NR == 182358218 {print}' fichero_grande.txt >> fichero_grande_bad.txt

y en pocos minutos tenía las cuatro líneas en un fichero a mi disposición.

Y lo mejor: ¡¡ejecutado con (el para mí imprescindible) cygwin en un entorno Windows!!

Saludos.

Carlos.

      

Esta es vieja: se trata de calcular la edad basándose en dos fechas dadas (p.ej.: entre “hoy” y la fecha de nacimiento).

En principio la cosa parece fácil: hacer substracciones de las fechas basadas en los días o, mejor, en los meses. Pero la cosa se vuelve un poco más oscura cuando vemos que no todos los meses tienen el mismo número de días (28, 30, 31 incluso 29 en años bisiestos)

Oracle elimina estos engorros mediante una muy útil función: “MONTHS_BETWEEN”, que acepta dos fechas, y devuelve el número de meses entre ambas. La lógica de la función y la aritmética de fechas de Oracle se enargan de todos los cálculos necesarios para devolver la solución deseada (aunque hay que haber actualizado al menos a 9.2.0.6 para evitar molestos ‘bugs’):

SQL*Plus: Release 9.2.0.7.0 - Production on Mar Oct 7 17:49:42 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Release 9.2.0.7.0 - 64bit Production JServer Release 9.2.0.7.0 - Production SQL> SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('19720401','YYYYMMDD')) 2 > / 12 FROM DUAL; MONTHS_BETWEEN(SYSDATE,TO_DATE('19720401','YYYYMMDD'))/12 --------------------------------------------------------- 36,5181297

Con una división por 12 y algún ‘TRUNC’, pan comido.

Es capaz incluso de calcular sin problemas cuando la “fecha de nacimiento” está muy lejos en el pasado:

SQL> SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('00010101','YYYYMMDD')) 2 > / 12) FROM DUAL; TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('00010101','YYYYMMDD'))/12) ---------------------------------------------------------------- 2007 SQL>

En Teradata las cosas no son tan fáciles, ya que no existe función “MONTHS_BETWEEN” ni ninguna otra análoga, por lo que hay que ‘buscarse la vida’, como dicen los modernos.

Hay soluciones indicadas que se basan en hacer un ‘CAST’ a MONTH(4) (que en realidad son INTERVAL MONTH()) y convertirlo a INTEGER antes de dividirlo por 12 (años):

SELECT CURRENT_DATE, CAST((CURRENT_DATE - CAST('1972-04-01' AS DATE) MONTH(4)) AS INTEGER) / 12; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Date ((Date - '1972-04-01') MONTH/12) -------- -------------------------------- 08/10/07 36 BTEQ -- Enter your DBC/SQL request or BTEQ command:

La cosa en principio funciona bien, pero se estropea si intentamos calcular la edad de Cristo (más o menos):

SELECT CURRENT_DATE, CAST((CURRENT_DATE - CAST('0001-01-01' AS DATE) MONTH(4)) AS INTEGER) / 12; *** Failure 7453 Interval field overflow. Statement# 1, Info =0 *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:

Así que el hábil ‘CAST’ nos deja con el culo al aire para edades ‘provectas’, por lo que hay que probar otros caminos.

Hay quien prefiere enredarse con complejas llamadas a ‘EXTRACT’ sobre las fechas e ir extrayendo YEAR, MONTH, DAY e ir construyendo una aritmética con los trozos (CASE incluidas)…

Hay también quien prefiere recurrir a ‘JOINs’ con SYS_CALENDAR.CALENDAR y construir de nuevo la aritmética…

Por mi parte, prefiero volver la vista a la forma en que Teradata almacena fechas. En efecto, Teradata almacena las fechas de una forma particular: mediante enteros (’INTEGERs’) siguiendo la fórmula:

(YEAR - 1900) * 10000 + MONTH * 100 + DAY

Así:

SELECT CURRENT_DATE, CAST(CURRENT_DATE AS INTEGER); *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Current Date Current Date ------------ ------------ 2008-10-07 1081007 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Entonces, si hacemos algo así como:

SELECT CURRENT_DATE, ( CAST(CURRENT_DATE AS INTEGER) - CAST(CAST('1972-04-01' AS DATE) AS INTEGER) ) / 10000 EDAD; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Current Date EDAD ------------ ----------- 2008-10-07 36 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Y también funciona para calcular la edad que tendría hoy Nerón (por poner un ejemplo):

SELECT CURRENT_DATE, ( CAST(CURRENT_DATE AS INTEGER) - CAST(CAST('0037-12-15' AS DATE) AS INTEGER) ) / 10000 EDAD; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Current Date EDAD ------------ ----------- 2008-10-07 1970 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Saludos.

Carlos.

      

Oracle ha anunciado su “HP Oracle Database Machine“. Se trata de una solución “hardware/software” para ofrecer un alto rendimiento en sistemas de “Data Warehousing” (”I/O-intensive“) con grandes volúmenes de datos.

Oracle afirma que el sistema “is a complete, optimized and preconfigured package of software, servers, and storage” (completo, optimizado y preconfigurado paquete de software, servidores y almacenamiento). Está basado en lo que llaman “Exadata Storage Servers” que son sistemas de almacenamiento ‘inteligentes’ que efectúan algunas labores de preproceso de las búsquedas (SELECTs…) antes de enviárselos al motor de Base de Datos en sí (Oracle RAC). Con esto se consigue mejorar el rendimiento y ,sobre todo, los I/O bottlenecks.

La solución auna hardware de HP y Software Oracle.

Pues bien, este sistema es básicamente igual en arquitectura a los servidores Teradata: Teradata, aunque es conocido como un sistema de bases de datos para “Data Warehousing”, es mucho más: la solución Teradata engloba software y hardware. Teradata corre básicamente en servidores Teradata específicamente diseñados para ejecutar el software Teradata de la manera más eficiente y con máximo rendimiento. El diseño “hardware” del almacenamiento, comunicaciones, memoria, procesadores… está pensado por y para conseguir máximo rendimiento con este software específico.

Así pues: Oracle está siguiendo un camino que Teradata empezó hace muchos años…

Saludos.

Carlos.