Generar SQL

Generar SQL

Compila una definición de audience criteria a SQL de BigQuery (GoogleSQL). Un mismo motor sirve dos entradas:

• POST /api/audience-criterias/generate-sql — compila una definición enviada en el cuerpo (aún no persistida).
• GET /api/audience-criterias/:id/sql — compila un criterio ya guardado, referenciado por su id.

Ambos devuelven la misma forma de respuesta: IAudienceCriteriaSql, una unión discriminada por el campo format. El format (parametrizado o literal) se elige por solicitud y gobierna tanto la forma del query como los campos de la respuesta.

Auth: Requerida — permiso audience-criteria:view-sql (ambos endpoints).

POST /api/audience-criterias/generate-sql

Compila una definición enviada en el cuerpo, sin persistirla.

Auth: Requerida — permiso audience-criteria:view-sql

Cuerpo de la solicitud

Nivel raíz (GenerateSqlDto): la definición del criterio (CriteriaDefinitionDto) más el campo format.

Definición del criterio

Es el mismo árbol que recibe Crear, sin los campos name/description. Se documenta aquí de forma autocontenida.

queryBlocks[] — bloque de consulta

Cada elemento de queryBlocks (CreateQueryBlockDto) es un statement SQL completo:

ColumnRefDto — referencia a columna

Se usa en todo el árbol (select, filtros, group by, joins, agregados, ventana temporal):

Toda columna se cualifica siempre con su table para evitar ambigüedad.

from — fuente de datos (FromDto)

Cada JoinDto:

Cada JoinPredicateDto es un par de columnas: { left: ColumnRefDto, right: ColumnRefDto }.

select — proyección (SelectDto)

Los items se discriminan por kind:

• Columna — kind: "column":

  Campo	Tipo	Requerido	Descripción
  kind	"column"	Sí	Discriminador.
  column	ColumnRefDto	Sí	Columna proyectada.
  alias	string	No	Debe matchear ^[A-Za-z_][A-Za-z0-9_]*$.

• Agregado — kind: "aggregate":

  Campo	Tipo	Requerido	Descripción
  kind	"aggregate"	Sí	Discriminador.
  aggregate	AggregateDto	Sí	Expresión de agregación.
  alias	string	Sí	Aquí el alias es obligatorio. Mismo regex de identificador.

AggregateDto:

where — árbol de predicados (WhereDto)

El where es un árbol recursivo. El nodo raíz es siempre un grupo:

• Grupo — { kind: "group", logicalOperator: LogicalOperatorEnum, children: (Predicado | Grupo)[] }. Se permiten grupos anidados (AND/OR a varios niveles).
• Predicado (hoja) — { kind: "predicate", column: ColumnRefDto, comparisonOperator: ComparisonOperatorEnum, value?: PredicateValue }.

Forma de value según comparisonOperator

ScalarValue = string | number | boolean. Además, cada operador debe ser compatible con el tipo de la columna (ver reglas de validación).

groupBy — columnas de agrupación

Lista opcional de ColumnRefDto[]. Cuando el bloque agrega (declara groupBy o tiene un agregado en el select), el groupBy debe coincidir exactamente con las columnas no agregadas del select (ver reglas de validación).

having — filtro sobre agregados (HavingDto)

Misma estructura recursiva que where (grupos AND/OR anidados y misma regla de value por operador), pero la hoja opera sobre un agregado, no sobre una columna:

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

timeWindow — ventana temporal (TimeWindowDto)

Unión discriminada por timeOperator. Acota el bloque a un período de tiempo sobre una columna TIMESTAMP del catálogo (reference). Es un campo hermano del where, no un nodo del árbol de filtros.

TimeUnitEnum: DAYS | WEEKS | MONTHS | YEARS.

Semántica de negocio de cada operador:

• ALL_TIME — sin ventana; considera todo el histórico.
• IN_THE_LAST — ventana rodante relativa a "ahora": los últimos value unit (p. ej. últimos 30 DAYS). Se recomputa en cada corrida.
• IN_THE_FIRST — ventana relativa al origen del ciclo de vida del usuario (su fecha de alta): los primeros value unit de vida de cada usuario. Se ancla por usuario, no a una fecha fija.
• SINCE — desde la fecha civil from (inclusiva) en adelante.
• BETWEEN — entre from y to, ambas fechas civiles inclusivas de su día completo.

composedWith[] — composición con otras audiencias

Cada elemento (CreateCompositionDto) combina esta definición con otra audiencia vía una operación de conjunto:

Ejemplo

Definición de una audiencia de usuarios con al menos una orden pagada en Chile o México. Al omitir format, la respuesta es parametrizada (default).

curl -X POST https://api.reten.ai/api/audience-criterias/generate-sql \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "Content-Type: application/json" \
  -d '{
    "type": "AUDIENCE",
    "queryBlocks": [
      {
        "order": 0,
        "from": { "table": "orders", "joins": [] },
        "select": {
          "distinct": true,
          "items": [
            {
              "kind": "column",
              "column": { "table": "orders", "column": "user_id" },
              "alias": "user_id"
            }
          ]
        },
        "where": {
          "kind": "group",
          "logicalOperator": "AND",
          "children": [
            {
              "kind": "predicate",
              "column": { "table": "orders", "column": "status" },
              "comparisonOperator": "EQUALS",
              "value": "paid"
            },
            {
              "kind": "predicate",
              "column": { "table": "orders", "column": "country" },
              "comparisonOperator": "IN",
              "value": ["CL", "MX"]
            }
          ]
        }
      }
    ]
  }'

import axios from 'axios';

const body = {
  type: 'AUDIENCE',
  queryBlocks: [
    {
      order: 0,
      from: { table: 'orders', joins: [] },
      select: {
        distinct: true,
        items: [
          {
            kind: 'column',
            column: { table: 'orders', column: 'user_id' },
            alias: 'user_id',
          },
        ],
      },
      where: {
        kind: 'group',
        logicalOperator: 'AND',
        children: [
          {
            kind: 'predicate',
            column: { table: 'orders', column: 'status' },
            comparisonOperator: 'EQUALS',
            value: 'paid',
          },
          {
            kind: 'predicate',
            column: { table: 'orders', column: 'country' },
            comparisonOperator: 'IN',
            value: ['CL', 'MX'],
          },
        ],
      },
    },
  ],
  // format: 'literal',  // opcional; por defecto '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>' } },
);
const sql = response.data; // IAudienceCriteriaSql

La respuesta se documenta en IAudienceCriteriaSql, con un ejemplo de cada formato.

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

Compila un criterio ya guardado, referenciado por su id. La definición no viaja en el cuerpo: se toma del criterio persistido en el tenant.

Auth: Requerida — permiso audience-criteria:view-sql

Status 200 OK (es un GET).

Parámetros de ruta

Parámetros de query

GetSqlDto:

Ejemplo

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>"

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; por defecto 'parameterized'
    headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>' },
  },
);
const sql = response.data; // IAudienceCriteriaSql

Formato de salida — format

El campo format decide cómo se emiten los valores del SQL y qué campos trae la respuesta. Es opcional; si se omite, el valor efectivo es parameterized.

• En generate-sql (POST) el format va en el cuerpo.
• En :id/sql (GET) el format va como query param.

Solo se aceptan los strings "parameterized" y "literal"; cualquier otro valor produce un 400.

Respuesta — IAudienceCriteriaSql

Es una unión discriminada por format. Ambas variantes comparten una base y difieren en los campos de binding.

Base (común a los dos formatos):

• format: "parameterized" — IParameterizedAudienceCriteriaSql:

  Campo	Tipo	Descripción
  format	"parameterized"	Discriminador.
  query	string	SQL con placeholders @pN.
  cteName	string	Nombre del CTE final.
  params	Record<string, ScalarValue>	Binding de cada @pN → su valor. ScalarValue = string | number | boolean.
  types	Record<string, string>	Tipo de parámetro de BigQuery de cada @pN (p. ej. STRING, INT64, TIMESTAMP, BOOL, FLOAT64, NUMERIC, BIGNUMERIC).

• format: "literal" — ILiteralAudienceCriteriaSql:

  Campo	Tipo	Descripción
  format	"literal"	Discriminador.
  query	string	SQL con los valores ya inlineados como literales.
  cteName	string	Nombre del CTE final.

Ejemplo — parameterized (default)

Respuesta 201 al POST de ejemplo de arriba (sin format):

{
  "format": "parameterized",
  "query": "WITH op_a1b2c3d45678_0 AS (\nSELECT DISTINCT `orders`.`user_id` AS `user_id`\nFROM `reten-prod.tenant_a1b2c3d4.orders` AS orders\nWHERE `orders`.`status` = @p0 AND `orders`.`country` IN (@p1, @p2)\n)\nSELECT * FROM op_a1b2c3d45678_0",
  "cteName": "op_a1b2c3d45678_0",
  "params": { "p0": "paid", "p1": "CL", "p2": "MX" },
  "types": { "p0": "STRING", "p1": "STRING", "p2": "STRING" }
}

El query es una sola cadena con saltos \n. Formateado para lectura:

WITH op_a1b2c3d45678_0 AS (
SELECT DISTINCT `orders`.`user_id` AS `user_id`
FROM `reten-prod.tenant_a1b2c3d4.orders` AS orders
WHERE `orders`.`status` = @p0 AND `orders`.`country` IN (@p1, @p2)
)
SELECT * FROM op_a1b2c3d45678_0

Cada @pN se liga con params[pN] (su valor) y types[pN] (su tipo BigQuery); sin ese binding el query no es ejecutable.

Ejemplo — literal

La misma definición con format: "literal" (en la variante by-id, ?format=literal):

{
  "format": "literal",
  "query": "WITH op_a1b2c3d45678_0 AS (\nSELECT DISTINCT `orders`.`user_id` AS `user_id`\nFROM `reten-prod.tenant_a1b2c3d4.orders` AS orders\nWHERE `orders`.`status` = 'paid' AND `orders`.`country` IN ('CL', 'MX')\n)\nSELECT * FROM op_a1b2c3d45678_0",
  "cteName": "op_a1b2c3d45678_0"
}

Formateado para lectura:

WITH op_a1b2c3d45678_0 AS (
SELECT DISTINCT `orders`.`user_id` AS `user_id`
FROM `reten-prod.tenant_a1b2c3d4.orders` AS orders
WHERE `orders`.`status` = 'paid' AND `orders`.`country` IN ('CL', 'MX')
)
SELECT * FROM op_a1b2c3d45678_0

Los valores quedan inlineados como literales seguros (strings escapados y entre comillas simples); no hay params ni types.

Reglas de validación

Antes de compilar, la definición pasa por las mismas dos capas de validación que Crear, y ambas devuelven 400 Bad Request:

1. Validación del DTO (estructura, tipos, enums, campos requeridos, format válido). message es un arreglo de strings y se rechazan propiedades desconocidas (incluidos name/description).
2. Validación de negocio (la definición debe generar SQL válido y coherente contra el catálogo). message es un string único con la regla incumplida.

Respuestas de error