aidp-ai-sql

aidp-ai-sql — LLM-in-SQL with ai_generate()

Call an LLM directly inside Spark SQL on AIDP — summarize, classify, extract, or narrate over lakehouse data without leaving SQL. A signature differentiator: most competitor agents can't do this inline.

This is a SQL-helper skill. Interactive Spark SQL runs through the bundled helper scripts/aidp_sql.py (it mints a UPST from the api_key DEFAULT profile, auto-creates a scratch notebook, and returns JSON). No aidp MCP and no AIDP_SESSION required.

When to use

• "Summarize / classify / extract / enrich these rows with AI in SQL."
• Generate a grounded narrative over an aggregate (e.g. a finance summary over a spend rollup).

Signature (model FIRST)

ai_generate('<model>', '<prompt>')

e.g. ai_generate('openai.gpt-5.4', 'Summarize this supplier spend: ...').

LIVE-VERIFIED model-first (model, prompt) signature with openai.gpt-5.4, openai.gpt-4o, and xai.grok-4.

Verify before relying on it (no-fabrication): confirm the exact signature and the available model names live on the target cluster before treating this as guaranteed — run a trivial SELECT ai_generate('<model>', 'hello') cell first (see smoke test below). Model availability varies by environment. If a model name fails, list/ask for the correct one rather than guessing.

Don't gate on the /models REST catalog. ai_generate resolves the model at the Spark engine level, so it can work even when aidp-models-catalog's GET /models?modelType=GENERATIVE_AI returns an empty list. The smoke test (not the catalog endpoint) is the source of truth for whether ai_generate works.

How to run a cell

python "$PLUGIN_DIR/scripts/aidp_sql.py" \
  --region <region> --datalake <DATALAKE_OCID> --workspace <ws> --cluster <cluster-key> \
  --code "<python/spark code>"

Returns JSON: {"status":"ok|error","outputs":[...],"spark_job_ids":[...]}. Exit 0 on success, 1 on cell error. See scripts/aidp_sql.py for full flags (--profile, --session-profile, --notebook, --timeout).