New and changed in Vertica 12.0.4

• 1: admintools
• 2: Client connectivity
• 3: Client drivers
• 4: Complex types
• 5: Containers and Kubernetes
• 6: Database management
• 7: Directed queries
• 8: Documentation updates
• 9: Eon Mode
• 10: Apache Kafka integration
• 11: Loading data
• 12: Machine learning
• 13: Partitioning
• 14: Performance improvements
• 15: Resource management
• 16: SDK updates
• 17: Security and authentication
• 18: Stored procedures
• 19: Upgrade
• 20: User-defined extensions

1 - admintools

Setting re_ip timeout

You can configure how long re_ip executes a given task before it times out, by editing the setting of prepare_timeout_sec in admintools.conf. By default, this parameter is set to 7200 (seconds).

2 - Client connectivity

Set idle timeouts during connection initialization

You can set the IdleTimeoutInitializingConnectionsMs configuration parameter to change the length of time that the server waits for an idle client during each step in connection initialization, before a session is created.

Client hostnames

The hostnames of clients (as reported by their operating systems) are now recorded in the CLIENT_OS_HOSTNAME column of the following tables:

• SESSIONS
• CURRENT_SESSION
• USER_SESSIONS

3 - Client drivers

ODBC and vsql: support for M1 and M2 processors

The ODBC and vsql client drivers now support Apple M1 and M2 processors. For details, see Client drivers support.

4 - Complex types

UNNEST function

The new UNNEST function expands one or more array columns. UNNEST is similar to EXPLODE, with two key differences:

• UNNEST returns element values but not positions.

• UNNEST automatically expands all arrays that are passed to it.

EXPLODE performance optimization

The EXPLODE function translates a single row of input into multiple rows of output, one per collection element. There is, therefore, usually no benefit to partitioning the input. Previously, a query using EXPLODE required an OVER clause and Vertica partitioned the input before calling EXPLODE. Now, you can skip this partitioning step by using the skip_partitioning function parameter and omitting the OVER clause.

CROSS JOIN EXPLODE

You can use the output of EXPLODE as if it were a relation by using CROSS JOIN EXPLODE in a query, as in the following example:

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

=> SELECT student, score FROM tests
   CROSS JOIN EXPLODE(scores) AS t (pos, score)
   ORDER BY score DESC;
 student | score
---------+-------
 Sam     |    98
 Sam     |    97
 Bob     |    92
 Tom     |    91
 Sam     |    85
 Tom     |    82
 Bob     |    79
 Bob     |    78
 Tom     |    75
 Tom     |    68
(10 rows)

For details, see Joining on Results.

5 - Containers and Kubernetes

Server-side encryption (SSE)

Veritca on Kubernetes supports all three types of S3 SSE:

• SSE-3
• SSE-KMS
• SSE-C

For details, see Configuring communal storage and Custom resource definition parameters.

Set configuration parameters

You can set configuration parameters in the Vertica server with the communal.additionalConfig parameter.

For details, see Custom resource definition parameters.

Set depot volume type

You can store the depot in an emptyDir volume with the local.depotVolume parameter. When you use an emptyDir volume, the local.storageClass and local.requestSize parameters only apply to data stored in PersistentVolumes.

For details about the custom resource parameters, see Custom resource definition parameters. For details about volume types, see the Kubernetes documentation.

EventTrigger custom resource definition (CRD)

The EventTrigger CRD extends the Kubernetes Job workload resource to watch a VerticaDB custom resource (CR) for a state change. When a condition on the VerticaDB CR changes to a specified status, the EventTrigger runs a Job.

For details, see EventTrigger CRD.

Use underscore character in subcluster name

You can now use the underscore character ( "_" ) in the subcluster name. For details about subcluster naming conventions, see Subclusters on Kubernetes.

Vertica image with no private keys

Vertica on Kubernetes provides an image that does not contain static SSH keys to authenticate internal communication between the pods. This requires that you provide SSH keys to the custom resource (CR). You can conceal your SSH keys in a Secret, and pass that Secret to the CR with the sshSecret field.

For details about the image and passing SSH keys to the CR, see the following:

• Vertica images
• Custom resource definition parameters
• VerticaDB CRD

Custom name label

You can customize your VerticaDB CRD application's name with the app.kubernetes.io/name label:

spec:
  labels:
    app.kubernetes.io/name: app-name

If you do not add a custom name label, then the default value is vertica. For additional details about the name label, see the Kubernetes documentation.

Delete PVCs after VerticaDB delete

When you delete a VerticaDB custom resource, the VerticaDB operator deletes any PVCs that are associated with that instance. For details about Vertica on Kubernetes persistent storage, see Containerized Vertica on Kubernetes.

6 - Database management

Amazon simple notification service (SNS) notifiers

You can now send messages to SNS topics with notifiers. For details, see Configuring reporting for the simple notification service (SNS).

7 - Directed queries

INSERT...SELECT hints

You can now annotate the query of an INSERT...SELECT statement with directed query hints.

8 - Documentation updates

Data exploration

When data is not well-specified in a schema, developing a table definition so you can load the data can be challenging. Vertica provides tools you can use to explore data and refine a table definition. These tools and processes are now described in a new top-level section of the documentation, Data exploration.

9 - Eon Mode

Subcluster sandboxing

Sandboxing enables you to spin-off a secondary subcluster from an existing cluster, resulting in two mutually isolated clusters that share the same data but do not interfere with each other. After sandbox creation, you can perform standard database operations and queries, such as creating new tables or loading libraries, without affecting the other cluster. For example, dropping a table in the sandboxed subcluster does not drop the table in the main cluster, and vice versa.

Sandboxes support many use cases, including the following:

• Testing a new version of Vertica without needing to spin-up a new cluster and reload data.

• Trying out features without compromising the consistency of the main cluster.

• Sharing data with another team by giving them access to a sandboxed subcluster.

After removing the sandbox and performing necessary cleanup tasks, the subcluster can rejoin the main cluster.

10 - Apache Kafka integration

kafka_conf_secret parameter

The kafka_conf_secret parameter conceals sensitive data that you must pass to rdkafka to configure the Vertica and Kafka integration. Vertica does not log or store values that you pass in kafka_conf_secret.

This option is available on tools and functions that use the kafka_conf parameter.

For details, see Directly setting Kafka library options and Apache Kafka integration.

11 - Loading data

Optimization for external tables on object stores

When an external table uses data that is stored on an object store (S3, GCS, or Azure), and that data has many levels of partitioning, performance can be affected. The ObjectStoreGlobStrategy configuration parameter allows you to read and prune partitions using a different strategy, improving performance when queries are selective and there are many partition directory levels. With this setting, fewer calls are made to the object store API. For details, see Partitions on Object Stores.

Iceberg support

You can now define external tables backed by Parquet data stored by Apache Iceberg. An Iceberg table consists of data files and metadata describing the schema. Unlike other external tables, an Iceberg external table need not specify column definitions (DDL). The information is read from Iceberg metadata at query time. For certain data types you can adjust column definitions, for example to specify VARCHAR sizes. See CREATE EXTERNAL TABLE ICEBERG.

12 - Machine learning

Model versioning

Model versioning provides an infrastructure to track and manage the status of registered models in a database. The versioning infrastructure supports a collaborative environment where multiple users can submit candidate models for individual applications. dbadmin and users with the MLSUPERVISOR role can manage and change the status of all registered models, including which models are currently in production.

Vertica provides the following model versioning functionality:

• REGISTER_MODEL: Registers a model and adds it to the versioning environment.

• CHANGE_MODEL_STATUS: Changes the status of a registered model.

• REGISTERED_MODELS: Lists registered models in the database.

• MODEL_STATUS_HISTORY: Tracks the status history of registered models.

For details about the model versioning environment and an in-depth example, see Model versioning.

Poisson regression

Vertica now supports the Poisson regression algorithm to model count data. Poisson regression offers an alternative to linear regression or logistic regression and is useful when your desired prediction range is non-negative real numbers or integers.

Autoregresssive integrated moving average (ARIMA) support

Vertica now supports autoregressive integrated moving average (ARIMA) models for time series analytics. ARIMA models combine the abilities of AUTOREGRESSOR and MOVING_AVERAGE models by making future predictions based on both preceding time series values and errors of previous predictions. You can use the ARIMA function to create and train an ARIMA model and the PREDICT_ARIMA function to make predictions.

For an in-depth example that trains and makes predictions with an ARIMA model, see ARIMA model example.

13 - Partitioning

Window partitioning for 1:N transform functions using PARTITION ROW

Previously, window partitioning could only be used for 1:1 analytic functions. Now, the PARTITION ROW syntax can be used for window partitioning 1:N transform functions.

14 - Performance improvements

Refreshing partitioned projections

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 this partition is refreshed, 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.

15 - Resource management

Default user resource pool

The new configuration parameter DefaultResourcePoolForUsers lets you configure the default user resource pool that is assigned to new users, including LDAP users. For details, see User resource allocation.

16 - SDK updates

One-to-many UDTFs and PARTITION ROW

Single-phase user-defined transform functions (UDTFs) written in C++ and Python now support the isExploder class property, which indicates whether the UDTF performs a one-to-many transform. If this property is set to True within the getTransformFunctionProperties factory method, the function uses an OVER(PARTITION ROW) clause by default and thereby removes the need to specify an OVER clause when calling the UDTF. Users can also include any expression in SELECT queries that call a one-to-many UDTF.

For more information about this class property and one-to-many UDTFs, see Partitioning options for UDTFs and TransformFunctionFactory class. See Python example: explode for an in-depth example detailing a one-to-many UDTF.

17 - Security and authentication

OAuth

JSON web token validation

Vertica can now validate OAuth tokens without contacting the identity provider by verifying the signer of the OAuth token. To enable this feature, create an authentication record that uses 'oauth' and set the authentication parameters to use JWT validation. For details, see Configuring OAuth authentication.

Just-in-time user provisioning

Just-in-time (JIT) user provisioning is the act of automatically configuring an authenticated user and their roles based on information provided by the identity provider. When a client authenticates to Vertica with an OAuth token the authentication record enables JIT user provisioning, Vertica automatically creates the user (if the user does not already exist in Vertica). Additionally, if Keycloak is the identity provider, Vertica grants to the users their roles as specified by Keycloak.

For example, if a client presents an OAuth token to authenticate as user Alice with role director, and Alice does not exist in Vertica, Vertica automatically creates the user Alice, grants to her the authentication record. This configuration also uses Keycloak as the identity provider, so Vertica grants to her the director role as a default role.

For details, see Just-in-time user provisioning

JDBC: custom truststore

You can use a custom truststore to secure the connection between the JDBC client and the identity provider. This is useful if your identity provider's certificate is not signed by a well-known certificate authority.

The path to the truststore and its password are specified with the following connection properties:

• oauthtruststorepath

• oauthtruststorepassword

If oauthtruststorepath is not specified, the JDBC client uses the default truststore.

For details on this and other connection properties, see JDBC connection properties. For details on configuring OAuth, see Configuring OAuth authentication.

18 - Stored procedures

Scheduled execution

You can now schedule the automatic execution of stored procedures. This can be used to automate various tasks, like logging database activity, revoking privileges, or creating roles. For details, see Scheduled execution.

Viewing overloaded stored procedures

You can now view the source code of a particular implementation of an overloaded stored procedure with EXPORT_OBJECTS. For details, see Exporting objects.

19 - Upgrade

Upgrade to any Vertica version higher than your current version

Previously, Vertica upgrades were incremental, requiring you to upgrade to each major and minor Vertica version in your upgrade path. You can now skip intermediate version and upgrade directly to any version of Vertica higher than your current version.

20 - User-defined extensions

Redactable user-defined extension and session parameters

User-defined extension and session parameters that meet any of the following criteria are automatically redacted from logs and system tables like QUERY_REQUESTS:

• Named "secret" or "password"

• End with "_secret" or "_password"