Visão Geral

Este Curso Greenplum Architecture e SQL Greenplum foi projetado para fornecer aos alunos um conhecimento e compreensão mais profundos da Arquitetura e SQL Greenplum e de como escrevê-los. Os alunos aprenderão a Arquitetura Greenplum e SQL começando no nível mais básico e indo até o nível mais avançado com muitos exemplos.

Objetivo

Após a conclusão do curso Greenplum Architecture and SQL, os alunos serão capazes de:

• Identifique e aplique funções SQL básicas
• Entenda a cláusula WHERE
• Contraste distinto vs. grupo por
• Reconhecer e aplicar funções, como:
• Agregação, Junção, Data, OLAP, Visualização, Subconsulta e Agregado Estatístico
• Reconhecer e produzir tabelas temporárias
• Definir e distinguir Strings
• Interrogue os dados
• Definir operadores
• Demonstrar conhecimento da Linguagem de Manipulação de Dados (DML)

Publico Alvo

• Qualquer pessoa que deseje aprender a Arquitetura Greenplum e SQL, desde iniciantes até um público avançado

Pre-Requisitos

Não se aplica!

Materiais

Conteúdo Programatico

Introduction to the Greenplum Architecture

1. The Basics of a Single Computer
2. Data in Memory is Fast as Lightning
3. Parallel Processing Of Data
4. Symmetric Multi-Processing (SMP) Server
5. Commodity Hardware Servers are Configured for Greenplum
6. The Segment's Responsibilities The Host's Plan is Either All Segments or a Single Segment
7. Greenplum has Linear Scalability
8. The Architecture of A Greenplum Data Warehouse
9. Nexus is Now Available For Greenplum

Greenplum Table Structures

1. The Concepts of Greenplum Tables
2. Tables are Either Distributed by Hash or Random
3. Random Distribution Uses a Round Robin Technique
4. Table are Either a Heap or Append-Only
5. Tables are Stored in Either Row or Columnar Format
6. Comparing Normal Table Vs. Columnar Tables
7. Segments on Distributions are Aligned to Rebuild a Row
8. Visualize the Data – Rows vs. Columns
9. Table Rows are Either Sorted or Unsorted
10. Creating a Clustered Index in Order to Physically Sort Rows
11. Physically Ordered Tables Are Faster on Certain Queries
12. Another Way to Create a Clustered Table
13. Creating a B-Tree Index and then Running Analyze
14. Creating a Bitmap Index
15. Tables Can Be Partitioned
16. Creating a Partitioned Table Using a List
17. Creating a Multi-Level Partitioned Table
18. Not Null and Unique Constraints
19. Unique Constraints That Fail
20. Primary Key Constraints
21. A Primary Key Automatically Creates a Unique Index
22. Creating an Automatic Number Called a Sequence
23. Multiple INSERT example Using a Sequence

Hashing and Data Distribution

1. Distribution Keys Hashed on Unique Values Spread Evenly
2. Distribution Keys With Non-Unique Values Spread Unevenly
3. Best Practices for Choosing a Distribution Key
4. The Hash Map Determines which Segment owns the Row
5. The Hash Map Determines which Node will Own the Row
6. Hash Map Determines which Node will Own the Row
7. A Review of the Hashing Process
8. Non-Unique Distribution Keys have Skewed Data

The Technical Details

1. Greenplum Limitations
2. Tables are Distributed Across All Segments
3. The Table Header and the Data Rows are Stored Separately
4. Segments Store Rows inside a Data Block Called a Page
5. To Read a Data Block a Node Moves the Block into Memory
6. A Full Table Scan Means All Nodes Must Read All Rows
7. Rows are Organized inside a Page
8. Heap Page
9. Creating a Table that has a Clustered Index
10. Clustered Index Page
11. The Row Offset Array is the Guidance System for Every Row
12. The Row Offset Array Provides Two Search Options
13. The Row Offset Array Helps With Inserts
14. B-Trees
15. The Building of a B-Tree for a Clustered Index
16. When Do I Create a Clustered Index?
17. When Do I Create a Non Clustered Index?
18. B-Tree for Non Clustered Index on a Clustered Table
19. Adding a Non Clustered Index To A
20. B-Tree for Non Clustered Index on a Heap Table

Physical Database Design

1. The Four Stages of Modeling for Greenplum - Check out #4
2. The Logical Model
3. First, Second and Third Normal Form
4. The Employee_Table and Department_Table can be Joined
5. The Employee_Table and Department_Table Join SQL
6. The Extended Logical Model Template
7. User Access is of Great Importance
8. User Access in Layman’s Terms
9. User Access for Joins in Layman’s Terms
10. The Nexus Shows Users the Table’s Distribution Key
11. Data Demographics:
12. Distinct Rows
13. Distinct Rows Query
14. Max Rows Null
15. Max Rows Null Query
16. Max Rows Per Value
17. Max Rows Per Value
18. Typical Rows Per Value
19. Change Rating
20. Typical Rows Per Value Query For Greenplum Systems
21. SQL to Get the Average Rows Per Value for a Column (Mean)
22. Factors When Choosing Greenplum Indexes
23. Distribution Key Data Demographics Candidate Guidelines
24. Distribution key Access Considerations
25. Step 1 is to Pick All Potential Distribution Key Columns
26. Step 2 is to Pick All Potential Secondary Indexes
27. Answer to 2nd Step to Picking Potential Secondary Indexes
28. Choose the Distribution Key and Secondary Indexes
29. 3rd Step is to Picking your Indexes
30. Our Index Picks

Denormalization

1. Denormalization
2. Derived Data
3. Repeating Groups
4. Pre-Joining Tables
5. Storing Summary Data with a Trigger
6. Summary Tables or Data Marts the Old Way
7. Horizontal Partitioning the Old Way and the New Way
8. Vertical Partitioning the Old Way
9. Columnar Tables Are the New Vertical Partitioning

Nexus for Greenplum

1. Nexus Queries Every Major System
2. Nexus Data Visualization
3. Nexus is Doing a Five-Table Join
4. Nexus Generates the SQL Automatically
5. Nexus Delivers the Report
6. Cross-System Joins From Teradata, Oracle and SQL Server
7. The Tabs of the Super Join Builder
8. The 9 Tabs of the Super Join Builder
9. Objects Tab 1- Selecting Columns in the Objects Tab
10. Columns Tab 2- Removing Columns From the Report in the Columns Tab
11. Sorting Tab 3
12. Joins Tab 4
13. Where Tab 5- Using the WHERE Tab For Additional WHERE or AND
14. SQL Tab 6 – check paragraph below
15. Answer Set Tab 7
16. Analytics Tab 9
17. Nexus Data Movement
18. Moving a Single Table To a Different System
19. The Single Table Data Movement Screen
20. Moving an Entire Database To a Different System
21. The Database Mover Screen and Options Tab
22. Converting DDL Table Structures
23. Compare and Synchronize
24. Compare Two Different Databases From Different Systems
25. Comparisons Down to the Column Level
26. The Results Tab
27. View Differences
28. Synchronizing Differences In the Results Tab
29. Hound Dog Compression

The Basics of SQL

1. Introduction
2. SELECT * (All Columns) in a Table
3. Fully Qualifying a Database, Schema and Table
4. SELECT Specific Columns in a Table
5. Sort the Data with the ORDER BY Keyword
6. ORDER BY Defaults to Ascending
7. Use the Name or the Number in your ORDER BY Statement
8. Two Examples of ORDER BY using Different Techniques
9. Changing the ORDER BY to Descending Order
10. NULL Values sort First in Ascending Mode (Default)
11. NULL Values sort Last in Descending Mode (DESC)
12. Major Sort vs. Minor Sorts
13. Multiple Sort Keys using Names vs. Numbers
14. Sorts are Alphabetical, NOT Logical
15. Using A CASE Statement to Sort Logically
16. How to ALIAS a Column Name
17. A Missing Comma can by Mistake become an Alias
18. Comments using Double Dashes are Single Line Comments
19. Comments for Multi-Lines

The WHERE Clause

1. The WHERE Clause limits Returning Rows
2. Double Quoted Aliases are for Reserved Words and Spaces
3. Character Data needs Single Quotes in the WHERE Clause
4. Comparisons against a Null Value
5. Use IS NULL or IS NOT NULL when dealing with NULLs
6. Using Greater Than or Equal To (>=)
7. AND in the WHERE Clause
8. OR in the WHERE Clause
9. Troubleshooting Character Data
10. Using Different Columns in an AND Statement
11. What is the Order of Precedence?
12. Using Parentheses to change the Order of Precedence
13. Using an IN List in place of OR
14. IN List vs. OR brings the same Results
15. Using a NOT IN List
16. Null Values in a NOT IN List Bring Back No Rows
17. A Technique for Handling Nulls with a NOT IN List
18. BETWEEN is Inclusive
19. NOT BETWEEN is Also Inclusive
20. LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
21. LIKE command Underscore is Wildcard for one Character
22. ilike
23. LIKE Command Works Differently on Char Vs Varchar
24. Troubleshooting LIKE Command on Character Data
25. Introducing the TRIM Command
26. Introducing the RTRIM Command
27. Numbers are Right Justified and Character Data is Left
28. A Visual of CHARACTER Data vs. VARCHAR Data
29. Use the TRIM command to remove spaces on CHAR Data
30. Escape Character in the LIKE Command changes Wildcards
31. Escape Characters Turn off Wildcards in the LIKE Command
32. Introducing the RTRIM Command
33. An example of Data with Left and Right Justification
34. A Visual of CHARACTER Data vs. VARCHAR Data
35. RTRIM command Removes Trailing spaces on CHAR Data
36. Using Like with an AND/OR Clause to Find Letters

Distinct vs. Group By

1. The Distinct Command
2. Distinct vs. GROUP BY

Aggregation

1. The 3 Rules of Aggregation
2. There are Five Aggregates
3. Troubleshooting Aggregates
4. GROUP BY when Aggregates and Normal Columns Mix
5. GROUP BY delivers one row per Group
6. GROUP BY Dept_No or GROUP BY 1 the same thing
7. Limiting Rows and Improving Performance with WHERE
8. WHERE Clause in Aggregation limits unneeded Calculations
9. Keyword HAVING tests Aggregates after they are Totaled
10. Aggregates Return Null on Empty Tables
11. Keyword HAVING is like an Extra WHERE Clause for Totals
12. Keyword HAVING tests Aggregates after they are Totaled
13. Getting the Average Values Per Column
14. Average Values Per Column For all Columns in a Table
15. Three types of Advanced Grouping
16. Group By Grouping Sets/Rollup
17. GROUP BY Cube

Join Functions

1. Redistribution
2. Big Table Small Table Join Strategy
3. Duplication of the Smaller Table across All-Distributions
4. If the Join Condition is the Distribution Key no Movement
5. Matching Rows That Are On The Same Node Naturally
6. Strategy 1 of 4 – The Merge Join
7. Strategy 2 of 4 – The Hash Join
8. Strategy 3 of 4 – The Nested Join
9. Strategy 4 of 4 – The Product Join
10. A Two-Table Join Using Traditional Syntax
11. A two-table join using Non-ANSI Syntax with Table Alias
12. You Can Fully Qualify All Columns
13. A two-table join using ANSI Syntax
14. Both Queries have the same Results and Performance
15. LEFT OUTER JOIN
16. RIGHT OUTER JOIN
17. FULL OUTER JOIN
18. Which Tables are the Left and which Tables are Right?
19. INNER JOIN with Additional AND Clause
20. ANSI INNER JOIN with Additional AND and WHERE Clause
21. OUTER JOIN with Additional WHERE and AND Clause
22. Evaluation Order for Outer Queries
23. The DREADED Product Join
24. The Horrifying Cartesian Product Join
25. The ANSI Cartesian Join will ERROR
26. The CROSS JOIN
27. The Self Join
28. The Self Join with ANSI Syntax
29. How would you Join these two tables?
30. An Associative Table is a Bridge that Joins Two Tables
31. The 5-Table Join – Logical Insurance Model
32. The Nexus Query Chameleon Writes the SQL for Users.

Date Function

1. Current_Date
2. Current_Date, Current_Time, and Current_Timestamp
3. Current_Time vs. LocalTime With Precision
4. Local_Time and Local_Timestamp With Precision
5. Now() and Timeofday() Functions
6. Adding A Week to a Date
7. Add or Subtract Days from a date
8. Formatting Dates and Dollar Amounts
9. The EXTRACT Command
10. EXTRACT Command on the Century
11. Date_part Command
12. Date_Trunc Command With Time/Dates
13. The AGE Command
14. Epoch
15. Using Intervals
16. Interval Arithmetic Results
17. A Complex Time Interval example using CAST
18. The OVERLAPS Command
19. Using Both CAST and CONVERT in Literal Values
20. A Better Technique for YEAR, MONTH, and DAY Functions

Conversions and Formatting

1. Postgres Conversion Functions
2. To_Char command Examples
3. Formatting A Date with To_Char
4. To_Number
5. To_Date
6. To_Timestamp

Sub-query Functions

1. An IN List is much like a Subquery
2. The Subquery
3. The Three Steps of How a Basic Subquery Works
4. These are Equivalent Queries
5. The Final Answer Set from the Subquery
6. Should you use a Subquery of a Join?
7. The Basics of a Correlated Subquery
8. The Top Query always runs first in a Correlated Subquery
9. Correlated Subquery Example vs. a Join with a Derived Table
10. How to handle a NOT IN with Potential NULL Values
11. IN is equivalent to =ANY
12. Using a Correlated Exists
13. How a Correlated Exists matches up
14. The Correlated NOT Exists

OLAP Functions

1. CSUM
2. The ANSI CSUM
3. Troubleshooting The ANSI OLAP on a GROUP BY
4. Reset with a PARTITION BY Statement
5. PARTITION BY only Resets a Single OLAP not ALL of them
6. Moving SUM
7. How ANSI Moving SUM Handles the Sort
8. Moving SUM every 3-rows Vs a Continuous Average
9. Partition By Resets an ANSI OLAP
10. Both the Greenplum Moving Average and ANSI Version
11. Moving Average
12. The Moving Window is Current Row and Preceding
13. How Moving Average Handles the Sort
14. Moving Average every 3-rows Vs a Continuous Average
15. Partition By Resets an ANSI OLAP
16. Moving Difference using ANSI Syntax with Partition By
17. RANK Defaults to Ascending Order
18. Getting RANK to Sort in DESC Order
19. RANK() OVER and PARTITION BY
20. RANK and DENSE RANK
21. PERCENT_RANK() OVER
22. COUNT OVER for a Sequential Number
23. Troubleshooting COUNT OVER
24. The MAX OVER Command
25. Troubleshooting MAX OVER
26. The MIN OVER Command
27. Troubleshooting MIN OVER
28. Finding a Value of a Column in the Next Row with MIN
29. The Row_Number Command
30. Using a Derived Table and Row_Number
31. Ordered Analytics OVER
32. CURRENT ROW AND UNBOUNDED FOLLOWING
33. Different Windowing Options
34. The CSUM For Each Product_Id and the Next Start Date
35. How Ntile Works
36. Ntile
37. Ntile Percentile
38. Using Tertiles (Partitions of Four)
39. NTILE
40. Using FIRST_VALUE
41. FIRST_VALUE
42. LAST_VALUE
43. LEAD
44. LAG
45. CUME_DIST
46. SUM(SUM(n))

Temporary Tables

1. There are Two Types of Temporary Tables
2. CREATING A Derived Table
3. Naming the Derived Table
4. Aliasing the Column Names in The Derived Table
5. Multiple Ways to Alias the Columns in a Derived Table
6. CREATING A Derived Table using the WITH Command
7. The Same Derived Query shown Three Different Ways
8. Most Derived Tables Are Used To Join To Other Tables
9. The Three Components of a Derived Table
10. Visualize This Derived Table
11. A Derived Table and CAST Statements
12. Clever Tricks on Aliasing Columns in a Derived Table
13. MULTIPLE Derived Tables using the WITH Command
14. Three Steps to Creating a Temporary Table
15. Three Versions of Creating a Temporary Table
16. ON COMMIT PRESERVE ROWS is the Greenplum Default
17. ON COMMIT DELETE ROWS
18. How to Use the ON COMMIT DELETE ROWS Option
19. ON COMMIT DROP
20. Create Table AS/LIKE
21. Creating a Clustered Index on a Temporary Table

Substrings and Positioning Functions

1. The CHARACTERS Command Counts Characters
2. CHARACTER_LENGTH and OCTET_LENGTH
3. The TRIM Command
4. Trim Combined with the CHARACTERS Command
5. A Visual of the TRIM Command Using Concatenation
6. The SUBSTRING Command
7. An example using SUBSTRING, TRIM and CHAR Together
8. The POSITION Command finds a Letters Position
9. Concatenation
10. Concatenation and SUBSTRING
11. Four Concatenations Together
12. Troubleshooting Concatenation

Interrogating the Data

1. The NULLIF Command
2. The COALESCE Command – Fill In the Answers
3. COALESCE is Equivalent to This CASE Statement
4. The COALESCE Command
5. The Basics of CAST (Convert and Store)
6. A Rounding Example
7. Some Great CAST (Convert And STore) example
8. Using an ELSE in the Case Statement
9. Using an ELSE as a Safety Net
10. Rules For a Valued Case Statement
11. Rules for a Searched Case Statement
12. Valued Case Vs. A Searched Case
13. The CASE Challenge
14. Combining Searched Case and Valued Case
15. A Trick for getting a Horizontal Case
16. Nested Case

Set Operators Functions

1. Rules of Set Operators
2. INTERSECT Explained Logically
3. UNION Explained Logically
4. UNION ALL Explained Logically
5. EXCEPT Explained Logically
6. An Equal Amount of Columns in both SELECT List
7. Columns in the SELECT list should be from the same Domain
8. The Top Query handles all Aliases
9. The Bottom Query does the ORDER BY (a Number)
10. Great Trick: Place your Set Operator in a Derived Table
11. UNION Vs UNION ALL
12. Using UNION ALL and Literals
13. A Great example of how EXCEPT works
14. USING Multiple SET Operators in a Single Request
15. Changing the Order of Precedence with Parentheses
16. Using UNION ALL for speed in Merging Data Sets

View Functions

1. The Fundamentals of Views
2. Creating a Simple View to Restrict Sensitive Columns/Rows
3. Basic Rules for Views
4. Exception to the ORDER BY Rule inside a View
5. Views sometimes CREATED for Formatting
6. Creating a View to Join Tables Together
7. Another Way to Alias Columns in a View CREATE
8. The Standard Way Most Aliasing is Done
9. What Happens When Both Aliasing Options Are Present
10. Resolving Aliasing Problems in a View CREATE
11. Answer to Resolving Aliasing Problems in a View CREATE
12. Aggregates on View Aggregates
13. Altering A Table
14. A View that Errors After An ALTER

Table Create and Data Types

1. Greenplum Has Only Two Distribution Policies
2. Creating a Table With A Single Column Distribution Key
3. The Default Table Storage is a Heap
4. Creating a Table With a Multi-Column Distribution Key
5. Creating a Table With Random Distribution
6. Creating a Table With No Distribution Key
7. Guidelines for Partitioning a Table
8. Creating a Partitioned Table Using a Range
9. A Visual of One Year of Data with Range Partitioning
10. Creating a Partitioned Table Using a Range Per Day
11. Creating a Partitioned Table Using a List
12. Creating a Multi-Level Partitioned Table
13. Not Null Constraints
14. Unique Constraints
15. Primary Key Constraints
16. Check Constraints
17. Append Only Tables
18. Column-Orientated Tables
19. CREATE INDEX Syntax
20. Create Table LIKE
21. Greenplum Data Types

Data Manipulation Language (DML)

1. INSERT Syntax # 1
2. INSERT Syntax # 2
3. INSERT example with Syntax 3
4. INSERT/SELECT Command
5. Two UPDATE Examples
6. Subquery UPDATE Command Syntax
7. Join UPDATE Command Syntax
8. Fast UPDATE
9. The DELETE Command Basic Syntax
10. To DELETE or to TRUNCATE
11. Subquery and Join DELETE Command Syntax
12. ANALYZE and VACUUM
13. ANALYZE
14. What Columns Should You Analyze?
15. Why Analyze?
16. VACUUM

Greenplum Explain

1. How to See an EXPLAIN Plan
2. The Eight Rules to Reading an EXPLAIN Plan
3. Interpreting Keywords in an EXPLAIN Plan
4. Interpreting an EXPLAIN Plan
5. A Single Segment Retrieve – The Fastest Query
6. EXPLAIN With an ORDER BY Statement
7. EXPLAIN ANALYZE
8. EXPLAIN With a Range Query on a Table Partitioned By Day
9. EXPLAIN That Uses a B-Tree Index Scan
10. EXPLAIN That Uses a Bitmap Scan
11. EXPLAIN With a Simple Subquery
12. EXPLAIN With a Columnar Query
13. EXPLAIN With a Clustered Index
14. EXPLAIN With Join that has to Move Data
15. EXPLAIN With Join that has to Move Data
16. Changing the Join Query Changes the EXPLAIN Plan
17. Analyzing the Tables Structures For a 3-Table Join
18. An EXPLAIN For a 3-Table Join
19. Explain of a Derived Table vs. a Correlated Subquery

Statistical Aggregate Functions

1. The Stats Table
2. Above, is the Stats_Table data in which we will use in our statistical examples
3. The STDDEV_POP Function
4. The STDDEV_SAMP Function
5. The VAR_POP Function
6. The VAR_SAMP Function
7. The VARIANCE Function
8. The CORR Function
9. The COVAR_POP Function
10. The COVAR_SAMP Function
11. The REGR_INTERCEPT Function
12. The REGR_SLOPE Function
13. The REGR_AVGX Function
14. The REGR_AVGY Function
15. The REGR_COUNT Function
16. The REGR_R2 Function
17. The REGR_SXX Function
18. The REGR_SXY Function
19. The REGR_SYY Function
20. Using GROUP BY