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.

Dentro de un proyecto, el SQL de toda audiencia que no sea la audiencia raíz sale intersectado con la audiencia raíz del proyecto — ver Intersección con la audiencia raíz.

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 (QueryBlockDto) 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:

operand —lo que se agrega— se discrimina por kind:

ArithmeticOperatorEnum: ADD | SUBTRACT | MULTIPLY | SAFE_DIVIDE (una división que da NULL cuando el denominador es cero, en vez de hacer fallar la consulta). Las columnas de una expresión aritmética tienen que ser numéricas (INT64, NUMERIC, BIGNUMERIC, FLOAT64), y las expresiones se anidan hasta 3 niveles de arithmetic, contando el exterior.

where — árbol de predicados (WhereDto)

El where es un árbol recursivo. El nodo raíz es siempre un grupo, y sus hojas son de dos tipos:

Grupo — kind: "group"

Hoja de columna — kind: "predicate"

Compara una columna escalar cualificada por su tabla.

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

Hoja de registro — kind: "recordPredicate"

Filtra por una columna RECORD REPEATED (un array de structs, como attributes). Significa existe al menos un elemento del array que cumple el 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" }
    ]
  }
}

Qué columnas admiten un filtro de registro lo dice el catálogo: las que vienen con kind: "record".

Sub-árbol relativo

El subWhere es un grupo AND/OR cuyas hojas se resuelven contra los sub-campos que el registro declara.

La diferencia con el árbol de primer nivel es una sola, y es la fuente de error más común:

Hojas admitidas dentro de un sub-árbol relativo:

• Hoja escalar — { "kind": "predicate", "field": string, "comparisonOperator": …, "value"?: … }. El field debe ser un sub-campo escalar del registro.
• Registro anidado — { "kind": "recordPredicate", "field": string, "subWhere": … }. Un registro dentro de otro registro; se anida sin límite declarado, siempre que el catálogo declare cada nivel.
• Grupo — la misma forma de grupo, para combinar las hojas anteriores con AND/OR.

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 una transacción dentro de sus primeros 30 días desde su fecha de alta (IN_THE_FIRST). 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 "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; 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>', 'x-project-id': '<project-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 proyecto activo.

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

Estado 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>" \
  -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; por defecto 'parameterized'
    headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-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	Cadena de CTEs con placeholders @pN.
  finalCteName	string	Nombre del CTE final.
  subjectColumn	string	Columna de identidad que expone el CTE final.
  granularity	"table_row" | "record_element"	Qué es una fila del CTE final (en una audiencia, siempre table_row).
  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	Cadena de CTEs con los valores ya inlineados como literales.
  finalCteName	string	Nombre del CTE final.
  subjectColumn	string	Columna de identidad que expone el CTE final.
  granularity	"table_row" | "record_element"	Qué es una fila del CTE final (en una audiencia, siempre table_row).

Ejemplo — parameterized (default)

Respuesta 201 al POST de ejemplo de arriba (sin 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" }
}

El query es una sola cadena con saltos \n — una cadena de CTEs sin el WITH inicial y sin el SELECT final. Formateado para lectura:

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 se liga con params[pN] (su valor) y types[pN] (su tipo BigQuery); sin ese binding el query no es ejecutable. p0 es de la audiencia y p1 de la audiencia raíz del proyecto, que aquí conserva su definición por defecto (ver Intersección con la audiencia raíz). El INNER JOIN a user_properties no está en la solicitud: IN_THE_FIRST mide los primeros días de cada usuario desde su fecha de alta, así que la API lo agrega.

Ejemplo — literal

La misma definición con format: "literal" (en la 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"
}

Formateado para lectura:

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
)

Los valores quedan inlineados como literales seguros (aquí 30 y TRUE; los strings van escapados y entre comillas simples); no hay params ni types.

Intersección con la audiencia raíz

Todo proyecto tiene una audiencia raíz, y toda otra audiencia del proyecto se evalúa dentro de ella. En ambos endpoints, el SQL de cualquier audiencia que no sea la audiencia raíz sale intersectado con la audiencia raíz del proyecto:

• La audiencia y la audiencia raíz conservan sus propios CTEs, y el CTE final intersecta el último CTE de cada una: CTE_AUDIENCE_USERS AS (SELECT * FROM <último CTE de la audiencia> INTERSECT DISTINCT SELECT * FROM <último CTE de la audiencia raíz>).
• En el formato parameterized, los parámetros @pN de la audiencia van primero y los de la audiencia raíz a continuación.
• No hay intersección cuando la audiencia compilada es la propia audiencia raíz — GET /:id/sql con el id de la audiencia raíz, o un borrador cuyo criteriaId es la audiencia raíz.
• La intersección es posicional (SELECT * en los dos lados): la audiencia debe proyectar las mismas columnas que la audiencia raíz, en el mismo orden y con tipos compatibles — con la definición por defecto de la audiencia raíz, solo user_id. Si no, el SQL falla al ejecutarse.

Los CTEs intermedios llevan en su nombre un prefijo del id de cada audiencia (en un borrador enviado sin criteriaId, ese prefijo cambia en cada solicitud), así que referencia siempre el resultado a través de finalCteName.

Por ejemplo, una audiencia de usuarios con una transacción COMPLETED, en un proyecto cuya audiencia raíz se editó a country = 'CL' (formato literal, formateado para lectura):

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
)

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