New and changed in Vertica 23.3

• 1: Client connectivity
• 2: Client drivers
• 3: Complex types
• 4: Containers and Kubernetes
• 5: Data export and replication
• 6: Database management
• 7: Eon mode
• 8: File exporters
• 9: Loading data
• 10: Machine learning
• 11: Management Console
• 12: Projections
• 13: Security and Authentication
• 14: SQL functions and statements
• 15: Stored procedures
• 16: Users and privileges

1 - Client connectivity

Workload routing

Workload routing lets you create rules for routing client connections to particular subclusters based on their workloads.

The primary advantages of this type of load balancing is as follows:

• Database administrators can associate certain subclusters with certain workloads (as opposed to client IP addresses).
• Clients do not need to know anything about the subcluster they will be routed to, only the type of workload they have.
• Database administrators can change workload routing policies at any time, and these changes are transparent to all clients.

For details, see Workload routing.

2 - Client drivers

ODBC: Improved row error reporting

When loading data with a COPY or prepared statement for an INSERT, SQLGetDiagRec() now returns the diagnostic information for the first 50 failures. Previously, SQLGetDiagRec() only returned the diagnostic information for the first failure.

For details, see Tracking load status (ODBC).

ADO.NET: Linux and macOS support

The ADO.NET driver is now available on NuGet.org for all platforms: Windows, Linux, and macOS. To use it, you must first install .NET Core 3.1+ (.NET 6.0 recommended).

Cross-platform support for this driver also brings the following changes for the ADO.NET client driver 23.3.0 and above:

• Logging is now configured with Vertica.Data.dll.config, rather than with HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ADO.NET\Driver in the Windows registry. Existing configurations in HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ADO.NET\Driver are automatically migrated to Vertica.Data.dll.config. For details, see Log properties.
• To use the driver, you can now reference it in your project, rather than registering it in .NET Core. For details, see Installing the ADO.NET client driver.
• Kerberos is currently only supported on Windows.

For installation and usage details, see Installing the ADO.NET client driver.

3 - Complex types

DEFAULT and SET USING

Columns of scalar types in tables that also contain columns of complex types can now use the DEFAULT and SET USING options. See CREATE TABLE and ALTER TABLE.

Columns of complex types are still restricted from using these options.

Parquet loose schema matching

The PARQUET parser do_soft_schema_match_by_name option now supports complex types.

Joins for EXPLODE and UNNEST

You can use the output of EXPLODE and UNNEST as if it were a relation in a query. In addition to CROSS JOIN, you can now use LEFT JOIN to include NULL results, as in the following example:

=> ALTER SESSION SET UDPARAMETER FOR ComplexTypesLib skip_partitioning = true;

=> SELECT student, MIN(score), AVG(score) FROM tests
LEFT JOIN LATERAL EXPLODE(scores) AS t (pos, score)
GROUP BY student;
 student | MIN |       AVG
---------+-----+------------------
 Bob     |  78 |               83
 Lee     |     |
 Pat     |     |
 Sam     |  85 | 93.3333333333333
 Tom     |  68 |               79
(5 rows)

The LATERAL keyword is required with LEFT JOIN. It is optional for CROSS JOIN.

4 - Containers and Kubernetes

HTTP service configuration

The httpServerMode custom resource definition parameter controls whether the Vertica server starts its HTTP server.

For details, see Custom resource definition parameters.

Custom HTTP port

The subclusters[i].httpNodePort custom resource definition parameter lets you set a custom port for external HTTPS connections when subclusters[i].serviceType is set to NodePort.

For details, see Custom resource definition parameters.

Prometheus server metrics

Vertica on Kubernetes now exposes server metrics with the HTTPS service. After you configure the HTTPS service to accept client requests, you can export time series metrics for data reporting and visualization.

For details, see the following:

• Prometheus integration
• Prometheus metrics
• HTTPS service

5 - Data export and replication

Server-based replication

Server-based replication copies data from one Eon Mode database to another. Data is copied directly from the source database's communal storage location to the target database's communal storage location. Unlike vbr-based replication, server-based replication supports replication across platforms and between databases with different node counts.

Usually, you use server-based replication to keep a table or schema in the target database up to date with a corresponding table or schema in the source database. Replication automatically determines what has changed in the source database and only transfers those changes to the target database. For more information, see Server-based replication.

6 - Database management

Node Management Agent

You can now manage your Vertica database with the Node Management Agent (NMA). The NMA provides a REST API for administrating your nodes, which can be easier to integrate with and use in cases where you cannot use ssh to connect to a Vertica node.

The current iteration of the NMA is suitable for basic and general cluster management tasks. Future versions will add endpoints and utilities to support more complex workflows.

For details, see Node Management Agent.

HTTPS service

You can now manage your Vertica database with the HTTPS service. The HTTPS service provides a REST API for managing your database and accessing server, cluster, and host metrics in Prometheus exposition format.

The HTTPS service requires that you configure mutual mode TLS (mTLS). For details, see HTTPS service.

7 - Eon mode

Add additional subclusters to sandboxes

You can now add and remove additional secondary subclusters to existing sandboxes. For details, see Adding subclusters to existing sandboxes and Removing a sandbox's secondary subclusters.

8 - File exporters

EXPORT TO statements can overwrite or append to directories

If the target directory for export exists, you can use the ifDirExists parameter to EXPORT TO PARQUET, EXPORT TO ORC, EXPORT TO JSON, and EXPORT TO DELIMITED to overwrite or append new files to the existing directory.

9 - Loading data

Iceberg schema evolution

Vertica can now read Apache Iceberg tables that have undergone the following types of schema evolution:

• Changed column types
• Added or dropped columns
• Added or dropped struct fields

See CREATE EXTERNAL TABLE ICEBERG.

10 - Machine learning

Expanded PMML support

Vertica now supports additional PMML tags and attributes, including the FieldRef and LocalTransformations tags. For a list of all supported PMML model types, tags, sub-tags, and attributes, see PMML features and attributes.

Complex type support for imported TensorFlow models

The PREDICT_TENSORFLOW_SCALAR function makes predictions with imported TensorFlow models that have complex type input and output columns. This function accepts and outputs a single ROW, where each field in the ROW contains a 1D ARRAY tensor. For details, see the function reference page and TensorFlow integration and directory structure.

To use this function with an imported TensorFlow model, you must use a column-type argument of 1 when calling the freeze_tf2_model.py script. For more information, see tf_model_desc.json overview.

11 - Management Console

User authentication with Keycloak

The Management Console (MC) integrates with Keycloak to authenticate user accounts. The MC provides the following authentication options to integrate with your corporate identity management workflows:

• Local: User account information is stored and managed within the MC.
• Federated: Authenticate MC user accounts with information that is stored in a federated LDAP or LDAPS server.
• Identity Provider (IDP): Authenticate MC user accounts with information that is managed by an IDP.

For details, see Users, roles, and privileges in MC.

12 - Projections

Improved partitioned projections refresh performance

In previous releases, new and updated projections were not available to queries before they were completely refreshed with all table data. This delayed availability of projections to process query requests. Now, when you refresh the projections of a partitioned table—for example, a new projection of a table with existing data—the refresh operation first loads data from the partition with the highest range of keys. After refreshing this partition, Vertica begins to refresh the partition with next highest partition range. This process continues until all projection partitions are refreshed. In the meantime, while the refresh operation is in progress, projection partitions that have completed the refresh process become available to process query requests.

13 - Security and Authentication

Single sign-on (SSO) for ODBC OAuth

The Vertica ODBC driver can now automatically open your web browser and direct you to Keycloak's authentication endpoint to sign in with SSO. This method of authenticating with the identity provider is an alternative to manually retrieving the OAuthAccessToken from the various endpoints.

This functionality is currently limited to Keycloak. For details, see Retrieving an access token.

Simplified control channel Spread TLS

Vertica nodes use the control channel to communicate. You can secure this communication channel with TLS by enabling EncryptSpreadComm. You can now also set EncryptSpreadComm when you create the database. Previously, you could only set this parameter on a running database with ALTER DATABASE.

For details, see Control channel Spread TLS.

14 - SQL functions and statements

SET SESSION AUTHORIZATION for current session

The SET SESSION AUTHORIZATION statement changes the current and session user for the current SQL session. Changing the session is helpful for testing and debugging purposes.

For details, see SET SESSION AUTHORIZATION.

15 - Stored procedures

Nested stored procedures

You can now call stored procedures and execute meta-functions from inside other stored procedures. For details, see Nested stored procedures.

Transaction semantics

Stored procedures now only automatically commit after successful execution of the entire stored procedure, including any nested stored procedures. You can also manually commit with PERFORM COMMIT. For details, see Transaction semantics.

Previously, stored procedures committed the transaction before executing, and each embedded SQL statement executed in its own autocommitted transaction.

Session semantics

Stored procedures now support session semantics. All session-based changes made in a stored procedure persist after execution.

16 - Users and privileges

Inherited privileges for models

Machine learning models can now inherit the privileges of its parent schema. Inherited privileges can be enabled at the model level and materialized into explicit grants with ALTER MODEL.

This feature can be disabled with DisableInheritedPrivileges, which disables privilege inheritance at the database level.