Unified Console

• 1: Install Unified Console
• 2: Validate MC and UC installation
• 3: Import the CA certificate
• 4: Configure the UC port
• 5: Connect to Unified Console
• 6: Get started with Unified Console
• 7: Configure AI preferences
• 8: MCP Token generation and configuration
• 9: Configure the MCP client
• 10: Vertica AI Assist
• 11: SQL Editor
• 12: Upgrade Unified Console

1 - Install Unified Console

Management Console installation includes Unified Console by default. Unified Console requires no separate installation.

Overview of UC installation

This section explains how to install, set up, validate, connect to, begin using UC, and how to reinstall UC if required.

Set up and get started with UC

Complete the following tasks to ensure that UC is installed correctly and ready for use:

• Install MC
• Validate MC installation
• Import the CA certificate
• (Optional) Configure the UC port
• Connect to UC
• Get started with UC

Expected Result

You can access Management Console, open Unified Console successfully, and begin managing your environment.

Reinstall Unified Console

Reinstall UC only if UC is unavailable, corrupted, or not functioning correctly after installation or upgrade. Preserve your existing UC configuration before reinstalling UC.

When to Reinstall UC

• UC does not start after installation or upgrade.
• UC files are corrupted or missing.
• Troubleshooting steps do not resolve the issue.

Step 1: Back up the UC configuration

This step preserves your existing UC configuration so that you can restore it after reinstallation.

Run

cp -r /opt/vconsole/uc/config/dbconfigs /tmp/uc_backup

Expected result

The backup directory is created successfully and contains the UC configuration files.

Success: Configuration files are copied to /tmp/uc_backup.

Failure: The command returns a permission or file-not-found error.

Step 2: Reinstall MC

UC is installed as part of MC. Reinstall MC to reinstall UC. For information about installing MC, see Install MC.

Expected result

MC and UC are reinstalled successfully.

Success: Installation completes without errors.

Failure: Installation stops or reports errors.

Step 3: Restore the UC configuration

Restore the saved configuration after reinstallation completes.

Run:

cp -r /tmp/uc_backup/* /opt/vconsole/uc/config/dbconfigs/

Expected result

The previous UC configuration is restored.

Success: Configuration files are available in the UC configuration directory.

Failure: Restore command returns errors or configuration files are missing.

2 - Validate MC and UC installation

UC is installed along with MC. Validate your MC installation and verify that all required services are running correctly. Begin by identifying where MC is hosted and then perform the applicable validation steps for UC.

Validate Management Console and Unified Console Installation

After installing or upgrading Management Console, validate the deployment to ensure that both MC and UC are operating correctly and are accessible to users.

This validation procedure helps you confirm that:

• MC is accessible from a browser.
• UC loads successfully.
• Required services are running.
• Required certificates are trusted.
• Network connectivity is configured correctly.
• No installation or startup failures have occured.

Perform this validation:

• After a new installation
• After an upgrade
• After changing the UC port
• After updating CA certificates
• After troubleshooting connectivity issues
• After troubleshooting sign-in issues

Before you begin, identify whether your deployment is On-premises or Cloud.

Validation workflow

Perform validation in the following order:

1. Verify that users can access MC and UC.
2. Verify the installed MC version.
3. Verify that MC services are running.
4. Verify that UC processes are running.
5. Verify network connectivity.
6. Review logs only if any validation fails.

On-premises deployment

Step 1: Verify browser access

1. Open the MC URL in your browser: https://<MC_HOSTNAME>:8433
2. Verify the following:
   • The login page loads successfully.
   • The browser does not display certificate warnings.
   • You can sign in successfully.
   • UC opens after login.

If MC and UC are accessible, basic installation and service startup have completed successfully.

Step 2: Determine the installed MC version

The validation steps differ slightly between MC versions.

Run:

rpm -qa | grep vertica-console

Example output:

vertica-console-26.3.0-1.x86_6

Record the installed version before proceeding.

Step 3: Verify the MC service

Run:

sudo /opt/vconsole/shared/vertica-consoled status

Expected output:

Vertica Console: Running
status OK

Verify:

• Service status is displayed as Running.
• No startup or service errors are reported.

Step 4: Perform version-specific validation

MC version 26.2

Verify browser certificates

Confirm that all required CA certificates are imported into the browser.

Verify that:

• No certificate warnings appear.
• HTTPS connections are trusted.

Verify UC runtime files

Run:

ls -ltr /opt/vconsole/uc/run

Verify that:

• PID files exist.
• Runtime files are present.
• Runtime files are being updated.
• No stale lock files exist.

MC Version 26.3

Verify UC runtime components

Run:

ls -ltr /opt/vconsole/uc/run

Verify that:

• UC processes are running.
• PID files exist.
• Runtime files are present.

Cloud deployment

Step 1: Verify browser access

Open the MC URL.

https://<MC_HOSTNAME>:8433

Verify the following:

• The login page appears.
• Authentication succeeds.
• UC loads correctly.

Step 2: Determine the installed MC version

Run:

rpm -qa | grep -i console

Example output:

vertica-console-26.3.0-1.x86_64

Step 3: Verify the MC service

Run:

sudo /opt/vconsole/shared/vertica-consoled status

Expected output:

Vertica Console: Running
status OK

Verify:

• MC service is running.
• No startup errors are reported.

Step 4: Perform version-specific validation

MC version 26.2

Verify CA certificates

Confirm that the required CA certificates are imported into your browser.

Verify that:

• HTTPS connections are trusted.
• Certificate warnings do not appear.

Verify UC runtime files

Run:

ls -ltr /opt/vconsole/uc/run

Verify that:

• Runtime files exist.
• PID files are present.
• No startup failures are reported.

Verify security group configuration

Ensure that the cloud security group allows the following ports:

• TCP 8090
• TCP 8433

Verify connectivity

Run:

nc -zv <MC_HOSTNAME> 8090
nc -zv <MC_HOSTNAME> 8433

Expected result:

• Both connection tests succeed.

MC version 26.3

Verify UC runtime components

Run:

ls -ltr /opt/vconsole/uc/run

Verify that:

• UC processes are running.
• PID files exist.
• No startup failures are reported.

Verify security group configuration

Ensure that the cloud security group allows TCP 8433.

Verify connectivity

Run:

nc -zv <MC_HOSTNAME> 8433

Expected result:

• The connection test succeeds.

Troubleshooting installation validation failures

If any validation step fails, collect diagnostic information before contacting support.

Check MC service logs

Collect the recent MC service log entries:

journalctl -u vertica-consoled -n 200

Review:

• Service startup failures
• Port binding issues
• SSL errors
• Certificate validation errors

Locate UC log files

Find all UC log files:

find /opt/vconsole -name "*.log"

Common locations include:

• /opt/vconsole/log/
• /opt/vconsole/uc/logs/

Review:

• Startup failures
• Process crashes
• Certificate-related errors
• Connectivity errors

Verify endpoint reachability

Run one of the following commands:

telnet <MC_HOSTNAME> 8433
curl -vk https://<MC_HOSTNAME>:8433
nc -zv <MC_HOSTNAME> 8433

Expected result:

• The endpoint accepts incoming connections.

Validate a custom UC port configuration

By default, UC uses port 8433. If you configured UC to use a different port:

• Verify that the new port is reachable.
• Verify that firewall rules allow access to the configured port.
• Verify that cloud security groups allow access to the configured port.
• Confirm that users access MC using the updated port.

Example:

https://<MC_HOSTNAME>:<CUSTOM_PORT>

Verify connectivity:

nc -zv <MC_HOSTNAME> <CUSTOM_PORT>

Validation complete

The installation is considered successful when the following conditions are met:

• MC is accessible from a browser.
• Users can sign in successfully.
• Unified console loads correctly.
• MC service status is Running.
• The required UC processes are running.
• The required network ports are reachable.
• No startup failures or certificate-related errors are reported.

The MC and UC deployment are ready for use.

3 - Import the CA certificate

UC uses self-signed certificates by default. When you sign in to MC and switch to UC, the browser displays a security prompt. To avoid this prompt each time when switching to UC, import the CA certificates.

About CA certificate

Import a CA certificate to establish a trusted HTTPS connection between your web browser and UC. After you import the certificate, the browser can verify the identity of UC and securely connect without displaying certificate trust warnings.

What are CA certificates?

A Certificate Authority (CA) certificate is a digital certificate that establishes trust between a client and a server. UC uses HTTPS certificates that are signed by an internal CA certificate. Browsers do not automatically trust this internal CA because it is not issued by a public certificate authority.

By importing the UC CA certificate into your browser's trusted certificate store, you allow the browser to trust the certificates used by UC.

This CA certificate enables secure HTTPS communication between the browser and UC.

Why import the CA certificate?

Importing the CA certificate helps you:

• Establish a trusted HTTPS connection to UC.
• Eliminate browser certificate warnings when accessing UC.
• Verify the identity of the UC server.
• Protect communication between your browser and UC through encryption.

Before you begin

Verify that you have the following:

• Access to the UC host.
• Access to the UC certificate directory /opt/vconsole/uc/certs.
• The CA certificate file /opt/vconsole/uc/certs/ca.crt.
• Administrative permissions on the client machine where you plan to import the CA certificate.

How certificate trust works

UC generates an internal CA certificate and uses it to sign service certificates. Before your browser can trust UC, you must import the following certificate into the browser's trusted certificate store:

/opt/vconsole/uc/certs/ca.crt

You only need to import the CA certificate once on each client machine.

Configure Google Chrome

Import the UC CA certificate into the Windows Trusted Root Certification Authorities store that Google Chrome uses.

Step 1: Open the Certificate Manager

• Open Google Chrome.
• In the address bar, enter: chrome://certificate-manager/
• Select Local certificates.
• Click Manage imported certificates from Windows.

Step 2: Import the CA Certificate

• Select the Trusted Root Certification Authorities tab.
• Click Import.
• Click Browse.
• Select /opt/vconsole/uc/certs/ca.crt.
• Click Next.
• Select Place all certificates in the following store.
• Click Browse.
• Select Trusted Root Certification Authorities.
• Click OK.
• Click Next.
• Click Finish.

Verify:

• The import operation completes without errors.
• The certificate appears in the Trusted Root Certification Authorities store.
• You can open UC without browser trust warnings.

Success

• The UC login page loads normally.
• The browser displays a secure HTTPS connection.
• No certificate warning appears.

Failure

• The browser displays a certificate warning.
• The browser reports that the connection is not trusted.
• UC displays HTTPS security errors.

Configure Microsoft Edge

Step 1: Open the Certificate Manager

• Open Microsoft Edge.
• In the address bar, enter: edge://certificate-manager/
• Select Local certificates.
• Click Manage imported certificates from Windows.

Step 2: Import the CA certificate

• Click the Trusted Root Certification Authorities tab.
• Click Import.
• Click Browse.
• Select /opt/vconsole/uc/certs/ca.crt.
• Click Next.
• Select Place all certificates in the following store.
• Click Browse.
• Select Trusted Root Certification Authorities.
• Click OK.
• Click Next.
• Click Finish.

Verify:

• The import operation completes successfully.
• The certificate is listed in the Trusted Root Certification Authorities store.
• UC opens without browser trust warnings.

Success

• The UC login page loads successfully.
• The browser reports a secure connection.
• No trust or certificate warnings appear.

Failure

• The browser displays certificate trust warnings.
• HTTPS connection errors occur.
• UC does not open through HTTPS without user intervention.

Configure Mozilla Firefox

Mozilla Firefox maintains its own certificate store. You must import the CA certificate directly into Firefox.

Step 1: Open Certificate Settings

• Open Mozilla Firefox.
• In the address bar, type about:preferences#privacy.
• Click Settings and select Privacy and security.
• From the Connection and software security drop-down list, select Advanced settings.
• Scroll to Certificates.
• Click Manage Certificates.

Step 2: Import the CA certificate

• Select the Authorities tab.
• Click Import.
• Browse to /opt/vconsole/uc/certs/ca.crt.
• Click Open.
• Select Trust this CA to identify websites.
• Click OK.
• Click OK again to close the Certificate Manager.

Verify:

• The certificate appears in the Authorities list.
• Firefox connects to UC without certificate warnings.
• The UC login page opens successfully.

Success

• Firefox establishes a secure HTTPS connection to UC.
• No certificate trust warnings appear.
• The UC login page loads successfully.

Failure

• Firefox displays security warnings.
• Firefox reports that the issuer is not trusted.
• The UC login page does not load securely.

Replace the default CA certificate with a custom certificate

You can replace the default UC-generated CA certificate with a certificate issued by your organization's internal security team.

Organizations commonly use custom certificates to:

• Comply with corporate security policies.
• Use certificates issued by an approved internal CA.
• Standardize certificate management across systems.
• Eliminate the need to trust product-generated certificates separately.

File naming requirements

Use the same file names that UC expects. Replacing the default files while preserving the file names ensures that services continue to locate and use the certificates correctly.

Step 1: Back up existing certificates

Create a backup of the existing certificate files before making changes.

Step 2: Copy the custom certificates

Copy the replacement certificate files to /opt/vconsole/uc/certs.

Step 3: Restart UC services

Restart the services so that UC begins using the new certificates.

Run:

./scripts/service.sh start

Verify:

• Services start successfully.
• UC is accessible through HTTPS.
• The browser trusts the new certificate chain.

Success

• The UC login page loads successfully.
• The certificate details display the new issuer.
• HTTPS connections succeed.

Failure

• Services fail to start.
• The browser displays certificate errors.
• HTTPS connections fail.

Verify certificate configuration

After importing the certificate or replacing it with a custom certificate:

• Sign in to MC.
• Switch to UC.
• Verify that the browser establishes an HTTPS connection without warnings.

Success

• UC opens successfully.
• No trust warning appears.
• The browser indicates a secure connection.

Failure

• The browser displays certificate warnings.
• The browser reports an untrusted issuer.
• The browser cannot establish a secure connection.

CA certificate example

The UC certificate directory contains the following files:

/opt/vconsole/uc/certs
ca.crt
ca.key
bff.crt
bff.key
metadata.crt
metadata.key
sqlexec.crt
sqlexec.key
auth.crt
auth.key
ai.crt
ai.key
aiassist.crt
aiassist.key

Certificate architecture

• ca.crt is the root CA certificate used by UC.
• ca.key is the private key associated with the CA certificate.
• Service certificates such as bff.crt, auth.crt, and ai.crt are signed by ca.crt.
• All UC services rely on the same internal CA certificate.

Additional information

You can generate certificates by using generate-certs.sh. If UC runs on a cloud-hosted virtual machine, allow inbound traffic on the following ports:

• 5450
• 8433

These ports must be reachable for browser access and service communication.

4 - Configure the UC port

This section provides information about how you can configure the UC port.

About the UC port

A UC port is the network port that UC uses to accept incoming connections from web browsers. The UC port is defined by the port number in the mc.uchostname setting in the console.properties file. For example:

mc.uchostname=https://uc-host.example.com:8433

In this example:

• https://uc-host.example.com is the UC host name.
• 8433 is the UC port.
• UC accepts HTTPS connections on port 8433.

Configure the UC Port

Configure the UC port to ensure that UC can accept incoming connections without conflicting with other services running on the host.

Overview

Administrators configure the UC port during installation or when resolving network port conflicts. UC uses the port specified in the mc.uchostname setting in the console.properties file. After you configure the UC port and restart UC, users should be able to access UC through the configured URL.

Step 1: Locate the configuration file

This step identifies the file that controls the UC endpoint and listening port.

Why it matters UC reads this setting at startup.

Configuration file /opt/vconsole/config/console.properties

Step 2: Configure the UC port

Edit the mc.uchostname property and specify the required port number.

Example

mc.uchostname=https://<host>:8433

To change the port, update only the port number.

mc.uchostname=https://uc-host.example.com:8434

Expected result The URL contains the required host name and port number.

• The URL format remains valid.
• The UC port is unique and available.
• Only the UC port number is changed.

Step 3: Restart UC

Restart the UC service to apply the configuration change.

Success UC starts normally and the login page loads on the configured UC port.

Failure The browser displays connection refused, page unavailable errors, or the UC login page does not load.

Example UC port in mc.uchostname

mc.uchostname=https://<host>:8433

Valid UC port range

Provide only numeric identifiers for UC ports, as UC accepts only numeric values. UC rejects non-numeric values, negative values, values between 0 and 1023, and values above 49151.

Example errors

ERROR: port 80 in mc.uchostname is outside the usable range 1024-49151
ERROR: cannot start the service like this - refusing to start

Use different ports for MC and UC

Use separate ports for MC and UC because a port supports only one service at a time.

Example error

ERROR: UC BFF port 5450 conflicts with the MC port (console.port=5450)

Resolution Configure a unique port in mc.uchostname that differs from console.port and restart UC.

5 - Connect to Unified Console

This section provides information about how you can access the Unified Console from your environment.

Prerequisistes

1. Ensure that MC is installed and accessible.
2. Ensure that you have valid MC user credentials.
3. Use a supported web browser.

Connect to Unified Console

1. Open a supported web browser.

2. In the address bar, enter the URL of MC installation using either the host IP address or hostname, along with the configured MC port.

   • IP address - https://ip-address:mc-port/ (Example: https://10.20.30.40:5450/)

     Note

      By default, `mc-port` is 5450. Alternatively, you can also enter a customized port number.
     

   • Host name - https://hostname:mc-port/ (Example: https://hostname:5450/)

3. In the MC logon dialog, enter your username and password and click Log in.

   Note

    If you do not have an MC username/password, contact your administrator.
   

4. Click the user profile icon. The user account menu is displayed.

5. Select Switch to Unified Console. The Unified console is displayed.

6 - Get started with Unified Console

Unified Console (UC) introduces a centralized, AI-enabled experience for managing and interacting with Vertica databases.

UC UI tour

After logging in to MC and switching to UC, the home page appears. This page presents all accessible databases as interactive widgets. Both AI-enabled and non-AI-enabled databases are visible.

7 - Configure AI preferences

AI preferences allow you to configure the large language model (LLM), provider, connection details, access permissions, and MCP server in UC. These preferences determine how AI features are configured and made available to users and databases. This section provides information about how you can configure the AI preferences in UC.

Enable AI Assistance

The Enable AI Assistance banner appears only when none of the available databases are AI enabled. You can enable AI for a database by configuring an AI model. Click Enable in the banner to configure or edit the AI preferences. If at least one database in the system is AI enabled, this banner is not displayed.

Configure or edit AI preferences

You can configure or edit the AI preferences in UC to make AI features available to users.

1. Log in to UC.

2. Choose AI preferences under Settings in the home page. The AI preferences page opens and displays the following information:

   • Host - The host name.
   • Model - The large language model (LLM) that is used.
   • Creator - The MC role who created the LLM.
   • Enabled for - The database for which the LLM is enabled.
   • Status - The current status of the LLM.

3. Hover over ⋮ and select Edit. The following model details display:

   • Model provider
   • Model name
   • URL of the service hosting the model
   • Region
   • Secret access key
   • API key
   • Who should have access
   • Which databases can access this
   • MCP status

4. From the Model provider list, choose the AI model provider that creates, trains, maintains, and hosts AI models. You can choose from Anthropic, Google Gemini, and Amazon Bedrock.

5. From the Model name list, choose the specific LLM to use for inference.

6. In the URL of the service hosting the model field, enter the root network address used to send requests to an AI service's API. Use the endpoint URL provided in your selected AI provider's documentation or API configuration page.

7. In the Region field, choose the appropriate service region.

   Note

      This is applicable only if your provider is Amazon Bedrock.
   

8. In the Secret access key field, enter the credential (secret key) required for secure authentication.

9. In the API key field, enter the key used to authenticate API requests.

   Note

      If your provider is Amazon Bedrock, enter the **Access Key Id**. Enter the **API key** only if you are using a provider other than Amazon Bedrock.
   

10. In the Who should have access area, choose the required users depending on the role played by the user in the organization. For more information, see Configuration roles in MC.

11. In the Which databases can access this area, choose the databases that can access the LLM.

12. Click MCP status to view the MCP client configuration. For more information, see Configure the MCP client.

13. Click Save.

Add a model

You can add a model from the AI preferences.

1. Login to UC.
2. Choose AI preferences under Settings in the home page. The AI preferences page opens.
3. Click +Add new model. Follow steps 4-13 in Configure or edit AI preferences.

8 - MCP Token generation and configuration

Vertica AI Assist authenticates requests to the MCP server using Jason Web Tokens (JWTs). The system uses these tokens to authenticate all MCP requests during the session.

MCP token generation and configuration

This section helps administrators generate, configure, and validate MCP tokens for Vertica AI Assist.

Overview

MCP tokens are Jason Web Tokens (JWTs) used by Vertica AI Assist to authenticate requests to the MCP server.

Prerequisites

Before configuring tokens, perform these steps on a Vertica node:

• Create a database from VCluster UI.
• Start the MCP server. During its initial startup, the MCP server creates /opt/vertica/config/mcp_server.yaml with the default setting token_api_enabled: false.

Initial MCP administrator token setup

For security reasons, manually generate the first administrator token on a Vertica node. This ensures that only an authorized administrator can create and receive the initial token.

Step 1: Generate the initial administrator token

Run:

/opt/vertica/bin/mcp_server --generate-token 
--user <db_user> 
--dbname <database_name> 
--dbpass '<password>' 
--description "UMC Integration Token" 
--expiration-days <N>

Expected result:

User ID:      mcp_superuser
DB User:      release
Expires:      2027-06-05 02:34:43 EDT
Valid Days:   365 days
Roles:        [admin]
Storage:      /opt/vertica/config/mcp_server/userdb
JWT Token:    <generated JWT>

Store this JWT in a secure location. Vertica AI Assist uses it to generate user access tokens.

Enable the MCP token generation API

The token generation endpoint is disabled by default. To enable it:

1. Stop the MCP server.

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

2. Open /opt/vertica/config/mcp_server.yaml.
3. Set token_api_enabled to true.
4. Save the file.
5. Restart the MCP server.

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

After you enable the endpoint, Vertica AI Assist can request user-specific access tokens from the MCP server.

Configure the MCP administrator JWT

To generate user access tokens, configure the MCP administrator JWT for Vertica AI Assist.

1. Open /opt/vconsole/uc/config/dbconfigs/<cluster>-<database>.yaml.
2. Locate jwt_key.
3. Copy the generated administrator JWT to jwt_key.
4. Save the configuration file.

Token scoping and role-based access

Vertica AI Assist generates user-specific access tokens. Each generated token is scoped to the user's MC role, ensuring that users can perform only the operations they are authorized to perform.

MCP role capabilities

MCP roles and their capabilities are summarized in this table.

Role summary

The summary of MCP roles is given below.

• Admin
• Operator
• Viewer

Admin

Admin provides unrestricted MCP access, including the following:

• Cluster administration
• Security management
• Backup and restore
• Database administration
• User management
• All MCP tools

Operator

Operator provides operational capabilities, including the following:

• Cluster monitoring
• Start and stop the cluster
• Query management
• Job queue operations
• Performance troubleshooting

Operators cannot modify cluster configuration or manage users.

   Assign the `Operator` role only to trusted users as it allows cluster start and stop operations.

Viewer

Viewer provides read-only access to monitoring information, including the following:

• Database schemas
• User sessions
• System metrics
• Cluster status

Viewers cannot perform administrative or operational changes.

Token lifecycle

Vertica AI Assist automatically manages user access tokens throughout the user session.

• User tokens remain valid for 1 day.
• Tokens are automatically refreshed while the session is active.
• Tokens are permanently deleted when the user logs out.
• No manual token management is required.

Permission enforcement

Vertica AI Assist enforces the permissions associated with the MC role of the authenticated user. If a user requests an action that exceeds their assigned permissions, Vertica AI Assist:

• Rejects the request.
• Informs the user that they do not have the required permissions to perform the action.
• Does not execute any unauthorized action.

This behavior helps ensure that users can perform only the actions permitted by their assigned role.

Compatibility with earlier Vertica versions

Per-user access tokens are available in Vertica 26.3 and later versions. When Vertica AI Assist connects to an earlier Vertica version, it automatically uses a compatible authentication method. For Vertica versions earlier than 26.3:

• The user token generation endpoint is not available.
• Vertica AI Assist automatically detects the Vertica version.
• Vertica AI Assist uses a shared administrator connection for compatibility.
• No additional configuration is required.

This fallback behavior allows Vertica AI Assist to operate with earlier Vertica versions and automatically use per-user access tokens in Vertica 26.3 and later.

9 - Configure the MCP client

This section provides information about how you can configure the MCP client to enable secure communication between Vertica AI Assist and the Vertica environment.

About the MCP client

The MCP client is a secure bridge that connects Vertica AI Assist to Vertica ensuring that database credentials are never exposed to the LLM. It provides:

• A secure, controlled interface between Vertica AI Assist and Vertica.
• Support for standardized database operations such as query execution, schema retrieval, and EXPLAIN plans.
• Authentication using JWT tokens, along with connection pooling and secure handling of user credentials.

Pre-requisites

Before configuring the MCP client, ensure that the MCP server is installed and configured. For information about installing and configuring the MCP server, see MCP server.

Configure the MCP client

Configuring the MCP client enables Vertica AI Assist to securely access databases and perform operations such as natural language to SQL (NL2SQL) conversion, query optimization, and query explanation. The configuration process establishes:

• Secure AI-driven database access
• Support for multiple database connections
• Schema-aware SQL generation
• Authenticated and auditable operations After configuring the MCP serve, use the configuration script to supply the required connection details.

Configuration script

Configure the MCP client using the mcp_configure.sh script located in /opt/vconsole/uc/scripts. This script prompts for the required inputs and generates a YAML configuration file (a human-readable format for configuration data) in config/dbconfigs/. The MCP client uses this file to establish a connection with the MCP server for AI operations.

Gather the following information before you run the configuration script:

1. JWT Token — Authentication token for the MCP server. Generate this token on the Vertica host where the MCP server is installed. For information about obtaining JWT tokens, see MCP server.
2. MCP Server Hostname or IP address — The system on which the MCP server is running. Identify the host by checking the node that is running MCP.
3. MCP Port — The port used by the MCP server. The default port is 8667.
4. Cluster and Database Information — Obtain this information from the MC View infrastructure page:
   • MC cluster name
   • Vertica database name

Run the configuration script

1. Navigate to the scripts directory.

   cd /opt/vconsole/uc/scripts
   

   Note

      Run the configuration script only from this directory.
   

2. Run the configuration script.

   ./mcp_configure.sh
   

   The configuration script prompts you to enter the JWT token, MCP server hostname or IP address, MCP port, cluster, and database information.
3. Do one of the following:
   • Yes - The configuration script generates a configuration file named <cluster_name>_<db_name>_conf.yaml.
   • No - The configuration script prompts you to re-enter the MCP configuration details.
   • Exit - The configuration script exits without creating a configuration file.

Enable AI for other databases by repeating these steps for each database.

Post configuration

After completing the configuration:

1. Restart the AI service.
2. Verify connectivity by checking the log file logs/ai.log.
3. Look for the message MCP connected.

View the MCP server status

To view the MCP server status:

1. Choose AI preferences under Settings in the home page. The AI preferences page opens and displays the following information:
   • Host - The host name.
   • Model - The large language model (LLM) that is used.
   • Creator - The person who created the LLM.
   • Enabled for - The database for which the LLM is enabled.
   • Status - The current status of the LLM.
2. Hover over ⋮ and select Edit. All the details of the LLM are displayed. The MCP server status can be one of the following:

• Active - Indicates successful connection between the MCP server and the Vertica database.
• Inactive - Indicates when the connection could not be established due to errors. The MCP server might be inactive due to one of these reasons:
  • Invalid Token
  • Token Expired
  • MCP Server is not active
• Not supported - Indicates that the Vertica database version is below 26.1, which is not supported by MCP.

Troubleshooting MCP server in inactive status

The MCP server can be in Inactive status due to one of the following issues:

1. Missing or invalid configuration

   The YAML configuration file present in config/dbconfigs/ may be missing or contain incorrect cluster or database details. Re-run the configuration script with correct values.

2. Expired or invalid JWT token

   The token may have expired or been entered incorrectly. Generate a new token on the Vertica server and run the script with the new token generated.

3. MCP server not running

   Ensure that the MCP server is running.

This configuration ensures that Vertica AI Assist can safely interact with your database environment while maintaining enterprise-grade security, traceability, and performance.

10 - Vertica AI Assist

Vertica AI Assist is a natural language interface that enables you to interact with your database using AI capabilities. It helps you generate, execute, and optimize SQL queries without requiring advanced knowledge of SQL.

With Vertica AI Assist, you can:

• Reduce the time spent writing repetitive SQL queries.
• Generate, execute, and optimize SQL queries.
• Improve productivity when working with databases across teams.

Key capabilities

• Explain plan visualization: Automatically renders graphical representations of optimized explain plans, mapping complex operators and costs in an intuitive UI.
• Metadata-driven optimization: Enhances backend intelligence by incorporating real-time Vertica metadata—such as table statistics, projections, and resource usage—directly into optimization prompts for greater accuracy.
• Standardized optimization output: Delivers consistent, structured analysis comparing query performance before and after optimization, including clear rationales for every step.
• LLM-based optimization descriptions: Generates clear, human-readable summaries and tooltips, making technical optimization data accessible for all skill levels. Refines, enhances, and standardizes schema, table, and column descriptions with preferred LLMs to improve data discoverability and increase the accuracy of queries and insights generated.

Rather than manually interpreting execution plans, reviewing profiling data, or writing complex queries from scratch, you can ask Vertica AI Assist to analyze, explain, recommend, and generate content using plain-language prompts.AI-generated responses may vary in accuracy. Review all recommendations before applying them to production environments. The following models are supported:

• Anthropic Claude
• Google Gemini
• AWS Bedrock

Prerequisites

Configure the MCP client. Configuring the MCP client enables Vertica AI Assist to access databases and perform operations such as natural language to SQL (NL2SQL) conversion, query optimization, and query explanation. For information about configuring the MCP client, see Configure the MCP client.

Get started

To get started with Vertica AI Assist:

1. In the SQL editor, do one of the following:
   • Enter a SQL query or press Ctrl + I keys to generate a query using AI. <--or-->
   • Click Vertica AI Assist at the top. The list of available LLMs is displayed below the Vertica AI Assist label.
2. From the list of LLMs, choose an LLM.
   If you are using Vertica AI Assist for the first time, a legal disclaimer is displayed.
3. Choose Don't show this message again and click Agree and continue. A message Hi! I'm Vertica AI Assist. How can I help you today? is displayed.
4. Type your request in the field and click Enter. Vertica AI Assist analyzes and displays the results of your query. For example, enter Select * from nodes. Vertica AI Assist analyzes this query and displays the results.

Work with Vertica AI Assist

Depending on your objective, you can view or analyze data, ask Vertica AI Assist to generate SQL, analyze queries, explain execution behavior, ask questions and obtain answers related to Vertica, or create documentation.

Generate SQL queries

Use Vertica AI Assist when you know the information you need but don't want to write the SQL query yourself. For example, to find the top 10 customers by revenue in the last quarter, describe the request in natural language, and Vertica AI Assist generates the SQL query for you.

Example prompts

Use these sample prompts to generate SQL queries with Vertica AI Assist.

• Generate a sql query for duplicate emails in the users table.
• Show the top 10 customers by revenue this month.
• Count failed jobs by day for the last 30 days.
• Show daily active users for the last 14 days grouped by region.

Results

Vertica AI Assist generates:

• Ready-to-run SQL statements
• Suggestions for filters, sorting, grouping, and limits
• Alternative query approaches when applicable

Best practices

Provide as much context as possible, including:

• Table names
• Schema names
• Date ranges
• Filter criteria
• Sorting requirements

Example:

Analyze and Optimize SQL queries

Use Vertica AI Assist to understand query performance and identify opportunities for improvement.

Example prompts

Use these sample prompts to optimize SQL queries with Vertica AI Assist.

• Why is this query slow?
• Suggest a faster version of this query.
• What can I do to improve this SQL statement?

Optimization analysis

When Vertica AI Assist analyzes a query, it generates optimization notes that summarize:

• Query execution characteristics
• Potential bottlenecks
• Performance observations
• Recommended improvements

Recommendations may include:

• Query rewrites
• Improved filtering strategies
• Statistics-related suggestions
• Projection optimization guidance
• Reduced data retrieval through column selection

For example, replacing SELECT * FROM sales; with an explicit column such as SELECT REGION FROM sales reduces unnecessary data retrieval when only specific columns are required.

Vertica AI Assist optimizes all SQL queries except DDL and DML queries.

To optimize your SQL queries:

1. In the SQL editor, do one of the following:
   • Enter a SQL query or press Ctrl + I keys to generate a SQL query with AI. <--or-->
   • Click Vertica AI Assist at the top. The list of available LLMs is displayed below the Vertica AI Assist label.
2. Select the query.
3. Hover over the options and choose Optimize with AI. The final optimized query with improvements and the summary of the optimization are displayed in the Vertica AI Assist window. Vertica AI Assist optimizes the SQL query and processes the query results quicker than the original query specified.

Applying recommendations

1. Review the suggested improvements.
2. Determine whether they meet your business requirements.
3. Modify the query if appropriate.
4. Re-run the query and compare performance results.

Understand query execution

Vertica AI Assist can analyze query execution information and explain how Vertica processes a query.

Example prompts

Use these sample prompts to analyze query execution information in Vertica AI Assist.

• Explain this plan in simple terms.
• Which join is expensive here?
• Where is most of the execution time spent?

Explain queries with AI

You can get detailed information about an SQL query using the Explain with AI option.

1. In the SQL editor, do one of the following:

   • Enter a SQL query or press Ctrl + I keys to generate a SQL query with AI. <--or-->
   • Click Vertica AI Assist at the top. The list of available LLMs is displayed below the Vertica AI Assist label.

2. Select the query.

3. Hover over the options and choose Explain with AI. Review these insights:

   • Execution plan
   • Joins and functions
   • Performance characteristics
   • Optimization recommendations

   Note

       Vertica AI Assist explains and provides insights for all SQL queries except DDL and DML queries.
   

Execution insights

Vertica AI Assist explains:

• Tables and views accessed
• Join operations
• Query complexity
• Statistics availability
• Parallel execution
• Resource utilization
• Internal projections used

Key observations

The response includes a summary of important findings, such as:

• Join efficiency
• Statistics status
• Resource consumption
• Query complexity
• Potential performance risks

These observations help identify if additional investigation or optimization may be beneficial.

Review profile summary results

When optimization analysis is performed, Vertica AI Assist presents a Profile Summary that compares the current query profile with an optimized profile. The Profile Summary helps determine whether recommended changes are likely to improve performance.

Comparing profiles

Review the following columns:

• Current Value: Metrics from the original query execution
• Optimized Value: Predicted metrics for the recommended query

The available metrics are summarized in this table.

    The format of the available metrics can vary as LLM responses are not consistent.

Interpreting results

Note the following:

• Lower Duration values indicate faster execution.
• Lower Cost values indicate a potentially more efficient execution plan.
• Lower Queue Time values indicate reduced wait time for resources.
• Changes in node usage may indicate different workload distribution across the cluster.

If the current and optimized metrics are nearly identical, the query may already be efficiently optimized.

Download profile summary data

You can download profile summary data for offline analysis.

1. Review the profile summary.
2. Click download.tar.gz.
3. Download the generated .tar.gz archive. The archive contains profile-related data that can be shared with database administrators, support personnel, or performance engineering teams.

Visualize query execution profiles

Vertica AI Assist can generate graphical visualizations of query execution profiles to help you understand performance characteristics.

Create a visualization

1. Click Visualize in the Profile Summary section.
2. Vertica AI Assist generates an HTML-based visualization.
3. Open the provided link.
4. Review the execution tree interactively.

Visualization features

The visualization displays:

• Query execution tree structure
• Data flow between operators
• Execution times
• Node-level details
• Performance distribution throughout the query

You can zoom, pan, and interact with the generated execution tree to analyze query behavior in greater detail.

The MCP server generates the visualization files and makes them available through a link. Ensure you have network access to the MCP server location.

Learn Vertica concepts and best practices

Vertica AI Assist can answer product and architecture questions about Vertica.

Example prompts

Use these sample prompts to learn Vertica concepts and best practices.

• What is a projection?
• When should I partition a table?
• How should I design for large joins?
• What are the benefits of segmentation?

Results

Vertica AI Assist provides:

• Concept explanations
• Practical examples
• Architecture guidance
• Recommended best practices

This capability provides quick access to product information for both new and experienced Vertica users.

Generate table and schema documentation

Vertica AI Assist can create documentation for database objects.

Example prompts

Use these sample prompts to generate table and schema documentation.

• Describe this table for new team members.
• Generate column descriptions for this schema.
• Create documentation for this database.

Results

Vertica AI Assist generates:

• Business-friendly table descriptions
• Column-level explanations
• Schema summaries

Improve documentation with file uploads

When supported by the interface, you can upload reference materials to provide additional context. Supported file types include:

• PDF
• DOCX
• CSV
• XLSX

Providing supporting documents can improve the accuracy and completeness of generated descriptions and documentation.

Privacy and consent

Certain actions may require confirmation before data is processed. If prompted, do one of the following:

• Select Yes to continue.
• Select No to cancel the action. Your organization's privacy and governance policies apply to all AI-assisted interactions.

Session management

Starting a new session provides a fresh context for subsequent requests. Note the following:

• Conversations remain within the current session.
• You can start a new session at any time.

Troubleshooting common issues

If the generated results do not meet your expectations:

• Use more specific prompts.
• Include object names, schemas, and filters.
• Specify date ranges and business context.
• Ask follow-up questions such as:
  • Rewrite this query.
  • Simplify this explanation.
  • Make this query faster.
• Contact your administrator if you encounter configuration, permission, or connectivity issues.

Prompt writing guidelines

The quality of an AI response is influenced by several prompt parameters. Providing the right context helps Vertica AI Assist generate more accurate SQL, recommendations, explanations, and documentation. The most important ones are:

1. Clarity

   State exactly what you want. Avoid ambiguous or vague instructions. Example: Summarize this document in 5 bullet points for database administrators.

2. Context

   Provide relevant background information. Include domain, audience, and purpose. Example: This guide is for new Vertica customers with no database experience.

3. Task definition

   Clearly specify the desired action. Examples: Summarize, explain, compare, rewrite, analyze, generate, and so on.

4. Role or persona

   Specify the role for the model. Example: "Act as a database administrator."

5. Output format

   Specify the structure of the response. Examples: Bullet list, table, JSON, step-by-step procedure, and so on

6. Audience

   Identify the intended audience. Examples: Beginners, administrators, developers, data analysts, executives, and so on.

7. Constraints

   Set limits on length, style, terminology, or scope. Examples: Summarize in 100 words, avoid technical jargon, and so on.

8. Examples

   Provide examples to improve consistency and accuracy. The model learns the expected style and format from the examples.

9. Specificity

   More specific prompts generally produce better results. Example: Instead of "Explain Kubernetes", consider using a strong prompt such as "Explain Kubernetes networking to a Linux administrator in 200 words."

10. Success criteria

    Define what a good answer looks like. Example: "Include benefits, limitations, and one real-world example."

11 - SQL Editor

Use the SQL editor to write, edit, and execute SQL statements within the Unified Console without using any external tools.

Key features

Use the SQL editor to:

• Execute analytical queries.
• Save and reuse queries.
• View query results and execution details.
• Validate query execution.
• Access schema information.

Working with SQL editor

After logging in to the UC application, the home page appears. To launch the SQL editor, select > in the database widget.

Run an SQL query

You run SQL queries:

1. Click + in the SQL editor. The New query tab opens.

2. Enter your query in the SQL editor.

3. Select one of the following options:

   • Click Run to run the SQL query.
   • Click Run selection to run only the selected SQL query. This option is helpful when you have multiple SQL queries but need to run only one SQL query.
   • Click Format query to arrange and format the SQL query for better readability.

4. Hover over the SQL query and click one of the following icons:

   • Click Run to run the SQL query.
   • Click Optimize with AI to display the Vertica AI Assist window.
   • Click Format SQL to arrange and format the SQL query for better readability.
   • Click Explain with AI. This returns a formatted description of the database optimizer's plan for executing the specified statement. By default, the output represents the query plan as a hierarchy, where each level or path represents a single database operation that the optimizer uses to execute a query.

5. In the Enter query name field at the top, enter the name of the query.

6. To save the SQL query results, do the following: a. Select Export CSV. b. Enter the file name and select Export.

   In the SQL editor, you can view upto a maximum of 10 thousand records only.

Query plans

When you submit a query, the query optimizer quickly chooses the projections to use, optimizes and plans the query execution, and logs the SQL statement to its log. This planning results in an query plan, which maps out the steps the query performs. A query plan is a sequence of step-like paths that the database cost-based query optimizer uses to execute queries. The database can produce different query plans for a given query. For each query plan, the query optimizer evaluates the data to be queried: number of rows, column statistics such as number of distinct values (cardinality), distribution of data across nodes. It also evaluates available resources such as CPUs and network topology, and other environment factors.

Query plan view options

These are the options for displaying query plans:

• Path information
• Profile analysis
• Drill down
• Tree view

In the SQL query results page, select Path information from the list at the bottom of the page. This view displays the query plan path. You can expand or collapse the view to see different levels of detail. For information about Explain-related query plans, see EXPLAIN-Generated query plans.

In the Query results page, select Tree view from the list at the bottom of the page. The Tree Path view details the query plan in the form of a tree. The tree view does not contain any metrics because the query has not yet executed. The results appear in the form of a tree with a root and connected branches in the form of parent-child relationship.

Profile analysis data

In the Query results page, select Profile analysis from the list at the bottom of the page. After you profile an SQL query, the Explain page displays profile data and metrics in a pie chart. Hover over the slices on the pie chart or over the names of the phases to get additional information. You can see the approximate number of milliseconds (ms) and percentage used during each phase.

Profile metrics

After running an SQL query, in the Query results page, select Path information from the list at the bottom of the page. In the Path Information view, the area to the right of each query path contains profile metrics for that path.

• Disk—Bytes of data accessed from the disk by each query path. If none of the query paths accessed the disk data, all the values are 0.
• Memory—Bytes of data accessed from memory by each query path.
• Sent—Bytes of data sent across the cluster by each query path.
• Received—Bytes of data received across the cluster by each query path.
• Time—Time taken for execution of the SQL query in milliseconds (ms).

Hover over the progress bars to view more information, such as total bytes and percentages.

History view

The History tab stores the queries run previously. When you run a query, it appears in the Query History list. Queries of the last 2 weeks only are displayed.

To run a SQL query:

1. Click ….
2. Select Run.

To remove a SQL query:

1. Click ….
2. Select Remove.

Favorite view

You can bookmark SQL queries and run them again later in the Favorites tab. Saved SQL queries display as a list of favorites next to the History tab.

To bookmark a SQL query as a favorite:

1. Select the SQL query.
2. Select Run.
3. Select Favorite.
4. Enter a name and select Add.

To run a SQL query from the Favorites view:

1. Select ….
2. Select Run. After successful execution of the query, the results are displayed in the SQL editor.

To rename a favorite SQL query:

1. Select the SQL query.
2. Click ….
3. Select Rename.
4. Enter the new name and select Update.

To remove a favorite SQL query:

1. Select the query.
2. Click ….
3. Select Remove.
4. When prompted for confirmation, select Remove.

Upload and download scripts

You can create SQL queries offline and upload them to the system.

1. In the SQL query page, select ⋮ and Upload script. The Upload script dialog appears.
2. Select Browse or +.
3. Select Upload.

   Note

      All uploaded code or content is the sole responsibility of the uploading party. Vertica does not review, endorse, or accept any liability for uploaded materials. Uploads are made at the uploading party's own risk.
   

   The file content is loaded to the SQL editor.

You can also download SQL queries in the system.

1. In the SQL query page, select ⋮ and Download script. The SQL query is downloaded in a file with .sql extension in the Downloads folder.

Create a table

You can create tables in the database logical schema.

1. In the Schema view, do one of the following:

   • Select +Create table.

     OR

   • Expand a schema in the Schema view and click + for Tables. The Create table page appears.

2. In the Table name field, enter the name of the table to create. This name must be unique among names of all sequences, tables, and projections within the schema. Ensure that the table name meets the following requirements:

   • Must comply with keyword restrictions and rules for Identifiers.
   • Cannot begin with v_; this prefix is reserved for system tables.

3. From the Schema list, select an existing schema for which the table needs to be created. If no schema is specified, the table is created in the public schema.

4. (Optional) Click Generate Descriptions to generate column descriptions using AI. For information about generating descriptions, see Generate descriptions with AI.

5. (Optional) Click +Add column to add columns to the table.

6. In the Column name field, enter the name of the column to be created. A table can have up to 9800 columns.

   Note

      Provide unique column names for all columns. You cannot provide the same column name for multiple columns in the table.
   

7. From the Type list, select an internal data type.

8. In the Length field, enter the maximum number of characters that the column can store. It is a crucial aspect of database design that helps enforce data integrity, optimize storage, and ensure data consistency. For more information about the data types and their corresponding length or size, see data type.

   Note

      Provide a valid number. The system does not consider invalid numbers or characters.
   

9. Select the Nullable option to permit this column to store NULL values, representing missing, unknown, or optional data. Selecting this option allows rows to be inserted without providing a value for that specific column.

10. Select the Primary option to designate this column as the primary key. The primary key is a column in a database table that uniquely identifies each row, ensuring data integrity by prohibiting duplicate or NULL values. It enables efficient data retrieval and acts as a target for foreign keys to establish table relationships. Every table must have one primary key.

11. Select the Unique option to specify a unique constraint on a column so that each value in that column is unique among all other values.

12. Select the Order by option to sort the result set of a query in a specific order based on the values in one or more columns. This allows you to control the presentation of the data for better analysis and readability, as database rows are not guaranteed to be in any specific order by default.

13. Select More. The Advance configuration page appears. You can set the foreign key references to link one table with another in this page. This page is enabled only when you enter the column name.

14. In the Reference tab, do the following:

    • From the Reference schema list, select a reference schema which is a specification that defines the structure, data types, and relationships for data, often including mechanisms to link to or reuse other schemas or subschemas.
    • From the Reference table list, select a table that links to the primary key of another table. It ensures that the data in both tables stays connected and valid. It acts as a primary source for validating input, mapping values, and normalizing data, allowing other tables to reference it through foreign keys. If you omit this column, the database uses the table's primary key. Only those columns that have at least one primary key in the reference table are listed.
    • From the Reference column list, select a reference column. Updates to the referenced data are automatically reflected in all locations. Primary key columns only are listed.

      Note

         When linking a new table with an existing table, ensure that the data types of the primary key and foreign key match. A mismatch in the data types of the primary key and foreign key results in an error.
      

15. In the Sequence tab, from the Sequence list, select the name of the sequence. Use sequences to set the default values of columns to sequential integer values.

16. In the Column constraints tab, enter the boolean expression that evaluates a column's value on each row in the Check constraintsfield.

17. Select Save.

18. To import data from a csv file, do the following:

    • Select Import from CSV.
    • Select Browse.
    • Navigate to the folder containing the CSV file and click Open.

      Note

         Only the header rows in the CSV file are saved as column names. The system does not infer data types from the data in the CSV file. All uploaded code or content is the sole responsibility of the uploading party. Vertica does not review, endorse, or accept any liability for uploaded materials. Uploads are made at the uploading party's own risk.
      

    • Select Save.

19. (Optional) In the Search column names field, enter the column name that you need to search.

20. (Optional) Click Generate Descriptions to generate table descriptions using AI. For information about generating descriptions, see Generate descriptions with AI.

21. Select SQL preview. A real-time preview of the SQL statement is displayed. The SQL syntax is validated and errors are displayed. This helps you identify errors and fix them before running the SQL queries.

22. Click the Download icon to download the SQL query to a file with .sql extension.

23. In the Partition expression field, specify a partition key that is derived from one or more table columns. Creating partitions decreases the SQL query execution time and improves table efficiency. For information about partitioning tables, see Partition clause.

24. Select Create. The table is created and displayed under the schema where you created it.

Flex tables

Flex tables are a different kind of database table designed for loading and querying unstructured data, also called semi-structured data. Flex tables allow you to load data with different schemas into a single table. You can then explore this data and materialize real columns from it.

If you know some of the columns in the data, you can define them. A flex table that also has real columns is called a hybrid table. Both flex and hybrid tables are fully supported tables, stored as projections and with the same K-safety as your database.

After you create a flex table, you can quickly load data without specifying its schema. This allows you to load arbitrary JSON data, log files, and other semi-structured data and immediately begin querying it. For more information about flex tables, see Flex tables and Understanding flex tables.

Create a flex table

1. Expand a schema in the Schema view and click + for Flex tables. The Create Flex Table page appears.
2. In the Flex table name field, enter the name of the flex table.
3. From the Schema list, select an existing schema for which the flex table needs to be created. If no schema is specified, the flex table is created in the public schema. Follow steps 4-24 in Create a table.
4. Select Create. The flex table is created and displayed under the schema where you created it.

Views

A view is a stored query that comprises one or more SELECT statements. Views dynamically access and compute data from the database at execution time. Views are read-only and can reference any combination of tables, temporary tables, and other views. Views do not support insert, update, delete, or copy operations. For more information about views, see Views.

Create a view

1. Expand a schema in the Schema view and click + for Views. The Create view page appears. Do the following:
2. Review and edit the viewname.
3. Enter the SELECT statement that the view executes. The SELECT statement can reference tables and other views.
4. Format and execute the view. See Create an SQL query.
5. Click the Reload icon to refresh the schema. The new view appears in the Views folder.

Example

The following example shows how you can create a view that contains data from multiple tables.

=> CREATE VIEW temp_t0 AS SELECT * from t0_p1 UNION ALL
     SELECT * from t0_p2 UNION ALL
       SELECT * from t0_p3 UNION ALL
         SELECT * from t0_p4 UNION ALL
           SELECT * from t0_p5;

Create a schema

You can create as many schemas as necessary for your database. For example, you can create a schema for each database user. By default, only an administrator can create a schema or give a user the right to create a schema.

The database logical schema comprises a set of tables and referential integrity constraints in the database. The objects in the logical schema are visible to SQL users. The logical schema does not include projections, which make up the physical schema. The physical schema comprises a set of projections used to store data on the disk. The projections in the physical schema are based on the objects in the logical schema.

1. In the Schema view, expand the +Create table list and select Schema. The Create schema page appears.

2. In the Schema name field, enter the name of the schema. Ensure that the schema name meets the following requirements:

   • Must be unique among all other schema names in the namespace under which the schema is created.
   • Must comply with keyword restrictions and rules for Identifiers.
   • Cannot begin with v_; this prefix is reserved for system tables.
   • If the schema name contains a period, the part before the period cannot be the same as the name of an existing namespace.

   Inheritance of privileges specifies whether to enable or disable default inheritance of privileges for new tables in the schema.

3. (Optional) Do one of the following:

   • In the Description field, enter a brief description of the schema.

   OR

   • Click Generate Descriptions with AI to generate descriptions for the schema using AI. For information about generating descriptions, see Generate descriptions with AI.

4. Do one of the following:

   • Select Include to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.

   OR

   • Select Exclude to disable inheritance of schema privileges. By default, inheritance of schema privileges is disabled.

5. Select Create. The schema is created and displayed in the Schema view.

Generate descriptions with AI

You can use AI to automatically generate descriptions for schemas, tables, flex tables, and columns. Instead of manually writing descriptions, you can provide business context and supporting documents to generate meaningful descriptions that help users understand the purpose and contents of database objects.

You can generate descriptions only for databases that are AI-enabled.

Generate descriptions with AI for a table or flex table

1. Enter the table or flex table name.
2. Select the schema in which the object will be created.
3. Click Generate description. The Generate descriptions dialog box appears.
4. In the Context field, provide information about the object, such as its purpose, business domain, or the type of data it contains.
5. (Optional) Select Browse and upload supporting documents that provide additional business or technical context. The supported file formats are .csv, .doc, .docx, .pdf, .xls, and .xlsx. Uploaded files must be unencrypted and must not exceed 50 MB. Images are not supported and cannot be used to generate descriptions.
6. (Optional) An AI model is selected by default. You can select a different model if required.
7. Click Generate. AI generates descriptions based on the available metadata, context, and supporting documents.
8. Review and update the generated descriptions and click Save.

   Note

   The quality of generated descriptions depends on the information provided. Supplying detailed context and supporting documentation improves the accuracy and relevance of the generated descriptions.
   

Generate descriptions with AI for a schema

1. Enter the schema name.
2. Click Generate descriptions with AI. The Generate descriptions dialog box appears.
3. In the Context field, provide information about the object, such as its purpose, business domain, or the type of data it contains.
4. (Optional) Select Browse and upload supporting documents that provide additional business or technical context. The supported file formats are .csv, .doc, .docx, .pdf, .xls, and .xlsx. Uploaded files must be unencrypted and must not exceed 50 MB. Images are not supported and cannot be used to generate descriptions.
5. (Optional) An AI model is selected by default. You can select a different model if required.
6. Click Generate. AI generates descriptions based on the available metadata, context, and supporting documents.
7. Review and update the generated descriptions and click Save.

   Note

   The quality of generated descriptions depends on the information provided. Supplying detailed context and supporting documentation improves the accuracy and relevance of the generated descriptions.
   

Procedures

You can condense complex database tasks and routines into procedures. Procedures live and can be executed from inside your database; this lets them communicate and interact with your database directly to maintain, execute queries, and update tables. To view procedures, you can open the SQL editor and search for or browse to the procedure. Select the procedure to review details and manage the procedure. For more information about procedures, see Stored procedures.

Create a procedure

Ensure that you have non-superuser privilege to create procedures.

1. Expand a schema in the Schema view and click + for Procedures. The Create procedure page appears. Do the following:
2. Review and edit the procedure_name and parameters.
3. Enter the procedure logic.
4. Format and execute the procedure. See Create an SQL query.
5. Click the Reload icon to refresh the schema. The new stored procedure is displayed in the Procedures folder.

View information about procedures

To view information about procedures, do the following:

1. Open the SQL editor.
2. Expand the schema.
3. Hover over and select Procedures. The Procedures page displays the following information:
   • Language - Specifies the language of the procedure source. It can be either PLvSQL or PLpgSQL.
   • Namespace name - Name of the database or namespace that contains table:
     • Database name: If specified, it must be the current database.
     • Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, it is assumed the object is in the default namespace.
   • Owner - The name of the person who created the stored procedure.
   • Procedure arguments - A comma-delimited list of formal parameters, each specified as parameter-name and parameter-type.
   • Procedure name - The name of the stored procedure, where procedure-name conforms to conventions described in Identifiers.
   • Schema name - Name of the schema. The default is public.
   • Security - Determines whose privileges to use when the procedure is called and executes it as if the user is one of the following:
     • DEFINER: User who defined the procedure.
     • INVOKER: User who called the procedure. For more information about these parameters, see create procedure.

Example

This procedure returns the input values as a result set:

=> CREATE PROCEDURE echo_int_varchar(INOUT x INT, INOUT y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
   RAISE NOTICE 'This procedure outputs a result set of its inputs:';
END
$$;

=> CALL echo_int_varchar(3, 'a string');
NOTICE 2005:  This procedure outputs a result set of its inputs:
 x |    y
---+----------
 3 | a string
(1 row)

Entity relationships

Entity relationships provide a graphical representation of the relationships between tables based on primary key and foreign key relationships. You can view entity relationships within a single schema or across multiple schemas to better understand your database structure and how tables are connected.

View entity relationships for a table within a schema

To view entity relationships between tables within a schema:

1. In the Schema view, locate the schema for which you want to view relationships.
2. Click the More options menu (⋮) next to the schema name.
3. Select View relationships. The entity relationships diagram appears, displaying all tables in the schema and their relationships.

View entity relationships for a table across schemas

To view entity relationships between tables across different schemas:

1. In the Schema view, select the table for which you want to view relationships.
2. Click View entity relationships. The entity relationships diagram appears, displaying the selected table and all related tables across schemas.

Color coding

The entity relationships diagram uses color coding to distinguish tables based on their location relative to the current schema:

• Blue header - Tables within the current schema.
• Black header - Tables outside the current schema that have relationships with tables in the current schema.

Diagram controls

Use the controls to manage and navigate the entitiy relationships diagram:

• Drag and Pan - Move around the diagram to view tables and relationships that are not currently visible.
• Zoom in/out - Hover over or the zoom controls to zoom in for detailed views or zoom out to view the entire diagram.
• Download - Download the diagram in SVG format.
• Refresh - Updates the diagram by retrieving the latest metadata from the database, ensuring that newly added, modified, or removed tables, columns, keys, and relationships are accurately reflected.
• Search - Search for a table within the diagram. As you type a table name, the matching table is highlighted and its entity relationships are displayed.

Primary key indicators

Columns marked with a key icon represent primary key columns.

12 - Upgrade Unified Console

Follow the procedure below to upgrade MC.

Upgrade Unified Console

To upgrade UC with a new version, follow these steps.

Backup MC before you upgrade

1. Log in as root or a user with sudo privileges on the server where MC is already installed.

2. Open a terminal window and shut down the MC process:

   # /etc/init.d/vertica-consoled stop
   

   For versions of Red Hat 7/CentOS 7 and above, use:

   # systemctl stop vertica-consoled
   

3. Back up MC to preserve configuration metadata.

   Important

   A full backup is required in order to restore MC to its previous state. Restoring MC is essential if the upgrade fails, or you decide to revert to the previous version of Vertica. For details, see Backing up MC.

4. Stop the database if MC was installed on an Ubuntu or Debian platform.

Upgrading UC

1. Download the MC package from the Vertica Website:

   vertica-console-current-version.Linux-distro)
   

   Save the package to a location on the target server, such as /tmp.

2. On the target server, log in as root or a user with sudo privileges.

3. Change to the directory where you saved the MC package.

4. Install MC using your local Linux distribution package management system—rpm, yum, zypper, apt, dpkg. For example:

   Red Hat 8

   # rpm -Uvh vertica-console-current-version.x86_64.rpm
   

   Debian and Ubuntu

   # dpkg -i vertica-console-current-version.deb
   

5. If you stopped the database before upgrading MC, restart the database.

   As the root user, use the following command:

   /etc/init.d/verticad start
   

   For versions of Red Hat 8/CentOS 8 and above, run:

   # systemctl start verticad
   

6. Open a browser and enter the URL of the UC installation, one of the following:

   • IP address:

     https://ip-address:uc-port/
     

   • Server host name:

     https://hostname:uc-port/
     

   By default, uc-port is 8433.

7. Stop the UC services.

    sudo ./opt/vconsole/uc/scripts/services.sh stop  
   

8. Restart the UC services.

    sudo ./opt/vconsole/uc/scripts/services.sh start