The Alenia AIOps platform connects directly to Azure Databricks to perform automated diagnosis, data lineage analysis, and autonomous remediation sagas across the Ayvens fleet management platform. Because production databases store critical lease contracts and vehicle assets, all SQL operations must adhere to Zero-Trust architecture, automated Abstract Syntax Tree (AST) guardrails, and deterministic blast radius isolation.
This document details the Databricks SQL Statement Execution API integration, catalog access policies, query rewriting rules, and safety verification algorithms.
Automated diagnostics and remediation tasks interact with Azure Databricks using the official REST API v2.0 for SQL statement execution.
https://adb-6494852855344788.8.azuredatabricks.netPOST /api/2.0/sql/statements4b5d8cbc96293d6cDATABRICKS_TOKEN resolved dynamically from environment/Vault. Literal credentials are strictly prohibited.{
"statement": "SELECT CLASSID, LOCALCODE, LOCALVALUE FROM `alenia-aiops-dev`.silver.localvalue_t WHERE CLASSID = 27 AND SUBSIDIARYCODE = 'FR' LIMIT 10",
"warehouse_id": "4b5d8cbc96293d6c",
"wait_timeout": "30s",
"on_wait_timeout": "CONTINUE",
"catalog": "alenia-aiops-dev",
"schema": "silver"
}
[POST /api/2.0/sql/statements]
|
v
(HTTP 200 OK)
|
v
+-----------+
| PENDING |
+-----------+
|
v
+-----------+
| RUNNING |
+-----------+
|
+-------+-------+
| |
v v
+-----------+ +-----------+
| SUCCEEDED | | FAILED |
+-----------+ +-----------+
| |
v v
[Extract [Log SRE Alert
Result Data] & Trigger Rollback]
wait_timeout is set to "30s", fast analytical queries complete synchronously, returning status SUCCEEDED and direct chunked results in the initial HTTP response.PENDING or RUNNING, the connector polls GET /api/2.0/sql/statements/{statement_id} using exponential backoff with a maximum deadline of 60 seconds.POST /api/2.0/sql/statements/{statement_id}/cancel call is dispatched immediately and the operation fails closed.EnforceSandboxTarget)To protect reference datasets from accidental corruption or developer oversight, the platform enforces catalog access policies at the API client boundary.
aiops_participants_synthethic_data: Strict Read-Only (SELECT only).aiops_hackathon_dev_ai: Strict Read-Only (SELECT only).`alenia-aiops-dev`: Read/Write execution sandbox.ayvens_sandbox_EnforceSandboxTarget)Before any SQL statement is dispatched to the Databricks SQL Warehouse, the query is inspected for mutating clauses (INSERT, UPDATE, DELETE, MERGE).
If a mutating query references tables in aiops_participants_synthethic_data or aiops_hackathon_dev_ai, the query engine applies an AST rewriting rule to redirect the target table to `alenia-aiops-dev`:
def ayvens_sandbox_EnforceSandboxTarget(sql_query: str) -> str:
"""Redirect mutating statements from read-only catalogs to the sandbox catalog."""
# Replaces catalog prefix dynamically
rewritten = re.sub(
r"(?:aiops_participants_synthethic_data|aiops_hackathon_dev_ai)\.(silver|bronze|gold|solutions)\.",
r"`alenia-aiops-dev`.\1.",
sql_query,
flags=re.IGNORECASE,
)
return rewritten
SQLSafetyValidator)Every SQL statement generated dynamically by remediation engines or LLM intent pipelines must pass validation by SQLSafetyValidator (backed by sqlglot) before transmission to the network.
The validator rejects statements containing dangerous DDL or unrestricted operations:
DROP TABLE, DROP DATABASE, DROP SCHEMATRUNCATE TABLEALTER TABLE (except explicit schema migrations governed by CI/CD)GRANT, REVOKE (privilege escalation attempts);) to prevent multi-statement injectionAll UPDATE and DELETE operations MUST possess a top-level WHERE clause.
The validator inspects the root statement AST:
# Evaluates top-level WHERE directly on parsed expression
where_clause = parsed.args.get("where")
if not where_clause:
return False, "UPDATE and DELETE statements must contain a valid top-level WHERE clause."
Note: A subquery containing a WHERE clause inside an unscoped UPDATE does not satisfy this rule.
Remediation safety strictly prohibits boolean tautologies that would cause unrestricted row mutations:
WHERE 1=1, WHERE 'a'='a', WHERE 2=2WHERE localcode = 'X' OR 'a'='a'-- or /* ... */If a tautology is detected, validate_sql() immediately returns (False, "Tautological WHERE condition detected").
Table and column names must strictly match the regex identifier pattern:
^[A-Za-z0-9_.]+$
All literal string values must be escaped (' replaced by '') to prevent SQL injection.
Remediations execute under a transactional Saga pattern that guarantees recovery from partial failures:
`alenia-aiops-dev`.silver.localvalue_t or `alenia-aiops-dev`.solutions.solutions.bronze.localvalue_history_t recording the actor principal, mutated attributes, and timestamp.DiagnosisTrailThe execution of each phase is recorded in the Shield DiagnosisTrail lifecycle trace:
The lifecycle trace is persisted as JSONB in PostgreSQL public.events and exposed via the Cockpit console for operational review and auditability.