# Oracle prometheus metrics

## Extract date and #operations from MMINVENTORY_PRO schema

SELECT EXTRACT(DATE FROM timestamp) AS DATE, value as OperationCount,  
CASE  
    WHEN metricname ='oracledb_table_modifications_mminventory_inserts' THEN 'INSERTS'
    WHEN metricname ='oracledb_table_modifications_mminventory_deletes' THEN 'DELETES'
    WHEN metricname ='oracledb_table_modifications_mminventory_updates' THEN 'UPDATES'
	else "OTHER"
    END
AS OperationType
FROM `mm-engineering-metrics-prod.oracle_metrics.prometheus`
WHERE metricname like "%mminventory%"

## Current Value and last value

SELECT timestamp, metricname, value, lag(value) over(order by timestamp) as valor_anterior, value - LAG(value) OVER (ORDER BY timestamp) as OPERATIONS
FROM `mm-engineering-metrics-prod.oracle_metrics.prometheus`
WHERE metricname='oracledb_table_modifications_mminventory_updates'
AND timestamp BETWEEN PARSE_TIMESTAMP("%d-%m-%Y %H:%M:%S", '16-03-2021 00:00:00') AND PARSE_TIMESTAMP("%d-%m-%Y %H:%M:%S", '16-03-2021 01:00:00')
order by timestamp


da valores negativos. por qué?
1. una metrica se ejecuta cada 15 min.
2. puede que no cada 15 min se saquen los valores de todas las tablas (fixed)
3. puede estar calculando contra un registro que no es exactamente igual.
5. cada registro es una tabla, pero no tiene un identificador como tal (need to be fixed)- ETL?
6. los contadores se ponen a 0 cuando se realiza un analyze de la tabla. Este analyze no se realiza bajo ningún patrón. El valor puede ser negativo a partir de ese momento.

## Extract TABLE_OWNER y TABLE_NAME

SELECT REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) as table_owner, REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) as table_name, value, timestamp
FROM `mm-engineering-metrics-prod.oracle_metrics.prometheus`
WHERE metricname like "%global%"

## + Add Operation type

SELECT REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) as table_owner, REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) as table_name, value, timestamp,
CASE  
    WHEN metricname ='oracledb_global_table_modifications_inserts' THEN 'INSERTS'
    WHEN metricname ='oracledb_global_table_modifications_deletes' THEN 'DELETES'
    WHEN metricname ='oracledb_global_table_modifications_updates' THEN 'UPDATES'
    WHEN metricname ='oracledb_global_table_modifications_num_rows' THEN 'NUM_ROWS'
	else "OTHER"
END
AS OperationType
FROM `mm-engineering-metrics-prod.oracle_metrics.prometheus`
WHERE metricname like 'oracledb_global_table_modifications%'
order by timestamp

## Create tables to insert data modeled

EXECUTE IMMEDIATE
 "CREATE TEMP TABLE MetricsTable (table_owner STRING, table_name STRING, value FLOAT64, timestamp TIMESTAMP, OperationType STRING)";

INSERT INTO MetricsTable(
SELECT REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) as table_owner, REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) as table_name, value, timestamp,
CASE  
    WHEN metricname ='oracledb_global_table_modifications_inserts' THEN 'INSERTS'
    WHEN metricname ='oracledb_global_table_modifications_deletes' THEN 'DELETES'
    WHEN metricname ='oracledb_global_table_modifications_updates' THEN 'UPDATES'
    WHEN metricname ='oracledb_global_table_modifications_num_rows' THEN 'NUM_ROWS'
	else "OTHER"
END
AS OperationType
FROM `mm-engineering-metrics-prod.oracle_metrics.prometheus`
WHERE metricname like 'oracledb_global_table_modifications_%'
AND value <>0
order by OperationType, table_owner, table_name, timestamp
);
EXECUTE IMMEDIATE
 "CREATE TEMP TABLE MetricsTable2 (table_owner STRING, table_name STRING, OperationType STRING, timestamp TIMESTAMP, value FLOAT64, last_value FLOAT64, diferencia FLOAT64)";
INSERT INTO MetricsTable2(
SELECT  TABLE_OWNER, TABLE_NAME, OperationType, timestamp, value, lag(value) over(order by  OperationType, table_owner, table_name, timestamp) as last_value, value - (lag(value) over(order by  OperationType, table_owner, table_name, timestamp)) as Diferencia
FROM MetricsTable
ORDER BY OperationType, table_name, timestamp
);
SELECT table_owner, table_name, OperationType, timestamp, value, last_value, if (diferencia <0, value, diferencia)
FROM MetricsTable2
WHERE OperationType <> 'NUM_ROWS'
AND table_name='MM_CDRSF3'
AND table_owner='MAS_DATA'
ORDER BY OperationType, timestamp;

## V1:

EXECUTE IMMEDIATE
  "CREATE TEMP TABLE MetricsTable (table_owner STRING, table_name STRING, current_value FLOAT64 , timestamp TIMESTAMP, OperationType STRING)";
INSERT INTO
  MetricsTable(
  SELECT
    REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) AS table_owner,
    REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) AS table_name,
    value,
    timestamp,
    CASE
      WHEN metricname ='oracledb_global_table_modifications_inserts' THEN 'INSERTS'
      WHEN metricname ='oracledb_global_table_modifications_deletes' THEN 'DELETES'
      WHEN metricname ='oracledb_global_table_modifications_updates' THEN 'UPDATES'
      WHEN metricname ='oracledb_global_table_modifications_num_rows' THEN 'NUM_ROWS'
    ELSE
    "OTHER"
  END
    AS OperationType
  FROM
    `mm-engineering-metrics-prod.oracle_metrics.prometheus`
  WHERE
    metricname LIKE 'oracledb_global_table_modifications_%'
    AND value <>0
  ORDER BY
    OperationType,
    table_owner,
    table_name,
    timestamp );
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE MetricsTable2 (table_owner STRING, table_name STRING, OperationType STRING, timestamp TIMESTAMP, current_value FLOAT64, last_value FLOAT64, changes FLOAT64)";
INSERT INTO
  MetricsTable2(
  SELECT
    TABLE_OWNER,
    TABLE_NAME,
    OperationType,
    timestamp,
    current_value,
    LAG(current_value) OVER(ORDER BY OperationType, table_owner, table_name, timestamp) AS last_value,
    current_value - (LAG(current_value) OVER(ORDER BY OperationType, table_owner, table_name, timestamp)) AS changes
  FROM
    MetricsTable
  ORDER BY
    OperationType,
    table_name,
    timestamp );
SELECT
  table_owner,
  table_name,
  OperationType,
  timestamp,
  current_value,
  last_value,
IF
  (changes <0,
    current_value,
    changes) AS changes
FROM
  MetricsTable2
WHERE
  OperationType <> 'NUM_ROWS'
  AND table_owner='MMINVENTORY_PRO'
ORDER BY
  OperationType,
  timestamp;

## V2 Iterate over owner

DECLARE i INT64 DEFAULT 0;
DECLARE start_date TIMESTAMP DEFAULT '2021-01-01';
DECLARE owner_name STRING;
DECLARE temp_table STRING DEFAULT 'MetricTable';
DECLARE owner ARRAY<STRING> DEFAULT [
        'RATOR',
        'WO_DATA',
        'APOLLO_PROP',
        'NEBAREQUEST_PRO',
        'MM_MIGRATIONS',
        'INCIDENT',
        'NEBAFAULT_PRO',
        'MMINVENTORY_PRO',
        'GESCAL_PRO',
        'MM_CO_MIGRATION_PRO',
        'MAS_DATA',
        'SCAI',
        'NEBAPPAI_PRO',
        'FIELDORDERS',
        'MM_CHVEL',
        'MM_MIG_NEBA_VULA',
        'ECUEVAS'
];

SET i=0;
WHILE i<ARRAY_LENGTH(owner) DO
--WHILE i = 0 DO
    SET owner_name =owner[OFFSET(i)];
    SET temp_table= temp_table||i;
    -- DEFINO vairable de fecha para almacenar el último dato insertado.
    
    SET start_date=(SELECT MAX(timestamp) from `mm-engineering-metrics-prod.oracle_metrics.oracle_metrics_data` WHERE table_owner=owner_name);

    -- Creo tabla termporal para extraer datos de prometheus y convertirlos en tablas. Solo saco datos cuando el 'values' es diferente de 0.
    EXECUTE IMMEDIATE
    "CREATE TEMP TABLE MetricTable1 (table_owner STRING, table_name STRING, timestamp TIMESTAMP, current_value FLOAT64 ,  operation_type STRING)";
    INSERT INTO
    MetricTable1(
    SELECT
        REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) AS table_owner,
        REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) AS table_name,
        TIMESTAMP(REGEXP_EXTRACT(tags, '"timestamp"\\:"([0-9-]+ [0-9:]+)(?:..........)"',1)) AS metric_date,  
        value,
        CASE
        WHEN metricname ='oracledb_all_table_modifications_inserts' THEN 'INSERTS'
        WHEN metricname ='oracledb_all_table_modifications_deletes' THEN 'DELETES'
        WHEN metricname ='oracledb_all_table_modifications_updates' THEN 'UPDATES'
        ELSE
        "OTHER"
    END
        AS operation_type
    FROM
        `mm-engineering-metrics-prod.oracle_metrics.prometheus`
    WHERE
        metricname LIKE 'oracledb_all_table_modifications_%'
        AND REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) = owner_name
        AND value <> 0 -- valor diferente de 0
        AND tags not like '%BIN$%' -- no quiero las tablas de la papelera
       -- AND TIMESTAMP(REGEXP_EXTRACT(tags, '"timestamp"\\:"([0-9-]+ [0-9:]+)(?:..........)"',1)) > start_date -- solo quiero datos mayores que el último que se insertó
    ORDER BY
        operation_type,
        table_owner,
        table_name,
        metric_date );
    -- Select temp table content
    SELECT *
    FROM MetricTable1;
  
    -- Creo tabla temporal para ordenar los datos y calcular último valor y diferncia.
    EXECUTE IMMEDIATE
      "CREATE TEMP TABLE MetricsTable2 (table_owner STRING, table_name STRING, operation_type STRING, timestamp TIMESTAMP, current_value FLOAT64, last_value FLOAT64, changes FLOAT64)";
    INSERT INTO
      MetricsTable2(
      SELECT
        table_owner,
        table_name,
        operation_type,
        timestamp,
        current_value,
        LAG(current_value) OVER(ORDER BY operation_type, table_owner, table_name, timestamp) AS last_value,
        current_value - (LAG(current_value) OVER(ORDER BY operation_type, table_owner, table_name, timestamp)) AS changes
      FROM
        MetricTable1
      ORDER BY
        operation_type,
        table_name,
        timestamp );
    CREATE TABLE IF NOT EXISTS  `mm-engineering-metrics-prod.oracle_metrics.oracle_metrics_data_prueba`
    (
        table_owner STRING,
        table_name STRING,
        operation_type STRING,
        timestamp TIMESTAMP,
        current_value FLOAT64,
        last_value FLOAT64,
        changes FLOAT64
        
    )
    PARTITION BY DATE(timestamp)
    CLUSTER BY table_owner, table_name, operation_type
    OPTIONS(
      description="Metricas Oracle - Cambios realizados en tablas"
    );

    INSERT INTO `mm-engineering-metrics-prod.oracle_metrics.oracle_metrics_data_prueba`
    (SELECT
      table_owner,
      table_name,
      operation_type,
      timestamp,
      current_value,
      last_value,
    IF
      (changes <0,
        current_value,
        changes) AS changes
    FROM
      MetricsTable2
    ORDER BY
      operation_type,
      timestamp);

  set i=i+1;
  END WHILE;

## V3 - Final

DECLARE i INT64 DEFAULT 0;
DECLARE control string DEFAULT 'Comienzo';
DECLARE metric_last_date TIMESTAMP DEFAULT '2021-01-01';
DECLARE owner_name STRING;
DECLARE owner ARRAY<STRING> DEFAULT [
        'RATOR',
        'WO_DATA',
        'APOLLO_PROP',
        'NEBAREQUEST_PRO',
        'MM_MIGRATIONS',
        'INCIDENT',
        'NEBAFAULT_PRO',
        'MMINVENTORY_PRO',
        'GESCAL_PRO',
        'MM_CO_MIGRATION_PRO',
        'MAS_DATA',
        'SCAI',
        'NEBAPPAI_PRO',
        'FIELDORDERS',
        'MM_CHVEL',
        'MM_MIG_NEBA_VULA',
        'ECUEVAS'
];
-- Esta es la tabla que contendrá los datos mostrados en DataStudio
CREATE TABLE IF NOT EXISTS  `mm-engineering-metrics-prod.oracle_metrics.oracle_metrics_data_prueba`
    (
        table_owner STRING,
        table_name STRING,
        operation_type STRING,
        metric_date TIMESTAMP,
        current_value FLOAT64,
        last_value FLOAT64,
        changes FLOAT64
        
    )
    PARTITION BY DATE(metric_date)
    CLUSTER BY table_owner, table_name, operation_type
    OPTIONS(
      description="Metricas Oracle - Cambios realizados en tablas"
    );

-- Creo tabla para cargar todos los datos de prometheus con formato tabla
CREATE TABLE IF NOT EXISTS `mm-engineering-metrics-prod.oracle_metrics.prometheus_load` 
  (
      table_owner STRING, 
      table_name STRING, 
      metric_date TIMESTAMP, 
      current_value FLOAT64 ,  
      operation_type STRING
   )
    PARTITION BY DATE(metric_date)
    CLUSTER BY table_owner, table_name, operation_type
    OPTIONS(
      description="Prometheus data load"
    );




SET i=0;

WHILE i<ARRAY_LENGTH(owner) DO
    
    -- DEFINO vairable de fecha para almacenar el último dato insertado.
    SET metric_last_date=(SELECT MAX(metric_date) from `mm-engineering-metrics-prod.oracle_metrics.oracle_metrics_data_prueba` WHERE table_owner=owner_name);

    IF metric_last_date IS NULL 
    THEN set metric_last_date=timestamp('2001-01-01'); 
    END IF;

    SET owner_name =owner[OFFSET(i)];
  
   -- Cargo la tabla base

    INSERT INTO `mm-engineering-metrics-prod.oracle_metrics.prometheus_load`
    (
        SELECT distinct
            REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) AS table_owner,
            REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) AS table_name,
            TIMESTAMP(REGEXP_EXTRACT(tags, '"timestamp"\\:"([0-9-]+ [0-9:]+)(?:..........)"',1)) AS metric_date,  
            value,
            CASE
            WHEN metricname ='oracledb_all_table_modifications_inserts' THEN 'INSERTS'
            WHEN metricname ='oracledb_all_table_modifications_deletes' THEN 'DELETES'
            WHEN metricname ='oracledb_all_table_modifications_updates' THEN 'UPDATES'
            ELSE
            "OTHER"
            END AS operation_type
        FROM
        `mm-engineering-metrics-prod.oracle_metrics.prometheus`
        WHERE
            metricname LIKE 'oracledb_all_table_modifications_%'
            AND ( REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1)) = owner_name
            AND (TIMESTAMP(REGEXP_EXTRACT(tags, '"timestamp"\\:"([0-9-]+ [0-9:]+)(?:..........)"',1))) > metric_last_date -- solo quiero datos mayores que el último que se insertó en la tabla promeetheus
        ORDER BY
            operation_type,
            table_owner,
            table_name,
            metric_date 
    );
    -- Creo tabla temporal para los datos de prometheus de esta ejecución y la cargo
    EXECUTE IMMEDIATE
      "CREATE OR REPLACE TEMP TABLE PrometheusLoadTemp (table_owner STRING, table_name STRING, metric_date TIMESTAMP, current_value FLOAT64, operation_type STRING)";
    INSERT INTO PrometheusLoadTemp
    (SELECT distinct
        REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1) AS table_owner,
        REGEXP_EXTRACT(tags, '"table_name"\\:"([A-Z_0-9-]+)"',1) AS table_name,
        TIMESTAMP(REGEXP_EXTRACT(tags, '"timestamp"\\:"([0-9-]+ [0-9:]+)(?:..........)"',1)) AS metric_date,  
        value,
        CASE
        WHEN metricname ='oracledb_all_table_modifications_inserts' THEN 'INSERTS'
        WHEN metricname ='oracledb_all_table_modifications_deletes' THEN 'DELETES'
        WHEN metricname ='oracledb_all_table_modifications_updates' THEN 'UPDATES'
        ELSE
        "OTHER"
        END AS operation_type
     FROM
        `mm-engineering-metrics-prod.oracle_metrics.prometheus`
     WHERE
        metricname LIKE 'oracledb_all_table_modifications_%'
        AND ( REGEXP_EXTRACT(tags, '"table_owner"\\:"([A-Z_0-9-]+)"',1)) = owner_name
        AND (TIMESTAMP(REGEXP_EXTRACT(tags, '"timestamp"\\:"([0-9-]+ [0-9:]+)(?:..........)"',1))) > metric_last_date -- solo quiero datos mayores que el último que se insertó en la tabla promeetheus
     ORDER BY
        operation_type,
        table_owner,
        table_name,
        metric_date 
        );
    -- Creo tabla temporal para ordenar los datos y calcular último valor y diferncia.
    EXECUTE IMMEDIATE
      "CREATE OR REPLACE TEMP TABLE MetricsTableTemp (table_owner STRING, table_name STRING, operation_type STRING, metric_date TIMESTAMP, current_value FLOAT64, last_value FLOAT64, changes FLOAT64)";
    INSERT INTO
      MetricsTableTemp(
      SELECT
        table_owner,
        table_name,
        operation_type,
        metric_date,
        current_value,
        LAG(current_value) OVER(ORDER BY operation_type, table_owner, table_name, metric_date) AS last_value,
        current_value - (LAG(current_value) OVER(ORDER BY operation_type, table_owner, table_name, metric_date)) AS changes
      FROM
        PrometheusLoadTemp
      WHERE table_owner=owner_name
      ORDER BY
        operation_type,
        table_name,
        metric_date
         );
    
    -- Creo tabla temporal para guardar todos los datos que f se insertarán en la tabla final que es fija.
    EXECUTE IMMEDIATE
      "CREATE TEMP TABLE IF NOT EXISTS MetricFinalTemp (table_owner STRING, table_name STRING, operation_type STRING, metric_date TIMESTAMP, current_value FLOAT64, last_value FLOAT64, changes FLOAT64)";
    
    INSERT INTO MetricFinalTemp
    (SELECT
      table_owner,
      table_name,
      operation_type,
      metric_date,
      current_value,
      last_value,
    IF
      (changes <0,
        current_value,
        changes) AS changes
    FROM
      MetricsTableTemp
    ORDER BY
      operation_type,
      metric_date);
    

  SET i=i+1;
  END WHILE;

  -- Inserto los dataos ya tratados en la tabla final.
  
  INSERT INTO `mm-engineering-metrics-prod.oracle_metrics.oracle_metrics_data_prueba`
    (SELECT *
      FROM MetricFinalTemp
    );