Cadenas a filas con STRTOK_SPLIT_TO_TABLE()

18 enero \18\UTC 2016

La conversión de columnas de cadenas de datos separados por un ‘token’ en filas se ha tratado ya en este blog. Entonces se hizo utilizando recursividad (WITH RECURSIVE…), en esta ocasión vamos a ver cómo se puede hacer con la nueva función STRTOK_SPLIT_TO_TABLE() -disponible en Teradata desde la versión 14.0- y algunas particularidades que tiene su uso.

La función hace lo que dice su nombre: convierte en tablas cadenas delimitadas por ‘tokens’. Pero hace falta saber un poco más de ella: lo principal es que necesita un valor ‘key’ para referenciar las filas de la tabla resultante de la cadena de ‘tokens’ a partir de las filas de la tabla original. Esta clave funciona como una especie de PK/FK.

Las columnas de cadenas separadas por ‘tokens’ pueden ser tanto caracteres “normales” (CHAR, VARCHAR) como CLOB. La cadena ‘token’ (delimitador) puede ser de hasta 64 caracteres (el tipo es VARCHAR(64)), aunque normalmente será un único caracter (de hecho, su “DEFAULT” es el espacio simple (‘ ‘)).

Bueno, pues con todo esto vamos a crearnos la consabida tabla de ejemplos:

 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE CARLOS.PRUEBA_08 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ID_N INTEGER NOT NULL,
      C_TXT CHAR(1) NOT NULL,
      V_TXT VARCHAR(25) NOT NULL )
PRIMARY INDEX ( ID_N )
;


 *** Table has been created.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA_08 VALUES (1,'A','001');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA_08 VALUES (2,'B','002,003');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA_08 VALUES (4,'D','004,005,006');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM CARLOS.PRUEBA_08
ORDER BY 1,2;


 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  C_TXT  V_TXT
-----------  -----  -------------------------
          1  A      001
          2  B      002,003
          4  D      004,005,006

Basándonos en la definición y ejemplos de la documentación Teradata, podemos hacer:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT d.*
FROM TABLE (TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE(CARLOS.PRUEBA_08.ID_N, 
                                              CARLOS.PRUEBA_08.V_TXT, 
                                              ',')
            RETURNS (outkey integer, 
                     tokennum integer, 
                     token varchar(20) character set unicode) 
           ) as d 
ORDER BY 1,2;


 *** Query completed. 6 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

     outkey     tokennum  token
-----------  -----------  --------------------
          1            1  001
          2            1  002
          2            2  003
          4            1  004
          4            2  005
          4            3  006

¿Qué hemos tenido en cuenta? Pues que hemos elegido la columna ID_N como nuestra clave y la coma ‘,’ como nuestro separador. Fácil.

Pero supongamos que quermos ir un poco más allá: que queremos conservar en lo posible la estructura de la tabla original, con sus tres columnas (aunque podrían ser más). Entonces tenemos que recurrir a una versión un poco más complicada. Lo primero es que la clave elegida (inkey/outkey) puede ser VARCHAR y, aunque la documentación dice que puede ser de hasta 10 caracteres (VARCHAR(10)), lo cierto es que admite hasta VARCHAR(32). Otra cosa muy importante a tener en cuenta es que esta clave VARCHAR debe ser CHARACTER SET UNICODE (al igual que el valor de cada parte de la cadena devuelta).

Sabiendo esto, podemos concatenar las columnas que necesitemos (hasta un límite de VARCHAR(32)):

          
SELECT d.*
FROM TABLE (TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE(
               CAST(CARLOS.PRUEBA_08.ID_N AS CHAR(1)) || '|' ||
                  CARLOS.PRUEBA_08.C_TXT, 
               CARLOS.PRUEBA_08.V_TXT, 
               ',')
            RETURNS (
               outkey VARCHAR(32) character set unicode, 
               tokennum integer, 
               token varchar(20) character set unicode)
           ) d 
ORDER BY 1,2;


 *** Query completed. 6 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

outkey         tokennum  token
----------  -----------  --------------------
1|A                   1  001
2|B                   1  002
2|B                   2  003
4|D                   1  004
4|D                   2  005
4|D                   3  006

OK. Entonces podemos volver a descomponer la clave en sus columnas originales utilizando la función STRTOK(), que es prima hermana de nuestra STRTOK_SPLIT_TO_TABLE(), y utilizar simples CAST para que el resultado sea lo más parecido a nuestra tabla original:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(TD_SYSFNLIB.STRTOK(d.OUTKEY,'|',1) AS INTEGER) ID_N,
       CAST(TD_SYSFNLIB.STRTOK(d.OUTKEY,'|',2) AS CHAR(1)) C_TXT,
       CAST(d.TOKEN AS VARCHAR(20)) V_TXT_PART
FROM TABLE (TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE(
               CAST(CARLOS.PRUEBA_08.ID_N AS CHAR(1)) || '|' ||
                  CARLOS.PRUEBA_08.C_TXT, 
               CARLOS.PRUEBA_08.V_TXT, 
               ',')
            RETURNS (OUTKEY VARCHAR(32) CHARACTER SET UNICODE, 
                     TOKENNUM INTEGER, 
                     TOKEN VARCHAR(20) CHARACTER SET UNICODE)
           ) d 
ORDER BY 1,2,3;


 *** Query completed. 6 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  C_TXT  V_TXT_PART
-----------  -----  -------------------------
          1  A      001
          2  B      002
          2  B      003
          4  D      004
          4  D      005
          4  D      006

Saludos.

Carlos.


¡Ha muerto Bowie!

11 enero \11\UTC 2016

Cada vez que ocurre algo así siento que se muere un trozo de mi propia vida. Y en este caso, un trozo muy grande.

Voy a poner inmediatamente el Scary Monsters a todo volumen. Y luego el Heroes. Y luego el Lodger. Y luego…

Saludos.

Carlos.


Hoy…

8 enero \08\UTC 2016
image

Roy's birthday

Saludos.

Carlos.


¡Feliz Navidad!

24 diciembre \24\UTC 2015

Feliz Navidad a todos.

DSC_0001

(La foto la he hecho con mi juguete nuevo).

Saludos.

Carlos.

 


17.000.000.000 filas

16 diciembre \16\UTC 2015

O sea, diecisiete mil millones de filas, es lo que hemos estado moviendo hoy en la base de datos Teradata.

Saludos.

Carlos.


Tengo un juguete nuevo…

4 diciembre \04\UTC 2015

image

Y ahora, a estudiar.

Saludos.

Carlos.


“Logging Online Archive Off”

4 noviembre \04\UTC 2015

Puede que te haya pasado. Miras la tabla de eventos de backup (Dbc.RCEvent) o una de sus vistas (Dbc.EventsV…) y te encuentras un montón de filas con EventType “Logging Online Archive Off”. Y no sabes por qué están ahí y por qué hay tantas.

Dbc.EventsV

Pero vamos por partes: ¿Qué es eso del “Online Archive”? Pues ni más ni menos que un mecanismo para poder hacer un backup (“dump” en términos de arcmain) de tablas que estén siendo actualizadas (INSERT, UPDATE o DELETE). Dicho mecanismo es simple: cuando se inicia el “Logging Online Archive” para una tabla (también se pueden ejecutar para bases de datos) se crea una subtabla de log donde se guardan las filas tal y como estaban en el momento de activar el “logging” (“before image rows”) y también las modificaciones a la tabla en forma de “redo records”. Así se archivará una imagen estable de la tabla más los “redo records”. En caso de recuperar la tabla desde el backup, se recuperará la imagen estable de la tabla y se aplicarán los “redo records” correspondientes a las actualizaciones que ocurrieron durante el backup (“roll forward”).

La activación del “Logging Online Archive” se hace mediante el comando LOGGING ONLINE ARCHIVE ON y se detiene mediante LOGGING ONLINE ARCHIVE OFF, que además borra las subtablas de log existentes.

Y pensarás: ‘todo esto es muy interesante, pero yo no he activado el “Logging Online Archive” y, por supuesto, tampoco lo he desactivado’. Cierto. Entonces ¿de dónde salen los eventos de RCEvent?

Pues los eventos vienen, ni más ni menos, de los comandos DROP TABLE que se hayan ejecutado. Cuando se envía un comando DROP TABLE Teradata ejecuta un “Logging Online Archive Off” para asegurarse de que si existen subtablas de log sean borradas junto con la tabla principal. Además, “Logging Online Archive Off” no devuelve ningún error si se ejecuta sin que se hubiera ejecutado antes un “Logging Online Archive On”, por lo que es una ejecución ‘por si acaso’.

También aparecerán este tipo de eventos provocados por las ejecuciones de utilidades (fastload, multiload…), ya que cuando éstas terminan (sin error) se efectúan DROPs de las tablas de error, trabajo y log.

Saludos.

Carlos.


Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 62 seguidores