Greenplum - Architecture and SQL

Tom Coffing, Leona Coffing

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

Greenplum is the first open source data warehouse. Purchased and improved by EMC, sold to Dell, makes Greenplum one of the most powerful and widely-used systems in the world. This incredible MPP data warehouse is designed for on-premises systems and the cloud. This book details the architecture of the Greenplum Data Warehouse and the SQL commands available. This book is perfect for anyone who designs, administers or queries Greenplum. The book educates readers on how to create tables and indexes, how the data is distributed, and how the system processes the data. Plus, it is followed up with over 700 pages of SQL examples and explanations. The Authors Tera-Tom Coffing, who has written over 75 successful books on Data Warehousing and Leona Coffing, Chief Financial Officer (CFO) of Coffing Data Warehousing bring a combined 40 years of experience of data warehouse knowledge to create this must have book.

Chapter 1 – Introduction to the Greenplum Architecture

“The man who has no imagination has no wings.”

– Muhammad Ali

What is Parallel Processing?

“After enlightenment, the laundry”

- Zen Proverb

“After parallel processing the laundry, enlightenment!”

- Greenplum 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 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

“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. In Greenplum, these parallel processing units are referred to as Segments. Above, we can see a table called Orders. There are 16 rows in the table. Each segment 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." Each Segment shares nothing and holds a portion of every table.

Symmetric Multi-Processing (SMP) Server

A Symmetric Multi-Processing system has multiple processors for extra power, but these processors share a single operating system, memory pool and they share access to the disks. This is a great architecture for speed, similar to a restaurant that is quick and organized, but it lacks the ability for unlimited expansion. When there are too many cooks in the kitchen you need an MPP system that scales many SMP systems together as one parallel processing data warehouse.

A Symmetric Multi-Processing (SMP) system is a single server that is sometimes referred to as a node. Watch next how Greenplum uses these servers (called Segment Servers) to create a Massively Parallel Processing (MPP) system using commodity hardware.

Commodity Hardware Servers are configured for Greenplum

Greenplum allows you to utilize commodity hardware servers called a Segment Host. Greenplum also allows you to configure your parallel processes called segments. The number of segments per Segment Host is usually defined by the number of CPU's the Segment Host contains. The Segment Hosts are connected together to create a Massively Parallel Processing (MPP) system from each SMP.

Greenplum allows commodity hardware to be utilized to create one giant Greenplum cluster.

Commodity Hardware Allows For One Segment per CPU

Greenplum provides incredible speeds with inexpensive costs by allowing customers to purchase commodity hardware. The rule of thumb is to create one segment per CPU. If you have two dual-core CPU processors in a server you should build four segments. By connecting multiple SMP servers together you can scale your Greenplum cluster in a linear fashion. Double the segments and double your speeds forever!

The Master Host

•When a user logs into Greenplum, the host will log them in and be responsible for their session.

•The host checks the SQL syntax, creates the EXPLAIN plan, checks the security, and builds a plan for the segments to follow.

•The host uses system statistics and statistics from the ANALYZE command to build the best plan.

•The host doesn't hold user data, but instead holds the Global System Catalog.

•The host always delivers the final answer set to the user.

The host is the boss and the segments are the workers. Who doesn't lover their boss? Users login to the host and never communi...

Table of contents

• Cover
• The Tera-Tom Video Series
• Current Books in the Tera-Tom Genius Series
• Current Books in the Tera-Tom Genius Series
• Our Recommended Book In The Tera-Tom Genius Series
• Tera-Tom- Author of over 75 Books
• The Best Query Tool Works on all Systems
• Trademarks and Copyrights
• About Tom Coffing
• About Leona Coffing
• Contents
• Chapter 1 – Introduction to the Greenplum Architecture
• Chapter 2 – Greenplum Table Structures
• Chapter 3 – Hashing and Data Distribution
• Chapter 4 – The Technical Details
• Chapter 5 – Physical Database Design
• Chapter 6 – Denormalization
• Chapter 7 - Nexus
• Chapter 8 – The Basics of SQL
• Chapter 9 – The WHERE Clause
• Chapter 10 – Distinct vs. Group By
• Chapter 11 – Aggregation
• Chapter 12 – Join Functions
• Chapter 13 – Date Functions
• Chapter 14 – Conversions and Formatting
• Chapter 15 – Sub-query Functions
• Chapter 16 – OLAP Functions
• Chapter 17 – Temporary Tables
• Chapter 18 – Character Strings
• Chapter 19 – Interrogating the Data
• Chapter 20 – Set Operators Functions
• Chapter 21 – View Functions
• Chapter 22 – Table Create and Data Types
• Chapter 23 – Data Manipulation Language (DML)
• Chapter 24 – ANALYZE and VACUUM
• Chapter 25 – Greenplum Explain
• Chapter 26 – Statistical Aggregate Functions