10.1.x Release Notes

Vertica
Software Version: 10.1.x

IMPORTANT: Vertica for SQL on Hadoop Storage Limit

Vertica for SQL on Hadoop is licensed per node, on an unlimited number of central processing units or CPUs and an unlimited number of users. Vertica for SQL on Hadoop is for deployment on Hadoop nodes. Includes 1 TB of Vertica ROS formatted data on HDFS.

This 1 TB of ROS limit enforcement is currently only contractual, but it will begin to be technically enforced in Vertica 10.1. Starting with Vertica 10.1, if you are using Vertica for SQL on Hadoop, you will not be able to load more than 1 TB of ROS data into your Vertica database. If you were unaware of this limitation and already have more than 1 TB of ROS data in your database at this time, please make any necessary adjustments to stay below the limit, or contact our sales team to explore other licensing options.

IMPORTANT: Before Upgrading: Identify and Remove Unsupported Projections

With version 9.2, Vertica has removed support for pre-join and range segmentation projections. If a table's only superprojection is one of these projection types, the projection is regarded as unsafe.

Before upgrading to 9.2 or higher, you are strongly urged to check your database for unsupported projections. If the upgrade encounters these projections, it is liable to fail. You must then revert to the previous installation.

Solution: Run the pre-upgrade script

Vertica has provided a pre-upgrade script that examines your current database and sends to standard output its analysis and recommendations. The script identifies and lists any unsupported projections. If the script finds projection buddies with different SELECT and ORDER BY clauses, it generates a deploy script. Run this script to remedy projections so they comply with system K-safety.

https://www.vertica.com/pre-upgrade-script/

For more information, see Fixing Unsafe Buddy Projections in the Vertica documentation.

Updated: 5/13/2021

About Vertica Release Notes

The Release Notes contain the latest information on new features, changes, fixes, and known issues in Vertica 10.1.x.

They also contain information about issues resolved in:

• Hotfixes
• Service Packs
• Major Releases
• Minor Releases

Downloading Major and Minor Releases, and Service Packs

The Premium Edition of Vertica is available for download at https://support.microfocus.com/downloads/swgrp.html.

The Community Edition of Vertica is available for download at https://www.vertica.com/download/vertica/community-edition.

The documentation is available at https://www.vertica.com/docs/10.1.x/HTML/index.htm.

Downloading Hotfixes

Hotfixes are available to Premium Edition customers only. Contact Vertica support for information on downloading hotfixes.

 

What's New in Vertica 10.1.1

Take a look at the Vertica 10.1.1 New Features Guide for a complete list of additions and changes introduced in this release.

admintools

New restart_node Option

The node IP addresses of an Eon Mode database on Kubernetes must occasionally be updated—for example, a pod fails, or is added to the cluster or rescheduled. When this happens, you must update the catalog with the new IP addresses of affected nodes. Vertica's restart_node tool addresses this requirement with its new option ‑‑new-host-ips, which lets you change the node IP addresses of an Eon Mode database that is running on Kubernetes.

This option is only supported for Eon databases on Kubernetes.

For details, see Restarting a Node with New Host IPs.

Backup, Restore, Recovery, and Replication

New vbr Configuration File Option for Foreign Key Constraints

The new drop_foreign_constraints vbr configuration file option enables you to restore individual objects independent of their foreign key constraints from a full or object-level backup.

For details, see Restoring Individual Objects from a Full or Object-Level Backup and [Misc] Miscellaneous Settings.

Data Types

One-Dimensional Arrays in ORC Files

The ORC parser now supports reading one-dimensional arrays of primitive types for both external and internal tables. For details, see Reading Arrays in ORC Data.

Documentation Updates

New File Systems Section in SQL Reference

To make information about interacting with external file systems easier to find, the SQL Reference now has a new File Systems and Object Stores section. The reference pages in this section describe URI formats and authentication requirements, summarize applicable configuration parameters, and provide examples. These reference pages supplement the topics about performing specific file-system actions such as loading data and creating communal storage locations.

Eon Mode

Fetching Pinned Objects to a Warming Depot

When depot warming is enabled, on startup the node now checks the new configuration parameter PreFetchPinnedObjectsToDepotAtStartup. If this parameter is enabled, the node gets a list of objects that are pinned on this node's subcluster and queues them for fetching to the depot.

For details, see Depot Warming.

Eon Mode Communal Storage on Azure Blob Storage

You can now create Eon Mode databases on Azure using Azure's blobs for communal storage, with the following restrictions:

• Vertica does not support the use of Vertica MC or admintools to administer data located on Azure.
• The vbr backup and restore utility is not currently supported on Azure.

For details, see Eon Mode Databases on Azure.

Loading Data

Load from Azure Blob Storage

You can now load data and create external tables from data stored in Azure Blob Storage. Use the azb URI scheme. For more information, see Azure Blob Storage Object Store.

Machine Learning

PMML 4.4 support

Vertica now supports PMML models of version 4.4 and below.

Management Console

Changes to MC Email Configuration

For more information on configuring email notifications for MC, see Set Up Email.

• To accommodate for SMTP servers that don't require a username and password, the SMTP Username and SMTP Password fields are now optional.
• You can now automatically trust the SMTP server's certificate with Trust SSL Certificate.

New Local SSD Options for Google Cloud Platform (GCP) in Eon Mode

In Eon Mode, Management Console supports new local SSD options (ephemeral storage) for GCP. Vertica uses the new local SSD options to provide new volume configuration defaults, described in the following table:

For details about persistent storage and instance types available for each new default configuration, see Eon Mode Volume Configuration Defaults for GCP.

New Feedback Button

There is a new Feedback button that enables you to submit a star rating and free-text comments to Vertica. The button displays vertically at the bottom-right of your browser.

Vertica collects the following metadata along with your feedback submission:

• Date
• Time
• Page title
• Vertica database version
• Vertica Management Console version
• Web browser type, version, and resolution

New Option to Provide User Analytics and Tracking Information

You can provide Vertica with analytic information about your MC usage, for use when making improvements to the MC in future releases. Your information is stored anonymously and securely, and never shared with third-party organizations.

Choose whether to participate when you accept the End-User License Agreement Terms for the MC during your first login, or go to the User Analytics and Tracking section in MC settings > Configuration. For details, see Managing MC Settings.

Parquet Export

Additional Compression Support

EXPORT TO PARQUET now supports compression with GZIP, Brotli, and ZSTD, in addition to Snappy which was already supported.

Export to Azure Blob Storage

You can use EXPORT TO PARQUET to export data to Azure Blob Storage. If you are using Azure managed identities, you do not need to configure credentials in Vertica. Alternatively, you can specify credentials in session or database parameters.

As with other object stores, data is exported directly to the target directory instead of being written to a temporary location first, and you must wait for the export to finish before reading the data.

SDK Updates

Polymorphic Outputs

User-defined transform functions and user-defined analytic functions now support polymorphic output types. You can declare, in the factory, that the function returns any type, and determine the return type at runtime. For details, see Creating a Polymorphic UDx.

Security and Authentication

GRANT TRUSTED for Access Policies

By default, Vertica only allows users to edit data that they can see. By specifying GRANT TRUSTED for a new or existing access policy, you can now override this behavior and defer entirely to explicit GRANT statements when assessing whether a user can perform a DML operation on the target table.

For more information on this option and DML operations as they relate to access policies, see Access Policies and DML Operations.

SQL Functions and Statements

ARGMAX and ARGMIN

Vertica now supports the analytic functions ARGMAX and ARGMIN. These functions are patterned after the mathematical functions argmax(f(x)) and argmin(f(x)), which return the value of x that maximizes and minimizes f(x), respectively. Similarly, the new Vertica functions ARGMAX and ARGMIN take two arguments, target and arg, where both arguments are columns or column expressions in the queried dataset. ARGMAX and ARGMIN respectively find the row with largest and smallest non-null value in target, and return the value of arg in that row.

TO_JSON

The new TO_JSON function returns the JSON representation of a complex-type value, including mixed and nested complex types.

STRING_TO_ARRAY Improvements

The STRING_TO_ARRAY function now allows you to specify several more special characters for collections beyond the element delimiter that was already supported. You can now also specify the characters that mark the beginning and end of the array, the null element, and an enclosing character for elements. These parameters behave the same way as the corresponding options when loading delimited data.

To support these additional optional parameters, the syntax has changed. The previous syntax is deprecated.

APPROXIMATE_QUANTILES

The new APPROXIMATE_QUANTILES function computes an array of weighted, approximate percentiles of a column within some user-specified error.

LISTAGG Separators

The LISTAGG function now supports multi-character separators.

MAKEUTF8

The new MAKEUTF8 function takes a string and removes or replaces characters that are not UTF-8. You can use this function when loading data as an alternative to cleaning input data before loading.

System Tables

ACCESS_POLICY

The ACCESS_POLICY system table now contains a new column called TRUST_GRANTS, which indicates whether a given access policy includes the GRANT TRUSTED option. This option determines how access policies handle DML operations. For more information, see Access Policies and DML Operations.

What's Deprecated in Vertica 10.1.1

This section describes the two phases Vertica follows to retire Vertica functionality:

• Deprecated: Vertica announces deprecated features and functionality in a major or minor release. Deprecated features remain in the product and are functional. Published release documentation announces deprecation on this page. When users access this functionality, it may return informational messages about its pending removal.
• Removed: Vertica removes a feature in a major or minor release that follows the deprecation announcement. Users can no longer access the functionality, and this page is updated to verify removal. Documentation that describes this functionality is removed, but remains in previous documentation versions.

Deprecated

The following Vertica functionality was deprecated and will be retired in future versions:

Removed

No functionality was removed in this release.

For more information see Deprecated and Retired Functionality in the Vertica documentation.

Vertica 10.1.1-1: Resolved Issues

Release Date: 5/13/2021

This hotfix addresses the issues below.

Vertica 10.1.1: Resolved Issues

Release Date: 4/30/2021

This hotfix addresses the issues below.

Known issues Vertica 10.1.1

Updated: 4/30/21

Vertica makes every attempt to provide you with an up-to-date list of significant known issues in each release. We will update this list as we resolve issues and as we learn of new issues.

What's New in Vertica 10.1

Take a look at the Vertica 10.1 New Features Guide for a complete list of additions and changes introduced in this release.

Backup, Restore, Recovery, and Replication

Support for Object-Level Backup and Restore in Eon Mode

You can now perform object-level backup, restore, and replication in Eon Mode databases. You can back up individual schemas or tables, and restore or replicate individual tables or schemas from full or object-level backups.

See Creating Backups and Restoring Backups for information about object-level operations.

Support for Google Cloud Storage

Vertica now supports backup and restore of Enterprise Mode and Eon Mode databases on Google Cloud Platform (GCP) to Google Cloud Storage (GCS). Supported backup and restore tasks include:

• full backup
• full restore
• subset restore
• object-level backups
• object-level restores

Support for backup and restore to GCS is limited to databases on GCP. For example, you cannot backup a database with communal storage on AWS to a GCS location.

See Configuring Backups to and from Cloud Storage for details about configuring backups from Vertica on GCP to GCS. For a complete guide on Vertica and GCP, see Vertica on Google Cloud Platform.

New [CloudStorage] Section in Configuration Files

Configuration files have a new [CloudStorage] section that replaces the [S3] section. S3 configuration parameter names were changed from s3_parameter_name to cloud_storage_parameter_name. For databases that use the S3 protocol, the renamed parameters are functionally identical to the original S3-prefixed parameters.

Using S3-prefixed names generate a warning and deprecation message.

For details, see [CloudStorage] and Deprecated and Removed Functionality.

New Configuration File Parameters

vbr configuration files support new configuration parameters:

• Under the [CloudStorage] section:
  • cloud_storage_ca_bundle lets you use an SSL server certificate bundle to authenticate backups.
  • cloud_storage_concurrency_delete lets you configure the number of delete threads created when deleting files from cloud storage.
• Under the [Transmission] section:

  concurrency_delete lets you configure the number of delete threads created when deleting files in Enterprise Mode.

For details, see [CloudStorage] and [Transmission] Data Transmission .

New Sample VBR .ini File

A new sample vbr .ini file, backup_restore_cloud_storage.ini, provides guidance on creating a configuration file to back up or restore an Enterprise Mode or Eon Mode database to a supported cloud storage location.

The new sample configuration file supersedes backup/restore .ini files from earlier Vertica versions: backup_restore_S3.ini and eon_backup_restore.ini.

Database Management

Node Health Checks: Local Storage Locations

Automatic node health checks now check the following local storage locations for read-write access:

• TEMP, DATA
• TEMP
• DATA
• DEPOT

Data Types

Mixed Structs and Arrays in Parquet External Tables

Columns in the Parquet format can contain structs, represented in Vertica by the ROW type, and arrays, represented by the ARRAY type. For external tables and the Parquet format only, you can define a column for heterogeneous combinations of these two types: a struct containing array fields or an array of structs. These types can be nested up to the maximum nesting depth.

For details, see Reading Combinations of Structs and Arrays.

Complex Types Support in ORC Files

When using external tables with ORC data, you can now use the ROW data type to represent ORC structs. The syntax is the same as for Parquet files. With this change, the previous method of handling struct fields as expanded columns is deprecated.

You can now declare, but not query, columns for arrays and maps in external tables, allowing you to consume the column and use the rest of the ORC data.

For details, see Reading Complex Types from ORC Files.

Eon Mode

Depot Pinning Enhancements

New meta-functions support pinning projections to database depots:

• SET_DEPOT_PIN_POLICY_PROJECTION
• CLEAR_DEPOT_PIN_POLICY_PROJECTION

Also, SET_DEPOT_PIN_POLICY_PARTITION now supports depot pinning partitions for specific projections.

Duplicating an Existing Subcluster

You can now create a duplicate of an existing subcluster. When you pass the new --like option to the admintools db_add_subcluster tool, it creates a subcluster that inherits most configuration settings from the source subcluster. This feature is useful if you want to scale your database for query throughput by creating more secondary subclusters. For details, see Duplicating a Subcluster.

Apache Kafka Integration

Encrypted Notifiers

You can now configure encryption for new and existing notifiers with the following parameters:

• TLSMODE specifies whether to use encryption and the validation process for certificates.
• CA BUNDLE specifies one or more trusted root CA certificates used to validate the Kafka endpoint's server certificate. For details, see Managing CA Bundles.
• CERTIFICATE specifies a client certificate for validation by the Kafka endpoint.

For details on these and other parameters, see CREATE NOTIFIER and ALTER NOTIFIER.

Spark Integration

Support for New Spark and Scala Versions

A new version of the Spark Connector supports Scala 2.12 with Spark versions 2.4.1 and 3.0. See Vertica Integration for Apache Spark in the supported platforms section for details.

Loading Data

Parquet Metadata Caching

When reading data in the Parquet format, Vertica now caches the metadata for each Parquet file during planning for use during execution. This caching reduces the number of reads and the network latency they add. If the file storage location charges for API calls, this caching can also reduce costs.

Vertica uses TEMP storage for the cache, and only if TEMP storage is on the local file system.

For details, see Loading Parquet Data.

JSON and Avro Parsers Support Native Arrays

You can now load one-dimensional arrays of primitive types from JSON and Avro data. You can continue to use flexible complex types to load arrays of any dimensionality. For details, see Loading JSON Data and Complex Data Types for favroparser.

Parquet Parser Supports Loose Schema Matching

By default, the Parquet parser uses strong schema matching. This means that the load must consume all columns in the Parquet data in the order they occur in the data. You can, instead, use loose schema matching, which allows you to select the columns you want and ignore the rest. Loose schema matching depends on the names of the columns in the data rather than their order, so the column names in your table must match those in the data. Types must match or be coercible. For details on how to use loose schema matching, see Loose Schema Matching on the PARQUET (Parser) reference page.

You can use loose schema matching for columns of primitive types and one-dimensional arrays of primitive types. For other complex types, use flexible complex types instead. See Using Flexible Complex Types.

Machine Learning

In 10.1 Vertica has added several enhancements to Machine Learning:

XGBoost

Vertica now provides robust support for XGBoost (Extreme Gradient Boosting), a popular supervised-learning algorithm used for regression or classification on large datasets. XGBoost relies on sequentially-built shallow decision trees to provide high accuracy with a low probability of overfitting the data and a highly scalable training method. The name of this tree boosting method is a direct reference to its ability to perform training very fast in a distributed computing environment.

In release 10.1, Vertica provides the ability to use the XGBoost algorithm for regression and classification of data in Vertica, by providing new and updated functions which support the XGBoost algorithm.

XGBoost training functions create XGBoost models, either an XGBoost classifier model or XGBoost regressor model. All actions for Vertica models are supported for XGBoost models, just as they are for Random Forest for example, including exporting and importing the model. The XGBoost algorithm and XGBoost functions and models are fully implemented within Vertica, and do not require the use of any outside tools.

Soundex

Vertica now supports Soundex, a widely-used phonetic approximation algorithm that turns English words into a 4-character code. Ideally, this code is shared by phonetically-similar words. It was patented in the 1920s and is present in some form in DB2, Ingres, MySQL, MS SQL Server, Oracle, PostgreSQL, and SQLite.

Soundex is useful for queries along the lines of "Find me people with names similar to Smith (e.g., Smythe)".

Management Console

Password Security Enhancements

You can now set additional requirements for MC user passwords and login security. These include new options for managing:

• password length
• password minimum and maximum lifetime
• login attempt limits
• account lockout duration
• security questions

For details, see Managing MC User Passwords.

Self-service Password Resets

Users must now set security questions when logging in for the first time. They can then reset their passwords from the MC login screen with the Forgot Password function.

New Create New Database Cluster Wizard for Amazon Web Services

There is a new Management Console wizard to create an Eon Mode database cluster on Amazon Web Services (AWS). The wizard includes an intuitive design and tooltips to provide guidance when making selections.

To create a database cluster using the new workflow, select Create a New Vertica Database Cluster from the Management Console home page.

Instance Options When Provisioning a Subcluster in Eon Mode on AWS or GCP

When you are provisioning a subcluster on AWS or Google Cloud Platform (GCP), you can create a subcluster using any instance type, volume type, and volume size. Previously, you had to use the same instance type and data settings as the existing cluster instances. Using different instance types provides flexibility to create subclusters for specific workloads or user groups.

Because a cluster can have different instance types with different volume allocations, you can no longer drop or create a database from Database Cluster View on AWS or GCP. This prevents Vertica from using incorrect instance types when creating the database. You have the option to terminate the cluster on Database Cluster View, and you can create a database from the Management Console home page.

See Managing Workload Resources in an Eon Mode Database for details about optimizing subclusters by workload.

New Option to Load Test data with Amazon Web Services (AWS) CloudFormation Template (CFT)

There is new option to load Vertica TestDrive data when you are provisioning a 4 node AWS cluster using CFT. Loading TestDrive data lets you preview how Vertica operates in production. By default, this option is set to No.

See CloudFormation Template (CFT) Overview and Launch MC and AWS Resources with a CloudFormation Template for information about using CFTs with Vertica.

Query Management

New Alias for IGNORECONST Hint

Directed queries now support :v as an alias of IGNORECONST.

SDK Updates

Scalar Functions Can Return Multiple Values

A user-defined scalar function (UDSF) can now return more than one value. Multiple values, of scalar types, are returned in a ROW. For details on how to work with multiple values, see Scalar Functions (UDSFs). For an example, see C++ Example: Returning Multiple Values.

Security and Authentication

Default Password Hashing Algorithm

• The MD5 hashing algorithm for passwords is now deprecated and will be removed in a future release.
• The system-level parameter SecurityAlgorithm now defaults to SHA512 (previously MD5).
• The NONE option for SecurityAlgorithm was removed. Previously, NONE specified the old default hashing algorithm MD5. If a database used MD5 by setting SecurityAlgorithm to NONE, the 10.1 upgrade sets that parameter to MD5.

For details, see Hash Authentication Parameters.

New PROFILE Parameters

• PASSWORD_MIN_CHAR_CHANGE: When resetting password, specifies the minimum number of characters that must be different from the previous password.
• PASSWORD_MIN_LIFE_TIME: Specifies the number of days a password must be set before it can be reset.

For details, see Profiles.

Assign Pre-hashed Passwords to Users

CREATE USER and ALTER USER now accept pre-hashed passwords. For details, see Setting and Changing User Passwords

CA Bundles

You can now create Certificate Authority (CA) bundles. These contain root CA certificates that can be used to validate connections. For details, see Managing CA Bundles.

SQL Functions and Statements

IMPLODE Function for Arrays

The IMPLODE function takes a column of values and returns an array containing them. It is the inverse of EXPLODE. The two functions can be used together to explode an array, filter values from it, and then re-assemble the array.

For details, see Arrays and Sets (Collections).

EXPLODE Function Can Expand Multiple Arrays

The EXPLODE function can now expand more than one array in a single call, removing the need to use subqueries to explode multiple arrays. The function can now also return columns in any order; the array column(s) need not be first. A new parameter specifies the number of arrays to explode; all non-array columns and all array columns after the specified number are passed through unmodified.

CREATE OR REPLACE VIEW

CREATE privileges on a view's schema and DROP privileges on the view are now sufficient for replacing an existing view with CREATE OR REPLACE VIEW. Previously, only the view owner could use CREATE OR REPLACE VIEW while specifying an existing view.

For details on these privileges, see Creating Views.

Supported Data Types

Avro Logical Types for favroparser and KafkaAvroParser

Vertica supports Avro logical types. Use Avro logical types to annotate primitive or complex types to create a derived type. Below is a list of the logical types that Vertica supports:

• Decimal
• Date
• Time
• Timestamp
• Duration

For details, see Logical Data Types for favroparser.

Supported Platforms

Microsoft Edge is a supported web browser for Vertica Management Console.

Tables

Higher Limit on Table Column Definitions

Vertica tables now support up to 9800 column definitions.

Expanded Support for SET USING/DEFAULT Expressions

DEFAULT and SET USING expressions in flattened tables now support correlated subqueries, and can combine these with expressions that reference values in the current table. For details, see Expressions with correlated subqueries.

User-Defined Extensions

Higher Limit on Function Arguments

User-defined functions, such as transform and scalar functions, now support up to 9800 arguments.

What's Deprecated in Vertica 10.1

This section describes the two phases Vertica follows to retire Vertica functionality:

• Deprecated: Vertica announces deprecated features and functionality in a major or minor release. Deprecated features remain in the product and are functional. Published release documentation announces deprecation on this page. When users access this functionality, it may return informational messages about its pending removal.
• Removed: Vertica removes a feature in a major or minor release that follows the deprecation announcement. Users can no longer access the functionality, and this page is updated to verify removal. Documentation that describes this functionality is removed, but remains in previous documentation versions.

Deprecated

The following Vertica functionality was deprecated and will be retired in future versions:

Removed

The following functionality is no longer accessible as of the releases noted:

For more information see Deprecated and Retired Functionality in the Vertica documentation.

Vertica 10.1.0-3: Resolved Issues

Release Date: 3/17/2021

This hotfix addresses the issues below.

Vertica 10.1.0-2: Resolved Issues

Release Date: 2/23/2021

This hotfix addresses the issues below.

Vertica 10.1.0-1: Resolved Issues

Release Date: 2/9/2021

This hotfix addresses the issues below.

Vertica 10.1.0: Resolved Issues

Release Date: 2/1/2021

This hotfix addresses the issues below.

Known issues Vertica 10.1.0

Updated: 2/1/21

Vertica makes every attempt to provide you with an up-to-date list of significant known issues in each release. We will update this list as we resolve issues and as we learn of new issues.