Gerar SQL

Gerar SQL

Compila uma definição de audience criteria para SQL do BigQuery (GoogleSQL). Um mesmo motor atende duas entradas:

• POST /api/audience-criterias/generate-sql — compila uma definição enviada no corpo (ainda não persistida).
• GET /api/audience-criterias/:id/sql — compila um critério já salvo, referenciado por seu id.

Ambos retornam a mesma forma de resposta: IAudienceCriteriaSql, uma união discriminada pelo campo format. O format (parametrizado ou literal) é escolhido por requisição e governa tanto a forma do query quanto os campos da resposta.

Dentro de um projeto, o SQL de toda audiência que não seja a audiência raiz sai intersectado com a audiência raiz do projeto — veja Interseção com a audiência raiz.

Auth: Obrigatória — permissão audience-criteria:view-sql (ambos endpoints).

POST /api/audience-criterias/generate-sql

Compila uma definição enviada no corpo, sem persisti-la.

Auth: Obrigatória — permissão audience-criteria:view-sql

Corpo da requisição

Nível raiz (GenerateSqlDto): a definição do critério (CriteriaDefinitionDto) mais o campo format.

Definição do critério

É a mesma árvore que Criar recebe, sem os campos name/description. Está documentada aqui de forma autocontida.

queryBlocks[] — bloco de consulta

Cada elemento de queryBlocks (QueryBlockDto) é um statement SQL completo:

ColumnRefDto — referência a coluna

Usado em toda a árvore (select, filtros, group by, joins, agregados, janela temporal):

Toda coluna é sempre qualificada com sua table para evitar ambiguidade.

from — fonte de dados (FromDto)

Cada JoinDto:

Cada JoinPredicateDto é um par de colunas: { left: ColumnRefDto, right: ColumnRefDto }.

select — projeção (SelectDto)

Os items são discriminados por kind:

• Coluna — kind: "column":

  Campo	Tipo	Obrigatório	Descrição
  kind	"column"	Sim	Discriminador.
  column	ColumnRefDto	Sim	Coluna projetada.
  alias	string	Não	Deve casar com ^[A-Za-z_][A-Za-z0-9_]*$.

• Agregado — kind: "aggregate":

  Campo	Tipo	Obrigatório	Descrição
  kind	"aggregate"	Sim	Discriminador.
  aggregate	AggregateDto	Sim	Expressão de agregação.
  alias	string	Sim	Aqui o alias é obrigatório. Mesmo regex de identificador.

AggregateDto:

operand —o que é agregado— é discriminado por kind:

ArithmeticOperatorEnum: ADD | SUBTRACT | MULTIPLY | SAFE_DIVIDE (uma divisão que retorna NULL quando o denominador é zero, em vez de fazer a consulta falhar). As colunas de uma expressão aritmética precisam ser numéricas (INT64, NUMERIC, BIGNUMERIC, FLOAT64), e as expressões se aninham até 3 níveis de arithmetic, contando o externo.

where — árvore de predicados (WhereDto)

O where é uma árvore recursiva. O nó raiz é sempre um grupo, e suas folhas são de dois tipos:

Grupo — kind: "group"

Folha de coluna — kind: "predicate"

Compara uma coluna escalar qualificada por sua tabela.

{
  "kind": "predicate",
  "column": { "table": "user_properties", "column": "country" },
  "comparisonOperator": "IN",
  "value": ["CL", "MX"]
}

Folha de registro — kind: "recordPredicate"

Filtra por uma coluna RECORD REPEATED (um array de structs, como attributes). Significa que pelo menos um elemento do array satisfaz o sub-filtro.

{
  "kind": "recordPredicate",
  "column": { "table": "event_transactions", "column": "attributes" },
  "subWhere": {
    "kind": "group",
    "logicalOperator": "AND",
    "children": [
      { "kind": "predicate", "field": "key", "comparisonOperator": "EQUALS", "value": "canal" },
      { "kind": "predicate", "field": "value", "comparisonOperator": "EQUALS", "value": "web" }
    ]
  }
}

Quais colunas aceitam um filtro de registro é informado pelo catálogo: as que vêm com kind: "record".

Sub-árvore relativa

O subWhere é um grupo AND/OR cujas folhas são resolvidas contra os sub-campos que o registro declara.

Há uma única diferença em relação à árvore de nível superior, e ela é a origem mais comum de erros:

Folhas permitidas dentro de uma sub-árvore relativa:

• Folha escalar — { "kind": "predicate", "field": string, "comparisonOperator": …, "value"?: … }. O field deve ser um sub-campo escalar do registro.
• Registro aninhado — { "kind": "recordPredicate", "field": string, "subWhere": … }. Um registro dentro de outro registro; aninha sem limite declarado, desde que o catálogo declare cada nível.
• Grupo — o mesmo formato de grupo, para combinar as folhas anteriores com AND/OR.

Forma de value por comparisonOperator

ScalarValue = string | number | boolean. Além disso, cada operador deve ser compatível com o tipo da coluna (ver regras de validação).

groupBy — colunas de agrupamento

Lista opcional de ColumnRefDto[]. Quando o bloco agrega (declara groupBy ou tem um agregado no select), o groupBy deve coincidir exatamente com as colunas não agregadas do select (ver regras de validação).

having — filtro sobre agregados (HavingDto)

Mesma estrutura recursiva que where (grupos AND/OR aninhados e mesma regra de value por operador), mas a folha opera sobre um agregado, não sobre uma coluna:

• Predicado (folha) — { kind: "predicate", aggregate: AggregateDto, comparisonOperator: ComparisonOperatorEnum, value?: PredicateValue }.
• Grupo — idêntico ao de where.

timeWindow — janela temporal (TimeWindowDto)

União discriminada por timeOperator. Restringe o bloco a um período de tempo sobre uma coluna TIMESTAMP do catálogo (reference). É um campo irmão de where, não um nó da árvore de filtros.

TimeUnitEnum: DAYS | WEEKS | MONTHS | YEARS.

Semântica de negócio de cada operador:

• ALL_TIME — sem janela; considera todo o histórico.
• IN_THE_LAST — janela móvel relativa a "agora": os últimos value unit (ex.: últimos 30 DAYS). Recalculada a cada execução.
• IN_THE_FIRST — janela relativa à origem do ciclo de vida do usuário (sua data de cadastro): os primeiros value unit de vida de cada usuário. Ancorada por usuário, não a uma data fixa.
• SINCE — a partir da data civil from (inclusive).
• BETWEEN — entre from e to, ambas datas civis inclusivas do dia completo.

composedWith[] — composição com outras audiências

Cada elemento (CreateCompositionDto) combina esta definição com outra audiência via uma operação de conjunto:

Exemplo

Definição de uma audiência de usuários com uma transação dentro dos seus primeiros 30 dias desde a data de cadastro (IN_THE_FIRST). Ao omitir format, a resposta é parametrizada (padrão).

curl -X POST https://api.reten.ai/api/audience-criterias/generate-sql \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "x-project-id: <project-id>" \
  -H "Content-Type: application/json" \
  -d '{
    "queryBlocks": [
      {
        "order": 0,
        "from": { "table": "event_transactions" },
        "select": {
          "distinct": true,
          "items": [
            {
              "kind": "column",
              "column": { "table": "event_transactions", "column": "user_id" }
            }
          ]
        },
        "timeWindow": {
          "timeOperator": "IN_THE_FIRST",
          "reference": { "table": "event_transactions", "column": "event_date" },
          "unit": "DAYS",
          "value": 30
        },
        "where": { "kind": "group", "logicalOperator": "AND", "children": [] }
      }
    ]
  }'

import axios from 'axios';

const body = {
  queryBlocks: [
    {
      order: 0,
      from: { table: 'event_transactions' },
      select: {
        distinct: true,
        items: [
          {
            kind: 'column',
            column: { table: 'event_transactions', column: 'user_id' },
          },
        ],
      },
      timeWindow: {
        timeOperator: 'IN_THE_FIRST',
        reference: { table: 'event_transactions', column: 'event_date' },
        unit: 'DAYS',
        value: 30,
      },
      where: { kind: 'group', logicalOperator: 'AND', children: [] },
    },
  ],
  // format: 'literal',  // opcional; padrão é 'parameterized'
};

const response = await axios.post(
  'https://api.reten.ai/api/audience-criterias/generate-sql',
  body,
  { headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-id>' } },
);
const sql = response.data; // IAudienceCriteriaSql

A resposta é documentada em IAudienceCriteriaSql, com um exemplo de cada formato.

GET /api/audience-criterias/:id/sql

Compila um critério já salvo, referenciado por seu id. A definição não viaja no corpo: é obtida do critério persistido no projeto ativo.

Auth: Obrigatória — permissão audience-criteria:view-sql

Status 200 OK (é um GET).

Parâmetros de rota

Parâmetros de consulta

GetSqlDto:

Exemplo

curl "https://api.reten.ai/api/audience-criterias/f47ac10b-58cc-4372-a567-0e02b2c3d479/sql?format=literal" \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "x-project-id: <project-id>"

import axios from 'axios';

const id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
const response = await axios.get(
  `https://api.reten.ai/api/audience-criterias/${id}/sql`,
  {
    params: { format: 'literal' }, // opcional; padrão é 'parameterized'
    headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-id>' },
  },
);
const sql = response.data; // IAudienceCriteriaSql

Formato de saída — format

O campo format decide como os valores do SQL são emitidos e quais campos a resposta traz. É opcional; se omitido, o valor efetivo é parameterized.

• Em generate-sql (POST) o format vai no corpo.
• Em :id/sql (GET) o format vai como query param.

Só são aceitas as strings "parameterized" e "literal"; qualquer outro valor produz um 400.

Resposta — IAudienceCriteriaSql

É uma união discriminada por format. Ambas as variantes compartilham uma base e diferem nos campos de binding.

Base (comum aos dois formatos):

• format: "parameterized" — IParameterizedAudienceCriteriaSql:

  Campo	Tipo	Descrição
  format	"parameterized"	Discriminador.
  query	string	Cadeia de CTEs com placeholders @pN.
  finalCteName	string	Nome do CTE final.
  subjectColumn	string	Coluna de identidade exposta pelo CTE final.
  granularity	"table_row" | "record_element"	O que é uma linha do CTE final (em uma audiência, sempre table_row).
  params	Record<string, ScalarValue>	Binding de cada @pN → seu valor. ScalarValue = string | number | boolean.
  types	Record<string, string>	Tipo de parâmetro do BigQuery de cada @pN (ex.: STRING, INT64, TIMESTAMP, BOOL, FLOAT64, NUMERIC, BIGNUMERIC).

• format: "literal" — ILiteralAudienceCriteriaSql:

  Campo	Tipo	Descrição
  format	"literal"	Discriminador.
  query	string	Cadeia de CTEs com os valores já inlineados como literais.
  finalCteName	string	Nome do CTE final.
  subjectColumn	string	Coluna de identidade exposta pelo CTE final.
  granularity	"table_row" | "record_element"	O que é uma linha do CTE final (em uma audiência, sempre table_row).

Exemplo — parameterized (padrão)

Resposta 201 ao POST de exemplo acima (sem format):

{
  "format": "parameterized",
  "query": "op_5e2a91c07b3d_0 AS (\nSELECT DISTINCT `event_transactions`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions\nINNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`\nWHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL @p0 DAY))\n),\nop_9a8b7c6d5e4f_0 AS (\nSELECT DISTINCT `user_properties`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties\nWHERE `user_properties`.`is_active` = @p1\n),\nCTE_AUDIENCE_USERS AS (\nSELECT * FROM op_5e2a91c07b3d_0\nINTERSECT DISTINCT\nSELECT * FROM op_9a8b7c6d5e4f_0\n)",
  "finalCteName": "CTE_AUDIENCE_USERS",
  "subjectColumn": "user_id",
  "granularity": "table_row",
  "params": { "p0": 30, "p1": true },
  "types": { "p0": "INT64", "p1": "BOOL" }
}

O query é uma única string com quebras \n — uma cadeia de CTEs sem o WITH inicial e sem o SELECT final. Formatado para leitura:

op_5e2a91c07b3d_0 AS (
SELECT DISTINCT `event_transactions`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions
INNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`
WHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL @p0 DAY))
),
op_9a8b7c6d5e4f_0 AS (
SELECT DISTINCT `user_properties`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties
WHERE `user_properties`.`is_active` = @p1
),
CTE_AUDIENCE_USERS AS (
SELECT * FROM op_5e2a91c07b3d_0
INTERSECT DISTINCT
SELECT * FROM op_9a8b7c6d5e4f_0
)

Cada @pN é vinculado com params[pN] (seu valor) e types[pN] (seu tipo BigQuery); sem esse binding o query não é executável. p0 é da audiência e p1 da audiência raiz do projeto, que aqui mantém sua definição padrão (veja Interseção com a audiência raiz). O INNER JOIN com user_properties não está na requisição: IN_THE_FIRST mede os primeiros dias de cada usuário a partir da data de cadastro, então a API o adiciona.

Exemplo — literal

A mesma definição com format: "literal" (na variante by-id, ?format=literal):

{
  "format": "literal",
  "query": "op_5e2a91c07b3d_0 AS (\nSELECT DISTINCT `event_transactions`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions\nINNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`\nWHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL 30 DAY))\n),\nop_9a8b7c6d5e4f_0 AS (\nSELECT DISTINCT `user_properties`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties\nWHERE `user_properties`.`is_active` = TRUE\n),\nCTE_AUDIENCE_USERS AS (\nSELECT * FROM op_5e2a91c07b3d_0\nINTERSECT DISTINCT\nSELECT * FROM op_9a8b7c6d5e4f_0\n)",
  "finalCteName": "CTE_AUDIENCE_USERS",
  "subjectColumn": "user_id",
  "granularity": "table_row"
}

Formatado para leitura:

op_5e2a91c07b3d_0 AS (
SELECT DISTINCT `event_transactions`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions
INNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`
WHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL 30 DAY))
),
op_9a8b7c6d5e4f_0 AS (
SELECT DISTINCT `user_properties`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties
WHERE `user_properties`.`is_active` = TRUE
),
CTE_AUDIENCE_USERS AS (
SELECT * FROM op_5e2a91c07b3d_0
INTERSECT DISTINCT
SELECT * FROM op_9a8b7c6d5e4f_0
)

Os valores ficam inlineados como literais seguros (aqui 30 e TRUE; strings vão escapadas entre aspas simples); não há params nem types.

Interseção com a audiência raiz

Todo projeto tem uma audiência raiz, e toda outra audiência do projeto é avaliada dentro dela. Em ambos os endpoints, o SQL de qualquer audiência que não seja a audiência raiz sai intersectado com a audiência raiz do projeto:

• A audiência e a audiência raiz mantêm seus próprios CTEs, e o CTE final intersecta o último CTE de cada uma: CTE_AUDIENCE_USERS AS (SELECT * FROM <último CTE da audiência> INTERSECT DISTINCT SELECT * FROM <último CTE da audiência raiz>).
• No formato parameterized, os parâmetros @pN da audiência vêm primeiro e os da audiência raiz em seguida.
• Não há interseção quando a audiência compilada é a própria audiência raiz — GET /:id/sql com o id da audiência raiz, ou um rascunho cujo criteriaId é a audiência raiz.
• A interseção é posicional (SELECT * nos dois lados): a audiência deve projetar as mesmas colunas que a audiência raiz, na mesma ordem e com tipos compatíveis — com a definição padrão da audiência raiz, apenas user_id. Caso contrário, o SQL falha ao ser executado.

Os CTEs intermediários levam no nome um prefixo do id de cada audiência (em um rascunho enviado sem criteriaId, esse prefixo muda a cada requisição), então referencie sempre o resultado por meio de finalCteName.

Por exemplo, uma audiência de usuários com uma transação COMPLETED, em um projeto cuja audiência raiz foi editada para country = 'CL' (formato literal, formatado para leitura):

op_f47ac10b58cc_0 AS (
SELECT DISTINCT `event_transactions`.`user_id`
FROM `<gcp-project>.<dataset>.event_transactions` AS event_transactions
WHERE `event_transactions`.`status` = 'COMPLETED'
),
op_9a8b7c6d5e4f_0 AS (
SELECT DISTINCT `user_properties`.`user_id`
FROM `<gcp-project>.<dataset>.user_properties` AS user_properties
WHERE `user_properties`.`country` = 'CL'
),
CTE_AUDIENCE_USERS AS (
SELECT * FROM op_f47ac10b58cc_0
INTERSECT DISTINCT
SELECT * FROM op_9a8b7c6d5e4f_0
)

Regras de validação

Antes de compilar, a definição passa pelas mesmas duas camadas de validação que Criar, e ambas retornam 400 Bad Request:

1. Validação do DTO (estrutura, tipos, enums, campos obrigatórios, format válido). message é um array de strings e propriedades desconhecidas são rejeitadas (incluindo name/description).
2. Validação de negócio (a definição deve gerar SQL válido e coerente com o catálogo). message é um string único com a regra violada.

Respostas de erro