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.
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.
What's Deprecated in Vertica 10.1.1
Vertica 10.1.1-7: Resolved Issues
Vertica 10.1.1-6: Resolved Issues
Vertica 10.1.1-5: Resolved Issues
Vertica 10.1.1-4: Resolved Issues
Vertica 10.1.1-3: Resolved Issues
Vertica 10.1.1-2: Resolved Issues
Vertica 10.1.1-1: Resolved Issues
Vertica 10.1.1: Resolved Issues
What's Deprecated in Vertica 10.1.0
Vertica 10.1.0-3: Resolved Issues
Vertica 10.1.0-2: Resolved Issues
Vertica 10.1.0-1: Resolved Issues
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:
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.
Hotfixes are available to Premium Edition customers only. Contact Vertica support for information on downloading hotfixes.
Take a look at the Vertica 10.1.1 New Features Guide for a complete list of additions and changes introduced in this release.
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.
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.
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.
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.
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.
You can now create Eon Mode databases on Azure using Azure's blobs for communal storage, with the following restrictions:
For details, see Eon Mode Databases on Azure.
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.
Vertica now supports PMML models of version 4.4 and below.
Changes to MC Email Configuration
For more information on configuring email notifications for MC, see Set Up Email.
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:
| Total Local SSDs | Depot | Temp |
| 5 | 4 x 375 GB | 1 x 375 GB |
| 6 | 5 x 375 GB | 1 x 375 GB |
| 16 | 12 x 375 GB | 4 x 375 GB |
| 24 | 20 x 375 GB | 4 x 375 GB |
For details about persistent storage and instance types available for each new default configuration, see Eon Mode Volume Configuration Defaults for GCP.
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:
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.
EXPORT TO PARQUET now supports compression with GZIP, Brotli, and ZSTD, in addition to Snappy which was already supported.
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.
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.
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.
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.
The new TO_JSON function returns the JSON representation of a complex-type value, including mixed and nested complex types.
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.
The new APPROXIMATE_QUANTILES function computes an array of weighted, approximate percentiles of a column within some user-specified error.
The LISTAGG function now supports multi-character separators.
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.
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.
This section describes the two phases Vertica follows to retire Vertica functionality:
The following Vertica functionality was deprecated and will be retired in future versions:
| Release | Functionality | Notes |
|---|---|---|
| 10.1.1 | STRING_TO_ARRAY(array,delimiter) syntax | Instead, use USING PARAMETERS syntax for collection_delimiter and other new parameters. See STRING_TO_ARRAY. |
| Support for Red Hat Enterprise Linux / CentOS 6.x |
No functionality was removed in this release.
For more information see Deprecated and Retired Functionality in the Vertica documentation.
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-78347 | Data Networking | In rare circumstances, the socket on which Vertica accepts internal connections could erroneously close and send a large number of socket-related error messages to vertica.log. This issue has been fixed. |
| VER-78350 | S3, Storage and Access Layer | Vertica now stores S3 storage location Location_path values in the same upper and lower case format that they were created in. |
| VER-78351 | UI - Management Console | After an automatic upgrade in AWS, the Management Console Overview page now displays properly. |
| VER-78354 | Catalog Engine | Standby nodes did not get changes to the GENERAL resource pool when it replaced a down node. This problem has been resolved. |
| VER-78357 | Optimizer | Updating the result set of a query that called the volatile function LISTAGG resulted in unequal row counts among projections of the updated table. This issue has been resolved. |
| VER-78362 | UI - Management Console | COPY type of queries have been added to the list of queries displayed for Completed Queries on Query Monitoring Activity page. |
| VER-78364 | ILM, Tuple Mover |
Several Vertica partition management operations support a force-split option: copy_partitions_to_table, drop_partitions, move_partitions_to_table, and swap_partitions_between_tables. In all cases, if force-split is true, and the operation specifies a range of partition keys that span multiple containers or part of a single container, Vertica executes the operation in two steps: 1. Breaks open the ROS containers 2. Reorganizes/merges the ROS containers as per operation requirements Occasionally, the Tuple Mover would attempt to merge the split ROS containers before step 2 was complete, and the operation would fail. This issue has been resolved: the two stages are now part of a single transaction. Until the transaction is complete, the Tuple Mover does not queue merge requests on the split containers. |
| VER-78441 | Admin Tools | If admintools called create_db and the database creation process was lengthy, admintools sometimes prompted users to confirm whether to continue waiting. If the user did not answer the prompt--for example, when create_db was called by a script--create_db completed execution without creating all database nodes and properly updating the configuration file admintools.conf. In this case, the database was incomplete and unusable. Now, the prompt times out after 120 seconds. If the user doesn't respond within that time period, create_db exits. |
| VER-78500 | Installation Program | Vertica now properly handles nofile and nproc values in environments with >1TB RAM. |
| VER-78523 | Depot | Vertica now properly detects and handles corrupted depot index files. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-77828 | Backup/DR | When restoring objects in coexist mode, the STDOUT now contains the correct schema name prefix. |
| VER-77871 | Kafka Integration | The Kafka scheduler no longer hangs in rare cases. |
| VER-77875 | Backup/DR | Vertica no longer checks for access permissions check for the root of the S3 bucket during backup/restore. |
| VER-78124 | Catalog Engine | Vertica now restarts properly for nodes that have very large checkpoint files. |
| VER-78129 | Optimizer | Queries that included the TABLESAMPLE option were not supported for views. This issue has been resolved: you can now query views with the TABLESAMPLE option. |
| VER-78130 | Execution Engine |
It was unsafe to reorder query predicates when the following conditions were true: * The query contained a predicate on a projection's leading sort order columns that restricted the leading columns to constant values, where the leading columns also were not run-length encoded. * A SIPS predicate from a merge join was applied to non-leading sort order columns of that projection. This issue has been resolved: query predicates can no longer be reordered when it contains a predicate on a projection's leading sort order columns that restrict leading columns to constant values, where the leading columns are not run-length encoded. |
| VER-78132 | Installation Program | The Vertica installer now optimizes and caches the information obtained in get_disks and reuses it instead of calling get_disks repeatedly. |
| VER-78147 | Data Removal - Delete, Purge, Partitioning | If you created a local storage location for USER data on a cluster that included standby nodes, attempts to drop the storage location returned with an error that Vertica was unable to drop the storage location from standby nodes. This issue has been resolved. |
| VER-78201 | Spread | Spread now responds properly to network changes. |
| VER-78211 | Catalog Engine | If you called close_session() while running analyze_statistics() on a local temporary table, Vertica sometimes crashed. This issue has been resolved. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-77844 | Kafka Integration | The vkconfig scheduler now properly supports an offset of -3. |
| VER-78065 | Execution Engine | A change in Vertica 10.1 prevented volatile functions from being called multiple times in an SQL macro. This change affected the throw_error function. The throw_error function is now marked immutable, so SQL macros can call it multiple times. |
| VER-78092 | SDK-Python | Python users can now define scalar functions as immutable. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-76256 | Hadoop | Vertica now properly handles queries of external tables of Parquet files with ZSTD compression. |
| VER-77571 | Data Collector | Vertica now properly handles custom data collector settings when upgrading to version 10.x. |
| VER-77634 | Data Export | Vertica now displays a more helpful warning message when using the deprecated s3export function on Minio storage. |
| VER-77928 | Hadoop | Exporting data to Parquet now properly respects resource pool limits. |
| VER-77946 | Execution Engine | Before evaluating a query predicate on rows, Vertica gets the min/max of the expression to determine what rows it can first prune from the queried dataset. An incorrect check on a block's null count caused Vertica to use the maximum value of an all-null block, and mistakenly prune rows that otherwise would have passed the predicate. This issue has been resolved. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-77181 | Spread | Vertica now properly detects duplicate tokens with the same arq ID. |
| VER-77389 | Installation Program | The Vertica pre-upgrade script now displays more helpful error messages for users running it against version 10 and later databases. |
| VER-77391 | Data load / COPY, Security | The COPY option REJECTED DATA to TABLE now properly distributes data between tables with identical names belonging to different schemas. |
| VER-77458 | Optimizer | SELECT clause CASE expressions with constant conditions and string results that were evaluated to shorter strings sometimes produced an internal error when participating in joins with aggregation. This issue has been resolved. |
| VER-77490 | Execution Engine | If a query includes 'count(s.*)' where s is a subquery, Vertica expects multiple outputs for s.*. Because Vertica does not support multi-valued expressions in this context, the expression tree represents s.* as a single record-type variable. The mismatch in the number of outputs can result in database failure. |
| VER-77523 | Security | Vertica now automatically creates needed default key projections for a user with DML access when that user performs an INSERT into a table with a primary key and no projections. |
| VER-77573 |
Monitoring |
Startup.log now contains a section that identifies nodes that were slow to join a cluster. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-77369 | Hadoop | Vertica now properly connects to an HDFS nameservice regardless of the upper- or lowercase spelling of the nameservice. |
| VER-77295 | Backup/DR | Vertica vbr can now delete restore points that are already partially deleted. |
| VER-77246 | Data Removal - Delete, Purge, Partitioning | Vertica DML performance is now improved when loading data into partitioned tables. |
| VER-77180 | Execution Engine |
Vertica was unable to optimize queries on v_internal tables, where equality predicates (with operators = or <=>) filtered on columns relname or nspname, in the following cases:
In this case, Vertica was unable to optimize equality predicate nspname = 'xyz'. In all these cases, the queries are now optimized as expected. |
| VER-77154 | Tuple Mover | Previously, the Tuple Mover attempted to merge all eligible ROS containers without considering resource pool capacity. As a result, mergeout failed if the resource pool could not handle the mergeout plan size. This issue has been resolved: the Tuple Mover now takes into account resource pool capacity when creating a mergeout plan, and adjusts the number of ROS containers accordingly. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-76899 | Optimizer | The NO HISTOGRAM event was set incorrectly on the dc_optimizer_events table's hidden epoch column. As a result, the suggested_action column was also set incorrectly to run analyze_statistics. This issue is resolved: the NO HISTOGRAM event is no longer set on the epoch column. |
| VER-76971 | Backup/DR | Object restore now functions properly for objects >1 TB on Eon Mode clusters running on S3. |
| VER-76994 | Optimizer | Using unary operators as GROUP BY or ORDER BY elements in WITH clause statements caused Vertica to crash. The issue is now resolved. |
| VER-77020 | Client Drivers - ADO | Previously, cancellation of an active query issued by Vertica's ADO.NET driver did not work; the query ran to completion instead of being canceled. This problem has been corrected and active queries are now properly interrupted and terminated by cancel requests. |
| VER-77021 | Optimizer | If you called a view and the view's underlying query invoked a UDx function on a table with an argument of '*' (all columns), Vertica crashed if the queried table later changed--for example, columns were added to it. The issue has been resolved: the view now returns the same results. |
| VER-77079 | Machine Learning | Vertica now properly handles combined use of the ROC function and the predict_svm_classifier. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-76360 | Catalog Sync and Revive, Depot, EON | Previously, when a node belonging to a secondary subcluster restarted, it lost files in its depot. This issue has been fixed. |
| VER-76361 | Execution Engine | The REGEXP_REPLACE function now properly accepts and returns LONG VARCHAR values. |
| VER-76400 | Kafka Integration | Kafka UDx now officially supports SASL SCRAM-SHA-256 AND SCRAM-SHA-512 |
| VER-76457 | Data Export | Previously during export to Parquet, Vertica wrote the time portion of each timestamp value as a negative number for all timestamps before POSTGRES EPOCH DATE (2000-01-01). Due to this some tools (e.g. Impala) could not load such timestamps from parquet files exported by Vertica. This is fixed now. |
| VER-76475 | Execution Engine | With Vertica running on machines with very high core counts, complex memory-intensive queries featuring an analytical function that fed into a merge operation sometimes caused a crash if the query ran in a resource pool where EXECUTIONPARALLELISM was set to a high value. This issue has been resolved. |
| VER-76486 | Depot | System tables continued to report that a file existed in the depot after it was evicted, which caused queries on that file to return "File not found" errors. This issue has been resolved. |
| VER-76522 | Installation Program, UI - Management Console | The Vertica Management Console installer has been optimized to reduce its storage footprint. |
| VER-76581 | Recovery | Before this fix, when DFS files go missing on one or more nodes, it would cause the LGE and expected recovery epoch to be pushed to the createEpoch of the DFS file. This recovery epoch might be less than AHM epoch and prevent normal startup with auto recovery as well as ASR. In another scenario, if DFS file is missing on multiple nodes, it would also prevent startup with auto recovery with insufficient UP nodes error. Here, ASR would also fail as it does not try to recover the DFS files as part of specified recovery and relied on auto Recovery to recover missing files.
After this fix, we no longer consider DFS files in determining LGE and expected recovery epoch so, missing DFS files cannot push recovery epoch to behind AHM. Also, ASR will try to recover missing DFS files from replicas on other nodes and drop any unrecoverable DFS files (those that are missing on all nodes in cluster). This will prevent auto recovery later on from erroring out due to insufficient UP nodes. The cluster startup with ASR and --force flag will now succeed. |
| VER-76631 | Data Export, Optimizer | Previously, during export to parquet Vertica checked the write permissions for the output location on all UP nodes in the cluster. This caused errors when some nodes that are not participating in the export didn't have write permissions for the output location. This is now fixed by Vertica checking write permissions only on participating nodes. |
| VER-76655 | UI - Management Console | In the wizard mode of the MC Database Designer, k-safety warning dialog had OK button unresponsive under certain conditions. The issue was resolved. |
| VER-76700 | Admin Tools | When adding a node to an Eon-mode database with Administration Tools, users were prompted to rebalance the cluster, even though this action is not supported for Eon. This issue was resolved: now Administration Tools skips this step for an Eon database. |
| VER-76756 | Optimizer, Security | For a view A.v1, its base table B.t1, and an access policy on B.t1: users no longer require a USAGE privilege on schema B to SELECT view A.v1. |
| VER-76769 | Execution Engine | In queries that used variable-length-optimized joins, certain types of joins incurred a small risk of a crashing the database due to a problem when checking for NULL join keys. This issue has been resolved. |
| VER-76802 | Client Drivers - JDBC, ComplexTypes | The JDBC client driver now returns the correct type ID for complex types. |
| VER-76803 | Client Drivers - ODBC, Client Drivers - VSQL, Client Drivers – OLEDB, Security | Vertica FIPS support is restored. |
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.
|
Issue |
Component |
Description |
| VER-75057 | ComplexTypes | In an external table, if a struct contains an array field and the struct is null, the whole struct (ROW) is treated as null in Vertica. This behavior differs from structs without arrays, where a null struct is treated as a ROW where all fields are null. |
| VER-70468 | Documentation | Creating a load balance group for a subcluster with a ROUNDROBIN load balance policy only has an effect if you set the global load balancing policy to ROUNDROBIN as well. This is the case, even though the LOAD_BALANCE_GROUPS system table shows the group's policy as ROUNDROBIN. |
| VER-69803 | Hadoop | The infer_external_table_ddl meta-function will hang if you do not set authentication information in the current session when analyzing a file on GCP. |
| VER-64916 | Kafka Integration | When Vertica exports data collector information to Kafka via notifier, the serialization logic does not properly handle Vertica internal data type translation. When importing or copying that data back into the Management Console, Vertica uses the raw data in Kafka and inserts garbled data into the extended monitoring database. |
| VER-64352 | SDK | Under special circumstances, a sequence of statements to create and drop Python libraries fails. For example, the following session statements attempt to create Python libraries lib1 and lib2:
CREATE LIBRARY lib1 AS :lib1_path DEPENDS :numpy_path LANGUAGE 'Python'; DROP LIBRARY lib1; CREATE LIBRARY lib2 AS :lib2_path DEPENDS :sklearn_path LANGUAGE 'Python'; Here, lib1 is a Python library that imports module numpy, while lib2 imports module sklearn, which relies on numpy. After lib1 is dropped, the CREATE LIBRARY statement for lib2 fails. |
| VER-62983 | Hadoop | When hcatalogconnector schemas are created with custom_partitions enabled, poor performance has been observed when there are many (500+) partitions. By default, custom_partitions are disabled on hcatalogconnector schemas. |
| VER-61420 | Data Removal - Delete, Purge, Partitioning | Partition operations, such as move_partitions_to_table, must split storage containers that have both partitions that match the operations and partitions that do not. Version 9.1 introduced an inefficiency whereby such a split may separate a storage container into one more storage container than necessary. Note that any extraneous containers created this way will eventually be merged by the TM. |
| VER-61069 | Execution Engine | In very rare circumstances, if a vertica process crashes during shutdown, the remaining processes might hang indefinitely. |
| VER-48020 | Hadoop | Canceling a query that involves loading data from ORC or Parquet files can be slow if there are too many files involved in the load. |
Take a look at the Vertica 10.1 New Features Guide for a complete list of additions and changes introduced in this release.
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.
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:
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.
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.
vbr configuration files support new configuration parameters:
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 .
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.
Automatic node health checks now check the following local storage locations for read-write access:
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.
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.
New meta-functions support pinning projections to database depots:
Also, SET_DEPOT_PIN_POLICY_PARTITION now supports depot pinning partitions for specific projections.
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.
You can now configure encryption for new and existing notifiers with the following parameters:
For details on these and other parameters, see CREATE NOTIFIER and ALTER NOTIFIER.
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.
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.
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.
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.
In 10.1 Vertica has added several enhancements to Machine Learning:
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.
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)".
You can now set additional requirements for MC user passwords and login security. These include new options for managing:
For details, see Managing MC User Passwords.
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.
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.
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.
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.
Directed queries now support :v as an alias of IGNORECONST.
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.
For details, see Hash Authentication Parameters.
For details, see Profiles.
CREATE USER and ALTER USER now accept pre-hashed passwords. For details, see Setting and Changing User Passwords
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.
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).
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 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.
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:
For details, see Logical Data Types for favroparser.
Microsoft Edge is a supported web browser for Vertica Management Console.
Vertica tables now support up to 9800 column definitions.
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 functions, such as transform and scalar functions, now support up to 9800 arguments.
This section describes the two phases Vertica follows to retire Vertica functionality:
The following Vertica functionality was deprecated and will be retired in future versions:
| Release | Functionality | Notes |
|---|---|---|
| 10.1 | Reading structs from ORC files as expanded columns | Instead, define as ROW type (see Reading Complex Types from ORC Files). |
| MD5 hashing algorithm for user passwords | Instead, use SHA-512 (see Hash Authentication Parameters). | |
|
S3 vbr configuration parameters:
|
Superseded by new vbr configuration parameters that accept values for cloud storage locations:
If a vbr configuration file contains an s3-prefixed parameter, vbr returns a warning. |
|
| ARRAY_CONTAINS function | Instead, use CONTAINS. | |
| flatten_complex_type_nulls parameter to the ORC and Parquet parsers | ||
| Vertica JDBC API com.vertica.jdbc.kv package |
Affects the following interfaces:
|
The following functionality is no longer accessible as of the releases noted:
| Release | Functionality | Notes |
|---|---|---|
| 10.1 |
Eon Mode meta-functions:
|
Superseded by:
|
| Reading structs from Parquet files as expanded columns | Superseded by Creating Tables Using Structs. | |
| Parquet parser skip_strong_schema_match parameter | Superseded by Using Flexible Complex Types. | |
|
Array-specific functions:
|
Superseded by functions that operate on collections, including arrays:
|
|
| HiveMetadataCacheSizeMB configuration parameter | Superseded by ParquetMetadataCacheSizeMB . |
For more information see Deprecated and Retired Functionality in the Vertica documentation.
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-76041 | EON, Tuple Mover | Commit resync is now disabled for background Tuple Mover jobs. |
| VER-76046 | Execution Engine | Calls to export_objects sometimes allocated considerable memory while user acesss privileges to the object were repeatedly checked. The accumulated memory was not freed until export_objects returned, which sometimes caused the database to go down with an out-of-memory error. This issue has been resolved: now memory is freed more promptly so it does not excessively accumulate. |
| VER-76059 | Tuple Mover | Occasionally, the Tuple Mover dequeued DVMERGEOUT and MERGEOUT requests simultaneously and executed only the DVMERGEOUT requests, leaving the MERGEOUT requests pending indefinitely. This issue has been resolved: now, after completing execution of any DVMERGEOUT job, the Tuple Mover always looks for outstanding MERGEOUT requests and queues them for execution. |
| VER-76061 | Monitoring | The output of get_expected_recovery_epoch is no longer truncated in clusters with more than 20 nodes. |
| VER-76136 | Cloud - Azure | Loads on large Azure clusters no longer fail due to storage service throttling. |
| VER-76137 | Backup/DR | Vbr now supports restores from MinIO S3 locations containing special characters. |
| VER-76190 | Depot | System tables continued to report that a file existed in the depot after it was evicted, which caused queries on that file to return "File not found" errors. This issue has been resolved. |
| VER-76252 | Data Export | Parquet files exported by Vertica now properly display date values when imported to Impala. |
| VER-76393 | Data Collector, EON | DC table files are now appropriately cleared when dc sync is disabled. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-75827 | Hadoop | In some configurations, the Vertica server process would crash while logging HDFS events. This issue has been resolved. |
| VER-75901 | Tuple Mover | When Mergeout Cache is enabled, the dc_mergeout_requests system table now contains valid transaction ids instead of zero. |
| VER-75913 | Tuple Mover | The Tuple Mover logged a large number of PURGE requests on a projection while another MERGEOUT job was running on the same projection. This issue has been resolved. |
| VER-75973 | Hadoop | Fixed a bug in Parquet Predicate Pushdown where sometimes the correct rowgroups were not pruned from the Parquet file based on predicates if the file was on HDFS. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER- 75904 | Security | Vertica no longer takes a catalog lock during authentication, after the user's password security algorithm has been changed from MD5 to SHA512. This is due to removing the updating of the user's salt, which is not used for MD5 hash authentication. |
This hotfix addresses the issues below.
|
Issue |
Component |
Description |
| VER-36888 | Admin Tools | stop_db no longer returns early and now returns the proper return code. |
| VER-45474 | Optimizer | When a node is down, DELETE and UPDATE queries now use optimized query plans that account for the down node. |
| VER-68356 | Security | LDAPLink now properly trims whitespace from LDAPLinkSearchBase. |
| VER-70760 | Optimizer | Queries with subqueries on tables with TopK projections sometimes returned with an error. The issue has been resolved. |
| VER-71074 | Security | Previously, CREATE OR REPLACE VIEW could only replace an existing view if the current user was the existing view's owner. Now, DROP privilege is sufficient to replace the view (in addition to CREATE on the SCHEMA, as before). |
| VER-72232 | ComplexTypes | As a fix, the QUERY_EVENTS system table will no longer make the recommendation to collect stats on ARRAY & SET columns. |
| VER-72394 | Execution Engine | This issue would occur when the root operator would treat non-DML queries as DML plans. The way Vertica collects the processed row-count statistics differs between these two types, so the results were incorrect. Vertica now correctly detects whether a query represents a DML plan. |
| VER-72735 | Security | LDAPLink now checks for circular role/group assignment, assigns the first one returned (skipping the rest of the circular relationship), and then logs the error in DC_LDAP_LINK_EVENTS. |
| VER-72850 | Optimizer | Incorrect usage of meta-function EXPORT_TABLES in the SELECT statement of EXPORT TO VERTICA caused the database to crash. This issue has been resolved: incorrect usage of meta-functions now returns with an error. |
| VER-72978 | Depot | Resizing the database depot with alter_location_size were propagated sequentially to all nodes, which adversely affected performance in a database with many nodes. This issue has been resolved: depot size changes are now propagated to all nodes in parallel (simultaneously). |
| VER-73110 | Backup/DR | Previously, when migrate_enterprise_to_eon found projections that were Inconsistent with cluster segmentation, it wrote to the migration log file an incorrect anchor table name; it also provided only one projection, <projection-name>_b0. This issue has been resolved: now the log file error log file lists the names of all tables with problematic projections. You can use these names as arguments to meta-function REBALANCE_TABLE. |
| VER-73382 | Execution Engine | Queries sometimes threw a Sort Order Violation error when join output that was sorted like the target projection was consumed by a single Analytic function that only partially sorted the data as required by the target projection. This issue has been resolved. |
| VER-73811 | Execution Engine | Expressions of the form <expression> IN (<list of constants>) were not handled correctly in cases where <expression> was of type LONG VARCHAR or LONG VARBINARY. If the IN list was long, query execution could be slow and use excessive memory. This problem has been resolved. |
| VER-73841 | Admin Tools | Adding nodes on large, busy databases now runs fewer queries and takes less time. |
| VER-73881 | Scrutinize | Scrutinize now collects a memory report log. |
| VER-73937 | Execution Engine | Under some circumstances, the size of the METADATA resource pool was calculated incorrectly. This issue has been resolved. |
| VER-74043 | Admin Tools | Relaxed the checks of user storage locations during revive Eon database. |
| VER-74116 | Optimizer | Analytic functions triggered an Optimizer error when used with a LIMIT OVER clause. This issue has been resolved. |
| VER-74130 | Depot, Tuple Mover | Calls to meta-function enforce_object_storage_policy() on an Eon-mode database wrote a large number of errors to the Vertica log file. This issue has been resolved: Vertica now returns with a single error message that the meta-function is not supported in Eon mode. |
| VER-74182 | Admin Tools | The performance of db_remove_subcluster has been improved for subclusters with several nodes. |
| VER-74300 | Backup/DR | migrate_enterprise_to_eon migrated grants on external procedures even though the procedures themselves were excluded from migration. This inconsistency caused the migrated database to crash and further attempts at startup to fail. This issue has been resolved: the migration now excludes privileges that are granted on external procedures, and also checks that all migrated objects have consistent dependencies. |
| VER-74350 | Execution Engine | Adding support for tables with more than 1600 columns adversely affected performance of some operations. This issue has been resolved. |
| VER-74355 | ComplexTypes | ALTER TABLE...ALTER COLUMN did not correctly handle attempts to create multi-dimensional arrays, reporting the dimensionality incorrectly. Multi-dimensional arrays are not supported for internal tables, and Vertica now treats this as an error. |
| VER-74512 | Depot | System table DEPOT_PIN_POLICIES was erroneously populated with enterprise storage policies. This issue has been resolved: now, in Enterprise mode, DEPOT_PIN_POLICIES is always empty. |
| VER-74521 | Optimizer | When a LIMIT OVER query executed on a multi-node database, the data was always sent back to the initiator which applied the LIMIT across all nodes. This interim step was unnecessary for INSERT...SELECT queries, which read data from the same node where it is stored. This issue is now resolved: Vertica no longer transfers data to the initiator for INSERT...SELECT queries, and thus avoids the extra overhead previously incurred. |
| VER-74575 | Optimizer | In certain outer join operations that included a LIMIT OVER (PARTITION BY) clause, Vertica applied GUA (guaranteed uniqueness) optimization before populating the Top-K attribute, which pruned the inner view and caused an internal error. This issue has been resolved: now the optimizer defers GUA optimization until after it populates all query-related attributes. |
| VER-74576 | Execution Engine | When computing COUNT window aggregates over Fixed type columns that included UUIDs, the aggregate incremented the count on reading a row from the Fixed type column. It then also hit an assertion failure intended for aggregating over invalid types. This issue has been resolved: the assertion failure no longer occurs in Fixed type window aggregates. |
| VER-74588 | Hadoop | Queries to external tables with Hive partition columns could crash Vertica if a predicate on a partition column throws an error. This issue has been fixed. |
| VER-74616 | Execution Engine | Enabling inter-node encryption adversely affected query performance across a variety of workloads. In many cases, this issue has been addressed with improved performance. |
| VER-74707 | Tuple Mover | Enabling both mergeout caching (deprecated) and reflexive mergeout adversely affected performance on a new or restarted database. This issue has been resolved: when reflexive mergeout is enabled, mergeout caching is automatically disabled. |
| VER-74783 | DDL - Table | ALTER TABLE...OWNER TO threw an error when the target table was already owned by the specified user. This issue has been resolved: it now returns with NOTICE instead of ERROR. |
| VER-74795 | Optimizer | Previously, the Vertica optimizer ignored a PROJS hint that specified a live aggregate projection (LAP), and returned a warning. This issue has been resolved: the optimizer now supports PROJS hints that specify a LAP and uses the projection if it is feasible for that query. |
| VER-74918 | Security | LDAPLink now properly supports instances where the number of members returned for an attribute exceeds 1500. |
| VER-75051 | Execution Engine | An internal memory error sometimes caused simple CASE statements of this format to fail:
CASE expression WHEN comparand THEN result ... [ ELSE result ] END This issue has been resolved. |
| VER-75105 | Execution Engine | Passing a volatile expression such as RANDOM() as an argument to an inline function such as CORR returned incorrect output if the argument appeared multiple times in the function body. This issue has been resolved: now, when you pass a volatile expression as an argument multiple times to an inline function, Vertica returns an error message. |
| VER-75196 | Execution Engine | Calling ALTER TABLE REORGANIZE immediately after setting group partitioning with ALTER TABLE sometimes caused Vertica to crash. This issue has been resolved. |
| VER-75265 | Execution Engine | When a subquery was used in an expression and implied a correlated join with relations in the parent query, and also aggregated output, it sometimes returned multiple rows for each matched join key and returned unexpected results. This issue has been resolved. |
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.
|
Issue |
Component |
Description |
| VER-76269 | Optimizer | Executing EXPLAIN COPY on a new table fails. |
| VER-75057 | ComplexTypes | In an external table, if a struct contains an array field and the struct is null, the whole struct (ROW) is treated as null in Vertica. This behavior differs from structs without arrays, where a null struct is treated as a ROW where all fields are null. |
The only warranties for products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. Micro Focus shall not be liable for technical or editorial errors or omissions contained herein.
The information contained herein is subject to change without notice.
Confidential computer software. Valid license from Micro Focus required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.
© Copyright 2006 - 2021 Micro Focus, Inc.
Adobe® is a trademark of Adobe Systems Incorporated.
Microsoft® and Windows® are U.S. registered trademarks of Microsoft Corporation.
UNIX® is a registered trademark of The Open Group.