MCP server

• 1: Installing the MCP server RPM
• 2: MCP server environment variables
• 3: Claude Desktop example
• 4: Curl examples
• 5: Query profiling
• 6: Machine learning with the MCP server
• 6.1: ML tools reference

$ /opt/vertica/bin/vertica_mcp_server --generate-token
Missing required parameters for non-interactive mode (--userid and --dbpass).
Falling back to interactive token generation...

═══════════════════════════════════════════════════════════════
              JWT Token Generator for MCP Server
                (with LevelDB Storage & VCluster)
═══════════════════════════════════════════════════════════════

Enter User ID: mcp_admin
Enter Database User: dbadmin
Enter Database Password:
Enter Expiration (days from now, default 365):
Enter Description (optional):
Enter VCluster Roles (comma-separated, allowed: 'admin', 'operator', 'viewer'; optional): admin
Generating JWT token...

✓ Token generated successfully!

Token Details:
  User ID:      mcp_admin
  DB User:      dbadmin
  Expires:      2027-06-09 06:03:12 EDT
  Valid Days:   365 days
  Roles:        [admin]
  Storage:      /opt/vertica/config/mcp_server/userdb

JWT Token:
<JWT_TOKEN>

You can now use this token in your MCP client requests:
  curl -k -X POST https://localhost:8667/mcp \
    -H "Content-Type: application/json" \
    -H "X-Api-Key: <JWT_TOKEN>" \
    -d '{...}'

$ /opt/vertica/bin/vertica_mcp_server --generate-token \
>   --userid my_mcp_viewer \
>   --dbuser mydbuser \
>   --dbpass mypassword \
>   --description "my viewer" \
>   --roles "viewer"

✓ Token generated successfully!

Token Details:
  User ID:      my_mcp_viewer
  DB User:      mydbuser
  Expires:      2027-06-09 10:35:38 EDT
  Valid Days:   365 days
  Roles:        [viewer]
  Storage:      /opt/vertica/config/mcp_server/userdb

JWT Token:
<JWT_TOKEN>

You can now use this token in your MCP client requests:
  curl -k -X POST https://localhost:8667/mcp \
    -H "Content-Type: application/json" \
    -H "X-Api-Key: <JWT_TOKEN>" \
    -d '{...}'

1 - Installing the MCP server RPM

The Vertica MCP server RPM cannot be installed on a host that has Vertica installed, whether Vertica is running or not. Install the MCP server on a dedicated host outside the Vertica cluster. An existing Vertica database cluster is not required at RPM installation time, but you must specify the required connection information before the MCP server can connect to the database.

Upgrading from a bundled MCP server to a standalone MCP server

Vertica version 26.2 and earlier bundle the MCP server with the database. To upgrade to the 26.3 standalone MCP server from an earlier release:

1. Upgrade the Vertica cluster to version 26.2.0-1.
2. On a host that does not have Vertica installed, install the MCP server RPM by following the steps described here.

Installing the MCP server

1. As root or with sudo, install the vertica-mcp-server RPM:

   MCP_OWNER_USER=mcpuser rpm -Uvh --replacepkgs --ignoresize mcp_server_rpm
   

   where mcp_server_rpm is the RPM path and filename, for example /tmp/vertica-mcp-server-26.3.0-0.x86_64.rpm.

   MCP_OWNER_USER sets the operating system user that owns the MCP server installation. The default value is mcpuser. Use --owner-user to specify a different user.

   On success, you should see the following output:

   Installed MCP binary to /opt/vertica/bin/vertica_mcp_server
   Ensured log directory exists at /opt/vertica/log
   Set ownership to mcpuser:mcpuser
   

   The installation process runs a compatibility check with the Vertica cluster. The check is skipped if the cluster is not reachable or the cluster URL is not provided at installation time.

2. Confirm the installed RPM version:

   rpm -qa vertica-mcp-server
   

   The output shows the installed version, for example:

   vertica-mcp-server-26.3.0-0.x86_64
   

3. Switch to the MCP user:

   su - mcpuser
   

   Caution

   You must switch to the MCP user before starting the MCP server for the first time. Starting the server as root sets incorrect permissions on /opt/vertica/config and /opt/vertica/log. If you start the server as root, you must manually correct permissions on those directories before the MCP user can start the server.

4. View the contents of the MCP server configuration file:

   cat /opt/vertica/config/mcp_server.yaml
   

   The file contains default values after a fresh installation, for example:

   # MCP Server Configuration File
   # This file was auto-generated with default values
   #
   # Configuration priority (highest to lowest):
   # 1. Environment variables (MCP_*)
   # 2. This YAML file
   # 3. Built-in defaults
   #
   # For environment variable overrides, use:
   # MCP_SERVER_ADDR, MCP_LOG_DIR, MCP_SSL_BASE_PATH, etc.
   #
   
   server_addr: :8667
   read_timeout: 15s
   write_timeout: 1m40s
   idle_timeout: 1m0s
   shutdown_timeout: 30s
   max_header_bytes: 1048576
   ssl_base_path: /opt/vertica/config/mcp_server
   ssl_cert_path: /opt/vertica/config/mcp_server/server.pem
   ssl_key_path: /opt/vertica/config/mcp_server/server.key
   ca_cert_path: /opt/vertica/config/mcp_server/ca.pem
   ca_key_path: /opt/vertica/config/mcp_server/ca.key
   use_pg_client: false
   vertica_host: vnode1
   vertica_port: "5433"
   vertica_dbname: ""
   vertica_sslmode: require
   query_timeout: 20s
   max_query_rows: 10000
   storage_type: leveldb
   storage_path: /opt/mcp_storage/userdb
   vcluster_enabled: true
   vcluster_server_url: https://vnode1:8665
   vcluster_cert_path: /opt/vertica/config/vcluster_server/admin.pem
   vcluster_key_path: /opt/vertica/config/vcluster_server/admin.key
   vcluster_ca_cert_path: /opt/vertica/config/vcluster_server/ca.pem
   vcluster_skip_tls_verify: true
   vcluster_nodes_cache_ttl: 5m0s
   log_dir: /opt/vertica/log
   max_active_jobs: 5
   max_running_jobs: 3
   max_finished_jobs: 50
   finished_job_retention: 240h0m0s
   query_tree_retention: 8760h0m0s
   external_url: ""
   node_host_overrides:
       192.168.1.101: vnode1
       192.168.1.102: vnode2
       192.168.1.103: vnode3
       192.168.1.104: vnode4
   job_queue_path: /opt/mcp_storage/job_queue
   

5. Edit the following fields in mcp_server.yaml:

   vi /opt/vertica/config/mcp_server.yaml
   

   • vertica_host: The hostname or IP address of the Vertica database server.
   • vertica_dbname: The name of the Vertica database to connect to.
   • vcluster_server_url: The URL of the VCluster Web Service, for example https://<vcluster_server_node>:8665.

   Note

   If you are upgrading from a previous bundled MCP server deployment, you can use your existing mcp_server.yaml config file instead of manually editing the default values.

6. Copy the VCluster server SSL certificate files to the MCP server host.

   scp -r <vcluster-server-node>:/opt/vertica/config/vcluster_server/admin.pem <mcp-server-node>:/opt/vertica/config/vcluster_server/
   scp -r <vcluster-server-node>:/opt/vertica/config/vcluster_server/admin.key <mcp-server-node>:/opt/vertica/config/vcluster_server/
   scp -r <vcluster-server-node>:/opt/vertica/config/vcluster_server/ca.pem <mcp-server-node>:/opt/vertica/config/vcluster_server/
   

7. Start the MCP server:

   /opt/vertica/bin/manage_mcp_server.sh start mcp_server
   

   On success, you should see output similar to the following:

   Doing action start
   Starting MCP server
   Started MCP server with PID 874791
   MCP server startup verified successfully
   

   Verify that the MCP server binary exists at /opt/vertica/bin/vertica_mcp_server and certificate files exist at /opt/vertica/config/mcp_server.

8. Generate a JWT token for the MCP server. For more information about JWT token generation options, see MCP server.

   /opt/vertica/bin/vertica_mcp_server --generate-token --userid user_id --dbpass password
   

9. Copy the JWT token string from the output of the previous command.

10. Update your AI agent configuration with the JWT token and the MCP server URL. The steps required depend on the type of agent you are using.

    For an example using Claude Desktop, see Claude Desktop example.

    The MCP server URL has the following format: https://<mcp_server_node>:8667/mcp

The AI agent can now connect to the database and query data.

Starting and stopping the MCP server

Switch to the MCP user and run the following command to start the MCP server:

/opt/vertica/bin/manage_mcp_server.sh start mcp_server

To stop the MCP server:

/opt/vertica/bin/manage_mcp_server.sh stop mcp_server

Uninstalling the MCP server

To uninstall the MCP server:

rpm -e vertica-mcp-server

To verify uninstallation:

rpm -qa | grep -i vertica-mcp-server

After uninstallation, the MCP server binary is removed from /opt/vertica/bin. The configuration .yaml file and log files are preserved.

2 - MCP server environment variables

The Vertica MCP server is configured through a YAML configuration file and environment variables, with the following order of precedence:

1. Environment variables
2. YAML configuration file (default path: /opt/vertica/config/mcp_server.yaml)
3. Built-in defaults

The following sections list the available environment variables and their corresponding YAML keys, grouped by area: server, TLS/SSL, database, storage and secrets, VCluster server, and job queue settings.

Server settings

These variables control the HTTP server's network and request-handling behavior.

TLS/SSL settings

The MCP server requires TLS. If it does not find a certificate/key pair at startup, it automatically generates a self-signed CA and server certificate.

Database settings

These variables configure the connection to the Vertica database.

Storage and secrets

These variables control how user credentials are stored and internal secrets are managed.

VCluster server settings

These variables configure integration with VCluster server, which provides cluster-management tools.

Job queue settings

These variables tune the server's internal asynchronous job queue for long-running operations.

3 - Claude Desktop example

This page provides examples for configuring the Vertica MCP server with Claude Desktop.

Configuring MCP server with Claude Desktop

Claude Desktop can connect to your MCP server to enable database query capabilities within Claude conversations.

Prerequisites

• Claude Desktop installed (latest version).
• MCP server running and accessible.
• Valid JWT token for authentication.
• Certificates to access VCluster server from the database administrator. For database adminstrators, the default certificates are located at /opt/vertica/config/vcluster_server/.
• NPX tool mcp-remote (npm install -g mcp-remote).

Configuration

The Claude Desktop configuration file location varies by OS:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json
• Linux: ~/.config/Claude/claude_desktop_config.json

Edit the configuration file and add your MCP server. Example configuration:

{
  "mcpServers": {
    "vertica-mcp-server": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "https://<mcp_server_node>/mcp",
        "--insecure",
        "--tls-skip-verify",
        "--cert",
        "<path_to_certificate>\\admin.pem",
        "--key",
        "<path_to_key_file>\\admin.key",
        "--ca-cert".
        "<path_to_ca_cert>\\ca.pem",
        "--header",
        "X-API-Key:${API_KEY_VALUE}"
        ],
      "env": {
        "NODE_TLS_REJECT_UNAUTHORIZED": "0",
        "API_KEY_VALUE": "replace with your API key"
      }
    }
  }
}

where

<path_to_certificate> is the path to the admin.pem file, such as "C:\Users\username\vcluster_server\admin.pem". <path_to_key_file> is the path to the admin.key file, such as "C:\Users\username\vcluster_server\admin.key". <path_to_ca_cert> is the path to the ca.pem file, such as "C:\Users\username\vcluster_server\ca.pem".

Verifying the configuration

• Restart Claude Desktop after saving the configuration (make sure kill all background processes)
• Check Claude's MCP status - look for your server in the MCP panel
• Test with a simple query:

  SELECT version()
  

4 - Curl examples

This page provides curl examples for testing and automating interactions with the Vertica MCP server. This is provided as a developer guide for interacting with the APIs.

Using the MCP server with curl

For testing and automation, you can interact with the MCP server directly using curl.

Basic request format

All MCP requests use JSON-RPC 2.0 format:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: YOUR_JWT_TOKEN" \
  -d '{
    "jsonrpc": "2.0",
    "method": "METHOD_NAME",
    "params": {
      "key": "value"
    },
    "id": 1
  }'

Initialize connection

First, initialize the MCP session:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "initialize",
    "params": {
      "protocolVersion": "2024-11-05",
      "capabilities": {
        "roots": {
          "listChanged": true
        }
      },
      "clientInfo": {
        "name": "curl-client",
        "version": "1.0.0"
      }
    },
    "id": 1
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "protocolVersion": "2024-11-05",
    "serverInfo": {
      "name": "vertica-mcp-server",
      "version": "1.0.0"
    },
    "capabilities": {
      "tools": {},
      "resources": {},
      "prompts": {}
    }
  }
}

List available tools

Discover what tools are available:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/list",
    "params": {},
    "id": 2
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 2,
  "result": {
    "tools": [
      {
        "name": "execute_query",
        "description": "Execute SQL queries against Vertica database",
        "inputSchema": {
          "type": "object",
          "properties": {
            "query": {
              "type": "string",
              "description": "SQL query to execute"
            },
            "subcluster": {
              "type": "string",
              "description": "Optional subcluster name"
            }
          },
          "required": ["query"]
        }
      },
      {
        "name": "submit_queue_query",
        "description": "Submit long-running query to job queue"
      },
      {
        "name": "list_jobs",
        "description": "List all jobs in the queue"
      }
    ]
  }
}

Execute a query

Run a simple SQL query:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "execute_query",
      "arguments": {
        "query": "SELECT version()"
      }
    },
    "id": 3
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 3,
  "result": {
    "content": [
      {
        "type": "text",
        "text": "{\"columns\":[\"version\"],\"rows\":[{\"version\":\"Vertica Analytic Database v24.3.0-0\"}],\"count\":1}"
      }
    ]
  }
}

Execute query with subcluster routing

Route query to a specific subcluster:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "execute_query",
      "arguments": {
        "query": "SELECT node_name, subcluster_name FROM nodes",
        "subcluster": "analytics_cluster"
      }
    },
    "id": 4
  }'

Submit long-running query to queue

For complex analytics queries that take longer to complete:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "submit_queue_query",
      "arguments": {
        "query": "SELECT customer_id, SUM(amount) FROM sales GROUP BY customer_id",
        "description": "Monthly sales by customer"
      }
    },
    "id": 5
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 5,
  "result": {
    "content": [
      {
        "type": "text",
        "text": "Job submitted successfully. Job ID: 550e8400-e29b-41d4-a716-446655440000"
      }
    ]
  }
}

Check job status

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "get_job",
      "arguments": {
        "job_id": "550e8400-e29b-41d4-a716-446655440000"
      }
    },
    "id": 6
  }'

List all jobs

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "list_jobs",
      "arguments": {}
    },
    "id": 7
  }'

Cancel a job

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "cancel_job",
      "arguments": {
        "job_id": "550e8400-e29b-41d4-a716-446655440000"
      }
    },
    "id": 8
  }'

Health check

Check server health without authentication:

curl -k https://localhost:8667/health

Response:

{
  "status": "healthy",
  "total_pools": 3,
  "max_pools": 100
}

Using jq for pretty output

Format JSON responses with jq:

curl -k -X POST https://localhost:8667/mcp \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  -d '{
    "jsonrpc": "2.0",
    "method": "tools/call",
    "params": {
      "name": "execute_query",
      "arguments": {
        "query": "SELECT table_name FROM tables LIMIT 5"
      }
    },
    "id": 1
  }' | jq '.'

Bash script example

Create a reusable script for queries:

#!/bin/bash
# mcp_query.sh

API_KEY="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
SERVER="https://localhost:8667/mcp"
QUERY="$1"

if [ -z "$QUERY" ]; then
    echo "Usage: $0 'SQL QUERY'"
    exit 1
fi

curl -k -X POST "$SERVER" \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: $API_KEY" \
  -d "{
    \"jsonrpc\": \"2.0\",
    \"method\": \"tools/call\",
    \"params\": {
      \"name\": \"execute_query\",
      \"arguments\": {
        \"query\": \"$QUERY\"
      }
    },
    \"id\": 1
  }" | jq -r '.result.content[0].text'

Usage:

chmod +x mcp_query.sh
./mcp_query.sh "SELECT COUNT(*) FROM customers"

5 - Query profiling

The Query Profiling feature in MCP server enables deep analysis of SQL queries, visualization of execution plans, and export of detailed query profile data. These tools help you understand query performance, resource usage, and execution flow across Vertica nodes and subclusters.

Key features

• Profile storage in tables: Save query profiles in persistent tables within a specified schema, or use temporary tables for ad-hoc analysis.
• Large Language Model (LLM)-driven analysis: Analyze queries using the MCP server's LLM, which processes operator statistics and performance aggregates (such as execution time and resource usage) computed by profiling tools.
• Interactive plan tree visualization: Visualize query plan trees, including data flow volumes and execution times per node, with interactive HTML output.
• Export/import profile data: Export all profile tables as a compressed tarball (.tar.gz) for sharing or offline analysis, and import them into other clusters or tools.
• Subcluster and sandbox support: Profile query execution on specific subclusters or sandboxes by routing requests to the desired subcluster or sandbox.
• Prompt support: Configure and execute analysis prompts with flexible input parameters to tailor profiling to your specific needs.

Profiling workflows

Query profiling supports two distinct workflows:

• Profile and analyze using prompts – A guided, step-by-step approach where the LLM orchestrates the entire workflow, including job monitoring and analysis generation.
• Direct tool access – Ask the LLM to use specific profiling tools directly without the built-in prompts, giving you fine-grained control over each step.

Both approaches produce the same profiling results that can be visualized, exported, and imported. Choose the approach that best fits your workflow.

Profile and analyze a query using prompts

Configure prompt inputs

1. In the LLM, click Add Connectors, select Add from vertica-mcp-server, and then click Analyze Query.

2. To begin profiling a SQL query, enter the required prompt details and click Add prompt:

   

   • Mode: Set to new to execute and profile a new query, or existing to analyze a previously executed query by providing its transaction ID and statement ID.
   • Query: The query you want to profile (for example, select avg(price) from sales;).
   • Target_schema: The schema where results will be stored (for example, qprof1). A new schema is automatically created if it does not already exist.
   • Key_id: A unique identifier for this profiling run (for example, k1). Profile table names have this ID appended (for example, qprof_query_profiles_k1).
   • Transaction_id: The transaction id for an existing query.
   • Statement_id: The statement id for an existing query.
   • User_instructions: Additional instructions to customize the analysis.
   • Analysis_scope: Specify the depth of analysis (basic or detailed). Defaults is basic.
   • Subcluster: The subcluster where the query should be executed.
   • Sandbox: The sandbox where the query should be executed. If both subcluster and sandbox are specified, sandbox is ignored.

   Note

   • Transaction ID and Statement ID: Provide Transaction_id and Statement_id to profile an already-executed query, or provide Query with Mode=new to execute and profile a new query. These fields are required when using Mode=existing, and should be left empty when using Mode=new.
   • Key ID: If you leave the Key ID blank for mode existing, the system auto-generates a timestamp-based identifier. Using an explicit Key ID makes it easier to reference and retrieve your profiling results later.

3. Click analyze_query_text in the chat to view the analysis workflow and tools the LLM will use.

Review workflow steps

The analysis workflow displays the following steps:

• Execute query: The qprof_execute tool runs the query with profiling enabled.
• Generate job ID: A job_id is created to track the asynchronous execution.
• Monitor completion: The LLM checks the job status once. If still running, it pauses and waits for your confirmation before checking again to prevent infinite loops.

Execute, monitor, and review analysis

When processing your analysis, the LLM performs these steps:

• Submit and monitor: The job is submitted and checked for completion. If still running, the LLM pauses and requests confirmation before checking again.
• Generate summary: Once the job completes, the LLM produces a comprehensive profile analysis, including performance assessment, key metrics (duration, success, queue wait time, nodes involved), bottleneck analysis, and optimization recommendations.

Profile storage

Profile data persists in the specified database schema and tables.

Analyze query profiles directly

Alternatively, you can ask the LLM to use specific profiling tools without the built-in prompts. This approach gives you direct control and works independently of the prompt-based workflow.

To use these tools, ask the LLM directly in the chat. For example, "Analyze this query profile using qprof_get_operator_stats" or "Get a summary of my query execution".

You can also analyze existing query profiles saved from previous runs. Provide the schema and key ID where the profile data is stored. For example: "Give me a summary of the query profile that is saved in the schema xyz with key slow_query". This is useful for analyzing historical query executions without re-executing them.

Available tools:

• qprof_get_profile_summary: Get a high-level summary of the query execution, including duration, status, and resource usage.
• qprof_get_events: Retrieve warnings, optimization hints, and execution issues.
• qprof_get_resources: Examine resource pool acquisition details and queue wait times.
• qprof_get_steps: See a detailed breakdown of execution phases and timing.
• qprof_get_operator_stats: Analyze operator-level performance metrics (for example, slowest operators, memory usage).
• qprof_get_path_stats: View aggregated statistics at the query plan path level.
• qprof_get_plan: Get the raw EXPLAIN plan for the query.
• qprof_get_plan_tree: Retrieve the complete query plan tree with structure, metrics, and operator details. This is best suited for LLM or programmatic analysis.

Each tool provides a focused view or set of metrics to help you understand and troubleshoot query performance.

Visualize, export, and import results

The following capabilities work with both profiling approaches above. Use them to interact with and share your profiling data.

Visualize query profile tree

You can visualize the query execution tree by asking the LLM to generate an interactive visualization:

1. Ask the LLM to visualize the query profile tree. Optionally, provide your MCP server IP and port.
2. The LLM generates an HTML file saved on the MCP server and provides a link to view it. To access the link, ensure you have network access to the MCP server's IP address and port (accounting for any port mappings in your environment).
3. Open the provided link in a browser to view the visualization.
4. The visualization tree displays all the details of the query profile. You can zoom in and out and interact with the query tree to analyze query execution.

Export query profile data

You can export all profile tables as a compressed tarball (.tar.gz) for sharing or further analysis.

1. Ask the LLM to export the profiling data.
2. The system generates the profile export details and a download link for the profile tarball (.tar.gz).
3. Open the link in a browser to download the tarball (.tar.gz) to share or analyze offline.

Import and analyze query profiles

You can import profile data from other clusters or tools for analysis in your current environment. You can also import a tarball (.tar.gz) into VCluster using Query Profile and use the target schema and key ID to analyze using the MCP Server Query Profiling tools. For more information about Query Profile in the VCluster UI, see Query Profile.

6 - Machine learning with the MCP server

The MCP server extends beyond SQL execution by providing integrated machine learning (ML) capabilities that support end-to-end data science workflows directly within the database. Using MCP tools and natural-language interaction, you can explore and prepare data, perform train-test splits, train and evaluate models, generate predictions, and interpret results without moving data out of Vertica or writing external code.

This in-database approach eliminates traditional ML pipeline complexity and preserves security and governance, while allowing you to iterate quickly through conversational, AI-assisted workflows. The integrated ML tools make advanced analytics accessible to both experienced data scientists and users with limited machine learning expertise, allowing them to build predictive models and derive actionable insights using simple natural-language prompts.

For users familiar with VerticaPy, the MCP server provides a no-code alternative for accessing similar in-database ML functionality without relying on VerticaPy or its associated dependencies.

For a complete reference of every ML tool, its input schema, and outputs, see ML tools reference.

Prerequisites

Before you run ML workflows, make sure the following requirements are met:

• The MCP server is running and your AI client is connected. For installation, startup, JWT token generation, and role and privilege requirements, see MCP server.
• Install the MachineLearningLib UDx library and the approximate package on the target database. These are required to run the ML tools. For installation steps, see install_packages.
• The ML tools rely on in-database machine learning functions, which are available in both Enterprise Mode and Eon Mode. Some tools have additional version requirements. For example, ml_correlation_matrix uses CORR_MATRIX, which requires version 9.2.1 or later.

ML tools overview

The MCP server provides a comprehensive set of tools that support the complete machine learning lifecycle, from data preparation through model governance. These tools can be combined to build end-to-end ML workflows using natural language.

Data preparation and feature engineering

Exploratory data analysis

Model training

Dimensionality reduction

Inference

Model evaluation

Model registry and governance

Run an ML workflow using prompts

Use the guided Vertica ML Workflow prompt when you want the MCP server to plan and run a complete pipeline for you. You supply a few input fields, such as the algorithm, table, and target, and the MCP server orchestrates the remaining steps, from preprocessing through evaluation. For an illustration of the alternative conversational approach, where you drive each stage with individual natural-language requests, see the customer churn example in the next section.

Configure prompt inputs

1. In the LLM, click Connectors, select Add from vertica-mcp-server, and then click Vertica ML Workflow.

2. To begin with the ML workflow, enter the required prompt details and click Add prompt:

   

   

   • Algorithm: Required. The machine learning algorithm to execute (for example, logistic_reg, rf_classifier, linear_reg, kmeans, pca, xgb_classifier, arima).
   • Model_name: Override the auto-generated model name for saving in the Vertica catalog (for example, my_model). Auto-generated if omitted.
   • Table: Source table or view name containing the dataset (for example, iris or public.iris).
   • Target: Target or label column name for supervised learning (for example, Species). Omit for unsupervised algorithms like kmeans or pca.
   • Features: Predictor columns as a comma-separated list (for example, SepalLengthCm, SepalWidthCm, PetalLengthCm, PetalWidthCm).
   • Params: Algorithm hyperparameters as a JSON string with string values only (for example, {"max_iterations": "200"}).
   • Schema: Default schema for unqualified table names (for example, public).
   • Context: Additional instructions or execution flags to customize the workflow (for example, mode=cv, do not use temp table, use permanent table).
   • Subcluster: The subcluster where tool calls should be routed.
   • Sandbox: The sandbox where tool calls should be routed. If both subcluster and sandbox are specified, sandbox is ignored.

3. Click Vertica ML Workflow_text in the chat to view the ML workflow and tools the LLM will use.

Review workflow steps

Before making any tool calls, the LLM presents the plan it will follow. This workflow text includes:

• Workflow configuration: The exact values used when filling tool arguments, such as the target column and the training algorithm.
• Naming conventions: How generated artifacts are named, including the auto-generated model name, one-hot encoder and label-mapping tables, train/test split tables, and normalizer model.
• NULL/unknown value policy: How missing inputs and unseen categories are handled for label encoding, one-hot encoding, and boolean columns.
• Workflow steps: The ordered steps the LLM will run (for example, detect outliers, encode columns, normalize, split, train, and evaluate). Steps marked optional can be skipped when not applicable.

The LLM replies with a short plan first—one bullet per step, noting any skipped optional steps and the reason, and waits for your confirmation before it executes.

Execute, monitor, and review analysis

After you confirm the plan, the LLM executes the workflow. For each numbered step, it either runs the appropriate ML tool or skips the step when it does not apply to your data or algorithm, and it shows the reason for each decision.

• Execute and monitor: The LLM runs each step in order, for example, the train/test split, model training, prediction, and evaluation. Heavy jobs run asynchronously, so the LLM polls each job with get_job_status and retrieves output with get_job_results, reporting progress as each stage completes.
• Generate final summary: Once all steps finish, the LLM produces a final summary that reports the model name and location, the dataset and train/test split, evaluation metrics and feature importance along with a brief interpretation and any refinement recommendations.

As the LLM works through the plan, it reports each step and whether it ran or skipped it:

When all steps finish, the LLM compiles the results into a final summary:

The summary includes the model's performance metrics and confusion matrix:

It also reports the feature importance scores for the trained model:

End-to-end ML workflow: customer churn prediction

The following example demonstrates a complete customer churn analysis workflow using a simulated telecommunications dataset containing 1.2 million customer records and an approximate churn rate of 25%. The dataset includes intentionally introduced missing values and outliers to illustrate common data quality challenges encountered during real-world analysis.

Unlike the guided Vertica ML Workflow prompt, this example illustrates the conversational approach, where you drive each stage with individual natural-language requests. It uses a simulated dataset to show the kinds of prompts you can use and how the MCP server responds at each step.

The churn analysis follows a standard data science workflow:

1. Data exploration and cleaning
2. Pattern discovery and feature engineering
3. Data preparation
4. Model training
5. Model evaluation and interpretation

The MCP server automatically identifies and invokes the appropriate ML tools based on your requests throughout the workflow.

Step 1: Explore and clean data

The analysis begins by examining the dataset structure and reviewing sample records.

To start, ask the MCP server "Describe the churn_data table and show me a few sample rows."

It describes the table and displays example rows so you can understand available attributes, data types, and overall data quality.

Once you understand the data, address any quality issues. Ask "Find and impute any missing values in churn_data." The MCP server identifies missing values and automatically applies appropriate imputation strategies:

• Numeric columns are typically filled using statistical methods such as mean imputation.
• Categorical columns are filled using the most frequently occurring value (mode).

In this example, missing values in Monthly Charges were replaced with the column mean, while missing values in Payment Method were replaced with the most common payment type.

Step 2: Detect outliers and analyze relationships

After addressing missing data, ask "Check the numeric columns in churn_data for outliers."

Using the ml_detect_outliers tool, the MCP server highlights anomalous values and quantifies their impact on the dataset, allowing you to decide whether to investigate, remove, or retain these records.

In this example, it accurately identified the 1,133 outliers that were artificially created in the dataset.

Next, ask "Show me a correlation matrix for the numeric columns in churn_data."

The MCP server generates the matrix, visualizes the relationships between numeric variables, and identifies key drivers associated with churn.

In the churn analysis example, the strongest relationships with customer churn were found in:

• Number of support calls (0.29)
• Monthly charges (0.21)
• Customer tenure (0.20)

These insights provide an initial understanding of the factors influencing customer retention.

Step 3: Feature engineering and data preparation

Machine learning models require numerical inputs. Categorical attributes such as Contract Type, Payment Method, and Internet Service must be encoded into model-ready numerical values. Ask "Encode the categorical columns in churn_data."

The MCP server automatically identifies categorical columns and performs the required encoding, transforming categories into numeric representations suitable for training.

The server can also normalize numeric features using standard scaling techniques, such as z-score normalization, ensuring that features operate on a comparable scale. This improves model performance and helps prevent bias toward variables with larger numerical ranges.

In this example, the tools successfully converted the numeric column to center around 0.

After preprocessing is complete, ask "Split churn_data into 70% training and 30% test sets."

In this example, the MCP server automatically selected a standard 70% training and 30% test split ratio for model development and validation.

Step 4: Train a machine learning model

After preparing the dataset, ask "Train a logistic regression model on the training data to predict churn." The MCP server trains a logistic regression model to predict customer churn.

During training, the server automatically generates feature importance information, providing immediate insight into which variables have the greatest influence on customer behavior.

Example findings included:

These feature importance metrics help explain model behavior and identify the most influential business factors.

Step 5: Evaluate the model

Ask "Evaluate the model on the test data and show me the classification metrics."

The trained model is evaluated against the testing dataset to measure predictive performance. In this example, the model achieved approximately 80% prediction accuracy despite the presence of simulated noise, missing values, and outliers.

The MCP server provides standard evaluation metrics, including:

• Confusion matrix
• Overall accuracy
• Precision
• Recall
• F1 score
• AUC (ROC)

These metrics enable you to assess model quality and determine whether additional refinement is required.

Interpret results and generate business insights

After evaluation, ask "Summarize the main drivers of churn and generate an executive summary." The MCP server summarizes the findings and identifies the primary drivers of customer churn.

Key conclusions from the churn analysis included:

• Contract type was the strongest predictor of churn.
• Customers with two-year contracts were significantly less likely to leave.
• A high volume of support calls was a strong warning indicator of churn risk.
• Higher monthly charges increased churn probability.
• Longer customer tenure reduced churn likelihood.

To make results easier to consume, you can ask the MCP server to generate a business-friendly executive summary. The server produces a visual report that highlights:

• Key churn drivers
• Feature importance rankings
• Recommended actions
• Business-focused interpretations

This allows technical findings to be communicated effectively to business stakeholders and decision-makers.

Benefits of ML integration in the MCP Server

The machine learning tools integrated into the MCP server provide several advantages:

• Enables end-to-end ML workflows through natural language interactions.
• Eliminates dependency on VerticaPy for common ML tasks.
• Supports automated data exploration, cleansing, and preparation.
• Simplifies model training and evaluation.
• Generates interpretable insights and executive-ready summaries.
• Makes advanced analytics accessible to users with limited data science expertise.

6.1 - ML tools reference

The machine learning (ML) tools work with data stored in the Vertica database and cover the full workflow from preparing data, exploring it, training models, generating predictions, and evaluating results to managing model versions through a governed registry.

Every tool operates on tables identified as table or schema.table. If no schema is specified, the default public schema is used. Most tools also accept optional sandbox and subcluster parameters to route the request to a specific node group.

Execution modes

Each tool runs in one of two modes:

• Synchronous: The result is returned immediately in the tool response.
• Asynchronous: A job_id is returned immediately. Use get_job_status(job_id) to poll for completion, and then get_job_results(job_id) to retrieve the output.

Tool categories

• Data preparation: Tools for splitting, cleaning, encoding, normalizing, and transforming data before model training.
• Exploratory data analysis: Tools for understanding data characteristics, relationships, and quality issues prior to modeling.
• Model training: Tools for training supervised, unsupervised, and time-series models directly inside the database.
• Prediction: Tools for scoring new data using a trained or registered model.
• Model evaluation: Tools for measuring model quality and understanding what drives predictions.
• Model registry and governance: Tools for registering trained models under a versioned family name and managing their lifecycle status.

Data preparation

Tools for splitting, cleaning, encoding, normalizing, and transforming data before model training.

ml_train_test_split

Splits a source table into training and testing tables using a seeded random partition. Both splits run concurrently as independent background jobs.

Asynchronous. Returns job_id values immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_impute

Imputes (fills) missing NULL values in a table using mean, mode, forward-fill, backward-fill, or an automatic method.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_encode_columns

Encodes categorical columns into numeric representations using one-hot or label encoding, persisting the encoder artifacts for reuse on new data.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_apply_encoding

Applies a previously fit encoder or label mapping to a new source table (for example, test or inference data), producing the same schema as the training-side encoding.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_normalize_fit

Computes and persists normalization parameters (min-max, z-score, or robust scaling) as a reusable model, optionally materializing a normalized view.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_apply_normalize

Applies a previously persisted normalization model to a source table, using the same scaling that was computed at fit time.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_normalize

Normalizes numeric columns using min-max, z-score, or robust scaling and exposes the result as a view, without persisting a reusable model.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_svd

Performs Singular Value Decomposition (SVD) on a numeric data matrix and persists the result as a reusable model.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_apply_svd

Applies a previously computed SVD model to a new data matrix, using the same feature extraction that was computed at fit time.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_pca

Fits a PCA (Principal Component Analysis) model on a table and saves it in the Vertica model catalog for subsequent use with ml_apply_pca.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_apply_pca

Transforms data using a fitted PCA model and writes the principal component coordinates to an output table.

Synchronous. Returns the result immediately.

Input schema:

Output:

Exploratory data analysis

Tools for understanding data characteristics, relationships, and quality issues prior to modeling.

ml_correlation_matrix

Computes the pairwise correlation matrix for numeric columns in a table, useful for exploratory analysis and feature selection.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_detect_outliers

Detects outliers in one or more numeric columns using statistical thresholds (z-score, robust z-score, or IQR).

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

Model training

Tools for training supervised, unsupervised, and time-series models directly inside the database.

ml_train_model

Trains Vertica in-database machine learning model, covering classification, regression, clustering, decomposition, and anomaly-detection algorithms.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_train_timeseries

Trains a time-series model (ARIMA, autoregressor, or moving average) on a table of observed values.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_cross_validate

Evaluates an ML algorithm using k-fold cross-validation, optionally with hyperparameter grid search, for a statistically robust performance estimate without a separate test set.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

To get the averaged metric results, run get_model_attribute on the saved model.

Prediction

Tools for scoring new data using a trained or registered model.

ml_predict

Runs predictions using a trained Vertica ML model, producing a new table with scored results.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

ml_predict_with_registered_model

Runs predictions using a registered model family name rather than a raw model name, resolving to the production version unless a specific version is requested.

Synchronous. Returns the result immediately.

Input schema:

Output:

Model evaluation

Tools for measuring model quality and understanding what drives predictions.

ml_classification_report

Computes classification evaluation metrics: accuracy, precision, recall, F1 score, and optionally AUC.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

Retrieved with get_job_results:

ml_regression_report

Computes regression evaluation metrics such as MAE, MSE, RMSE, R-squared, adjusted R-squared, AIC, and BIC.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

Retrieved with get_job_results:

ml_features_importance

Computes normalized feature importance scores from a trained model, for model interpretation and variable selection.

Asynchronous. Returns job_id immediately. Retrieve results with get_job_results(job_id) once the status is completed.

Input schema:

Output:

Retrieved with get_job_results:

Model registry and governance

Tools for registering trained models under a versioned family name and managing their lifecycle status.

ml_register_model

Registers an existing native Vertica model under a versioned, governed model family name.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_list_registered_models

Lists registered Vertica models, including their versions, statuses, and backing native model names.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_get_production_model

Resolves the current production version of a registered model family, used when no explicit version is supplied at prediction time.

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_change_model_status

Changes the lifecycle status of a registered model version (for example, promote to production, move to staging, archive, or decline).

Synchronous. Returns the result immediately.

Input schema:

Output:

ml_get_model_status_history

Fetches the status-change history for a registered model, showing how it moved through lifecycle states over time.

Synchronous. Returns the result immediately.

Input schema:

Output: