ob-agent-stream/sql/metrics_table.sql

create database if not exists ob_metrics;
create table if not exists ob_metrics.public.service_metrics_15s
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float
);

SELECT create_hypertable('service_metrics_15s', by_range('timestamp'));

create table if not exists ob_metrics.public.service_instance_metrics_15s
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512)
);

SELECT create_hypertable('service_instance_metrics_15s', by_range('timestamp'));

create table if not exists ob_metrics.public.endpoint_metrics_15s
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512),
        endpoint varchar(512)
);

SELECT create_hypertable('endpoint_metrics_15s', by_range('timestamp'));


create table if not exists ob_metrics.public.service_metrics_5m
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float
);

SELECT create_hypertable('service_metrics_5m', by_range('timestamp'));

create table if not exists ob_metrics.public.service_instance_metrics_5m
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512)
);

SELECT create_hypertable('service_instance_metrics_5m', by_range('timestamp'));

create table if not exists ob_metrics.public.endpoint_metrics_5m
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512),
    endpoint varchar(512)
);

SELECT create_hypertable('endpoint_metrics_5m', by_range('timestamp'));



create table if not exists ob_metrics.public.service_metrics_20m
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float
);

SELECT create_hypertable('service_metrics_20m', by_range('timestamp'));

create table if not exists ob_metrics.public.service_instance_metrics_20m
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512)
);

SELECT create_hypertable('service_instance_metrics_20m', by_range('timestamp'));

create table if not exists ob_metrics.public.endpoint_metrics_20m
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512),
    endpoint varchar(512)
);

SELECT create_hypertable('endpoint_metrics_20m', by_range('timestamp'));

create table if not exists ob_metrics.public.service_metrics_1h
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float
);

SELECT create_hypertable('service_metrics_1h', by_range('timestamp'));

create table if not exists ob_metrics.public.service_instance_metrics_1h
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512)
);

SELECT create_hypertable('service_instance_metrics_1h', by_range('timestamp'));

create table if not exists ob_metrics.public.endpoint_metrics_1h
(
    timestamp timestamp,
    app_name varchar(256),
    service_name varchar(512),
    apdex float,
    http_code_300_count int,
    http_code_400_count int,
    http_code_500_count int,
    span_status_error_count int,
    exception_count int,
    max_latency bigint,
    min_latency bigint,
    avg_latency bigint,
    request_count bigint,
    rpm bigint,
    count_le_500 bigint,
    count_le_1000 bigint,
    count_le_5000 bigint,
    quantile_50 bigint,
    quantile_90 float,
    quantile_95 float,
    quantile_99 float,
    instanceID varchar(512),
    endpoint varchar(512)
);

SELECT create_hypertable('endpoint_metrics_1h', by_range('timestamp'));




SELECT add_retention_policy('service_metrics_15s', INTERVAL '10 days');
SELECT add_retention_policy('service_metrics_5m', INTERVAL '10 days');
SELECT add_retention_policy('service_metrics_20m', INTERVAL '10 days');
SELECT add_retention_policy('service_metrics_1h', INTERVAL '10 days');
SELECT add_retention_policy('service_instance_metrics_15s', INTERVAL '10 days');
SELECT add_retention_policy('service_instance_metrics_5m', INTERVAL '10 days');
SELECT add_retention_policy('service_instance_metrics_20m', INTERVAL '10 days');
SELECT add_retention_policy('service_instance_metrics_1h', INTERVAL '10 days');
SELECT add_retention_policy('endpoint_metrics_15s', INTERVAL '10 days');
SELECT add_retention_policy('endpoint_metrics_5m', INTERVAL '10 days');
SELECT add_retention_policy('endpoint_metrics_20m', INTERVAL '10 days');
SELECT add_retention_policy('endpoint_metrics_1h', INTERVAL '10 days');