Vertica

Archive for the ‘Aggregate projections’ Category

What’s New in Dragline (7.1.0): Projections with Expressions

Projections with Expressions Video

Projections with Expressions from Vertica Systems on Vimeo.

HP Vertica 7.1 introduces the ability to use expressions with projections.

When you create a projection, you now have the option to use an expression as a column definition. For example, you can declare a column that sums two variables. When you load data into a projection with expressions, HP Vertica automatically calculates the values according to the expressions and inserts the data into that new projection.
Once you create a projection with expressions, you can query the projection directly to access your pre-calculated data and eliminate resource-intensive computations at query time.

Watch this video to learn more about using expressions with projections.

Projections with Expressions documentation.

See also:
Live Aggregate Projections
Top-K Projections

What’s New in Dragline (7.1.0): Top-K Projections

Top-K Projections Video

Top K Projections from Vertica Systems on Vimeo.

HP Vertica 7.1 introduces Top-K projections. A Top-K projection is a type of live aggregate projection that returns the top k rows from a partition.

Top-K projections are useful when you want to retrieve the top rows from a group of frequently updated, aggregated data. For example, say you want to view a list of the 5 singers who have the most votes in a competition. This value will change every time a vote comes in. But since the data is aggregated as it is loaded into your table and Top-k projection, all you have to do is grab the top 5 rows to see the top 5 singers at any moment. Using a Top-k projection is more efficient than if you had to aggregate (count all the votes) every time you wanted to find the top 5 singers.

Check out this video to learn more about Top-k projections and stayed tuned for our next video ‘Projections with Expressions’.

Top-K Documentation

What’s New in Dragline (7.1.0): Live Aggregate Projections

Live Aggregate Projections Video

Live Aggregate Projections from Vertica Systems on Vimeo.

 

HP Vertica 7.1 introduces live aggregate projections. A live aggregate projection is a projection that contains one or more columns of data that have been aggregated from a table.

If you frequently query data that requires aggregation, you could benefit from using a live aggregate projection. Because data in your live aggregate projection is aggregated at load time, rather than at the time you run a query, you can save time and resources. On subsequent data loads, HP Vertica updates the table and loads the aggregated values into the live aggregate projection. If you query the live aggregate projection any time after that, you’ll not only see the same results you would if you queried the data from the table and then aggregated it, but you’ll also use less resources in the process.

Check out this video to learn more about Live Aggregate Projections. Stay tuned for our next video, ‘Top-K Projections’.

See Also:

 Live Aggregate Projections with HP Vertica

Live Aggregate Projection documentation

Smart Grid Solution Demo

Live Aggregate Projections with HP Vertica

Projections

The Dragline release of HP Vertica offers an exciting new feature that is unique in the world of big data analytics platforms. We now offer Live Aggregate projections as part of the platform. The impact is that you can really fly through certain types of big data analytics that typically grind down any analytics system.

Before I get into that, however, it’s important to back up and give some background on HP Vertica projections. Many databases use indexes and materialized views to improve query performance. However, these secondary structures have drawbacks. Materialized views and indexes can bloat and become a very inefficient way to optimize data analytics. They can be time-consuming to keep up-to-date during data loading, can require frequent rebuilding, and they can be tedious to manage.

HP Vertica has always had a better solution to materialized views and indexes. Vertica has no raw uncompressed base tables, no materialized views, and no indexes. Our optimizations consist of optimized collections of table columns, which we call “projections”. There are several different types of projections. At the core, a projection could be an optimized collection of pre-sorted columns than may contain some or all of the columns of one or more tables. A projection that joins one or more tables is called a pre-join projection with the benefit of speeding up joins. A projection that contains a pre-calculated aggregate function such as average, top-K, sum, etc. is called an aggregate projection, which is a new feature of our Dragline release.

What’s cool about aggregate projections is that queries that rely on aggregate functions like SUM, MIN/MAX and COUNT are no longer bog down the system with excessive I/O and calculation. Now, these calculations can be calculated and updated as data loads. The HP Vertica query optimizer creates the projections and always keeps them up-to-date, ready to answer your aggregate queries without having to grind and churn through the data.

In real life analytics situations, this new feature accelerates the speed and performance by computing metrics on the data as it arrives for targeted and personalized analytics without programming accelerator layers. It’s particularly powerful if you’re implementing smart metering applications, for example, where you are helping your customers understand their usage and compare it to others in the neighborhood. The aggregate information is available in the projection without having to recalculate it over and over again so your data analytics system is free to take on other workloads without the fuss.

Speeding up aggregate functions should help with many use cases for today and tomorrow. We live in a world where data volumes from smart devices such as smart buildings, mobile phones, GPS devices and sensors are ever-increasing. We’re finding value in leveraging this data to predict usage based on history, predict equipment failure, maximize heating/cooling/lighting costs, detect fraud and more. HP Vertica continues to believe that projections offer a superior solution to materialized views and indexes. Projections remove the trade-off between performance and data size and offer the ultimate in flexibility for fast big data analytics.

Get Started With Vertica Today

Subscribe to Vertica