Visão Geral

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

Objetivo

Após a conclusão doCurso Greenplum Arquitetura Database, os alunos serão capazes de:

• Identificar e aplicar funções SQL básicas
• Entenda a cláusula WHERE
• Contraste distinto vs. agrupado por
• Reconhecer e aplicar funções, tais como:
• Agregação, Junção, Data, OLAP, Visualização, Subconsulta e Agregação Estatística
• Reconhecer e produzir Tabelas Temporárias
• Definir e distinguir Strings
• Interrogar os dados
• Definir operadores
• Demonstrar conhecimento da Linguagem de Manipulação de Dados (DML)

Pre-Requisitos

• Qualquer pessoa que tenha o desejo de aprender a Arquitetura Greenplum e SQL desde iniciantes até um público avançado

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.
33. Date Function
34. Current_Date
35. Current_Date, Current_Time, and Current_Timestamp
36. Current_Time vs. LocalTime With Precision
37. Local_Time and Local_Timestamp With Precision
38. Now() and Timeofday() Functions
39. Adding A Week to a Date
40. Add or Subtract Days from a date
41. Formatting Dates and Dollar Amounts
42. The EXTRACT Command
43. EXTRACT Command on the Century
44. Date_part Command
45. Date_Trunc Command With Time/Dates
46. The AGE Command
47. Epoch
48. Using Intervals
49. Interval Arithmetic Results
50. A Complex Time Interval example using CAST
51. The OVERLAPS Command
52. Using Both CAST and CONVERT in Literal Values
53. 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
7. Sub-query Functions
8. An IN List is much like a Subquery
9. The Subquery
10. The Three Steps of How a Basic Subquery Works
11. These are Equivalent Queries
12. The Final Answer Set from the Subquery
13. Should you use a Subquery of a Join?
14. The Basics of a Correlated Subquery
15. The Top Query always runs first in a Correlated Subquery
16. Correlated Subquery Example vs. a Join with a Derived Table
17. How to handle a NOT IN with Potential NULL Values
18. IN is equivalent to =ANY
19. Using a Correlated Exists
20. How a Correlated Exists matches up
21. 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))
47. Temporary Tables
48. There are Two Types of Temporary Tables
49. CREATING A Derived Table
50. Naming the Derived Table
51. Aliasing the Column Names in The Derived Table
52. Multiple Ways to Alias the Columns in a Derived Table
53. CREATING A Derived Table using the WITH Command
54. The Same Derived Query shown Three Different Ways
55. Most Derived Tables Are Used To Join To Other Tables
56. The Three Components of a Derived Table
57. Visualize This Derived Table
58. A Derived Table and CAST Statements
59. Clever Tricks on Aliasing Columns in a Derived Table
60. MULTIPLE Derived Tables using the WITH Command
61. Three Steps to Creating a Temporary Table
62. Three Versions of Creating a Temporary Table
63. ON COMMIT PRESERVE ROWS is the Greenplum Default
64. ON COMMIT DELETE ROWS
65. How to Use the ON COMMIT DELETE ROWS Option
66. ON COMMIT DROP
67. Create Table AS/LIKE
68. 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
13. Interrogating the Data
14. The NULLIF Command
15. The COALESCE Command – Fill In the Answers
16. COALESCE is Equivalent to This CASE Statement
17. The COALESCE Command
18. The Basics of CAST (Convert and Store)
19. A Rounding Example
20. Some Great CAST (Convert And STore) example
21. Using an ELSE in the Case Statement
22. Using an ELSE as a Safety Net
23. Rules For a Valued Case Statement
24. Rules for a Searched Case Statement
25. Valued Case Vs. A Searched Case
26. The CASE Challenge
27. Combining Searched Case and Valued Case
28. A Trick for getting a Horizontal Case
29. 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
17. View Functions
18. The Fundamentals of Views
19. Creating a Simple View to Restrict Sensitive Columns/Rows
20. Basic Rules for Views
21. Exception to the ORDER BY Rule inside a View
22. Views sometimes CREATED for Formatting
23. Creating a View to Join Tables Together
24. Another Way to Alias Columns in a View CREATE
25. The Standard Way Most Aliasing is Done
26. What Happens When Both Aliasing Options Are Present
27. Resolving Aliasing Problems in a View CREATE
28. Answer to Resolving Aliasing Problems in a View CREATE
29. Aggregates on View Aggregates
30. Altering A Table
31. 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
22. Data Manipulation Language (DML)
23. INSERT Syntax # 1
24. INSERT Syntax # 2
25. INSERT example with Syntax 3
26. INSERT/SELECT Command
27. Two UPDATE Examples
28. Subquery UPDATE Command Syntax
29. Join UPDATE Command Syntax
30. Fast UPDATE
31. The DELETE Command Basic Syntax
32. To DELETE or to TRUNCATE
33. Subquery and Join DELETE Command Syntax

ANALYZE and VACUUM

1. ANALYZE
2. What Columns Should You Analyze?
3. Why Analyze?
4. VACUUM
5. Greenplum Explain
6. How to See an EXPLAIN Plan
7. The Eight Rules to Reading an EXPLAIN Plan
8. Interpreting Keywords in an EXPLAIN Plan
9. Interpreting an EXPLAIN Plan
10. A Single Segment Retrieve – The Fastest Query
11. EXPLAIN With an ORDER BY Statement
12. EXPLAIN ANALYZE
13. EXPLAIN With a Range Query on a Table Partitioned By Day
14. EXPLAIN That Uses a B-Tree Index Scan
15. EXPLAIN That Uses a Bitmap Scan
16. EXPLAIN With a Simple Subquery
17. EXPLAIN With a Columnar Query
18. EXPLAIN With a Clustered Index
19. EXPLAIN With Join that has to Move Data
20. EXPLAIN With Join that has to Move Data
21. Changing the Join Query Changes the EXPLAIN Plan
22. Analyzing the Tables Structures For a 3-Table Join
23. An EXPLAIN For a 3-Table Join
24. 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