Queries API

The Queries API provides endpoints for executing natural language queries, managing query history, and working with saved queries.

Overview

The Queries API supports:

Natural language queries: Convert questions to SQL automatically
Direct SQL execution: Run SQL queries directly
Query history: Track all executed queries
Saved queries: Save and reuse common queries
Query explanations: Understand how queries work

Authentication

All query endpoints require authentication via JWT token:

Authorization: Bearer <your-jwt-token>

Endpoints

Execute Query

Execute a natural language or SQL query.

POST /api/v1/projects/{project_id}/queries/execute

Request Body

{
  "query": "What were the top 10 customers by total sales last month?",
  "type": "natural_language",
  "datasets": ["ds_1234567890"],
  "options": {
    "limit": 1000,
    "timeout_seconds": 30,
    "explain": true,
    "cache": true
  }
}

Parameters

Name	Type	Required	Description
query	string	Yes	The query to execute
type	string	No	Query type: "natural_language" (default) or "sql"
datasets	array	No	Dataset IDs to query (defaults to all)
options	object	No	Execution options

Response

{
  "query_id": "qry_abc123def456",
  "status": "completed",
  "type": "natural_language",
  "original_query": "What were the top 10 customers by total sales last month?",
  "generated_sql": "SELECT \n  c.customer_id,\n  c.customer_name,\n  SUM(s.amount) as total_sales\nFROM sales_data_2024 s\nJOIN customers c ON s.customer_id = c.id\nWHERE s.order_date >= '2024-12-01' \n  AND s.order_date < '2025-01-01'\nGROUP BY c.customer_id, c.customer_name\nORDER BY total_sales DESC\nLIMIT 10",
  "results": {
    "columns": [
      {
        "name": "customer_id",
        "type": "VARCHAR",
        "nullable": false
      },
      {
        "name": "customer_name",
        "type": "VARCHAR",
        "nullable": false
      },
      {
        "name": "total_sales",
        "type": "DECIMAL",
        "nullable": true
      }
    ],
    "rows": [
      ["CUST-001", "Acme Corp", 125000.50],
      ["CUST-045", "TechStart Inc", 98500.00],
      ["CUST-023", "Global Trading", 87250.75]
    ],
    "row_count": 10,
    "truncated": false
  },
  "explanation": {
    "summary": "This query finds the top 10 customers by total sales amount for December 2024",
    "steps": [
      "Joins sales data with customer information",
      "Filters for orders in December 2024",
      "Groups by customer to calculate total sales",
      "Orders by total sales descending",
      "Limits to top 10 results"
    ],
    "datasets_used": [
      {
        "id": "ds_1234567890",
        "name": "sales_data_2024",
        "tables": ["sales_data_2024", "customers"]
      }
    ]
  },
  "metadata": {
    "execution_time_ms": 245,
    "rows_scanned": 15420,
    "bytes_scanned": 1048576,
    "cache_hit": false,
    "query_plan_hash": "abc123"
  },
  "created_at": "2025-01-15T10:30:00Z"
}

Stream Query Execution

Execute a query with streaming results (useful for large result sets).

POST /api/v1/projects/{project_id}/queries/stream

Request Body

Same as execute query endpoint.

Response

Server-Sent Events (SSE) stream:

event: status
data: {"status": "parsing", "message": "Understanding your question..."}

event: sql
data: {"sql": "SELECT customer_id, SUM(amount) as total..."}

event: schema
data: {"columns": [{"name": "customer_id", "type": "VARCHAR"}, ...]}

event: row
data: {"values": ["CUST-001", "Acme Corp", 125000.50]}

event: row
data: {"values": ["CUST-045", "TechStart Inc", 98500.00]}

event: complete
data: {"row_count": 10, "execution_time_ms": 245}

Get Query History

Retrieve query execution history.

GET /api/v1/projects/{project_id}/queries

Parameters

Name	Type	Location	Required	Description
page	integer	query	No	Page number (default: 1)
limit	integer	query	No	Items per page (default: 20)
type	string	query	No	Filter by type: natural_language, sql
status	string	query	No	Filter by status: completed, failed, running
dataset_id	string	query	No	Filter by dataset ID
start_date	string	query	No	Filter by start date (ISO 8601)
end_date	string	query	No	Filter by end date (ISO 8601)

Response

{
  "queries": [
    {
      "id": "qry_abc123def456",
      "type": "natural_language",
      "query": "What were the top 10 customers by total sales last month?",
      "status": "completed",
      "row_count": 10,
      "execution_time_ms": 245,
      "created_at": "2025-01-15T10:30:00Z",
      "created_by": {
        "id": "user_123",
        "name": "John Doe"
      }
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total": 156,
    "pages": 8
  }
}

Get Query Details

Get detailed information about a specific query.

GET /api/v1/projects/{project_id}/queries/{query_id}

Response

Same format as execute query response, including full results.

Save Query

Save a query for later reuse.

POST /api/v1/projects/{project_id}/queries/{query_id}/save

Request Body

{
  "name": "Top Customers by Sales",
  "description": "Monthly report of top 10 customers by total sales",
  "tags": ["sales", "customers", "monthly"],
  "visibility": "project",
  "parameters": [
    {
      "name": "month",
      "type": "date",
      "default": "last_month",
      "description": "Month to analyze"
    },
    {
      "name": "limit",
      "type": "integer",
      "default": 10,
      "description": "Number of top customers"
    }
  ]
}

List Saved Queries

Get all saved queries in a project.

GET /api/v1/projects/{project_id}/saved-queries

Response

{
  "saved_queries": [
    {
      "id": "sq_xyz789",
      "name": "Top Customers by Sales",
      "description": "Monthly report of top 10 customers by total sales",
      "type": "natural_language",
      "query": "What were the top {limit} customers by total sales in {month}?",
      "tags": ["sales", "customers", "monthly"],
      "visibility": "project",
      "parameters": [
        {
          "name": "month",
          "type": "date",
          "default": "last_month"
        },
        {
          "name": "limit",
          "type": "integer",
          "default": 10
        }
      ],
      "usage_count": 42,
      "last_used_at": "2025-01-14T15:00:00Z",
      "created_at": "2024-12-01T10:00:00Z",
      "created_by": {
        "id": "user_123",
        "name": "John Doe"
      }
    }
  ]
}

Execute Saved Query

Execute a saved query with parameters.

POST /api/v1/projects/{project_id}/saved-queries/{saved_query_id}/execute

Request Body

{
  "parameters": {
    "month": "2024-12",
    "limit": 20
  },
  "options": {
    "cache": true,
    "timeout_seconds": 30
  }
}

Export Query Results

Export query results in various formats.

POST /api/v1/projects/{project_id}/queries/{query_id}/export

Request Body

{
  "format": "csv",
  "options": {
    "include_headers": true,
    "delimiter": ",",
    "quote_char": "\""
  }
}

Supported Formats

csv: Comma-separated values
json: JSON array of objects
excel: Excel spreadsheet
parquet: Apache Parquet format

Response

{
  "export_id": "exp_123456",
  "format": "csv",
  "status": "completed",
  "download_url": "https://downloads.providence.io/exports/exp_123456.csv",
  "expires_at": "2025-01-16T10:30:00Z",
  "size_bytes": 45678
}

Get Query Explanation

Get a detailed explanation of how a query works.

POST /api/v1/projects/{project_id}/queries/explain

Request Body

{
  "query": "SELECT * FROM sales WHERE amount > 1000",
  "type": "sql",
  "datasets": ["ds_1234567890"]
}

Response

{
  "explanation": {
    "summary": "This query retrieves all sales records with amounts greater than $1000",
    "query_plan": {
      "type": "SCAN",
      "table": "sales",
      "filter": "amount > 1000",
      "estimated_rows": 2500,
      "estimated_cost": 0.025
    },
    "optimizations": [
      "Using index on 'amount' column for faster filtering",
      "Partition pruning based on date range"
    ],
    "warnings": [],
    "suggestions": [
      "Consider adding LIMIT clause to control result size",
      "Add ORDER BY for consistent result ordering"
    ]
  }
}

Validate Query

Validate a query without executing it.

POST /api/v1/projects/{project_id}/queries/validate

Request Body

Same as execute query endpoint.

Response

{
  "valid": true,
  "generated_sql": "SELECT customer_id, SUM(amount)...",
  "datasets_required": ["ds_1234567890"],
  "estimated_rows": 10,
  "estimated_time_ms": 250,
  "warnings": [],
  "errors": []
}

Query Parameters

Queries can include parameters for dynamic execution:

{
  "query": "Show me sales for {{customer_name}} in {{date_range}}",
  "parameters": {
    "customer_name": "Acme Corp",
    "date_range": "last 30 days"
  }
}

Error Responses

400 Bad Request

{
  "error": {
    "code": "INVALID_QUERY",
    "message": "Query syntax error",
    "details": {
      "line": 3,
      "column": 15,
      "message": "Column 'custmer_id' not found. Did you mean 'customer_id'?"
    }
  }
}

408 Request Timeout

{
  "error": {
    "code": "QUERY_TIMEOUT",
    "message": "Query execution exceeded timeout",
    "details": {
      "timeout_seconds": 30,
      "execution_time_seconds": 31.5,
      "suggestion": "Try adding filters to reduce data scanned"
    }
  }
}

Webhooks

Query events can trigger webhooks:

query.started
query.completed
query.failed
query.saved

See the Webhooks documentation for details.

Best Practices

Use Natural Language: Let the AI understand intent rather than writing SQL
Specify Datasets: Limit queries to specific datasets for better performance
Cache Results: Enable caching for frequently run queries
Set Timeouts: Always specify reasonable timeouts
Use Parameters: Make queries reusable with parameters
Stream Large Results: Use streaming for queries returning many rows

Rate Limits

Query execution: 100 per minute per user
Saved queries: 1000 per project
Concurrent queries: 5 per user

See Rate Limiting for more details.

On this page