Cadenas a filas (‘tokenizer’) en Teradata.

Una y otra vez encuentro usuarios de foros preguntando por funcionalidades que de una u otra forma tienen como origen el problema de convertir cadenas de caracteres en filas (lo que algunos llaman ‘tokenizer‘). En pocas palabras, se trata de convertir una cadena de caracteres en filas que contienen los fragmentos de dicha cadena (palabras, campos, etc…) a partir de un caracter separador (‘token‘).

Ya escribí sobre cómo hacerlo en Oracle, pero en Teradata la cosa es un poco más complicada. Partiremos aquí también de una solución basada en ‘queries’ recursivas.

Hay que decir una vez más que por la arquitectura de Teradata la recursividad es algo que no le sienta demasiado bien, así que es posible encontrar problemas de rendimiento si se utiliza esta técnica en según que ‘queries‘, pero puede ser una solución de la que partir para muchos problemas cotidianos relacionados con el tratamiento de cadenas y de conversión de ‘queries’ desde otros SGBDR’s (Oracle, por ejemplo).

Vamos a utilizar cadenas separadas con guiones (‘-’) por claridad, pero valdría cualquier ‘token’ (incluso el espacio ‘ ‘).

Primero, como siempre, creamos una tabla de prueba y la cargamos con datos de ejemplo:

DROP TABLE MY_DB.PRUEBA01;

DROP TABLE MY_DB.PRUEBA01;

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID INTEGER NOT NULL,
      CTXT VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NULL)
PRIMARY INDEX ( ID );

CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID INTEGER NOT NULL,
      CTXT VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NULL)
PRIMARY INDEX ( ID );

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (0,'');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (0,'');

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1,'1-2-3-4-5');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1,'1-2-3-4-5');

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (10,'10-200-3000-40000');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (10,'10-200-3000-40000');

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (100,'100-20-3');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (100,'100-20-3');

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1000,'1000-200000');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1000,'1000-200000');

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (30,'En-un-lugar-de-La-Mancha');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (30,'En-un-lugar-de-La-Mancha');

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (9999,NULL);

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (9999,NULL);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT ID, CTXT
  FROM MY_DB.PRUEBA01
 ORDER BY ID, CTXT
;

SELECT ID, CTXT
  FROM MY_DB.PRUEBA01
 ORDER BY ID, CTXT
;

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

         ID  CTXT
-----------  ---------------------------------------------------------------------
          0
          1  1-2-3-4-5
         10  10-200-3000-40000
         30  En-un-lugar-de-La-Mancha
        100  100-20-3
       1000  1000-200000
       9999  (null)

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

La técnica que utilizaremos es ésta: iremos recorriendo las cadenas de forma recursiva sacando cada parte (cada fragmento entre ‘tokens‘) a una fila. Necesitaremos también el resto de la cadena para ir avanzando a la siguiente parte y tratarla. Así hasta terminarlo todo. Una vez entendida la estrategia, no hay más que un poco de INDEX() por aquí y un poco de SUBSTR() por allá. SQL puro y duro. Nada de magia, vamos.

Una cosa más: deberemos tener cuidado con los nulos (‘NULL’) y las cadenas vacías (”), que como sabemos (y a diferencia de Oracle) no son lo mismo.

Así pues, manos a la obra:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT *
  FROM RECPRUEBA
  ORDER BY ID, PARTNO;

WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT *
  FROM RECPRUEBA
  ORDER BY ID, PARTNO;

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

   ID  CTXT                      PARTNO  PART    POST
-----  ------------------------- ------  ------- ---------------------
    1  1-2-3-4-5                      1  1       2-3-4-5
    1  1-2-3-4-5                      2  2       3-4-5
    1  1-2-3-4-5                      3  3       4-5
    1  1-2-3-4-5                      4  4       5
    1  1-2-3-4-5                      5  5
   10  10-200-3000-40000              1  10      200-3000-40000
   10  10-200-3000-40000              2  200     3000-40000
   10  10-200-3000-40000              3  3000    40000
   10  10-200-3000-40000              4  40000
   30  En-un-lugar-de-La-Mancha       1  En      un-lugar-de-La-Mancha
   30  En-un-lugar-de-La-Mancha       2  un      lugar-de-La-Mancha
   30  En-un-lugar-de-La-Mancha       3  lugar   de-La-Mancha
   30  En-un-lugar-de-La-Mancha       4  de      La-Mancha
   30  En-un-lugar-de-La-Mancha       5  La      Mancha
   30  En-un-lugar-de-La-Mancha       6  Mancha
  100  100-20-3                       1  100     20-3
  100  100-20-3                       2  20      3
  100  100-20-3                       3  3
 1000  1000-200000                    1  1000    200000
 1000  1000-200000                    2  200000 

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Y vòilá.

Con esto podríamos buscar, por ejemplo, todas las segundas partículas de las cadenas (campo número 2 en un ‘registro’ de texto):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT ID,
       CTXT,
       PART
  FROM RECPRUEBA
 WHERE PARTNO = 2
  ORDER BY ID;

WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT ID,
       CTXT,
       PART
  FROM RECPRUEBA
 WHERE PARTNO = 2
  ORDER BY ID;

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

   ID  CTXT                     PART
-----  ------------------------ -------
    1  1-2-3-4-5                2
   10  10-200-3000-40000        200
   30  En-un-lugar-de-La-Mancha un
  100  100-20-3                 20
 1000  1000-200000              200000

Otros usos podrían ser indexar partículas de un texto, contar palabras, contar repeticiones, componer el texto al revés, etc, etc… En fin, el cielo es el límite.

Saludos.

Carlos.

Esta entrada fue publicada el a las Viernes 5 de Junio de 2009 y está archivada bajo las categorías Teradata. Puedes seguir las respuestas de esta entrada a través de sindicación RSS 2.0. Puedes dejar una respuesta, o trackback desde tu propio sitio.

Escribe un comentario