New and changed in Vertica 12.0.0

• 1: Client connectivity
• 2: Client drivers
• 3: Containers and Kubernetes
• 4: Database management
• 5: Diagnostic tools
• 6: Documentation updates
• 7: Eon Mode
• 8: Loading data
• 9: Machine learning
• 10: Management Console
• 11: Projections
• 12: SDK updates
• 13: Security and authentication
• 14: SQL functions and statements
• 15: Users and privileges
• 16: Vertica on the cloud

1 - Client connectivity

Keepalive and heartbeat protocol settings

These new parameters let you adjust TCP keepalive and heartbeat protocol settings at the database and session levels.

• KeepAliveIdleTime: The time, in seconds, before the first TCP keepalive probe is sent to ensure that the client is still connected.

• KeepAliveProbeInterval: The time interval, in seconds, between keepalive probes.

• KeepAliveProbeCount: The number of consecutive keepalive probes that must be unacknowledged by the client before the client connection is considered lost and closed.

For details, see Managing client connections.

2 - Client drivers

Node.js client driver

The vertica-nodejs client driver is now available.

Centralized OAuth configuration

To simplify OAuth configuration, you can now set OAuth parameters with a single JSON string with the new oauthjsonconfig (ODBC) and OAuthJsonConfig (JDBC) parameters.

To preserve existing configurations, the old parameters take precedence over the new JSON parameter, but the new JSON parameter is the recommended configuration method, and the old parameters have been deprecated.

JDBC DataSource user property

You can now get and set the user property of DataSource with getUser() and setUser(), respectively. For details, see JDBC API.

ODBC client driver enhancements

A number of enhancements have been made to the ODBC client driver to better conform to the ODBC specification. These changes may cause regressions in existing ODBC client programs:

• If an application calls SQLSetDescField to set any field other than the following, the record becomes unbound:

  • SET_DESC_COUNT

  • The deferred fields:

    • SQL_DESC_DATA_PTR

    • SQL_DESC_INDICATOR_PTR

    • SQL_DESC_OCTET_LENGTH_PTR

• SQLSetDescField must be called in the sequence order specified in the Microsoft documentation.

• If an application wants to set the precision or scale field for a numeric or date type, it must use SQLSetDescField to explicitly set those fields rather than relying on SQLBindParameter. For details, see the Microsoft documentation.

• If an application calls SQLGetData, the StrLen_or_IndPtr can return the actual length of the data available, SQL_NO_TOTAL, or SQL_NULL_DATA. If the data is truncated, Vertica returns SQL_NO_TOTAL. When checking the length of the available buffer, you must consider all cases, including SQL_NO_TOTAL and SQL_NULL_DATA. For details, see the Microsoft documentation on SQLGetData and Getting Long Data.

• If an application calls SQLExecute, the response will always attempt to set the SQLSTATE, including the status and other metadata.

• Many exception messages have been improved with more detailed error information.

JDBC: CallableStatement

The Vertica JDBC client driver now supports stored procedures through CallableStatement. For details, see Executing queries through JDBC.

3 - Containers and Kubernetes

Spread encryption on Vertica database

You can enable Spread encryption on the Vertica database. Use the encryptSpreadComm custom resource parameter to set the EncryptSpreadComm security parameter for the database.

For details, see Custom resource definition parameters.

Prometheus operator integration

Vertica on Kubernetes integrates with the Prometheus operator to facilitate adoption in environments that currently use the Prometheus operator.

For details, see Prometheus integration.

4 - Database management

Disk quota for schemas and tables

You can set disk quotas for schemas, individual tables, or both. Disk quotas are checked by most user operations that increase storage size. Quotas do not affect recovery, rebalancing, or Tuple Mover operations.

In Eon Mode, disk usage is an aggregate of all disk space used by all shards for the schema or table. This value is computed for primary subscriptions only. In Enterprise Mode, disk usage is the sum of disk space used by all storage containers on all nodes for the schema or table. This sum excludes buddy projections but includes all other projections.

The DISK_QUOTA_USAGES system table records quotas and current usage for objects that have quotas.

For details, see Disk quotas.

5 - Diagnostic tools

Scrutinize

scrutinize now collects the full time zone by running the following command:

timedatectl | grep "Time zone"

Output is directed to:

scrutinize-output-dir/VerticaScrutinize.timestamp/node-name/context/Commands/timezone

For example, after running scrutinize:

$ cat ./VerticaScrutinize.20220513110429/v_vmart_node0001/context/Commands/timezone
       Time zone: America/New_York (EDT, -0400)

6 - Documentation updates

Several organizational changes have been made to make information easier to find:

• Top-level topics have been re-ordered to place similar information together. The SQL Reference has been moved near the bottom with the other reference documentation. The lists of configuration parameters have been moved into the SQL Reference.

• In the SQL Reference, functions and meta-functions have been combined under Functions, and the alphabetical list includes all of them. (Previously, the alphabetical list included only meta-functions.) Meta-functions are labeled as such on their reference pages.

• The documentation about client drivers has been refactored.

• The information that was in the Concepts Guide has been placed with the other documentation on those topics. For example, the section about projections is now with the other documentation about projections.

• Several duplicate topics were removed, and several collections of short related topics have been consolidated.

7 - Eon Mode

Re-shard an Eon Mode database

The new RESHARD_DATABASE function allows you to change the number of shards in an Eon Mode database. Previously, the shard count was set during database creation and could not be altered.

For more information about re-sharding, see Change the number of shards in the database.

Graceful shutdown

If you want to drain a subcluster's client connections before shutting it down, you can now gracefully shut down subclusters using SHUTDOWN_WITH_DRAIN. The function allows existing connections on the subcluster to continue their work until either all connections close or a user-specified timeout is reached. When one of these conditions is met, the function proceeds to shut down the subcluster.

For more information about the graceful shutdown process, see Graceful Shutdown.

8 - Loading data

Partitioned file paths

Data files are sometimes partitioned in the file system using the directory structure. Partitioning moves values out of the raw data, where they have to be included for each row, and into the directory structure, saving disk space. Partitioning can also improve query performance by allowing entire directories to be skipped.

Previously, only the Parquet and ORC parsers could take advantage of partitioned file paths. Now COPY supports partitioned file paths for all parsers using the new PARTITION COLUMNS option. The hive_partition_cols parameter for the Parquet and ORC parsers is deprecated.

The hive_partition_cols parameter has the following behavior changes from previous releases:

• Nested partition directories must appear in consistent order in the file system. The following path pattern is invalid:

  /data/created=2022-01-01/region=north
  /data/region=south/created=2022-01-02
  

• If the column value cannot be parsed from the directory name, COPY rejects the path instead of treating the value as null.

• If the path is missing a declared partition column, COPY always returns an error. Previously, if do_soft_schema_match_by_name was true in the Parquet parser, the parser filled the column with nulls.

• Partition columns are no longer required to be the last columns in the table definition.

See Partitioned file paths.

9 - Machine learning

Isolation forest

Vertica now supports the isolation forest (iForest) algorithm. iForest is an unsupervised outlier detection algorithm that you can use for applications such as fraud detection and system health monitoring, or for removing outliers from your data. You can use the IFOREST function to train an iForest model and the APPLY_IFOREST function to apply the model to an input relation.

For an extended discussion on the iForest algorithm, see Isolation Forest.

Option to build LINEAR_REG and LOGISTIC_REG models without calculating intercept

You now have the option of training linear and logistic regression models without calculating an intercept. See LINEAR_REG and LOGISTIC_REG for details.

10 - Management Console

Edit custom alerts

You can edit the following properties of an existing custom alert:

• Alert Name

• SQL Query

• Variable values

For details, see Custom alerts.

Revive an Eon Mode database on Microsoft Azure

Management Console supports reviving Eon Mode database clusters on Microsoft Azure.

Alternate filesystem layouts for reviving and scaling on AWS

On Amazon Web Services (AWS), you can use alternate filesystem layouts for the depot, temp, and catalog Vertica directories for the following actions:

• Reviving a database

• Creating a subcluster

• Scaling a subcluster

For details, see Amazon Web Services in MC and Subclusters in MC.

11 - Projections

Partition range support for aggregate projections

Partition Ranges can now be specified for live aggregate and top-K projections.

12 - SDK updates

Polymorphic aggregate functions

User-defined aggregate functions (UDAFs) can now be polymorphic. A polymorphic function can accept any number and type of arguments. For information on how to write a polymorphic function, see Creating a polymorphic UDx.

Complex types in the Python SDK

You can now read and write complex types in user-defined extensions written in Python. The complex types support includes arrays, rows, and combinations of both. For details, see Arguments and return values. For examples of a Python UDx utilizing complex types, see Python example: matrix multiplication and Python example: complex types JSON parser.

13 - Security and authentication

Generic authentication errors

To improve security, Vertica no longer issues the following error when a user attempts to connect and authenticate:

Invalid username or password

In addition, Vertica no longer issues method-specific error messages, and all authentication failures will result in the same error message:

authentication failed for username "name"

This change may affect clients that decide to retry connection attempts based on the type of connection error code. One such example is vsql, which would previously attempt a plaintext connection if TLS failed (and TLSMODE was ENABLE), but not if vsql received an error code reporting incorrect user credentials.

Because the new, generic message and error code does not specify the cause for the failure, vsql cannot distinguish between a authentication error as a result of an invalid TLS configuration or invalid user credentials, and it will attempt to establish a plaintext connection in both cases.

Default authentication records

Vertica now creates three default authentication records and grants them to the public role. These authentication records have a priority of -1, so all user-created authentication records take priority over these default records.

In previous versions of Vertica, if no authentication records were enabled, the following implicit authentication records were applied:

• Users without a password were authenticated with the trust method. This implicit authentication record continues to apply in 12.0.0 for users without a password.

• Users with a password were authenticated with the password method. This implicit authentication record has been removed in favor of the default records.

Fallthrough authentication

You can now allow authentication records to fall through to the next record (in order of priority) upon failure. For details, see Fallthrough authentication.

Upgrade behavior

In previous versions, this fallthrough behavior was only available for ident (could fall through to any other authentication method) and ldap (could only fall through to other ldap methods) authentication methods and this behavior could not be disabled. As of Vertica 12.0.0, this behavior is disabled by default for all new authentication records, including ident and ldap.

To preserve the behavior of existing databases that depended on ident's fallthrough Vertica automatically enables fallthrough for ident authentication records on upgrade if all of the following are true:

• The database contains an ident authentication record.

• The ident authentication record has the highest priority.

• The database contains another user-defined authentication record.

Again, in previous versions, ldap records only fell through to other ldap records and skipped records that used other methods. This is no longer the case; ldap records are fallthrough-compatible with many other methods.

Therefore, to replicate old fallthrough behavior for ldap, your ldap records must be consecutive (in priority order) to fallthrough to each other.

14 - SQL functions and statements

INFER_TABLE_DDL supports JSON

You can now use the INFER_TABLE_DDL function to produce candidate table definitions from JSON files. Because a JSON file does not have an explicit schema, the function inspects the data itself. Because JSON data can vary from record to record or file to file, the function can return more than one candidate definition. In the following example, differences are highlighted:

=> SELECT INFER_TABLE_DDL ('/data/*.json'
    USING PARAMETERS table_name='restaurants', format='json',
max_files=3, max_candidates=3);
WARNING 0:  This generated statement contains one or more float types which might lose precision
WARNING 0:  This generated statement contains one or more varchar/varbinary types which default to length 80

                INFER_TABLE_DDL
------------------------------------------------------------------------
 Candidate matched 1/2 of total files(s):
  create table "restaurants"(
    "cuisine" varchar,
    "location_city" Array[varchar],
    "menu" Array[Row(
      "item" varchar,
      "price" float
    )],
    "name" varchar
  );
Candidate matched 1/2 of total files(s):
  create table "restaurants"(
    "cuisine" varchar,
    "location_city" Array[varchar],
    "menu" Array[Row(
      "items" Array[Row(
        "item" varchar,
        "price" numeric
      )],
      "time" varchar
    )],
    "name" varchar
  );

(1 row)

Immutable tables

Immutable tables are insert-only tables in which existing data cannot be modified, regardless of user privileges. Updating row values and deleting rows are prohibited. Certain changes to table metadata—for example, renaming tables—are also prohibited, in order to prevent attempts to circumvent these restrictions.

You set an existing table to be immutable with ALTER TABLE:

ALTER TABLE table SET IMMUTABLE ROWS;

For details, see Immutable tables.

15 - Users and privileges

LDAP link: user groups as default roles

By default, synchronizing LDAP users through the LDAP Link service automatically grants roles (derived from their LDAP groups) to the users. However, these are not default roles and therefore must be enabled manually with SET ROLE before they take effect.

The new LDAPLinkAddRolesAsDefault parameter (disabled by default) makes these roles default roles automatically:

=> ALTER DATABASE DEFAULT SET LDAPLinkAddRolesAsDefault = 1;

For details on this and other LDAP Link parameters, see LDAP link parameters.

16 - Vertica on the cloud

AWS i4i, r6i, and c6i instance types now supported

Vertica now supports three i4i, two r6i, and one c6i EC2 instance types for use in database clusters. See Supported AWS instance types.