Tablas SET y MULTISET en Teradata.

El modelo relacional -basado en la teoría de conjuntos- sobre el que están construídos los sistemas gestores de bases de datos relacionales (SGBDR’s) impide que en una misma tabla haya dos filas iguales (esto es un corolario de la primera forma normal). Es por ello que en la la mayoría de dichos SGBDR’s se impone el uso de una clave primaria (Primary Key) por tabla.

Teradata no funciona de esa manera: las PK’s son consideradas conceptos de diseño y no de implementación, y lo más parecido a ellas son los (Unique) Primary Indexes, que tienen más funciones de distribución (en los AMP’s) y rendimiento que de identidad.
No obstante el estándar ANSI sí que permite que en una tabla en la que no haya definidas restricciones de unicidad (PK’s, UK’s, Unique Indexes…) existan filas idénticas -aun a costa de romper la 1FN-.

Teradata incluye en la sintaxis de definición de tabla (DDL) la característica de SET o MULTISET. Si una tabla se define MULTISET, Teradata no efectuará un chequeo de unicidad de fila y permitirá que contenga filas idénticas (sin tener en cuenta otras posibles restricciones de unicidad mediante índices únicos). Como ya hemos dicho, MULTISET es el estándar ANSI.
Por otro lado, si una tabla se define SET Teradata sí efectuará un chequeo de unicidad de fila y no permitirá la existencia de filas idénticas. Este es el modo ‘por defecto’ de Teradata.

Pero las cosas son un poco más sutiles de lo que cabría suponer. La gestión de la aparición de errores puede sorpreder un poco:

 Teradata BTEQ 08.02.03.03 for WIN32.
 Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.LOGON MY_TD/MY_DB

.LOGON MY_TD/MY_DB
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.

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

CREATE TABLE MY_DB.PRUEBA01 (COL01 INTEGER NOT NULL, COL02 INTEGER)
PRIMARY INDEX (COL01);

CREATE TABLE MY_DB.PRUEBA01 (COL01 INTEGER NOT NULL, COL02 INTEGER)
PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (1,1);

INSERT MY_DB.PRUEBA01 VALUES (1,1);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (1,2);

INSERT MY_DB.PRUEBA01 VALUES (1,2);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (1,3);

INSERT MY_DB.PRUEBA01 VALUES (1,3);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (2,1);

INSERT MY_DB.PRUEBA01 VALUES (2,1);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (2,2);

INSERT MY_DB.PRUEBA01 VALUES (2,2);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (3,1);

INSERT MY_DB.PRUEBA01 VALUES (3,1);

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

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

SELECT * FROM MY_DB.PRUEBA01;

SELECT * FROM MY_DB.PRUEBA01;

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

      COL01        COL02
-----------  -----------
          2            1
          3            1
          1            1
          2            2
          1            2
          1            3

Hasta ahora lo único que hemos hecho es crear una tabla con datos que nos servirán de ‘fuente’. Las particularidades de SET y MULTISET las vamos a ver ahora.

Creamos cuatro tablas, con todas las variaciones posibles de SET, MULTISET y UNIQUE/NOT UNIQUE PRIMARY INDEX

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE MULTISET TABLE MY_DB.PRUEBA02_MS_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

CREATE MULTISET TABLE MY_DB.PRUEBA02_MS_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA03_S_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

CREATE SET TABLE MY_DB.PRUEBA03_S_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE MULTISET TABLE MY_DB.PRUEBA04_MS_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

CREATE MULTISET TABLE MY_DB.PRUEBA04_MS_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA05_S_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

CREATE SET TABLE MY_DB.PRUEBA05_S_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

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

Ahora nos disponemos a insertar en ellas el contenido de la primera columna de nuestra tabla ‘fuente’, que contiene datos iguales.

El comportamiento ante la inserción en la primera tabla (MULTISET / UNIQUE PRIMARY INDEX) es el esperado:

INSERT MY_DB.PRUEBA02_MS_UPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA02_MS_UPI SELECT COL01 FROM MY_DB.PRUEBA01;
 *** Failure 2801 Duplicate unique prime key error in MY_DB.PRUEBA02_MS_UPI.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

En efecto: falla con un error 2801 ‘Duplicate unique prime key error in MY_DB.PRUEBA02_MS_UPI‘.

Pero veamos que pasa con la siguiente tabla (SET / UNIQUE PRIMARY INDEX):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA03_S_UPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA03_S_UPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. 3 rows added.
 *** Total elapsed time was 1 second.

¡No ha habido error! Pero se han insertado 3 filas, no las 6 de la tabla fuente. La verificación de unicidad (SET TABLE) ha eliminado silenciosamente los duplicados antes de la inserción, por lo que la unicidad del índice no ha disparado el error como en el caso anterior.

El siguiente caso (MULTISET / NOT UNIQUE PRIMARY INDEX) es trivial: insertará todas las filas.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA04_MS_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA04_MS_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. 6 rows added.
 *** Total elapsed time was 1 second.

Y, por último, el caso SET / NOT UNIQUE PRIMARY INDEX, análogo al segundo: sin error, pero insertando sólo las filas distintas (3):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. 3 rows added.
 *** Total elapsed time was 1 second.

Un par de cosas más: si volvemos a ejecutar el último INSERT sobre la tabla SET no aparecerá ningún error, pero no insertará ninguna fila (‘No rows added’):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. No rows added.
 *** Total elapsed time was 1 second.

dado que todas existen ya en la tabla destino.

Segundo: Esto no funciona con un INSERT…VALUES, que SÍ lanzará un error de duplicados en una tabla SET:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA05_S_NUPI VALUES (1);

INSERT MY_DB.PRUEBA05_S_NUPI VALUES (1);
 *** Failure 2802 Duplicate row error in MY_DB.PRUEBA05_S_NUPI.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Así que realmente existe una gran diferencia entre SET y MULTISET, incluso si se definen índices únicos. SET hace que los INSERT…SELECT sólo inserten filas que no existan previamente en la tabla destino. NO lanzan ningún error de duplicidad, pero el número de filas insertadas puede ser menor que los que hay en la tabla fuente (e incluso ser cero).

Saludos.

Carlos.

5 respuestas a Tablas SET y MULTISET en Teradata.

  1. Javier dice:

    Cesar, muy bueno el articulo. Solo agregaria que a veces se utilizan tablas MULTISET, sabiendo que el universo de datos son registros unicos, ya que se mejora notablemente los tiempos de carga. Esto se debe a que el motor no tiene que verificar la existencia del registro que esta por ingresar. En estos casos el control de los registros unico se realiza en un paso anterior.

    • carlosal dice:

      Javier:

      Supongo que te estarás refiriendo a cargas con multiload, ya que a fastload le da igual que la tabla sea SET o MULTISET: elimina duplicados de todas formas.

      En cuanto a lo de la verificación de duplicados sólo decir que Teradata NO realiza dicha verificación si la tabla tiene definido un UPI o USI aunque esté definida como SET TABLE.

      Saludos.

      Carlos.

  2. […] SET y MULTISET en Teradata (II). Como vimos en Tablas SET y MULTISET en Teradata, la verificación de duplicados en tablas SET y MULTISET es un poco rara para los […]

  3. Luis dice:

    Y en los scripts TPT, como se controla la inserción de duplicados?, porque SET y MULTISET no aplica en absoluto….

    • carlosal dice:

      Luis:

      A ver, SET y MULTISET son atributos de las tablas y no de las herramientas de carga.

      RTFM: Teradata Parallel Transporter User Guide.
      Chapter 4: Loading Data Loading with the Load Operator

      Duplicate Rows
      Duplicate rows, which are exact duplicates of existing table rows, are never inserted, even if the
      target table is defined as a multiset table, which usually permits duplicate rows. Therefore,
      duplicate row violations are not captured in either error table. Instead, they are counted and
      reported in the status log at the end of a job.

      Saludos.

      Carlos.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: