🥇Monitoreo de procesos ETL en un pequeño almacén de datos

Muchos utilizan herramientas especializadas para crear procedimientos de extracción, transformación y carga de datos en bases de datos relacionales. El proceso de trabajo de las herramientas se registra y los errores se documentan.

En caso de error, el registro contiene información sobre qué tarea el instrumento no pudo ejecutar y en qué módulos (frecuentemente es Java) se detuvieron. En las últimas líneas se puede encontrar el error de la base de datos, como una violación de la clave única de la tabla.

Para responder a la pregunta sobre qué papel juega la información de errores de ETL, clasifiqué todos los problemas que ocurrieron en los últimos dos años en un gran almacén de datos.

Los errores de la base de datos incluyen aquellos como falta de espacio, desconexión, sesión bloqueada, etc.

Los errores lógicos incluyen aquellos como violaciones de claves de tabla, objetos no válidos, ausencia de acceso a objetos, etc.
El programador puede no ejecutarse a tiempo, puede bloquearse, etc.

Los errores simples no requieren mucho tiempo para corregirse. La mayoría de ellos pueden ser manejados por un buen ETL de forma autónoma.

Los errores complejos requieren abrir y revisar procedimientos de trabajo con datos, investigar fuentes de datos. A menudo llevan a la necesidad de probar cambios y despliegues.

Así que, la mitad de todos los problemas están relacionados con la base de datos. El 48% de todos los errores son errores simples.
Un tercio de todos los problemas está relacionado con el cambio de lógica o modelo del almacén, más de la mitad de estos errores son complejos.

Y menos de una cuarta parte de todos los problemas están relacionados con el programador de tareas, de los cuales el 18% son errores simples.

En general, el 22% de todos los errores ocurridos son complejos, su corrección requiere la mayor atención y tiempo. Ocurren aproximadamente una vez a la semana, mientras que los errores simples suceden casi todos los días.

Es evidente que el monitoreo de los procesos ETL será efectivo cuando el registro indique con máxima precisión el lugar del error y se requiera el mínimo tiempo para localizar la fuente del problema.

Monitoreo efectivo

¿Qué me gustaría ver en el proceso de monitoreo de ETL?

Inicio en — cuándo comenzó a trabajar,
Fuente — fuente de datos,
Capa — qué nivel del almacén se está cargando,
Nombre del trabajo ETL — procedimiento de carga que consiste en múltiples pasos pequeños,
Número de paso — número del paso que se está llevando a cabo,
Filas afectadas — cuántos datos ya se han procesado.
Duración en segundos — cuánto tiempo ha estado en ejecución.
Estado — si todo está bien o no: OK, ERROR, EN EJECUCIÓN, BLOQUEADO.
Mensaje — el último mensaje exitoso o la descripción del error.

Con base en el estado de los registros, se puede enviar un correo electrónico a otros participantes. Si no hay errores, el correo no es necesario.

Así, en caso de error, se indica claramente el lugar del incidente.

A veces ocurre que la propia herramienta de monitoreo no funciona. En tal caso, se puede llamar directamente en la base de datos a la vista utilizada para generar el informe.

Tabla de monitoreo ETL.

Para implementar el monitoreo de procesos ETL, se necesita solo una tabla y una vista.

Para ello, se puede volver a su pequeño almacenamiento. y crear un prototipo en la base de datos SQLite.

DDL de la tabla.

CREATE TABLE UTL_JOB_STATUS (
/* Tabla para registrar el log de ejecución del trabajo. Es importante que el trabajo tenga los pasos ETL_START y ETL_END o ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Identificador de sesión. Único para cada ejecución del trabajo */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Fecha y hora */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Fecha */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Nombre del trabajo como JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ..., ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Descripción de la tarea o mensaje de error */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

DDL de la vista/informe.

CREAR VISTA SI NO EXISTE UTL_JOB_STATUS_V
COMO 
/* Contenido: Registro de Ejecución del Paquete para los últimos 3 Meses. */
CON SRC COMO (
  SELECCIONAR LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	CASO CUANDO INSTR(JOB_NAME, 'FTP') ENTONCES 'TRANSFERENCIA' /* transferencia de archivos */
	     CUANDO INSTR(JOB_NAME, 'STG') ENTONCES 'ETAPA' /* etapa */
	     CUANDO INSTR(JOB_NAME, 'CLS') ENTONCES 'LIMPIEZA' /* limpieza */
	     CUANDO INSTR(JOB_NAME, 'DIM') ENTONCES 'DIMENSIÓN' /* dimensión */
	     CUANDO INSTR(JOB_NAME, 'FCT') ENTONCES 'HECHO' /* hecho */
		 CUANDO INSTR(JOB_NAME, 'ETL') ENTONCES 'ETAPA-MART' /* data mart */
	     CUANDO INSTR(JOB_NAME, 'RPT') ENTONCES 'INFORME' /* informe */
	     OTRA 'N/A' FIN COMO CAPA,
	CASO CUANDO INSTR(JOB_NAME, 'ACCESS') ENTONCES 'REGISTRO DE ACCESO' /* fuente */
	     CUANDO INSTR(JOB_NAME, 'MASTER') ENTONCES 'DATOS MAESTROS' /* fuente */
	     CUANDO INSTR(JOB_NAME, 'AD-HOC') ENTONCES 'AD-HOC' /* fuente */
	     OTRA 'N/A' FIN COMO FUENTE,
    JOB_NAME,
    STEP_NAME,
    CASO CUANDO STEP_NAME='ETL_START' ENTONCES 1 OTRA 0 FIN COMO START_FLAG,
    CASO CUANDO STEP_NAME='ETL_END' ENTONCES 1 OTRA 0 FIN COMO END_FLAG,
    CASO CUANDO STEP_NAME='ETL_ERROR' ENTONCES 1 OTRA 0 FIN COMO ERROR_FLAG,
    STEP_NAME || ' : ' || STEP_DESCR COMO STEP_LOG,
	SUBSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) COMO AFFECTED_ROWS
  DE UTL_JOB_STATUS
  DONDE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
SELECCIONAR JB.SID,
  JB.MIN_LOG_DT COMO START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) COMO LOG_DT,
  JB.SOURCE,
  JB.LAYER,
  JB.JOB_NAME,
  CASO
  CUANDO JB.ERROR_FLAG = 1 ENTONCES 'ERROR'
  CUANDO JB.ERROR_FLAG = 0 Y JB.END_FLAG = 0 Y strftime('%s','ahora') - JB.MIN_LOG_DT > 0.5*60*60 ENTONCES 'EN ESPERA' /* media hora */
  CUANDO JB.ERROR_FLAG = 0 Y JB.END_FLAG = 0 ENTONCES 'EN EJECUCIÓN'
  OTRA 'OK'
  FIN COMO ESTADO,
  ERR.STEP_LOG     COMO STEP_LOG,
  JB.CNT           COMO STEP_CNT,
  JB.AFFECTED_ROWS COMO AFFECTED_ROWS,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) COMO JOB_START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) COMO JOB_END_DT,
  JB.MAX_LOG_DT - JB.MIN_LOG_DT COMO JOB_DURATION_SEC
DE
  ( SELECCIONAR SID, SOURCE, LAYER, JOB_NAME,
           MAX(UTL_JOB_STATUS_ID) COMO UTL_JOB_STATUS_ID,
           MAX(START_FLAG)       COMO START_FLAG,
           MAX(END_FLAG)         COMO END_FLAG,
           MAX(ERROR_FLAG)       COMO ERROR_FLAG,
           MIN(LOG_DT)           COMO MIN_LOG_DT,
           MAX(LOG_DT)           COMO MAX_LOG_DT,
           SUM(1)                COMO CNT,
           SUM(IFNULL(AFFECTED_ROWS, 0)) COMO AFFECTED_ROWS
    DE SRC
    GROUP BY SID, SOURCE, LAYER, JOB_NAME
  ) JB,
  ( SELECCIONAR UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG
    DE SRC
    DONDE 1 = 1
  ) ERR
DONDE 1 = 1
  Y JB.SID = ERR.SID
  Y JB.JOB_NAME = ERR.JOB_NAME
  Y JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
ORDER BY JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;

Verificación SQL de la posibilidad de obtener un nuevo número de sesión

SELECCIONAR SUMA (
  CASO CUANDO start_job.JOB_NAME NO ES NULO Y end_job.JOB_NAME ES NULO /* trabajo existente finalizado */
	    Y NO ( 'y' = 'n' ) /* reinicio forzado PARÁMETRO */
       ENTONCES 1 OTRA 0
  FIN ) COMO IS_RUNNING
  DE
    ( SELECCIONAR 1 COMO dummy DE UTL_JOB_STATUS DONDE sid = -1) d_job
  UNIR EXTERNO A LA IZQUIERDA
    ( SELECCIONAR JOB_NAME, SID, 1 COMO dummy
      DE UTL_JOB_STATUS
      DONDE JOB_NAME = 'RPT_ACCESS_LOG' /* nombre del trabajo PARÁMETRO */
	    Y STEP_NAME = 'ETL_START'
      GROUP BY JOB_NAME, SID
    ) start_job /* inicios */
  EN d_job.dummy = start_job.dummy
  UNIR EXTERNO A LA IZQUIERDA
    ( SELECCIONAR JOB_NAME, SID
      DE UTL_JOB_STATUS
      DONDE JOB_NAME = 'RPT_ACCESS_LOG'  /* nombre del trabajo PARÁMETRO */
	    Y STEP_NAME en ('ETL_END', 'ETL_ERROR') /* estado de parada */
      GROUP BY JOB_NAME, SID
    ) end_job /* finales */
  EN start_job.JOB_NAME = end_job.JOB_NAME
     Y start_job.SID = end_job.SID

Características de la tabla:

El inicio y el final del proceso de tratamiento de datos deben ir acompañados de los pasos ETL_START y ETL_END
En caso de error, debe crearse un paso ETL_ERROR con su descripción
La cantidad de datos procesados debe resaltarse, por ejemplo, con asteriscos
Simultáneamente, se puede iniciar el mismo procedimiento con el parámetro force_restart=y; sin él, el número de sesión se asigna solo al procedimiento que ha finalizado
En modo normal, no se puede ejecutar en paralelo el mismo procedimiento de tratamiento de datos

Las operaciones necesarias para trabajar con la tabla son las siguientes:

Obtención del número de sesión del procedimiento ETL que se está ejecutando
Inserción de un registro de log en la tabla
Obteniendo el último registro exitoso del procedimiento ETL

En bases de datos como Oracle o Postgres, estas operaciones se pueden realizar mediante funciones integradas. Para sqlite, se requiere un mecanismo externo y en este caso está prototipado en PHP.

Salida

Así, los mensajes de error en las herramientas de tratamiento de datos juegan un papel mega-importante. Pero difíciles de considerar óptimos para una búsqueda rápida de la causa del problema. Cuando la cantidad de procedimientos se aproxima al centenar, el monitoreo de procesos se convierte en un proyecto complicado.

El artículo proporciona un ejemplo de una posible solución al problema en forma de prototipo. Todo el prototipo de un pequeño almacén está disponible en gitlab Utilidades ETL PHP para SQLite.

Fuente: habr.com