Estándar de Desarrollo Plataforma Teradata v.4.2-Publicado

(1)

LATAM AIRLINES GROUP

Estándar de Desarrollo Plataforma

Teradata

(Aplica desde TDT Versión 14.10 en adelante)

Arquitectura Corporativa LATAM

22/12/2014

Última Actualización: 18/05/2016

(2)

Estándar de Desarrollo

Plataforma Teradata

Arquitectura Corporativa LATAM

TERADATA

Fecha de Publicación: 2-12-2014 Fecha de Actualización: 18-05-2016

Página 1 de 43

CONTROL DE LAS MODIFICACIONES

Versión Descripción Autor Fecha Revisado por

1.0 Construcción del documento Juan Pablo Morales 2009-02-19 2

0

Se incorporan nuevos ítems y definiciones.

Juan Pablo Morales 2009-03-09 2

. 1

Corrección, ejemplo con alias nemotécnico y ajustes menores de formato. Guillermo Ordenes 2013-05-16 2 . 2 Incorporación de mejores prácticas Usuarios, Vistas Gloria Appelgren 2014-01-14 Incorporación y corrección de

Ambientes, Particiones, Tipos de Tablas y su operación, aplicación de estadísticas y otros Gloria Appelgren Hector Saavedra 2014-02-25 2014-03-11 2.0 Incorporación de links y modificaciones de sintaxis. Configuraciones y otros. Gloria Appelgren 2014-05-23 Guillermo Ordenes Hector Saavedra 3.0 Incorporación de observaciones y mejores prácticas

Cambio de orden de Temas

Gloria Appelgren 2014-06-15

TDT

4.0 Arquitectura de Referencia BI para EDW corporativo. Modificación de Modelos de Base de Datos – Estructura de Data Warehouse Corporativo – EDW.

Incorporación de nuevas funcionalidades TDT v14.10 de DB Columnar.

Mejores prácticas de Teradata Parallel Tranport. Extensión de los Operadores TPT Recomendación para la Gloria Appelgren 2014-09-30 TDT Guillermo Ordenes Vincent Beghin Elitsoft

(3)

TERADATA

Página 2 de 43

Recolección de estadísticas 4.1 DB Extra para “casos de uso”

en los que se requiera extracciones de datos desde TDT a otros sistemas. Gloria Appelgren 2016-03-15 Lorena Vivanco Vincent Beghin Guillermo Ordenes 4.2 Modificaciones a PI de tipo

Char o Varchar, Modificaciones a reglas de Tablas volátiles y temporales, Cálculo de Estadísticas y

responsabilidades, uso de vistas DBC, uso de NOPI y otros. Gloria Appelgren 2016-03-15 Guillermo Ordenes Vincent Beghin

Links importantes de documentación complementaria:

1. Estándar de Manejo de Históricos: EMH 2. Estándar de Construcción Objetos DB: ECODB 3. Estándar de Construcción de Sentencias SQL: ESQL 4. Validación MDC en Detalle: VMDC

5. Estándar de Construcción BTEQ. EBTEQ 6. Estándar DataStage. EDST

7. Políticas de Procesamiento DataStage: PPDST 8. Estándar Pentaho. EPTHO

9. Políticas de la Plataforma Teradata. PPTT

10. Estándares de Desarrollo Plataforma Teradata. EDPTT

(4)

TERADATA Fecha de Publicación: 2-12-2014 Fecha de Actualización: 18-05-2016 Página 3 de 43

Índice de Contenidos

1. INTRODUCCIÓN ... 5 2. ARQUITECTURA DE REFERENCIA ... 5

3. DISTRIBUCIÓN DE ESTRUCTURA DE BASE DE DATOS ... 6

3.1. DISTRIBUCIÓN DATABASE GENERAL ... 6

3.2. DATABASE PARA EXTRACCIONES ... 10

4. DEFINICIONES SOBRE EL USO CORRECTO DE TERADATA ... 10

5. TABLAS ... 11

5.1. CREACIÓN DE TABLAS ... 11

5.2. ORDEN DE SCRIPT DE CREACIÓN DE TABLAS INICIALES ... 12

5.3. ORDEN DE SCRIPT DE TRASVASIJE TABLAS ... 13

5.4. PRIMARY INDEX DE TABLAS ... 13

5.4.1. VALIDACIÓN DE DISTRIBUCIÓN DE DATOS ... 14

5.4.2. VALIDACIÓN DE SKEW FACTOR ... 14

5.5. TABLAS DE TIPO NOPI ... 16

5.6. SET VERSUS MULTISET ... 17

5.7. COMPRESIÓN DE TABLAS ... 17

5.8. PARTICIONAMIENTO DE TABLAS ... 19

5.9. FALLBACK Y JOURNALIST ... 20

5.10. JOINS DE TABLAS ... 20

5.11. TABLAS TEMPORALES Y VOLÁTILES ... 21

5.12. ALTER TABLE ... 21

6. VISTAS ... 22

7. MACROS ... 22

8. FUNCIONES ... 23

9. EXPLAIN Y VISUAL EXPLAIN ... 23

10. COLLECT STATISTICS... 25

11. PROCESOS DE CARGA – FRAMEWORK DE ETL/ELT ... 28

11.1. BTEQ ... 28

11.2. TERADATA PARALLEL TRANSPORTER (TPT) ... 28

12. CALIDAD DE DATOS ... 29

(5)

TERADATA

Página 4 de 43

14. USO Y ACCESO DBC ... 33

15. CONFIGURACIÓN CORRECTA DEL ODBC ... 33

16. ESTANDAR DE GENERACIÓN DE USUARIOS. ... 35

17. NOMENCLATURA DE TABLAS Y CAMPOS DIMENSIONALES... 35

18. ANEXOS ... 36

18.1. FORMATO GENERAL. SINTAXIS DE CREACIÓN DE TABLAS ... 36

18.2. INDEX WIZARD ... 37

18.3. SPOOL FANTASMA ... 37

18.4. USO DE DATA BLOCK SIZE ... 37

18.5. TERADATA COLUMNAR ... 38

18.6. CONJUNTO DE COMANDOS BTEQ ... 39

18.7. TERADATA PARALLEL TRANSPORTER (TPT) ... 39

18.7.1. OPERADORES PRODUCTORES ... 40 18.7.2. OPERADORES CONSUMIDORES... 41 18.7.3. OPERADORES STANDALONE ... 41 18.7.4. OPERADORES PERSONALIZADOS ... 42 18.8. COLLECT STATS ... 42 18.9. LINK DE UTILIDAD ... 43

(6)

TERADATA

Página 5 de 43 1. INTRODUCCIÓN

El siguiente documento tiene por objetivo indicar los estándares y mejores prácticas para los desarrollos en la plataforma Teradata versión 14 en adelante. Estos lineamientos son una referencia respecto a la construcción de objetos de base de datos, procedimientos y mejores prácticas a la hora de enfrentar un buen desarrollo en TDT, el cual es aplicable a Proyectos tradicionales y Business Intelligence relacionados con Datawarehouse para LATAM Airlines Group. En consecuencia, las áreas de validación y calidad podrán usar las recomendaciones que se proponen en el documento, con el objetivo de adaptarlas al proceso de certificación y poder mitigar los problemas de perfomance futuros y otros inconvenientes post producción.

2. ARQUITECTURA DE REFERENCIA

En este apartado se muestra la arquitectura de referencia para un Enterprise Data Warehouse (EDW), que todo proyecto de Gestión en LATAM Airlines Group, deberá considerar al momento de implementar en Teradata. Este lineamiento sigue las mejores prácticas de Teradata.

La siguiente figura muestra la arquitectura de referencia que define los lineamientos para el EDW implementado en Teradata.

Figura 1. Arquitectura de Referencia BI – Enterprise Data Warehouse en Teradata Las tres capas en Teradata corresponden a:

(7)

TERADATA

Página 6 de 43

Layer 1: Capa de Adquisición correspondiente a las bases Temporales, de Staging y Raw Históricos. Es la primera etapa de carga de datos al motor DB.

Layer 2: Capa de Integración de Datos: Corresponde a la ODS donde se encuentran las bases WRK y ODS, cuyo modelo normalizado o desnormalizado que almacena el data warehouse del negocio. Esta base debe responder en forma dinámica a las necesidades de data para gestión de la compañía y permita construir diferentes datamarts.

Layer 3: Corresponde a la capa de acceso y a la base FDM, donde se encuentran la base dimensional final y las vistas a tablas o de negocio que se requieren para explotar la base dimensional. Por lo tanto se encuentran los datamarts, vistas analíticas o de minería que sean requeridas por el negocio.

El objetivo del data warehouse corporativo es fomentar la reutilización y minimizar costos y el uso de recursos aprovechando los procesos de carga y las bases de datos que genera y usa la compañía para la gestión.

3. DISTRIBUCIÓN DE ESTRUCTURA DE BASE DE DATOS 3.1. DISTRIBUCIÓN DATABASE GENERAL

En este apartado se define las bases de datos con las que cuenta un proyecto. Cada una de ellas tiene un objetivo y ciertos permisos que facilitan la carga y la explotación de la base de datos Nombre DB corresponde al Nombre Corto del Proyecto. Para las bases existentes, Ej: BIFUEL, GPNR, GMIDT, etc. Para un Enterprise Data Warehouse, la base tendrá las siglas EDW.

Las Bases de Datos principales para EDW son: _FDM, _ODS, _LOG, _STG.

Figura 2. Modelo lógico y físico para la división en Bases de Datos en Teradata

Cada una de las bases de datos tiene un propósito específico y permisos los cuales se detallan en la siguiente tabla.

(8)

TERADATA Fecha de Publicación: 2-12-2014 Fecha de Actualización: 18-05-2016 Página 7 de 43 Nombre de DB y Ámbito

Descripción Permisos de usuario

para carga de datos (usr_o_....)

Desa/Beta/Prod

Permiso usuario explotación (usr_m_...) o (userid) Desa/Beta/Prod 1.- <NombreDB>_FDM Ej EDW_FDM

En esta base “Final Dimensional Model” - se encuentra el modelo dimensional final de los proyectos en el que se

encuentran las tablas de modelos estrella o copo de nieve.

Estos deben ser explotados a través de vistas que se detallan en los puntos 2 y 3 de esta tabla. Insert Update Select Delete N/A 2.- <nombreDB>_VT Ej: EDW_VT Es la base en la que se

encuentra las vistas a tablas VT (view table) del proyecto correspondiente a la capa de ACCESO. Los usuarios o clientes pueden acceder para explotar las Tablas de la base de datos de _FDM.

Vistas a Tablas: sus nombres

deben seguir la nomenclatura de las tablas. Para identificarlas, todas las consultas deberán llevar el prefijo de la base.

Select N/A

Select (sólo usuarios

Microstrategy, Administradores de Sistemas, CdN, Mantto y Arq)

3.-

<nombreDB>_VW: Ej:

EDW_VW

Es la base en la que se

encuentra las vistas de negocio del proyecto correspondiente a la capa Semántica. Se puede acceder para explotar la base de datos de _FDM. Esto se debe realizar a partir de las _VT y no directamente a las tablas del _FDM.

Vistas de Negocio: sus nombres

deben seguir la nomenclatura de las tablas. Para identificarlas, todas las consultas deberán llevar el prefijo de la base.

Select N/A

4.-

<nombreDB>_WRK Ej:

EDW_WRK

Es la base en la que se incorporan tablas de trabajo, previas a la carga del modelo final o intermedia entre STG y ODS. Create Drop Insert Update Select Delete N/A 5.- <nombreDB>_ODS Ej: EDW_ODS

Es la base dinámica en la que se encuentra una ODS

(Operational Data Store), correspondiente a un modelo normalizado. Contiene las

Insert Update Select Delete

(9)

TERADATA

Página 8 de 43 tablas fundamentales del

negocio que permitan construir los diferentes modelos

dimensionales para _FDM. También es posible mantener las tablas agregadas, fact y Lookup de manera que no sea necesario replicar las tablas en el FDM, sino que sean

accesadas a través de vistas. 6.-

<nombreDB>_VTODS Ej:

EDW_VTODS

Es la base que contiene las vistas a tablas del modelo en _ODS. Corresponde a la capa de acceso a _ODS.

Select N/A

Microstrategy, Administradores de Sistemas, CdN, Mantto y Arq) 7.-

<nombreDB>_VWODS Ej:

EDW_VWODS

Es la base que contiene las vistas de negocio que permiten esmascarar semánticamente la _ODS. Las vistas deben ser creadas apuntando a vistas de _VTODS. Nunca directo a las tablas.

Select N/A

8.-

<nombreDB>_LOG Ej:

EDW_LOG

Es la base que contiene el log de negocio. Su objetivo es dar visibilidad a CdN o Negocio para evaluar los indicadores.

Insert Update Select Delete N/A 9.- <nombreDB>_VTLOG Ej: EDW_VTLOG Es la base correspondiente a la capa de acceso a las tablas del log de negocio del punto 8.

Select N/A

Microstrategy, Administradores de Sistemas, CdN, Mantto y Arq) 9.1-

<nombreDB>_VWLOG Ej:

EDW_VWLOG

Es la base correspondiente a la capa semántica a las Vistas de LOG.

_VTLOG acceso a tablas con loocking for Access.

_VW se construyen utilizando las _VT.

Select N/A

Select a usuarios Microstrategy, Administradores de Sistemas, CdN, Mantto y Arq. 10.- <nombreDB>_STG Ej: EDW_STG

Es la base que almacena las tablas RAW Data y corresponde a la etapa de Staging de la carga de datos. Esta debe actualizarse de acuerdo al periodo

requerido por el negocio (diario, mensual o anual)

Create Drop Insert Update Select Delete N/A 11.- <nombreDB>_VTSTG Ej: EDW_VTSTG

Corresponde a la base que contendrá vistas a tablas de la base _STG. Está enfocada exclusivamente para las cargas de la base ODS y permite evitar los bloqueos de tablas.

Select N/A

<nombreDB>_VWSTG

Corresponde a la base que contendrá vistas de negocio y

(10)

TERADATA Fecha de Publicación: 2-12-2014 Fecha de Actualización: 18-05-2016 Página 9 de 43 Ej: EDW_ VWSTG

que apunta a la base _VTSTG. Está enfocada exclusivamente para las cargas de la base ODS y evitar los bloqueos de tablas.

<nombreDB>_TMP Ej:

EDW_TMP

Es la base en la que se

incorporan tablas temporales y que se pueden borrar

estructuras y datos.

Advertencia: Administración

podrá borrar sin necesidad de solicitar permisos. Create Drop Insert Update Select Delete N/A

14.-

<nombreDB>_HIS Ej:

EDW_HIS

Corresponde a la base Histórica de los Staging.

Sólo debe almacenar lo que estipule el DWH. Se debe administrar de manera que sólo esté disponible lo que se usará en el lapso de 1 año calendario.

Select Update Delete N/A 15.- <nombreDB>_VTHIS Ej: EDW_HIS

Corresponde a la base que contendrá vistas a tablas de la base _HIS. Está enfocada exclusivamente para las cargas de Históricos del Staging y busca optimizar los tiempos de carga en caso de requerir datos históricos. Se deba aplicar la política de manejo de históricos vigente.

Select N/A

16.- MCP_FDM (modelo dimensional) MCP_ODS (modelo operacional – 3FN) MCP_STG (sólo si es necesario para cargas de archivos externos)

Es la base en la que se incluyen las tablas de Log para el control de todos los proyecto.

Esta base es centralizada y entrega info de control de malla para todos los procesos de carga de Teradata en todas las capas (adquisición, integración y explotación).

Insert Update Select

N/A

17.- MCP_VT Es la base en la que se incluyen las vistas a la base MCP_FDM o MCP_ODS para control de carga de todos los proyecto pero que permita ser utilizado desde MSTR.

Select N/A

Select (sólo usuarios Microstrategy y

Administradores de Sistemas) 18.- MCP_VW Es la base en la que se incluyen

las vistas de negocio a la base MCP_VT para control de carga de todos los proyecto pero que permita ser utilizado desde MSTR.

Select N/A

Select (sólo usuarios Microstrategy y

(11)

TERADATA

Página 10 de 43 19.- MCP_SP Corresponde a la base con los

stored procedure exclusivos para el modelo de control de procesos centralizado.

exec N/A

Para los scripts de “creación de modelos” que se realizar en la primera oportunidad de paso a beta y producción serán aceptadas todas las instrucciones (create, drop, insert, etc) para los procesos de carga. Sin embargo, en los procesos de carga sistemática no deben existir scripts con instrucciones de Create, Alter y/o Drop en los ámbitos _FDM u _ODS. Esto será controlado en paso a beta.

3.2. DATABASE PARA EXTRACCIONES

Sólo en el caso de uso de “extracciones de datos” desde Teradata, que involucran procesos temporales y que prepararán archivos de salida para otros sistemas externos a Teradata. Se dispone de una base de datos centralizada y compartida que tiene como raíz una DB denominada EXTRA para este tipo de requerimientos.

Cada proyecto deberá gestionar una BD nueva dependiente de EXTRA y una BD de vistas con la estructura como se muestra en el ejemplo. Con lo anterior se garantiza la no interferencia con otros proyectos y recursos independientes.

La nomenclatura definida es: [Dirección]_[Negocio o CdN]_ [Proyecto] Estructura de Ejemplo:

>> EXTRA

 MKT_CU_HUB

o MKT_CU_HUB_WRK

 MKT_CU_HUB_VTWRK

La BD tendrá una cuenta de proceso. En este caso será de extracción ídem a las de procesos de carga (usuario “usr_o”) con permisos de lectura a los modelos a consumir, permisos de insert/update/drop sobre la base de trabajo y un spool adecuado para resolver las extracciones. No podrán tener una cuenta de tipo “usr_m”.

El tamaño de la BD propia debe se establecerá en 200 GB como límite máximo para este tipo de iniciativas. Sin embargo, se deberá estudiar cada caso y definir el tamaño requerido con las áreas indicadas. El responsable o proyecto deberá asegurar que las consultas que requiera realizar a bases existentes en Teradata estén optimizadas.

4. DEFINICIONES SOBRE EL USO CORRECTO DE TERADATA

La plataforma TERADATA es en esencia un motor de bases de datos relacional orientado al procesamiento de grandes volúmenes de datos para la gestión y el análisis. Es por ello que en LATAM esta plataforma está orientada al DataWarehouse Corporativo. Por lo tanto el uso

(12)

TERADATA

Página 11 de 43

operacional de la plataforma TERADATA no será aceptado, excepto para el ámbito de integración de datos.

5. TABLAS

5.1. CREACIÓN DE TABLAS

La creación de tabla es una de las actividades más relevantes para un buen desarrollo en la plataforma TDT. Una mala definición de tabla puede ocasionar serios problemas al desempeño al motor.

La mejor definición de una tabla depende de una serie de características y objetivos. Algunas preguntas típicas que ayudan a modelar y definir en forma más precisa una tabla.

1. ¿Cuál es el Primary Index de cada tabla? ¿Puede ser NOPI? 2. ¿Se debe usar la(s) columna(s) Primary Key como Primary Index?

3. ¿Cuándo debiera ser Non-Unique Primary Index? ¿Qué consecuencias trae esto? 4. ¿Necesitará Secondary Indexes? Si es así, ¿Qué Tipo? ¿Qué columna(s)?

5. ¿Debemos / Podemos permitir filas duplicadas? 6. ¿Qué tipo de datos debiera usar? ¿Qué atributos?

7. ¿Podemos / debemos comprimir? ¿Valores literales? ¿Tabla completa? 8. ¿Qué restricciones se deben definir? ¿Qué costos y consecuencias tiene eso? 9. ¿Se debe generar valores partir de otros campos?

10. ¿Puede/debe particionarse una tabla?

11. ¿Debemos realizar drop a las tablas al final de la sesión en áreas temporales?

La definición LATAM para la creación de tablas busca aplicar mejores prácticas para optimizar las bases de datos. Para más detalles de la estructura general de sintaxis para creación de tablas en Teradata se puede ver en el anexo 18.1 de este documento.

A continuación se presenta un ejemplo de una estructura típica de la creación de una Tabla en Teradata para LATAM, que recoge mejores prácticas para mejorar y optimizar el uso de recursos.

CREATE <<MULTISET/SET>> TABLE <<DATABASENAME>>.<<TABLE_NAME>>, NO FALLBACK,

NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT (

<<Field_1>> <<DataType1>> <<NOT NULL>>, <<Field_2>> <<DataType2>>

………

<<Field_N>> <<DataTypeN>> <<COMPRESS (for type)>> )

<<UNIQUE>> PRIMARY INDEX <<Name_Table>>_<<TypePI>>(<<Field(s)>>)

<<PARTITION BY RANGE_N>>(<<FieldFCH>> BETWEEN DATE <<'2013-09-01' AND DATE '2015-12-31'>> EACH INTERVAL <<IntervalType>> )

<<PARTITION BY (<<Partitioning Expression>>)>>;

COMMENT ON TABLE TABLE_NAME AS Ejemplo:'Tabla para registrar las transacciones de prueba.'; COMMENT ON COLUMN TABLE_NAME.FIELD_1 IS 'Identificador de los eventos’;

(13)

TERADATA

Dónde:

 << SET/ MULTISET>>: De preferencia use multiset si es posible, para más detalles ver apartado 3.3 sobre uso de SET/MULTISET

 <<Field_N>> es el nombre de los campos de la tabla, cuya nomenclatura está definida en el documento “Estándar de Construcción Objetos DB” (ECODB).

 <<DataTypeN>> corresponde al tipo de dato de un campo. Para más detalles puede hacer uso del siguiente link TDT-DataType

 <<UNIQUE>> PRIMARY INDEX. Se refiere al Índice Primario que debe ser definido por cada tabla, con el objetivo de obtener una buena distribución de datos en la plataforma. Para más detalle ver apartado 3.2.

 <<DATABASENAME>>. Corresponde al nombre de la base de datos en la que debe crear la o las tablas.

 <<<Table_Name>>_<<TypePI>>(<<Field(s)>>). Corresponde al nombre del Primary Index. Debe indicarse el nombre de la tabla, seguido del Tipo de PI: UPI (Unique Primary Index) o NUPI (No Unique Primary Index) y entre paréntesis van los campos que se han definido como PI. Para más detalle y ejemplos debe hacer uso del “Estándar de Construcción Objetos DB” (ECODB).

 <<PARTITION BY RANGE_N or CASE_N>>. Corresponde a las particiones definidas para la tabla. Deben seguir las definiciones estándares de TERADATA y su uso es obligatorio para todos los casos en que las consultas y tanto las cargas como la explotación, requieran optimización.

 Comment corresponde a las descripciones de la tabla y de cada uno de sus campos. La descripción debe ser explicativa de la función u operación de la tabla o campo y ojalá con algún ejemplo. Esto aplica sólo a tablas físicas. Tablas temporales o volátiles no aplica.

5.2. ORDEN DE SCRIPT DE CREACIÓN DE TABLAS INICIALES

Para la primera instalación, los scripts de creación de tablas, poblamiento y estadísticas, deben estar en el siguiente orden y por separado:

a) Crear la estructura de la tabla con su PI o NOPI.

b) Poblar la o las tablas. Realizar el Select-Insert o usar TPT - BTEQ.

c) Colectar Estadísticas al menos de los campos PI de las tablas físicas y de sus particiones en el caso de que existan. Este script en general será utilizado por los administradores semanalmente. Sólo si es estrictamente necesario, este script podrá ser utilizado en los procesos del proyecto y será el arquitecto quien verifique su utilización. (Ver apartado 9 para más detalle).

(14)

TERADATA

Esto aplica para la base STG principalmente, sin embargo, también aplica a las bases ODS y FDM e incluso LOG en la primera instalación del producto. Los scripts de cada base (ejemplo: STG, ODS y FDM al menos) deben ir por separado.

En especial el punto c) debe ser aplicable a todos los proyectos. Se reitera que las estadísticas debieran estar en procesos masivos semanalmente, excepto a los que requieran que los procesos actualicen las estadísticas frecuentemente debido a performance de estos mismos, como por ejemplo previo a la carga de ODS o FDM. Cada caso debe ser validado por el arquitecto y supervisado por el DBA.

En general el diseño físico de la base de datos en Teradata se debe considerar demografía de acceso, demografía de datos y volatilidad de éstos. Por ello no puede faltar el punto c). Para más detalles de diseño DB y tipos de índices en Teradata utilice el documento publicado por Teradata y que puede encontrar en TDT-DBDesign.

5.3. ORDEN DE SCRIPT DE TRASVASIJE TABLAS

Para realizar el trasvasije se deberá realizar un script por cada tabla. En el script de trasvasije por cada tabla debe contemplar:

a) Respaldar tabla origen. Es decir, renombrar las tablas existentes. b) Crear la estructura de la tabla destino con su PI y descripciones.

c) Poblar la tabla o las tablas, realizar el Insert- Select de tablas origen a destino. d) Borrar la tabla origen (drop de tabla renombrada o de respaldo)

e) Actualizar estadísticas ya sea por administración o por proceso. Deberá definirlo arquitecto del proyecto. En caso de mantenimientos, lo definirá DBA.

En otro script, debe colectar estadísticas al menos de los campos PI de las tablas físicas. Este script será utilizado por los administradores y no deben ser usadas por los procesos del proyecto. (Ver apartado 9 para más detalle).

5.4. PRIMARY INDEX DE TABLAS

Se debe asegurar una buena distribución de los datos en Teradata aplicando las mejores prácticas respecto a Primary Index (PI) o NOPI. Toda tabla debe tener un PI definido durante la creación o también puede indicarlo como NoPI (Este tipo de tablas son particularmente utilizadas para el volcados de cargas de datos en el staging). Los campos seleccionados como PI deben propiciar distribución de los datos en los AMPs en forma eficiente y el JOIN con otras tablas del modelo. Es recomendable usar tablas de tipo NOPI para el STG en los que sólo requiera una carga masiva de tablas sin transformaciones intermedias o posteriores.

La identificación de los PI para la explotación de la base de datos es importante a la hora de consultar el objeto. Para asegurar que el diseño físico haya incorporado un análisis de PI, todo proyecto deberá adjuntar el documento denominado “Análisis de PI y su

(15)

TERADATA

distribución”. Este documento debe contener al menos las tablas más representativas del modelo que haya sido aprobada por el arquitecto.

Generar tablas con mala distribución puede ocasionar serios problemas de perfomance de las consultas y de todo el sistema.

Siempre debe analizar la utilización de Primary Index Únicos (UPI), pero cuando no exista uno viable o no sirva para joins con otras tablas, entonces se debe verificar la utilización de un Primary Index no único (NUPI).

5.4.1. VALIDACIÓN DE DISTRIBUCIÓN DE DATOS

Para analizar la distribución de datos en base a PI, se puede utilizar la siguiente estructura de consulta que entrega la cantidad de registros que maneja cada AMP. Lo que corresponde a la distribución para el PI seleccionado.

SELECT (HASHAMP(HASHBUCKET(HASHROW(<<PI_SELECCIONADO>>)))) AS AMP_No, COUNT(*) AS Row_Count

FROM <<TABLA>>

GROUP BY AMP_No ORDER BY AMP_No;

5.4.2. VALIDACIÓN DE SKEW FACTOR

El “skew factor” debe tener un valor menor a 10. Pueden existir excepciones en el caso de que la cantidad de registros sea muy baja y no alcance a cubrir la totalidad de los AMPs. Para calcular el skew se puede utilizar la siguiente consulta. Si no tiene autorización, por favor solicítela a QA.

/* CALCULO DE SKEW */ ¿¿VALIDAR porque no me funcionó --- me dio errro SELECT

SUM(SKEWCALC.NumberOfRows) ASTotalRows ,MIN(SKEWCALC.NumberOfRows) ASMinRowsOnAmp ,MAX(SKEWCALC.NumberOfRows) ASMaxRowsOnAmp ,AVG(SKEWCALC.NumberOfRows) ASAVGRowsOnAmp ,100 - ( AVG(SKEWCALC.NumberOfRows)

/ MAX(SKEWCALC.NumberofRows) * 100 ) ASSkewFactor

FROM ( /* Tabla derivada para calculo de Skew*/ SELECT

HASHAMP (HASHBUCKET (HASHROW( /* Campos candiadtos para Primary Index (PI )*/ ))) ASAMPNumber ,COUNT(*) ASNumberOfRows FROM/* Base.Tabla */ <<base>>.<<Tabla>> GROUPBYTotalRows

No debe permitir valores nulos o vacíos en los campos que formarán parte del PI. Al momento de definir una tabla, se debe agregar “Not Null” a los campos del PI.

(16)

TERADATA

Para realizar copia de tabla, siempre debe hacerlo a una tabla vacía en el destino. Estas tablas deben tener el mismo PI. El beneficio de este es que se evita la comparación de registros ya existentes con los registros que se insertaran, y a su vez no hay redistribución de información.

Desde la versión 14.10 se permite utilizar Varchar en el PI. Este tipo de datos no tiene ningún impacto sobre recursos del servidor. El valor del PI es usado en el “Hash Algorithm” de la lataforma para generar la distribución de los datos entre los AMPs del sistema. Esto se efectúa independiente del tipo de dato y del tamaño de valor usado como PI.

Cuando se define un PI como NUPI intente que la cantidad de registros por combinatoria de NUPI no supere los 150 registros. Valores nulos o vacíos disparan esta cantidad.

Debe utilizar valores pequeños para los campos que formaran parte del PI. Esto es bueno al momento de actualizar estadísticas y en los Joins de tablas.

Se debe utilizar la menor cantidad de campos posibles al momento de definir un UPI o NUPI.

No confundir los Primary Index con Primary Key. Recordemos que en Teradata el Primary Index es el criterio de distribución de los registros en los AMPS y el Primary Key es una restricción de unicidad.

Método de

acceso Fortalezas Debilidades

Índice único primario UPI

 Es el método más eficiente, cuando la declaración SQL contiene el valor del índice primario.

 No requiere un archivo contenido en la base de datos cuando el número de filas retornadas es pequeño

 Ninguno, mientras la(s) columna(s) sean se bien escogidas.

Índice no único primario NUPI

 Provee un acceso eficiente cuando la declaración SQL contiene el valor del índice primario.

 No requiere un archivo contenido en la base de datos cuando el número de filas retornadas es pequeño

 Es lenta bajo un INSERT

 Puede disminuir la eficiencia, cuando algunos valores se repiten muchas veces en las misma columna.

Índice único secundario USI

 Provee un acceso eficiente cuando la declaración SQL contiene los valores de USI, y no se especifica valores de índices primarios.

 No requiere un archivo contenido en la base de datos para un SELECT.

 Requiere costo adicional para INSERT, UPDATE, MERGE y DELETE

Se sugiere su uso principalmente para explotación de datos. Para las cargas es afectado en performance.

Índice no único secundario NUSI

 Provee un acceso eficiente cuando el número de filas por valor en la tabla es pequeña.

 Puede requerir un archivo contenido en la base de datos.

 Requiere costo adicional para INSERT, UPDATE, MERGE y DELETE

(17)

TERADATA

 No será usada por el Optimizador de Teradata.

Búsqueda completa de tabla

 Accede a cada fila de la tabla a la vez

 Provee acceso usando cualquier condición

 Examina cada fila

 Requiere un archivo contenido en la base de datos tan largo como la tabla

Si no se puede asegurar una buena distribución o es difícil definirlo, utilice las herramientas existentes para identificar candidatos a PI o contacte a QA para solicitar servicio de análisis de PI.

5.5. TABLAS DE TIPO NOPI

Tablas NoPI son particularmente usadas para tablas de staging para carga de datos masivas (bulk data loads). Cuando una tabla no tiene primary index, sus filas pueden ser enviadas arbitrariamente a cualquier AMP, de manera que el sistema puede cargar datos en tablas staging más rápida y eficientemente usando operaciones insert en TPTLoad (Fastload) o Teradata Parallel Data Pump (TPump). El impacto en el rendimiento de CPU e I/O se reduce significativamente.

NoPI indica “No Primary Index”. Se debe especificar “NO PRIMARY INDEX” en la sintaxis de “CREATE TABLE”.

Algunas restricciones:

 No se debe crear tablas de cola como NoPI  No se debe crear tablas de errores como NoPI

 No se deben crear tablas NoPI tipo SET, sólo deben ser MULTISET.  Tablas NoPI no deben tener permanent journal.

 SQL UPDATE no pueden realizar update a tablas NoPI. Beneficios:

 Una table de tipo NoPI reduce el skew en tablas intermedias de ETL las cuales no tienen un PI natural.

 Las cargas (insert - TPTLoad (FastLoad) y/o TPump) a Tablas de Staging tipo NoPI son más rápidas.

(18)

TERADATA

Página 17 de 43 5.6. SET VERSUS MULTISET

Al momento de crear tablas se deberá usar “MULTISET” por sobre el “SET” para el poblamiento de tablas, esto para que el motor no ocupe más recursos en la identificación de registros duplicados.

Toda tabla de carga debe ser creada como MULTISET y con índice de tipo UPI, para así evitar retardos en la carga de datos y disminuir la carga de procesamiento del motor, la unicidad del dato estar garantizada. No se debe sobrecargar al motor con tablas de tipo SET.

1. Tabla SET con UPI. De esta manera Teradata chequea por duplicidad de valores PI y rechaza los duplicados, no hay necesidad de escanear toda la fila y todas las condiciones se satisfacen. Es recomendable.

2. Tabla MULTISET con UPI. De esta manera si ya se ha chequeado que no existan valores duplicados para PI, se satisfacen todas las condiciones. No requiere escaneo completo a las filas. Es recomendable.

3. Tabla SET con NUPI. Necesita escanear fila completa para evitar filas duplicadas en PI. Puede tener filas duplicadas si eso no es problema para el caso de negocio. No conviene esta combinación

4. Tabla MULTISET con NUPI, no requiere ningún chequeo, es aplicable sólo si las restricciones se han controlado en ETL y todo está bien en los datos. Es recomendable para el motor ya que no impacta en la carga, pero puede contener duplicados o skew podría ser alto.

Para optimizar las cargas, es más eficiente usar MULTISET UPI. Cualquier opción con NUPI debe ser justificada. Sólo en casos de que utilice NUPI, se recomienda realizar el manejo de duplicidad en la capa de ETL.

Es decir:

SI no tiene control de duplicidad en ETL entonces Teradata necesariamente tendrá que garantizar la no duplicidad y por lo tanto debe tener SET/MULTISET con UPI para que la revisión sea más eficiente.

5.7. COMPRESIÓN DE TABLAS

Teradata puede comprimir campos de una tabla para reducir el espacio utilizado, esto genera un mejor performance al leer o escribir en los campos con compresión aplicada. Existen algunas consideraciones:

 Campos índices no pueden ser comprimidos.

 La comprensión no aportará mayor beneficio si la columna en cuestión tiene valores mayoritariamente únicos.

(19)

TERADATA

Página 18 de 43  Los valores nulos son automáticamente comprimidos.

 El límite de valores a comprimir por columna es de 255.  La comprensión de datos no es aplicable a objetos volátiles.

La comprensión tiene que ver directamente con la definición de creación de una tabla, esto implica que cualquier actualización de la compresión de datos requiere una nueva definición para la tabla afectada por ende se hace necesario un “trasvasije” de datos renombrando la tabla original y creando la estructura modificada para realizar el trasvasije y luego dropear la tabla backup.

El impacto de actualizar una definición de comprensión para una tabla en uso guarda relación con el tamaño de dicha tabla ya es preciso contar con un espacio temporal para hacer el respectivo movimiento de datos. Por lo tanto es recomendable que todos los modelos incorporen la mayor cantidad de campos con compresión desde su desarrollo o su mantención.

Se puede mejorar la compresión de multivalores solo para los siguientes tipos de datos: • Nulls, Zeros, Blanks

• BYTE. Hasta 510 bytes • BYTEINT. Hasta 510 bytes

• CHARACTER. Hasta 510 caracteres.

• DATE. Expresado como COMPRESS (DATE ’yyyy-mm-dd’). • DECIMAL/NUMERIC

• DOUBLE PRECISION • FLOAT

• GRAPHIC. Hasta 510 caracteres. • INTEGER

• REAL • SMALLINT • VARCHAR • VARGRAPHIC

Para definir los valores candidatos que se pueden comprimir, se debe analizar la demografía del dato.

Siempre es recomendable aplicar compress a las tablas. Por lo tanto, si la tabla generada y cargada sobrepasa 1 GB debe incorporar compresión obligatoriamente con el fin de disminuir el tamaño.

(20)

TERADATA Fecha de Publicación: 2-12-2014 Fecha de Actualización: 18-05-2016 Página 19 de 43 Select databasename ,tablename

,(((sum(currentperm))/1024/1024) (decimal(15,2))) as espacio_mb From dbc.allspace

where

tablename = ‘<nombre de la tabla>’ and

Databasename = ‘<nombre de la base de dato>’

Groupn By databasename, tablename;

Para las actividades de análisis de compresión se debe usar MVC que permite obtener los DDL de los modelos con las compresiones sugeridas. Se puede aplicar posterior a un análisis.

5.8. PARTICIONAMIENTO DE TABLAS

Las particiones permiten mejorar el performance de las consultas. Por lo tanto es necesario realizar análisis y aplicar particionacionamiento (PPI: Partition Primary Index o MPPI: Multiple Partition Primary Index) en los modelo de datos siempre y cuando mejore las consultas o las eliminaciones en base a particiones.

Una partición o particiones pueden optimizar búsquedas pero dependerá de las estrategias o procesos que requiere realizar sobre la base. Una de estas condiciones son las siguientes: la tabla contiene algún campo fecha y además que el acceso a la tabla sea por fecha. Los campos de particionamiento estén en el PI si se requiere Otras condiciones de particionamiento dependen del problema de negocio que se desea resolver.

Si las condiciones no se dan para utilizar particiones favor contactar a QA para evaluar alternativas o consensuar la no utilización de PPI. Debe justificar si la tabla no lo requiere.

El proyecto deberá entregar el resultado del estudio de particionamiento de tablas, utilizando las mejores prácticas para la definición de los PPI. Las particiones a definir, dependerá de las estrategias para explotar el modelo. Para su implementación, todo proyecto o mantención debe explicar en la documentación las razones por las cuáles se elige un PPI en particular. En revisiones tempranas y control de calidad debe verificarse el uso apropiado de esta capacidad.

El o los PPI elegidos deben estar indicados en la creación de las tablas que aplica particionamiento.

Las particiones multinivel (MLPPIs), permiten mejorar significativamente el performance de ciertas queries y para realizar operaciones de insert de alto volumen, update y delete.

(21)

TERADATA

Para su administración posterior, debe ser entregado un script con indicación de mantenciones de los PPI al menos por un año las que serán responsabilidad de quienes se hagan cargo de las mantenciones. Más detalles en documentación oficial de Teradata en PPI-MPPI.

5.9. FALLBACK Y JOURNALIST

Fallback y Journalist no está permitido debido al exceso de uso de disco y I/O. Los scripts de creación de tablas deberán contener como se muestra a continuación.

CREATE <<MULTISET/SET>> TABLE <<BASE>>.<<NOMBRE_TABLA>>, NO FALLBACK,

NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT

Si algún proyecto o modelo requiere usar una o ambas capacidades, deberá ser fundamentado y aprobado por Arquitectura.

5.10. JOINS DE TABLAS

Se debe considerar que un inner joins, automáticamente analiza las tablas y utiliza la tabla de menor tamaño para su copia en los AMPs. En el caso de los outer joins Teradata no hace lo mismo, por lo que se debe tener cuidado en el orden de las tablas en el join.

Por la manera en que Teradata hace los joins, es ideal que las tablas usadas tengan el mismo Primary Index.

Nunca se debe realizar joins entre campos NUPI de una tabla versus NUPI de otra tabla. Si no se está seguro si los campos contienen o no nulos, debe filtrarlos en la sentencia where de la consulta. Ejemplo (… where col1 is not NULL;)

Utilizar los outer join solo cuando sea realmente necesario. Se recomienda no involucrar más de dos tablas en el outer joins.

Si se utilizara la sentencia union para hacer uniones de tablas, debe considerar que esta sentencia elimina los duplicados, en algunos casos, es recomendable utilizar las sentencias union all, en el caso de no realiza la validación de unicidad (ya que esta sentencia no elimina los dupicados) por lo que la respuesta es más rápida y ocupa menos recursos. Los registros duplicados no siempre son un problema y dependerá de la necesidad de la query.

Se recomienda nunca colocar campos de condición de filtro en los “ON” de los joins. Si va a utilizar subselect para realizar un join de tablas, debe filtrar las tablas antes de realizar el join, es decir, colocar los filtros dentro de cada subselect. Siempre se recomienda el uso de explain para analizar la consulta antes de ejecutarla.

(22)

TERADATA

Recordar que cuanto menos espacio utilice una consulta, mejor será su perfomance. Más lectura de disco (I/O) se traduce en consultas con mal performance y a su vez muy costosas de resolverlas.

5.11. TABLAS TEMPORALES Y VOLÁTILES

Las tablas temporales o volátiles deben ser implementadas en el ámbito _TMP de la base de datos.

La aplicación de estadísticas se debe aplicar a tablas temporales. Se podrán exceptuar las tablas cuya variabilidad de datos sea menor al 10% o si las tablas de origen y destino tienen el mismo PI y es para select/insert exclusivamente. Pero si este tipo de tablas es utilizado por vistas o por utilitario para realizar transformaciones o explotación de ellas, se debe incorporar script para cálculo de estadísticas.

Las tablas temporales, deben identificarse en el nombre con el prefijo TMP y con la concatenación al final de la fecha de creación. Por ejemplo:

 TMP_DET_CLIENTESLAN_130201: Esta tabla fue creada el 1 de febrero del 2013. Siempre debe considerar que el DBA podrá borrar las áreas TMP sin previo aviso.

Las tablas volátiles se deben identificar en el nombre con el prefijo VLTL. Por ejemplo:  VLTL_ DET_CLIENTESLAN. No requiere fecha porque su vigencia caduca al

finalizar la sesión del usuario.

El DBA será responsable de eliminar estos objetos mediante un proceso automático. Las tablas temporales y/o volátiles sirven de mucho a la hora de mejorar la perfomance de un query en particular. Además, sirven para hacer agrupaciones previas lo cual genera rapidez en ciertos casos. También permite minimizar la cantidad de registros a involucrar en un query que no son necesarios.

Es importante realizar explain a las consultas que utilizarán este tipo de tablas para verificar que la incorporación de temporales o volátiles alcance el objetivo esperado. Las tablas temporales y volátiles requieren generar estadísticas al menos de los campos PI, las que deben ser calculadas en los procesos según corresponda. (bteq o scripts).

5.12. ALTER TABLE

Los “alter table” solo serán permitidos en tablas pequeñas. Es decir, se puede realizar alter sobre tablas que no superen 1 GB de tamaño. No es recomendable para tamaños mayores a 1 Gb ya que corre el riesgo de bloqueo de la base de datos.

(23)

TERADATA

Página 22 de 43 6. VISTAS

El acceso a datos debe ser mediante vistas. Se pueden diferenciar dos tipos de vistas: vistas a tablas, vistas de negocio.

Las vistas a tablas corresponden a una capa de acceso de una tabla en específico y se debe ubicar en base <<BASE>>_VT. Todas las consultas deben tener como prefijo la base en la que se encuentra el objeto referenciado.

select <<fields>> from <<Base>>.<<Table>>;

Las vistas de negocio corresponde a una vista que incorpora alguna restricción o lógica de negocio a una o varias tablas.

Select <<some_fields>> from <<base>>.<<table o consultas>>

where <<condition>> <<group by some_fields >> <<order by some_fields >>. La vista tabla o tabla debe seguir las reglas indicadas en el capítulo de tablas.

Los usuarios finales no deben crear vistas, ya que se podría estar violando normas de seguridad a nivel de acceso a objetos, ya que un usuario eventualmente podría dar acceso a otro usuario que no posee el mismo perfil a través de una vista. Toda vista que no esté creada en un proyecto, debe ser solicitada a mantenciones LATAM.

Se debe optimizar el uso vistas anidadas considerando todas las mejores prácticas de las tablas y utilizando las herramientas propias de teradata para este objetivo (Visual Explain e Index Wizard en ambientes desarrollo y beta).

Es preferible generar solo una vista que llame a tablas y no a otras vistas, esto con el fin de eliminar filtros adicionales a vistas ya filtradas.

Todas las vistas que hacen refencias a tablas físicas directamente deben incorporar locking for access, esto es para evitar posibles bloqueos con otro tipo de sentencias. En el caso de vistas de tablas VT deben tener locking for Access, pero en el caso de Vistas de negocios VW, que hagan referencia a VT, no es necesario duplicar la sentencia de lock. Lo importante es que realice el locking antes de utilizar la tabla para las sentencias que corresponda.

Para más detalles debe hacer uso del Estándar de Construcción de SQL existente en http://arquitectura.lan.com.

7. MACROS

Las macros se utilizan para ejecutar un conjunto de tareas repetibles. Las macros son objetos de base de datos y por lo tanto pertenecen a un usuario o base de datos especificada en su creación. Una macro puede ser ejecutada por usuario, BTEQ o por otra macro. Sin embargo, las macros ejecutadas por otra no se permitirán.

(24)

TERADATA

Las macros deben ser cortas. Una macro completa es una sola transacción requiere un “Transient Journal” de la transacción total, por lo tanto sobrecargará el sistema si es muy extensa.

El código de las macros puede también especificar parámetros que pueden ser reemplazados por datos cada vez que la macro es ejecutada.

Las macros usualmente son más eficientes para queries repetitivas que para una sentencia simple. Las macros no están diseñadas para retornar parámetros a algún solicitante: Retornan solo el conjunto de respuestas para las sentencias SQL que contiene.

Las macros solo pueden ser ejecutadas con los privilegios EXEC sobre la BD Las macros pueden proveer seguridad a nivel de columna.

Las Macros tienen las siguientes ventajas: • Se reduce el tráfico de red.

• El plan de ejecución puede estar en caché, reduciendo la sobrecarga del parsing del motor.

• Aseguran un funcionamiento eficiente de las componentes en la sentencia SQL. • Permite tener componentes de base de datos reutilizables ahorrando recursos

del cliente.

• Pueden ser utilizados para obligar a la integridad de datos y modificadores de bloqueo.

• Pueden ser utilizados para hacer cumplir la seguridad.

8. FUNCIONES

En Teradata podemos encontrar funciones estándares y definidas por el usuario. Se aceptará utilizar las funciones estándares y no se aceptará funciones definidas por el usuario.

9. EXPLAIN Y VISUAL EXPLAIN

El uso del EXPLAIN es clave para visualizar el buen desempeño de una consulta. Es responsabilidad de cada área interesada en explotar Teradata, profundizar en la lectura y análisis de consultas mediante EXPLAIN, considerando que es parte integral de todo diseño, documentación y explotación de un modelo. Debido a que la demografía del dato cambia constantemente, el resultado de los análisis por explain varían.

Se debe considerar que los tiempos que el optimizador señala son solo de comparación, por lo tanto no son necesariamente exactos. Generalmente depende de aplicación de estadísticas y de otros factores relacionados con el diseño y los recursos disponibles.

(25)

TERADATA

Caso 1 (no óptimo):

SELECT rut, nombre FROM bd.pasajeros WHERE rut IN (SELECT rut FROM bd.viajes); Estimed time: 30 min.

Caso 2:

SELECT pax.rut, pax.nombre FROM bd.pasajeros pax inner join bd.viajes vjs ON pax.rut = vjs.rut;

Estimed time: 2 min.

No es correcto decir que el caso 2 demora 28 minutos menos que caso 1 Lo correcto es decir el caso 2 es 14 veces más eficiente que el caso 1.

Terminología output del Explain que se debe ser analizada:

All-AMPs …..

Redistributed by Hash Code Product join

All-AMPs Join Index not used

With Low / No Confidence

Terminología output del Explain cuando es óptimo:

Built Locally on the AMPs … Duplicated on all AMPs … Merge join

Single AMP JOIN By way of … index …

With High / Index Join Confidence

Es muy importante saber que en Teradata existen muchas formas de realizar una misma consulta sin perder los resultados. Los tiempos de espera son dramáticamente distintos entre una buena y mala consulta, es importante que cada Query que se desarrolle sea visualizada utilizando Explain.

Si un Query demora demasiado favor revisarlo y no volver a reintentar su ejecución sin antes haber analizado su plan de ejecución.

Tomar en cuenta también que para un buen desarrollo, primero las máquinas de desarrollo y beta, deben ser lo más parecido a su ambiente productivo. El optimizador se comporta distinto si las versiones de motor Teradata son distintas entre ambientes, o bien, si en producción las tablas poseen más datos que en ambientes de desarrollo y testing.

Intentar siempre utilizar el signo igual (“=”), cuando se requiere filtrar algún campo de la tabla esta sean siempre los que forman parte de UPI / NUPI.

(26)

TERADATA

Ejemplo:

Select rut, nombre from ejemplo.tabla where rut <> 3456768,

En este caso el primary index no es utilizado, por lo que el Teradata realiza un proceso costoso. (All-AMPs…..)

Se recomienda no realizar operaciones matemáticas con campos que forman parte del primary index.

El uso de Visual Explain estará restringido a usuarios líderes en las áreas de mantenimiento, arquitectura, continuidad de negocio y técnicos en los proyectos. Uno de los entregables de proyectos y mantenciones debe ser la evidencia del análisis de explain.

10. COLLECT STATISTICS

Una estadística adecuada logra disminuir notablemente los tiempos de ejecución y mejoran el performance de TDT sin embargo requiere más recursos para ello. Por lo tanto, cada nuevo proyecto o mantención debe aportar su propio set de estadísticas requeridas en función de las consultas que más frecuentemente se utilizarán o a lo menos de los PI definidos para cada tabla.

No obstante, en el transcurso del tiempo las consultas con las que se explote el modelo pueden variar, o dada la naturaleza dinámica de los negocios en particular pueden generarse nuevas consultas. Por lo tanto, es fundamental que el área encargada de la administración de la plataforma establezca un plan de actualización periódica de estadísticas. Para esta actividad TDT cuenta con una herramienta administrativa llamada “Statistics Wizard” que permite generar estadísticas para uno o varios modelos al mismo tiempo.

Los collects statistics pueden crearse o actualizarse tanto para índice o bien por campos que no participan en el PI.

Se debe usar sintaxis de la nueva versión para la colección y recolección de las estadísticas (recomendado desde la versión 14.0 en adelante)

Para la Colección: COLLECT STATISTICS COLUMN (o_orderdatetime, o_orderkey) ,COLUMN o_orderdatetime ,INDEX o_orderkey ON Orders;

(27)

TERADATA

Para la Recolección:

COLLECT STATISTICS ON Orders;

La recolección en este formato permite aprovechar la información de la colección original para usar un plan óptimo

Además las estadísticas a nivel de una columna puede aprovechar la información de las estadísticas combinadas generando en este caso específico un ahorro de 50% de CPU Para el caso de Indices se debe usar el siguiente comando:

Collect statistics index (col1, col2) ON ejemplo.tabla; (esta es requerida) Collect statistics column (col1) ON ejemplo.tabla; (esta es requerida) Collect statistics column (col2) ON ejemplo.tabla; (esta es requerida) Donde Col1 y Col2 son campos que participan del PI elegido.

Para el caso de campos que no participan del PI, los collect statistics consumen y exigen muchos recursos del sistema, por lo que se debe tener cuidado en el consumo de estos en estos casos.

Se debe analiza el tamaño de las tablas para decidir realizar un collect statistics utilizando todos los registros o con un una muestra representativa de ellos, por ejemplo 10%, como recomendación, sin embargo se puede otro tamaño de muestra, la que se debe justificar. Esta actividad en principio es responsabilidad del proyecto o mantención, sin embargo, en el on-going es responsabilidad del DBA para la recolección.

Ejemplo:

Collect statistics using sample index (col1, col2) ON ejemplo.tabla; Collect statistics using sample column (col3, col4) ON ejemplo.tabla; Dónde col1, col2, col3 y col4 no participan del Primary Index.

Se debe colectar Estadísticas de tablas temporales y volátiles al menos de los campos PI, las que deben ser calculadas en los procesos según corresponda (bteq o scripts).

Se recomienda generar y actualizar estadísticas en campos que son de alta modificación como lo pueden ser campos fecha, números de vuelos, etc.

Se debe calendarizar las actualizaciones de las estadísticas. Si las estadísticas no son actualizadas, los queries pueden tener un mal performance (Tarea que corresponde a DBA). Durante la construcción y explotación de una base de datos, se recomienda preparar planes de estadísticas. Para ello, con el utilitario SQL Assistant o Teradata Studio Express, se debe usar el comando “DIAGNOSTIC HELPSTATS ON FOR SESSION;”, de esta forma, obtener recomendaciones del motor y además evaluar la variabilidad de los datos en las tablas.

(28)

TERADATA

Cada vez que prepare una consulta, utilice explain. Para ello, si utiliza SQLAssistant, desde la ventana de la consulta a la cual se le requiere obtener estadísticas se presiona la tecla F6 (explain de la consulta), lo anterior permite ver el plan de ejecución con las recomendaciones de estadísticas hacia el final de dicho plan de ejecución. Del plan de ejecución deberán rescatar las estadísticas de alta recomendación (HighConf),

Otra técnica que se aplica a tablas con gran volumen de registros, es la utilización de “estadísticas parciales”. Así, se indica un porcentaje de los registros, lo cual permite reducir los tiempos de recolección de estadísticas. Se debe utilizar a criterio y dependerá de la variabilidad del dato y del tamaño de la tabla.

La recomendación de TDT es aplicar estadísticas a todas las tablas independientes de factor skew.

Para el caso de aplicación de estadísticas sobre campos que no participan del PI la recolección de estadísticas se debe aplicar con criterios ya que el collects demanda recursos del sistema. Por lo tanto, en el caso de que el motor recomiende aplicar sobre campos que no participan de PI, sólo se debe aplicarse en los que indiquen HighConf. Para el caso del refresco de éstas mismas, se debe verificar si tiene alta variabilidad de datos.

Observación: todo proyecto es responsable de entregar los scripts para la aplicación de estadísticas de sus modelos. La ejecución de scripts para recolectar estadísticas a tablas físicas es responsabilidad del DBA para los casos más centralizados y así definidos, sin embargo, es aceptado ejecutar estadísticas dentro de los procesos si es necesario para mejorar el performance de otros procesos que dependan de ellas, esto debe ser verificado por Arquitectos y DBA. La ejecución de scripts para recolectar estadísticas a tablas volátiles y/o temporales debe ser implementada en los procesos de proyectos.

Recomendaciones para TDT 14.10 sobre cuándo y a qué aplicar recopilación de estadísticas completa

 Columnas no indexadas utilizadas en el predicado  Todas las NUSI (No Unique Secundary Index)

 USI/UPI si es utilizada en un predicado non-equality (restricciones de rango)  La mayoría de las NUPIs (más abajo se discute en detalle)

 Estadísticas completa siempre requieren ser colectadas sobre columnas relevantes e índices en tablas pequeñas (menos de 100 filas por AMP)

 En todas las tablas particionadas que están sometidas a un crecimiento.  Partitioning columns of a row-partitioned table.

(29)

TERADATA

Página 28 de 43 11. PROCESOS DE CARGA – FRAMEWORK DE ETL/ELT

Para los procesos de carga se recomienda siempre utilizar las herramientas nativas de Teradata. En el caso de cargas a área de Staging es recomendable utilizar TPT load. Para las cargas de ODS y FDM es recomendado utilizar BTEQ.

11.1. BTEQ

Es un utilitario basada en herramientas vía comando que permite al usuario conectarse a base de datos Teradata. Provee una interacción en línea o batch que permite al usuario ejecutar sentencias SQL para las operaciones de datos.

Para más detalles de estándar y mejores prácticas BTEQ debe consultar el documento: Estándar de Construcción BTEQ. EBTEQ

11.2. TERADATA PARALLEL TRANSPORTER (TPT)

Teradata ha actualizado los conectores llamados TPT (Teradata Parallel Transporter) que consisten en las cuatro operaciones básicas:

1. Load Operator: Carga masiva de tablas vacías (FastLoad protocol)

Corresponde al uso del protocolo FASTLOAD. Herramienta de carga diseñada para mover grandes volúmenes de datos a tablas vacías. Sobre todo para proyectos que realizan cargas iniciales históricas que demandan mucho tiempo de proceso y muchos registros. Un escenario adecuado es cuando desde otras fuentes se deben cargar tablas temporales, de paso a modelos finales, que facilita la carga de datos iniciales en forma expedita.

2. Update Operator: Load/update/upsert/delete masiva de tablas (MultiLoad protocol)

Corresponde al uso del protocolo MULTILOAD. Herramienta que utiliza el paralelismo de Teradata. Multiload trabaja a nivel de bloques lo cual lo hace que sea una de las mejores herramientas para cargar tablas con datos ya existentes. Los update utilizando multiload no son tan eficientes como se puede esperar, en ciertos casos es más rápido eliminar los registros a actualizar e insertar el dato como si fuera nuevo, recordar que siempre se debe analizar qué es lo que se debe resolver antes de implementar una solución.

3. Stream Operator: Carga continua de tablas (TPump protocol)

Es un operador consumidor, que emula las utilidades de TPump para realizar Inserts, Updates, Deletes, y Upserts en forma paralela a alta velocidad en un “near-real-time”, a una o más tablas vacías o preexistentes, sin bloquear las tablas destino. El bloqueo es a nivel de fila, tal como se hace con los Operadores SQL. El operador stream permite realizar operaciones de carga en background durante el uso normal del sistema. Las instancias del operador Stream pueden

(30)

TERADATA

actualizar hasta 127 tablas en Teradata. Se pueden utilizar multiples instancias paralelas para mejorar el performance de la actualización de los datos.

4. Export Operator: Descarga masiva de tablas (FastExport protocol).

Corresponde al uso del protocolo FASTEXPORT. Herramienta de extracción de datos de Teradata. Es lo contrario del fastload pero tan eficiente como él. Es una herramienta extremadamente rápida para exportar grandes volúmenes de datos. El fastexport puede utilizar tanto una tabla como una vista de teradata como fuente de datos.

Cuando se requiere incluir información que reside en archivos fuera de Teradata se puede hacer utilizando el protocolo MULTILOAD . Si los tiempos de respuesta no son los esperados utilizar protocolos FASTEXPORT o FASTLOAD a una tabla de paso y luego integrar la información al Teradata con un Insert Select.

En los casos en que se debe realizar un update, es recomendable no usar la instrucción “update” ya que los procesos son altos. Además, se debe considerar que los rollback en Teradata son bastante costosos en lo que se refiere a tiempo y uso de recursos. En ese caso, el lugar de update se deberá borrar los registros e insertarlos con los valores nuevos.

Si el proceso requiere eliminar registros, mejor hacer una copia utilizando la sentencia insert select de los datos que no serán borrados en otra tabla. Renombrar la tabla original, verificar que el insert select haya copiado los registros esperados. Restaurar los permisos grant de la tabla si corresponde. Realizar un drop table de la tabla original.

Nunca abortar un proceso que lleva mucho tiempo en ejecución ya que los rollbacks son costosos en Teradata, sobre todo en los pasos de Merge.

12. CALIDAD DE DATOS

Asegurar la calidad de los datos es un asunto que hay que considerar. En general, mejorar la calidad implica realizar varias operaciones en las consultas con el objetivo de obtener o mantener los datos correctos. La mayoría de las consultas podrían provocar malos accesos a los datos o bien nunca utilizan los primary index indicados para el acceso a los objetos, lo que se traduce en mucha lectura de disco.

Es recomendable no cargar registros que no cumplen con algún tipo de calidad esperada por el usuario. Además, es recomendable asignar valores a campos que contienen datos nulos.

Utilizar más codificación en tablas de gran tamaño (tablas minor), o bien evitar almacenar descripciones en tabla de estas características.

Considerar tabla de formatos y valores posibles de almacenar.

BYTEINT: Values between -128 to +127