Batch queries

Below are some example SQL queries that might be helpful when querying batch logs. You can run these queries in the Velvet AI SQL editor, or any other tool you're comfortable with.

Batch logs

Each batch file upload unfurls the jsonl lines into individual requests (aka a log). The response column in the log will be null until a successful /v1/files/:id/content is run. Once completed - we expand the jsonl line, update the corresponding record’s response column, and merge the metadata.

Example SQL queries

Show model, temp, input_file_id, and custom_id for each batch with a custom_id like infer_attributes_from_person.

SELECT
  id,
  (metadata ->> 'model') AS model,
  (request -> 'body' ->> 'temperature') AS temperature,
  (metadata -> 'batch' -> 'input_file' ->> 'id') AS input_file_id,
  (metadata -> 'batch' ->> 'custom_id') AS custom_id,
  response,
  metadata,
  created_at,
  updated_at
FROM
  llm_logs
WHERE
  metadata -> 'batch' ->> 'custom_id' LIKE 'infer_attributes_from_person%'
ORDER BY
  COALESCE(updated_at, created_at)
  DESC;

Show total batch rows, completed, and incomplete (e.g. /v1/files/:id/content hasn’t been executed yet).

SELECT
  COUNT(*) AS total_rows_with_batch,
  COUNT(CASE 
           WHEN request IS NOT NULL AND response IS NOT NULL THEN 1 
         END) AS completed_rows,
  COUNT(CASE 
           WHEN request IS NOT NULL AND response IS NULL THEN 1 
         END) AS incomplete_rows
FROM
  llm_logs
WHERE
  metadata ? 'batch';

For calls of InferAttributesFromPerson, see what temperature is being set.

SELECT
  request -> 'body' -> 'messages' AS messages,
  response -> 'body' -> 'choices' -> 0 -> 'message' ->> 'content' AS content
FROM
  llm_logs
WHERE
  metadata -> 'batch' ->> 'custom_id' LIKE 'infer_attributes_from_person%'
ORDER BY
  COALESCE(updated_at, created_at) DESC;