HP Vertica - Architecture and SQL

Tom Coffing, Leslie Nolander

• Publisher: Coffing Publishing
• Year: 2015
• Topic: Computer Science
• eBook ISBN:
  9781940540344
• Subtopic: Data Warehousing
• Index: Computer Science

One of the most exciting new database inventions is Columnar technology. HP has built one of the best columnar databases in the world. HP Vertica is designed for both on-premises and cloud technology. This book details the architecture of the HP Vertica database and the SQL commands available. The book educates readers on how to create tables and indexes, how the data is distributed, and how the system process the data. Plus, it is followed up with over 500 pages of SQL examples and explanations. This book is a must have for anyone designing, implementing or querying the HP Vertica system. The Authors Tera-Tom Coffing, who has written over 75 successful books on Data Warehousing and Leslie Nolander, Chief Operations Officer (COO) of Coffing Data Warehousing bring a combined 40 years of experience of data warehouse knowledge to create this must have book.

Chapter 1 – What is Columnar?

“When you go into court you, are putting your fate into the hands of twelve people who weren’t smart enough to get out of jury duty.”

– Norm Crosby

What is Parallel Processing?

"After enlightenment, the laundry"

- Zen Proverb

"After parallel processing the laundry, enlightenment!"

-Matrix Zen Proverb

Two guys were having fun on a Saturday night when one said, "I’ve got to go and do my laundry." The other said, "What!?" The first man explained that if he went to the laundry mat the next morning, he would be lucky to get one machine and be there all day. But if he went on Saturday night, he could get all the machines. Then, he could do all his wash and dry in two hours. Now that's parallel processing mixed in with a little dry humor!

Nothing Happens on Disk

“When you are courting a nice girl, an hour seems like a second. When you sit on a red-hot cinder, a second seems like an hour. That’s relativity.”

– Albert Einstein

Data on disk does absolutely nothing. When data is requested, the computer moves the data one block at a time from disk into memory. Once the data is in memory, it is processed by the CPU at lightning speed. All computers work this way. The "Achilles Heel" of every computer is the slow process of moving data from disk to memory. The real theory of relativity is to find out how to get blocks of data from the disk into memory faster!

Data in Memory is fast as Lightning

“You can observe a lot by watching."

– Yogi Berra

Once the data block is moved off of the disk and into memory, the processing of that block happens as fast as lightning. It is the movement of the block from disk into memory that slows down every computer. Data being processed in memory is so fast that even Yogi Berra couldn't catch it!

Parallel Processing Of Data

"If the facts don't fit the theory, change the facts."

-Albert Einstein

Big Data is all about parallel processing. Parallel processing is all about taking the rows of a table and spreading them among many parallel processing units. Above, we can see a table called Orders. There are 16 rows in the table. Each parallel processor holds four rows. Now they can process the data in parallel and be four times as fast. What Albert Einstein meant to say was, “If the theory doesn't fit the dimension table, change it to a fact."

The Problem with Row-Based Data

Nothing happens on disk. For data to be processed, the block of disk data must be copied and moved into memory. The problem with row-based data is that the entire block must be moved into memory even when the query only needs to analyze a single column. When queries only need a few columns, moving the entire block is a lot of wasted energy.

Columnar Data Can Store Each Column in Their Own Block

Columnar systems can store each column of a table in their own individual block. This is extremely efficient when a query only needs a relatively few columns from the table to satisfy the query. Our query above only needs the Order_Total column to get the average Order_Total. Only one small block on each parallel process is moved into memory. Wow, that was fast!

Why Columnar?

Each data block holds a single column. The row can be rebuilt because everything is aligned perfectly. If someone runs a query that would return the average salary, then only one small data block is moved into memory. The salary block moves into memory where it is processed as fast as lightning. We just cut down on moving large blocks by 80%! Why columnar? Because like our Yiddish Proverb says, "All data is not kneaded on every query, so that is why it costs so much dough."

Row Based Blocks vs. Columnar Based Blocks

Both designs have the same amount of data. Both take up just as much space. In this example, both have 9 rows and five columns. If a query needs to analyze all of the rows or return most of the columns, then the row based design is faster and more efficient. However, if the query only needs to analyze a few rows or merely a few columns, then the columnar design is much lighter because not all of the data is moved into memory. Just one or two columns move. Take the road less traveled.

Visualize the Data – Rows vs. Columns

Both examples above have the same data and the same amount of data. If your applications tend to need to analyze the majority of columns or read the entire table, then a row-based system (top example) can move more data into memory. Columnar tables are advantageous when only a few columns need to be read. This is just one of the reasons why analytics goes with columnar like bread goes with butter. A row-based system must move the entire block into memory even if it only needs to read one row or even a single column. If a user above needed to analyze the Salary, the columnar system would move 80% less block mass.

The Architecture of Vertica

“Be the change that you want to see in the world.”

- Mahatma Gandhi

Vertica is a shared nothing architecture, designed as a collection of Linux cluster nodes connected by a TCP/IP network. Storage can be directly attached to each node, or SAN-based. This technology is relatively inexpensive. It might not "be the change you want to see in the world", but it will help your company "keep the change" because costs are low.

Vertica Architecture Terms

Host - A server with a 64-bit processor, memory, hard disk and TCP/IP network interface. Hosts share neither disk space nor main memory with each other. Vertica is a shared nothing MPP architecture.

Instance - An instance is a node running the Vertica process and disk storage (catalog and data) on a host. Only one instance of HP Vertica can be running on each host. Multiple instances make up a cluster.

Node - A host that is configured to run an instance of Vertica. It is a m...

Table of contents

• Cover
• The Tera-Tom Video Series
• The Tera-Tom Genius Series
• Tera-Tom- Author of over 50 Books
• The Best Query Tool Works on all Systems
• Trademarks and Copyrights
• About Tom Coffing
• About Leslie Nolander
• Contents
• Chapter 1 – What is Columnar?
• Chapter 2 – Vertica Data Distribution
• Chapter 3 – Clever Features of Vertica
• Chapter 4 - Nexus
• Chapter 5 – The Basics of SQL
• Chapter 6 – The WHERE Clause
• Chapter 7 – Aggregation
• Chapter 8 – Join Functions
• Chapter 9 – Date Functions
• Chapter 10 – OLAP Functions
• Chapter 11 – Temporary Tables
• Chapter 12 – Sub-query Functions
• Chapter 13 – Strings
• Chapter 14 – Interrogating the Data
• Chapter 15 – View Functions
• Chapter 16 – Set Operators Functions
• Chapter 17 – Table Create and Data Types
• Chapter 18 – Data Manipulation Language (DML)
• Chapter 19 – Statistical Aggregate Functions