Vertica

Author Archive

HP Vertica and Qlik Team Up to Hack for a Cause

On November 17 2014, HP Vertica took part in the annual Qlik Hackathon in Orlando, Florida. The hackathon, which took place the day before the Qlik World Conference, brought together engineers, developers, and Qlik experts from all over the world. The challenge was simple: leverage features from Qlik and its partners to create an application to aid the United Nations in their commitment to maintain international peace and security.

QlikHack1Qlik Hackathon: Bringing people together

Qlik and HP Vertica: The Perfect Fit
According to a Senior Political Affairs Officer at the United Nations Department of Political Affairs:

Internet information streams offer an instant snapshot, in real time, of the state of international affairs worldwide at any given moment. The patterns of nations’ Internet interaction with one another and their on-line articulation of emerging international issues frequently reveal diplomatic nuances and changing priorities. The synthesis and channeling of this information produces a powerful analytical tool and briefing resource, focusing not merely on content but also how the information is packaged and communicated – and to whom.

From the officer’s comment, we can see how companies like Qlik and HP Vertica are in a unique position to aid organizations like the United Nations in their goals.

Qlik and HP Vertica are a powerful combination because of Qlik’s ‘Direct Discovery’ feature, which is included in both the QlikView and Sense products. This feature enables business users to conduct associative analysis on large data sets that come directly from external big data sources, like HP Vertica. Users then have the ability to combine that big data with data stored in memory for rapid, intuitive visual analysis.

With the HP Vertica Analytics Platform, combined with the data visualization powers of Qlik products, the United Nations can take advantage of these massive internet information streams and apply the findings to important initiatives. The Qlik Hackathon provided a space for developers from all over the world to do just that.

Pre-Hackathon
Like all successful events, the Qlik Hackathon required preparation and collaboration. For weeks before the actual event, HP Vertica employees and partners were hard at work preparing.

HP Vertica employee Myles Collins worked with representatives from the United Nations, Qlik, Attivio, and Twitter to define goals and organize datasets for the hackers to use. The setup included accessing online data from various websites and Twitter feeds, retrieved by Attivio and Twitter, respectively. That data was then loaded into the HP Vertica Analytics Platform, which Myles installed on ‘on-metal’ servers provided by Rackspace.

A couple weeks before the Hackathon, partners met up for a test hackathon, to make sure everything ran smoothly. Once all the kinks were worked out, the hackathon was ready to begin.

The Hackathon Challenge
The morning of the hackathon, twenty teams of two, armed with resources from Qlik, HP Vertica, Attivio, Twitter, and Rackspace, geared up to create a web-based data visualization application using the Qlik Sense mashup API. This application would ultimately help the United Nations gather and explore data to help with their mission.

QlickHack2The hackers only had 8 hours to finish a challenge

The teams worked for eight hours straight to complete one of two challenges. For the first challenge, hackers needed to develop a diplomatic monitoring and analytics tool to monitor online data from official government sources. For example, teams could use the tool to analyze how official bodies of government in different countries respond to certain topics.
The second challenge asked participants to create similar visuals, but for public sentiment regarding UN initiatives. For example, teams could use this tool to gauge public opinion on how the UN has handled a certain situation.

QlikHack3Hackers hard at work

The applications produced during the hackathon are invaluable. They can help United Nations officers gauge sentiment from different bodies of people from different countries, which can lead to appropriate crises prevention or reaction initiatives.

Aftermath
In the aftermath of the hackathon, while the hackers rested, the partners were still hard at work judging the competition. The judges took many criterion into consideration, the foremost being: Does this address a United Nations need? The judges also based their decisions on technical criteria, innovation, ease of use, ability to leverage Qlik Sense’s Mashup API, and the “wow” factor. In the end, only three could win.

The winning teams, along with their Qlik Sense Mashups are shown here:

QlikHack4Third Place: Team 5

QlikHack5Second Place: Team 10

QlikHack6First Place: Team 9

HP supplied prizes to the winners, including HP 10 Plus Tablets, HP Chromebooks, and Beats Solo HP on-ear headphones.

The entire process was a wild success, not just for HP Vertica, but for the United Nations as well. Through the Qlik Hackathon, hackers not only got to experience the power of HP Vertica, but were able to apply their projects to an organization that strives for international peace and better standards of living.

Until next year, keep hacking!

Tech Support Series: Optimizing Projections

Welcome to another installment of our Top Tech Support Questions Answered blog series. In our first blog , we discussed ways to optimize your database for deletes. In this installment, we’ll talk about optimizing projections.

People often ask, “How can I optimize my projections for maximum query performance?” Like with many questions, the answer is “It depends.” This is because every database has a different structure and uses data in very different ways.

But fear not, we do have some general guidelines you can use to optimize your projections.

Your first step should always be to run Database Designer. The HP Vertica Database Designer creates optimized projections based on sample queries and sample data that you provide. However, you may find that you want to create your own projections as well.

If you feel you must create your own projections, focus on three characteristics:

  • Sort Order
  • Segmentation
  • Encoding

Sort Order
Choosing a good sort order can help you achieve maximum query performance. If you have queries that contain GROUP BY clauses, joins, or other predicates, it’s good practice to place the columns specified in those clauses early in the sort order. If you have no other criteria on how to sort your data, the fastest way to access the data is to first sort on the columns with the smallest number of distinct values (lowest cardinality) before the high-cardinality columns.

Segmentation
Also consider creating segmented projections on large tables to spread the query execution workload across multiple nodes. Projection segmentation also provides high availability and recovery, and optimizes query execution. Therefore, it’s important to determine which columns to use to segment a projection. For HP Vertica, hash segmentation is the preferred method of segmentation. Primary key columns that have a large number of unique data values (high cardinality) and acceptable skew in their data distribution are an excellent choice for hash segmentation.

Encoding
Database Designer implements optimum encoding for the data you provide. Likewise, when creating your own projections, make sure you specify the encoding on your projection columns to optimize query performance. With appropriate encoding, you can reduce your database footprint and improve query performance. Read more about the encoding types HP Vertica supports here.

So there you have it– three main characteristics to consider when creating your own projections. As mentioned before, all databases are different, so you may find that leveraging one approach over another is more beneficial for you. But focusing on these three things can make the whole process a little less daunting.

Stay tuned for more tech support blogs!

To learn about optimizing your projections using Database Designer, see our documentation.

New Configuration Parameter Storage and Setting Options

New Configuration Parameter Storage from Vertica Systems on Vimeo.

Set and Clear Configuration Parameters from Vertica Systems on Vimeo.

HP Vertica 7.1.x introduces a new way to store and set configuration parameters.

Previously, configuration parameter values were stored in individual vertica.conf files on each node. As of HP Vertica 7.1.0, these values are stored in the database catalog, allowing the values to stay consistent over all nodes.

Why is this better? Well, when the values were stored at the node level, it was possible that down nodes could have inconsistent values when returning to the cluster because the SET_CONFIG_PARAMETER statement only acts on up nodes. Additionally, there was no way to tell which vertica.conf file had the most up-to-date information. Now, since the values are stored in the database catalog, when the down node returns and grabs the latest catalog file, it also receives the most up-to-date parameter values.

Our new storage settings go hand-in-hand with the HP Vertica 7.1.1 syntax option that lets you SET and CLEAR parameters at the session, node, and database level. Previously, if you wanted to make node-level changes, you had to log on to an individual node hand-edit its vertica.conf file. Not only is this tedious, but if that node were to go down, you could lose those specific settings. As of 7.1.1, the new SET and CLEAR methods allow you to configure any one node’s parameter values from any other node. Use our new SET and CLEAR keywords with the ALTER statements to take advantage of this feature. With ALTER SESSION, ALTER NODE, and ALTER DATABASE, you can set and clear multiple parameters from any node.

Watch these videos to learn more about the new configuration parameter features.

To learn about when and how to set and clear configuration parameters, see our documentation.

HP Vertica for SQL on Hadoop

HP Vertica for SQL on Hadoop from Vertica Systems on Vimeo

HP Vertica now offers a SQL on Hadoop license, which allows you to leverage Vertica’s powerful analytics engine to explore data in Hadoop Distributed File System (HDFS).

This offering is licensed per-node/per-year term with no data volume limits.

With your SQL on Hadoop license, you get access to proven and enterprise features like:

  • Database designer
  • Management console
  • Workload management
  • Flex tables
  • External tables
  • Backup functionality

See our documentation on HP Vertica SQL on Hadoop for limitations.
To learn more about other HP Vertica licenses, view our Obtaining and Installing Your HP Vertica Licenses video or contact an HP Licensing center.

Connecting HP Vertica 7.x to Tableau Desktop 8.2

Connecting HP Vertica to Tableau Desktop from Vertica Systems on Vimeo.
Have you ever wanted to visualize your HP Vertica Analytics Platform with graphs, tables, maps, or other formats? The Tableau Desktop by Tableau Software visualization tool lets you do just that in a couple steps. Use the Tableau Desktop HP Vertica specific ODBC driver connector to access your data from HP Vertica and create different views for visual analysis. Watch this video to learn how to connect HP Vertica to Tableau Desktop using both the HP Vertica specific ODBC driver connector and the generic connector.

What’s New in Dragline (7.1.0): Resource Pool Routing

Resource Pool Routing from Vertica Systems on Vimeo.

HP Vertica 7.1.x introduces new features that allow you to dynamically reroute queries to secondary resource pools.

Use the new CASCADE TO parameter when creating or altering a resource pool to indicate a secondary resource pool to which queries can cascade and execute when they exceed the RUNTIMECAP of the pool on which they started running. This way, if a query exceeds its initial pool’s RUNTIMECAP, it can cascade to a designated secondary pool with a larger RUNTIMECAP instead of causing an error.

Because grant privileges are not considered on secondary pools, you can use this functionality to designate secondary resource pools where user queries can cascade to without giving users explicit permission to run queries on that pool.

Check out this video to learn more about dynamically rerouting queries.
For more information, see the documentation.

What’s New in Dragline (7.1.0): Active Standby Nodes

Active Standby Nodes Video

Active Standby Nodes from Vertica Systems on Vimeo.

HP Vertica 7.1.x introduces active standby nodes.

An active standby node is a type of HP Vertica node that does not perform computations or contain data, but exists as a backup, ready to replace a node that’s failed. When the active standby node replaces the failed node, the active standby node performs all the calculations of the failed node.

As an example, imagine your database cluster as a team of basketball stars, working together to play a game. Normally, things go pretty smoothly. But what if one of your players gets injured? In basketball, it is likely that another team member, who has since been sitting quietly on the sidelines watching the game, can jump in and perform the duties of the injured player. As of HP Vertica 7.1.x, you can now have that ‘extra player’ available for your cluster in the form of an active standby node. If a node gets injured, so to speak, the active standby node replaces it and performs all the calculations of the failed node.
Like in basketball, having an active standby node sitting on the sidelines is useful because you can swap it with the failed node (automatically or manually) and thus minimize down time.

Watch this video to learn more about active standby nodes and how to create them.

See the active standby node documentation for more information.

Get Started With Vertica Today

Subscribe to Vertica