Compatibility Functions

New SQL ROLLUP Function

HP Vertica 7.1 adds the GROUP BY clause Online Analytic Processing (OLAP) ROLLUP extension. The ROLLUP extension, and its accompanying grouping functions, let you perform multi-level aggregates in a single query.

How ROLLUP Works

ROLLUP performs aggregate operations on multiple levels in a hierarchy, creating sub-totals. HP Vertica7.1 then aggregates (or rolls up) these subtotals from the most detailed level to a grand total. ROLLUP requires an ordered list of grouping expressions used as arguments.

To aggregate data, ROLLUP performs these operations:

• Calculates the standard aggregate values in the GROUP BY clause.
• Creates progressively higher-level subtotals by moving from right to left through the grouping columns, starting with the lowest level.
• Continues this process until all the grouping columns have been incorporated and aggregations computed. It then reports a grand total.

Using ROLLUP equates to using a UNION ALL with a set of GROUP BY queries. With ROLLUP, you specify only the required groupings in the GROUP BY clause. This approach allows more efficient analysis and improved database performance.

ROLLUP Grouping Functions

ROLLUP supports the following grouping functions:

• GROUPING()
• GROUPING_ID()
• GROUP_ID()

These grouping functions identify the group to which each row belongs and enable sorting subtotal rows and results filtering.

Sorry, I mean function connect_by_path in Vertica 7.1

The SDK API almost always changes between major releases of HP Vertica (for example between versions 6.0 and 6.1), as HP expands the SDK's features in each major release. In addition, HP reserves the right to change the SDK API in the first service pack release (for example between (from version 7.1.0 to 7.1.1) to address any issues found in the initial release of a major version. After the first service pack, the SDK API is frozen and will not change for rest of the service packs for that major release (for example, 7.1.2, 7.1.3, and so forth).

These policies mean that:

• You need to update UDx libraries when you upgrade between major versions. For example, if you upgrade from version 7.0.1 to 7.1.0 you need to update your UDx libraries.
• You may need to update the UDx libraries if you upgrade from the first version of a major release to a later service pack release. For example, if you upgrade the server from version 7.1.0 to 7.1.2, you may need to update your UDx libraries. Version 7.1.1 (the first service pack release) may have SDK changes that make your libraries compiled with the 7.1.0 SDK incompatible with the HP Vertica 7.1.2 server.
• You do not need to update your UDx libraries when upgrading from one service pack to another. For example, if you upgrade from version 7.1.1 to 7.1.2, you do not need to update your UDx libraries. The SDK API version is the same between there versions and any later 7.1.x service pack releases.

...
Pre-upgrade Steps

Before upgrading your HP Vertica server, consider whether you have any UDx libraries that may be incompatible with the new version. Consult the release notes of the new server version to determine whether the SDK API has changed between the version of HP Vertica server you currently have installed and the new version. As mentioned previously, only upgrades from a previous major version or from the initial release of a major version to a service pack release can cause your currently-loaded UDx libraries to become incompatible with the server.

Any UDx libraries that are incompatible with the new version of the HP Vertica server must be recompiled. If you got the UDx library from a third party (such as through the HP Vertica Marketplace), you need to see if a new version has been released. If so, then download the UDx library and deploy it after you have upgraded the server (see Deploying A New Version of Your UDx Library).

If you developed the UDx yourself (or if whoever developed it supplied the UDx's source code) you must:

1. Recompile your UDx library using the new version of the HP Vertica SDK. See Compiling Your C++ UDx or Compiling and Packaging a Java UDx for more information.
2. Deploy the new version of your library. See Deploying A New Version of Your UDx Library.