Lab 68 — Redshift quando o BI dói

-- consulta.sql — a mesma pergunta do widget "receita por loja e categoria";
-- na arquitetura mínima roda contra o JOIN completo, na de produção contra a view.

-- Arquitetura mínima: JOIN recalculado a cada clique
SELECT l.nome_loja, p.category AS categoria, SUM(p.total_amount) AS receita
FROM pedidos_local p          -- tabela local, DISTSTYLE AUTO, sem SORTKEY explícita
JOIN lojas l ON l.store_id = p.store_id
WHERE p.order_date = CURRENT_DATE
GROUP BY l.nome_loja, p.category
ORDER BY receita DESC;

-- Arquitetura de produção: lê o agregado pronto
SELECT nome_loja, categoria, receita
FROM mv_vendas_dia            -- materialized view, já agregada por loja/categoria/dia
WHERE dia = CURRENT_DATE
ORDER BY receita DESC;

XN Merge  (cost=1000000234.56..1000000234.60 rows=16 width=48)
  ->  XN Sort  (cost=1000000234.56..1000000234.58 rows=16 width=48)
        Sort Key: sum(p.total_amount) DESC
        ->  XN HashAggregate  (cost=1000000234.10..1000000234.20 rows=16 width=48)
              ->  XN Hash Join DS_BCAST_INNER  (cost=25.00..1000000198.40 rows=900000 width=40)
                    Hash Cond: ("outer".store_id = "inner".store_id)
                    ->  XN Seq Scan on pedidos_local p  (cost=0.00..180000.00 rows=900000 width=24)
                    ->  XN Hash  (cost=18.00..18.00 rows=900 width=20)
                          ->  XN Seq Scan on lojas l  (cost=0.00..18.00 rows=900 width=20)

-- DS_BCAST_INNER: a tabela "lojas" (900 linhas) é copiada inteira para TODOS os nós
-- a cada execução, porque nenhuma DISTKEY garante que ela já esteja lá. Pequena hoje,
-- mas é rede e CPU gastos numa consulta que deveria custar quase zero de coordenação.

-- schema.sql — a tabela fato e a dimensão, com DISTKEY e SORTKEY escolhidos
-- a partir do padrão de uso real do painel, não de palpite.

-- Fato: distribuído por store_id, porque é a coluna do JOIN mais comum
-- (painel agrupa por loja em praticamente todo widget).
CREATE TABLE pedidos_local (
  order_id      BIGINT      NOT NULL,
  store_id      INTEGER     NOT NULL,
  category      VARCHAR(64) NOT NULL,
  order_date    DATE        NOT NULL,
  total_amount  DECIMAL(12,2) NOT NULL
)
DISTSTYLE KEY
DISTKEY (store_id)
COMPOUND SORTKEY (order_date, store_id);
-- COMPOUND porque 90% das consultas do painel filtram por order_date PRIMEIRO,
-- e store_id em seguida (drill-down por loja dentro de um dia). Interleaved
-- serviria melhor se o painel filtrasse por qualquer coluna isoladamente com
-- frequência parecida — não é o caso medido aqui.

-- Dimensão pequena: replicada inteira em cada nó, então o JOIN nunca precisa
-- buscar a linha da loja em outro nó pela rede.
CREATE TABLE lojas (
  store_id   INTEGER     NOT NULL,
  nome_loja  VARCHAR(120) NOT NULL,
  regiao     VARCHAR(60)
)
DISTSTYLE ALL;
-- 900 linhas: replicar em cada nó custa pouco armazenamento e elimina a
-- redistribuição inteira do lado da dimensão em todo JOIN do painel.

-- mv_vendas_dia.sql — pré-agrega o widget mais consultado do painel.
-- AUTO REFRESH: o Redshift decide sozinho QUANDO atualizar, com base em
-- mudança no dado-fonte — não existe scheduler externo como no CTAS do L66.

CREATE MATERIALIZED VIEW mv_vendas_dia
AUTO REFRESH YES
AS
SELECT
  p.order_date       AS dia,
  p.store_id,
  l.nome_loja,
  p.category         AS categoria,
  SUM(p.total_amount) AS receita,
  COUNT(*)            AS pedidos
FROM pedidos_local p
JOIN lojas l ON l.store_id = p.store_id
WHERE p.order_date >= DATEADD(day, -90, CURRENT_DATE)
-- 90 dias cobre o horizonte que o painel de fato consulta; além disso, o
-- drill-down cai para o schema externo via Spectrum (ver spectrum.sql).
GROUP BY p.order_date, p.store_id, l.nome_loja, p.category;

-- spectrum.sql — schema externo para o caso raro de drill-down além dos 90 dias
-- que a view local cobre. Lê o MESMO pedidos_parquet que o Athena consultava no L66.

CREATE EXTERNAL SCHEMA lake_spectrum
FROM DATA CATALOG
DATABASE 'lake_cadencia'
IAM_ROLE 'arn:aws:iam::<conta>:role/redshift-spectrum-lake'
CREATE EXTERNAL DATABASE IF NOT EXISTS;
-- Nenhum CREATE TABLE aqui: o Glue Data Catalog do L65 já descreve
-- pedidos_parquet, e o Spectrum lê essa definição diretamente.

-- Consulta de drill-down: histórico completo, sem tocar a tabela local
SELECT category AS categoria, SUM(total_amount) AS receita
FROM lake_spectrum.pedidos_parquet
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
GROUP BY category;
-- Este caminho NÃO usa a DISTKEY nem a SORTKEY do cluster — o dado externo
-- não está distribuído nem ordenado pelo Redshift, só filtrado e projetado.

# workgroup.tf — o workgroup de produção: fila dedicada, Multi-AZ, e o IAM
# role que autoriza o Spectrum a ler o mesmo lake do L66.

resource "aws_redshiftserverless_namespace" "cadencia" {
  namespace_name      = "${var.projeto}-dw"
  admin_user_password = null # gerenciado via Secrets Manager, nunca aqui
  manage_admin_password = true
  iam_roles           = [aws_iam_role.spectrum.arn]
  default_iam_role_arn = aws_iam_role.spectrum.arn
}

resource "aws_redshiftserverless_workgroup" "dashboard" {
  namespace_name = aws_redshiftserverless_namespace.cadencia.namespace_name
  workgroup_name = "${var.projeto}-dashboard"
  base_capacity  = 32 # RPU — dimensionado pelo pior caso medido nas provas, com folga

  # Multi-AZ: falha de computação numa zona não derruba o painel. Atributo
  # dedicado do workgroup Serverless — o dado em si já é durável independente
  # disso, é a DISPONIBILIDADE do motor de consulta que este campo protege.
  multi_az = true

  config_parameter {
    parameter_key   = "enable_user_activity_logging"
    parameter_value = "true"
  }
}

# IAM role que o Spectrum assume para ler o lake — restrito ao bucket e ao
# catálogo deste projeto, nunca a conta inteira.
data "aws_iam_policy_document" "spectrum" {
  statement {
    effect  = "Allow"
    actions = ["s3:GetObject", "s3:ListBucket"]
    resources = [
      "arn:aws:s3:::${var.bucket_lake_ouro}",
      "arn:aws:s3:::${var.bucket_lake_ouro}/pedidos_parquet/*",
    ]
  }
  statement {
    effect  = "Allow"
    actions = ["glue:GetTable", "glue:GetDatabase", "glue:GetPartitions"]
    # Glue não aceita ARN de partição individual para esta ação — "*" aqui é o
    # caso em que o recurso específico NÃO é suportado, não um atalho de preguiça.
    resources = ["arn:aws:glue:${var.regiao}:${var.conta}:catalog",
                 "arn:aws:glue:${var.regiao}:${var.conta}:database/lake_cadencia",
                 "arn:aws:glue:${var.regiao}:${var.conta}:table/lake_cadencia/pedidos_parquet"]
  }
}

// Program.cs — backend do painel: pool via Npgsql (Redshift fala protocolo
// Postgres), credencial lida do Secrets Manager, /health e /ready separados.

using Amazon.SecretsManager;
using Amazon.SecretsManager.Model;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);

// A senha NUNCA vem de appsettings nem de variável de ambiente fixa — só do
// Secrets Manager, buscada uma vez na inicialização e recarregada por rotação.
var secretsClient = new AmazonSecretsManagerClient();
var segredo = await secretsClient.GetSecretValueAsync(new GetSecretValueRequest
{
    SecretId = Environment.GetEnvironmentVariable("SECRET_ID_REDSHIFT")!,
});
var credencial = System.Text.Json.JsonSerializer.Deserialize<CredencialRedshift>(segredo.SecretString)!;

var connString = new NpgsqlConnectionStringBuilder
{
    Host = Environment.GetEnvironmentVariable("REDSHIFT_ENDPOINT"),
    Port = 5439,
    Database = "cadencia_dw",
    Username = credencial.Usuario,
    Password = credencial.Senha,
    Pooling = true,
    MinPoolSize = 2,
    MaxPoolSize = 20, // teto explícito: nunca uma conexão nova por requisição
    Timeout = 5,
}.ToString();

builder.Services.AddSingleton(new NpgsqlDataSourceBuilder(connString).Build());

var app = builder.Build();

// /health responde sem tocar o Redshift — confirma só que o PROCESSO está vivo.
app.MapGet("/health", () => Results.Ok("ok"));

// /ready toca o banco de verdade — é o que o ALB usa para decidir se manda
// tráfego. Se o Redshift estiver oscilando, /ready falha e /health continua ok,
// então o processo não é reiniciado à toa por um problema que não é dele.
app.MapGet("/ready", async (NpgsqlDataSource ds) =>
{
    await using var cmd = ds.CreateCommand("SELECT 1");
    await cmd.ExecuteScalarAsync();
    return Results.Ok("ready");
});

app.MapGet("/api/vendas-dia", async (NpgsqlDataSource ds) =>
{
    // Lê a view pré-agregada — nunca o JOIN cru contra pedidos_local.
    await using var cmd = ds.CreateCommand(
        "SELECT nome_loja, categoria, receita FROM mv_vendas_dia " +
        "WHERE dia = CURRENT_DATE ORDER BY receita DESC LIMIT 10");
    var linhas = new List<object>();
    await using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
        linhas.Add(new { Loja = reader.GetString(0), Categoria = reader.GetString(1), Receita = reader.GetDecimal(2) });
    return Results.Ok(linhas);
});

app.Run();

record CredencialRedshift(string Usuario, string Senha);

PROJETO=cadencia; REGIAO=us-east-1; DB=cadencia_dw
ENDPOINT=$(aws redshift-serverless get-workgroup --workgroup-name "${PROJETO}-dashboard" \
  --region "$REGIAO" --query 'workgroup.endpoint.address' --output text)

# ── Prova 1: EXPLAIN antes da DISTKEY ─────────────────────────────────────
psql "host=$ENDPOINT port=5439 dbname=$DB" -c "
  EXPLAIN SELECT l.nome_loja, p.category, SUM(p.total_amount)
  FROM pedidos_local p JOIN lojas l ON l.store_id = p.store_id
  WHERE p.order_date = CURRENT_DATE GROUP BY l.nome_loja, p.category;"

# ── Prova 3: p95 do painel completo, 8 widgets, 20 execuções ──────────────
for i in $(seq 1 20); do
  /usr/bin/time -f "%e" psql "host=$ENDPOINT port=5439 dbname=$DB" -c \
    "SELECT nome_loja, categoria, receita FROM mv_vendas_dia WHERE dia = CURRENT_DATE;" \
    2>> tempos_widget1.txt
done
sort -n tempos_widget1.txt | awk 'BEGIN{c=0} {a[c++]=$1} END{print a[int(c*0.95)]}'

PROJETO=cadencia; REGIAO=us-east-1

# 1. Snapshot final ANTES de qualquer destroy — o namespace não guarda o dado
#    local em lugar nenhum além dele mesmo.
aws redshift-serverless create-snapshot --namespace-name "${PROJETO}-dw" \
  --snapshot-name "${PROJETO}-dw-final-$(date +%Y%m%d)" --region "$REGIAO"

# 2. Terraform destroy cuida do workgroup, do namespace e das roles GERENCIADAS
terraform destroy -auto-approve

# 3. A materialized view e a tabela local morrem junto com o namespace — não
#    precisam de remoção separada, diferente do CTAS do Athena no L66, que vive
#    fora do Terraform. Aqui, tudo que é do Redshift é do namespace.

# 4. O que o destroy NÃO leva: o lake em S3 continua intacto — é o mesmo bucket
#    do L66/L67, e este laboratório nunca o modifica, só lê via Spectrum.
aws secretsmanager delete-secret --secret-id "${PROJETO}/redshift/admin" \
  --recovery-window-in-days 7 --region "$REGIAO"