The Brilliance of Netezza

Tom Coffing, John Nolan

• Publisher: Coffing Publishing
• Year: 2014
• eBook ISBN: 9781940540276
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

The Brilliance of Netezza will make all readers instant experts on the architecture of Netezza. With its clever design and "secret sauce" of FPGA Cards and Zone Maps, Netezza is quickly becoming a staple of all major enterprise data warehouses. This is a perfect opportunity to make yourself an invaluable asset to your company by obtaining the kind of knowledge that this book contains.

Chapter 1 – How Netezza Works

“Let me once again explain the rules. Tera-Tom books Rule!”

Tera-Tom Coffing

What is Parallel Processing?

“After enlightenment, the laundry”

- Zen Proverb

“After parallel processing the laundry, enlightenment!”

- Netezza 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!

The Basics of a Single Computer

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. That is all you need to know to be a computer expert!

Netezza Parallel Processes Data

“If the facts don’t fit the theory, change the facts.”

- Albert Einstein

Netezza has always been the pioneer in parallel processing and is even credited with the invention of the Appliance. In the picture above, you see that we have 16 orders; four orders placed on each disk. It appears to be four separate computers, but this is one system. Netezza systems work just like a basic computer as they still need to move data from disk into memory, but Netezza divides and conquers. Each Snippet Processing Unit (SPU) holds a portion of the data for every table.

Netezza is Born to be Parallel

Each SPU holds a portion of every table and is responsible for reading and writing the data that it is assigned to and from its disk. Queries are submitted to the host who plans, optimizes, and manages the execution of the query by sending the necessary snippets to each SPU. Each SPU performs its snippet or snippets independent of the others, completely following only the host’s plan. The final results of queries performed on each SPU is returned to the host where they can be combined and delivered back to the user.

Starts with a Linux User, a Database User and A Database

The host is a Linux server that runs the Netezza software and utilities. The host controls and coordinates the activity of the Netezza Appliance and performs query optimization. The host also controls table and database operations, gathers and returns query results, and monitors the Netezza system. Netezza systems have two hosts in a highly available (HA) configuration. The host is connected to the Netezza Database, which consists of a series of parallel processors called Snippet Processing Units (SPUs) often referred to as S-Blades. SPU and S-Blade are synonymous and the term is used interchangeably. This book will most often refer to them as SPUs.

Each SPU holds a Portion of Every Table

Every SPU has the exact same tables, but each SPU holds different rows of those tables.

When a table is created on Netezza, each SPU receives that table. When data is loaded, the rows are hashed by a distribution key, so each SPU holds a certain portion of the rows. If the host orders a full table scan of a particular table, then all SPUs simultaneously read their portion of the data. This is the concept of parallel processing.

The Rows of a Table are Spread Across All SPUs

A Distribution Key will be hashed to distribute the rows among the SPUs. Each SPU will hold a portion of the rows. This is the concept behind parallel processing.

The Brilliance of Netezza

The brilliance behind Netezza is in the Field Programmable Gate Array. It has many components. First, understand that all data on disk is compressed (average 4X). The FPGA card sits just outside the disk. When a query is run on a table, each SPU holds a portion of that data, so each SPU holds what is termed a slice of data. Each SPU has their own FPGA card and CPU. The first thing analyzed is the zone map to see if the data block has the qualifying data. If it does, then the data slice is transferred to the FPGA card where it is uncompressed. This strategy stores data compressed and transfers data compressed thus saving space and transfer time. The second thing the FPGA card does is to eliminate columns that are not needed, thus reducing the block even further. Next, the FPGA card eliminates unneeded rows. The FPGA card then sends the smaller block directly to the CPU for processing. This brilliant strategy delivers only the data needed to be processed and allows the CPU to focus on what it does best which is complex analysis, joins, and aggregations.

Compress Engine II – Adaptive Stream Compression

• Automatic, system-wide data compression when data is stored on disk.

• Zero tuning and zero administration required.

• A table is compressed using a patent-pending algorithm that actually compresses at the column level, but the data is stored as an entire row.

• There are different compression strategies that are based on the data in that column.

• All data types are compressed.

• 4X compression is the average, but up to 32X is possible.

• When data is being queried, it is first brought into the FPGA card where it is uncompressed there.

• Automatic compression saves enormous space on disk, and when blocks are transferred from disk into the FPGA card, there is less traffic.

The Achilles heal of any computer system is moving da...

Table of contents

• Cover
• Title Page
• 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 John Nolan
• Table of Contents
• Chapter 1 – How Netezza Works
• Chapter 2 – A Chip Off The Old Block
• Chapter 3 – How Netezza Distributes the Data
• Chapter 4 – Deep Dive Inside a Netezza Extent and Row
• Chapter 5 – How Joins Work Internally
• Chapter 6 – CTAS and CBT
• Chapter 7 - Temporary Tables
• Chapter 8 - Materialized Views
• Chapter 9 – Collecting Statistics
• Chapter 10 – Using nzsql
• Chapter 11 – Creating Tables
• Chapter 12 – Creating Databases and Users and Managing Them
• Chapter 13 – Systems Views
• Chapter 14 – Explains