{"id":37701,"date":"2019-10-31T22:19:10","date_gmt":"2019-10-31T19:19:10","guid":{"rendered":"https:\/\/prohoster.info\/blog\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh\/"},"modified":"2019-10-31T22:19:10","modified_gmt":"2019-10-31T19:19:10","slug":"monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","status":"publish","type":"post","link":"https:\/\/prohoster.info\/pt\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"Monitoramento de processos ETL em um pequeno armazenamento de dados","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Muitos utilizam ferramentas especializadas para criar procedimentos de extra\u00e7\u00e3o, transforma\u00e7\u00e3o e carregamento de dados em bancos de dados relacionais. O processo de opera\u00e7\u00e3o das ferramentas \u00e9 registrado, e os erros s\u00e3o documentados.<\/p>\n

Em caso de erro, o log cont\u00e9m informa\u00e7\u00f5es sobre a falha da tarefa por parte da ferramenta e quais m\u00f3dulos (frequentemente \u00e9 o Java) onde pararam. Nas \u00faltimas linhas, \u00e9 poss\u00edvel encontrar o erro do banco de dados, como a viola\u00e7\u00e3o de uma chave \u00fanica da tabela.<\/p>\n

Para responder \u00e0 pergunta sobre qual o papel da informa\u00e7\u00e3o de erros do ETL, classifiquei todos os problemas que ocorreram nos \u00faltimos dois anos em um grande armaz\u00e9m. <\/p>\n

\"Monitoramento<\/a><\/noindex><\/p>\n

Os erros de banco de dados incluem problemas como falta de espa\u00e7o, desconex\u00e3o, sess\u00e3o travada, entre outros.<\/p>\n

Os erros l\u00f3gicos incluem problemas como viola\u00e7\u00e3o de chaves da tabela, objetos inv\u00e1lidos, falta de acesso a objetos, etc.
\nO agendador pode ser executado fora do hor\u00e1rio, pode travar, entre outros.<\/p>\n

Erros simples n\u00e3o requerem muito tempo para serem corrigidos. A maioria deles pode ser tratada automaticamente por um bom ETL.<\/p>\n

Erros complexos exigem a abertura e verifica\u00e7\u00e3o dos procedimentos de manipula\u00e7\u00e3o de dados, investiga\u00e7\u00e3o das fontes de dados. Muitas vezes levam \u00e0 necessidade de testagem de altera\u00e7\u00f5es e de implanta\u00e7\u00e3o.<\/p>\n

Assim, metade de todos os problemas est\u00e1 relacionada ao banco de dados. 48% de todos os erros s\u00e3o erros simples.
\nUm ter\u00e7o de todos os problemas est\u00e1 associado \u00e0 altera\u00e7\u00e3o da l\u00f3gica ou modelo do armaz\u00e9m, e mais da metade desses erros s\u00e3o complexos.<\/p>\n

E menos de um quarto de todos os problemas est\u00e1 relacionado ao agendador de tarefas, dos quais 18% s\u00e3o erros simples.<\/p>\n

No total, 22% de todos os erros ocorridos s\u00e3o complexos, e sua corre\u00e7\u00e3o requer a maior aten\u00e7\u00e3o e tempo. Eles acontecem aproximadamente uma vez por semana, enquanto os erros simples ocorrem quase todos os dias.<\/p>\n

\u00c9 evidente que o monitoramento dos processos ETL ser\u00e1 eficaz quando o log indicar com precis\u00e3o o local do erro e exigir o menor tempo poss\u00edvel para localizar a fonte do problema.<\/p>\n

Monitoramento Eficaz<\/h4>\n

\nO que eu gostaria de ver no processo de monitoramento do ETL?<\/p>\n

\"Monitoramento
\nIn\u00edcio em \u2014 quando come\u00e7ou a opera\u00e7\u00e3o,
\nFonte \u2014 fonte de dados,
\nCamada \u2014 qual n\u00edvel do armaz\u00e9m est\u00e1 sendo carregado,
\nNome do Trabalho ETL \u2014 procedimento de carregamento que consiste em muitos pequenos passos,
\nN\u00famero do Passo \u2014 n\u00famero do passo que est\u00e1 sendo executado,
\nLinhas Afetadas \u2014 quantos dados j\u00e1 foram processados,
\nDura\u00e7\u00e3o sec \u2014 quanto tempo est\u00e1 levando,
\nStatus \u2014 se tudo est\u00e1 bem ou n\u00e3o: OK, ERRO, EXECUTANDO, PARADO
\nMensagem \u2014 a \u00faltima mensagem de sucesso ou descri\u00e7\u00e3o do erro.<\/p>\n

Com base no status dos registros, \u00e9 poss\u00edvel enviar um e-mail para outros participantes. Se n\u00e3o houver erros, o e-mail n\u00e3o \u00e9 necess\u00e1rio.<\/p>\n

Dessa forma, em caso de erro, o local do incidente est\u00e1 claramente indicado.<\/p>\n

\u00c0s vezes ocorre que a pr\u00f3pria ferramenta de monitoramento n\u00e3o funciona. Nesse caso, h\u00e1 a possibilidade de chamar diretamente a visualiza\u00e7\u00e3o no banco de dados, com base na qual o relat\u00f3rio foi gerado.<\/p>\n

Tabela de monitoramento ETL<\/h4>\n

\nPara implementar o monitoramento de processos ETL, basta uma tabela e uma visualiza\u00e7\u00e3o.<\/p>\n

Para isso, voc\u00ea pode voltar ao seu pequeno reposit\u00f3rio<\/a><\/noindex> e criar um prot\u00f3tipo em um banco de dados sqlite.<\/p>\n

DDL da tabela<\/b><\/p>\n

CREATE TABLE UTL_JOB_STATUS (\n\n\/* Tabela para registro de log de execu\u00e7\u00e3o de tarefa. Importante que a tarefa tenha os passos ETL_START e ETL_END ou ETL_ERROR *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Identificador de Sess\u00e3o. \u00danico para cada execu\u00e7\u00e3o da tarefa *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Data e hora *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Data *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Nome da tarefa como JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ... , ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Descri\u00e7\u00e3o da tarefa ou mensagem de erro *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL da visualiza\u00e7\u00e3o\/relat\u00f3rio<\/b><\/p>\n
CRIE A VIS\u00c3O SE N\u00c3O EXISTIR UTL_JOB_STATUS_V\nCOM \n\/* Conte\u00fado: Registro de Execu\u00e7\u00e3o de Pacote dos \u00faltimos 3 Meses. *\/\nCOM SRC COMO (\n  SELECIONE LOG_D,\n    LOG_DT,\n    UTL_JOB_STATUS_ID,\n    SID,\n\tCASO QUANDO INSTR(JOB_NAME, 'FTP') ENT\u00c3O 'TRANSFER\u00caNCIA' \/* transfer\u00eancia de arquivo *\/\n\t     QUANDO INSTR(JOB_NAME, 'STG') ENT\u00c3O 'EST\u00c1GIO' \/* est\u00e1gio *\/\n\t     QUANDO INSTR(JOB_NAME, 'CLS') ENT\u00c3O 'LIMPEZA' \/* limpeza *\/\n\t     QUANDO INSTR(JOB_NAME, 'DIM') ENT\u00c3O 'DIMENS\u00c3O' \/* dimens\u00e3o *\/\n\t     QUANDO INSTR(JOB_NAME, 'FCT') ENT\u00c3O 'FATO' \/* fato *\/\n\t\t QUANDO INSTR(JOB_NAME, 'ETL') ENT\u00c3O 'EST\u00c1GIO-MART' \/* data mart *\/\n\t     QUANDO INSTR(JOB_NAME, 'RPT') ENT\u00c3O 'RELAT\u00d3RIO' \/* relat\u00f3rio *\/\n\t     OUTRO 'N\/A' END AS CAMADA,\n\tCASO QUANDO INSTR(JOB_NAME, 'ACCESS') ENT\u00c3O 'REGISTRO DE ACESSO' \/* fonte *\/\n\t     QUANDO INSTR(JOB_NAME, 'MASTER') ENT\u00c3O 'DADOS PRINCIPAIS' \/* fonte *\/\n\t     QUANDO INSTR(JOB_NAME, 'AD-HOC') ENT\u00c3O 'AD-HOC' \/* fonte *\/\n\t     OUTRO 'N\/A' END AS FONTE,\n    JOB_NAME,\n    STEP_NAME,\n    CASO QUANDO STEP_NAME='ETL_START' ENT\u00c3O 1 OUTRO 0 END AS INICIO_BANDEIRA,\n    CASO QUANDO STEP_NAME='ETL_END' ENT\u00c3O 1 OUTRO 0 END AS FIM_BANDEIRA,\n    CASO QUANDO STEP_NAME='ETL_ERROR' ENT\u00c3O 1 OUTRO 0 END AS ERRO_BANDEIRA,\n    STEP_NAME || ' : ' || STEP_DESCR AS REGISTRO_DE_PASSO,\n\tSUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) AS LINHAS_AFETADAS\n  DE UTL_JOB_STATUS\n  ONDE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')\n)\nSELECIONE JB.SID,\n  JB.MIN_LOG_DT AS DATA_INICIO,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,\n  JB.FONTE,\n  JB.CAMADA,\n  JB.JOB_NAME,\n  CASO\n  QUANDO JB.ERROR_FLAG = 1 ENT\u00c3O 'ERRO'\n  QUANDO JB.ERROR_FLAG = 0 E JB.END_FLAG = 0 E strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 ENT\u00c3O 'PARADO' \/* meia hora *\/\n  QUANDO JB.ERROR_FLAG = 0 E JB.END_FLAG = 0 ENT\u00c3O 'EM EXECU\u00c7\u00c3O'\n  OUTRO 'OK'\n  FIM COMO STATUS,\n  ERR.STEP_LOG     COMO REGISTRO_DE_PASSO,\n  JB.CNT           COMO CONTAGEM_DE_PASSOS,\n  JB.LINHAS_AFETADAS AS LINHAS_AFETADAS,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS DATA_INICIO_TRABALHO,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS DATA_FIM_TRABALHO,\n  JB.MAX_LOG_DT - JB.MIN_LOG_DT COMO DURACAO_TRABALHO_SEC\nDE\n  ( SELECIONE SID, FONTE, CAMADA, JOB_NAME,\n           MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,\n           MAX(INICIO_BANDEIRA)       AS INICIO_BANDEIRA,\n           MAX(FIM_BANDEIRA)         AS FIM_BANDEIRA,\n           MAX(ERRO_BANDEIRA)       AS ERRO_BANDEIRA,\n           MIN(LOG_DT)           AS MIN_LOG_DT,\n           MAX(LOG_DT)           AS MAX_LOG_DT,\n           SUM(1)                AS CNT,\n           SUM(IFNULL(LINHAS_AFETADAS, 0)) AS LINHAS_AFETADAS\n    DE SRC\n    GROUP BY SID, FONTE, CAMADA, JOB_NAME\n  ) JB,\n  ( SELECIONE UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG\n    DE SRC\n    ONDE 1 = 1\n  ) ERR\nONDE 1 = 1\n  E JB.SID = ERR.SID\n  E JB.JOB_NAME = ERR.JOB_NAME\n  E JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID\nORDENAR POR JB.MIN_LOG_DT DESC, JB.SID DESC, JB.FONTE;<\/code><\/pre>\n
\nVerifica\u00e7\u00e3o SQL da possibilidade de obter um novo n\u00famero de sess\u00e3o<\/b><\/p>\n
SELECIONE SOMA (\n  CASO QUANDO start_job.JOB_NAME \u00c9 N\u00c3O NULO E end_job.JOB_NAME \u00c9 NULO \/* trabalho existente finalizado *\/\n\t    E N\u00c3O ( 'y' = 'n' ) \/* PARAMETRO de rein\u00edcio for\u00e7ado *\/\n       ENT\u00c3O 1 OUTRO 0\n  FIM ) COMO EST\u00c1_EXECUTANDO\n  DE\n    ( SELECIONE 1 COMO dummy DE UTL_JOB_STATUS ONDE sid = -1) d_job\n  JUN\u00c7\u00c3O EXTERNA \u00c0 ESQUERDA\n    ( SELECIONE JOB_NAME, SID, 1 COMO dummy\n      DE UTL_JOB_STATUS\n      ONDE JOB_NAME = 'RPT_ACCESS_LOG' \/* PARAMETRO de nome do trabalho *\/\n\t    E STEP_NAME = 'ETL_START'\n      GROUP BY JOB_NAME, SID\n    ) start_job \/* come\u00e7a *\/\n  ON d_job.dummy = start_job.dummy\n  JUN\u00c7\u00c3O EXTERNA \u00c0 ESQUERDA\n    ( SELECIONE JOB_NAME, SID\n      DE UTL_JOB_STATUS\n      ONDE JOB_NAME = 'RPT_ACCESS_LOG'  \/* PARAMETRO de nome do trabalho *\/\n\t    E STEP_NAME IN ('ETL_END', 'ETL_ERROR') \/* status de parada *\/\n      GROUP BY JOB_NAME, SID\n    ) end_job \/* termina *\/\n  ON start_job.JOB_NAME = end_job.JOB_NAME\n     E start_job.SID = end_job.SID<\/code><\/pre>\n
\nCaracter\u00edsticas da tabela:<\/p>\n