Contents
Chapter 1 – The Aster Data Architecture
Aster Data is a Parallel Processing System
Each vworker holds a Portion of Every Table
The Rows of a Table are Spread Across All vworkers
Aster Tables are defined as Fact or Dimension when Created
A More Detailed Look at the Fact Table Distribution
Dimension Table are Replicated
A Dimension Table is often Replicated across vworkers
Aster Data has Fact and Dimension Tables
Aster Tables are defined as Fact or Dimension when Created
Fact and Dimension Tables can be Hashed by the same Key
Aster Data Uses a Hash Formula
The Hash Map Determines which vworker will own the Row
The Hash Formula, Hash Map and vworker
Placing rows on the vworker Continued
A Review of the Hashing Process
Like Data Hashes to the Same vworker
Run ANALYZE to COLLECT STATISTICS on a Table
Chapter 2 - Four Options for Aster Data Table Design
There are Four Options to Aster Table Design
Straight up Distribute by Hash
Straight up Distribute by Hash - Problems
Straight up Distribute by Replication
Partition the Table with Logical Partitioning
This Partitioned Table Sorts Rows by Month of Order_Date
An All vworkers Retrieve By Way of a Single Partition
You can Partition a Table by Range or by List
A Partitioned By List Example with Three Tactical Queries
Aster Data Multi-Level Partitioning
Aster Allows for Multi-Level Partitioning
SQL Commands for Logical Partitioning as One Table
What Partitions are on my Table?
What does a Columnar Table look like?
A Comparison of Data for Normal Vs. Columnar
A Columnar Table is best for Queries with Few Columns
Quiz – How Many Blocks Move to vworker Memory?
Answer – How Many Containers Move to vworker Memory?
Chapter 3 - How Joins Work Inside the Aster Engine
Aster Moves Joining Rows to the Same vworker
Because of the Join Rule – Dimension Table are Replicated
The Two Different Philosophies for Table Join Design
What Could You Do If Two Tables Joined 1000 Times a Day?
Fact and Dimension Tables can be Hashed by the same Key
Joining Two Tables with the same PK/FK Distribution Key
A Performance Tuning Technique for Large Joins
The Joining of Two Tables with an Additional WHERE Clause
Aster Performs Joins Using Three Different Methods
Chapter 4 - Temporary and Analytic Tables
Create a Permanent Table Using Create Table AS (CTAS)
Create a Logically Partitioned Table and Populate It
Create a Temporary Table with using Create Table AS (CTAS)
A Temporary Table That Uses an Insert/Select
Create an Analytic Table Using an Insert/Select
Create an Analytic Table Using CREATE TABLE AS (CTAS)
Operations that Invalidate an Analytic Table
If an Analytic Table is Invalid
Chapter 5 – Tera-Tom’s Top Tips
Tera-Tom’s Top Tips # 3 Rewritten
When the GROUP BY Column is NOT the Distribution Key
Example of GROUP BY Column is NOT the Distribution Key
Tera-Tom’s Top Tips #6 – Use EXPLAIN
Explain Plan Showing a Hash Join
Explain Plan Showing a Merge Join
Explain Plan Showing a Nested Loop Join
There are Only Three Types of Scans
Which Columns Might You Create an Index?
Answer - Which Columns Might You Create an Index?
A Visual of an Index (Conceptually)
A Query Using an Index Uses All vworkers
Five Operational Tips for Efficient Indexing
createCompressedIndexOnCompressedTableByDefault Flag
Subtle SQL-MapReduce Processing
Aster Data Provides an Analytic Foundation
SQL-MapReduce Function . . . nPath
nPath MODE Clause has Overlapping or NonOverlapping
Pattern Operators Order of Precedence
Matching Patterns Which Repeat
Adding an Aggregate to nPath Results
Adding an Aggregate to nPath Results (Continued)
SQL-MapReduce Examples - Use Regular SQL
SQL-MapReduce Examples - Create Objects
SQL-MapReduce Examples - Subquery
SQL-MapReduce Examples - Query as Input
SQL-MapReduce Examples - Nesting Functions
SQL-MapReduce Examples - Functions in Derived Tables
SQL-MapReduce Examples - SMAVG
SQL-MapReduce Examples - Pack Function
SQL-MapReduce Examples - Pack Function (Continued)
SQL-MapReduce Examples - Pivot Columns
Using the GNOME Terminal Unzip the bank_web_data.zip
Use the Function ncluster_loader to Load the Bank Data
Run this nPath Map Reduce Function on your Table
Accumulate – nPath with a WHERE Clause
SQL-MapReduce Examples - Path Generator
SQL-MapReduce Examples - Path Generator (Continued)
SQL-MapReduce Examples - Path Generator (Continued)
SQL-MapReduce Examples - Path Generator (Continued)
SQL-MapReduce Examples - Path Generator (Continued)
SQL-MapReduce Examples - Linear Regression
SQL-MapReduce Examples - Linear Regression (Continued)
SQL-MapReduce Examples - Linear Regression (Continued)
SQL-MapReduce Examples - Naive Bayes
SQL-MapReduce Examples - Naive Bayes (Continued)
SQL-MapReduce Examples - Naive Bayes (Continued)
SQL-MapReduce Examples - Naive Bayes (Continued)
SQL-MapReduce Examples - Naive Bayes (Continued)
SQL-MapReduce Examples - Naive Bayes (Continued)
SQL-MapReduce Examples - Naive Bayes (Continued)
SQL-MapReduce Examples - Naive Bayes (Continued)
Join Aster, Teradata and Hadoop Tables; feed into MapReduce
Run Both of these Examples Together and Compare
Run this nPath Map Reduce Function
Finding Out What Functions You Have Installed
Answer Workshop 1 - Fill in the x’s
Answer Workshop 2 – Fill in the x’s
Answer Workshop 2 – You Could Have Used a GROUP BY
Workshop 3 – Answer to Add to the Query
Answer to Workshop 4 – Fill in the x’s
Workshop 5 – Find that Customer
Answer to Workshop 5 – Find that Customer
Workshop 6 – Change the MapReduce Function
Answer to Workshop 6 – Change the MapReduce Function
Workshop 7 – Build the MapReduce Function
Answer to Workshop 7 – Build the MapReduce Function
Best Answer to Workshop 7 – Build the MapReduce Function
Workshop 8 – Build the Accumulate in the Result
Answer to Workshop 8 – Build the Accumulate in the Result
SQL-MapReduce Examples - Linear Regression (Continued)
Workshop 9 – Build the Subquery
Answer to Workshop 9 – Build the Subquery
Workshop 10 – Do Your First Join
Answer to Workshop 10 – Do Your First Join
Answer to Workshop 10 – Do the Join Using a New Syntax
Workshop 11 – Super Join the Tables
Answer to Workshop 11 –Super Join the Tables
Answer to Workshop 11 – Super Join the Tables
Workshop 12 – Sessionize the Data
Answer to Workshop 12 – Sessionize the Data
Workshop 13 – What is this Query Doing?
Answer to Workshop 13 – What is this Query Doing?
Answer to Workshop 14 – Using ilike
Answer to Workshop 14 – Using ilike
Workshop 15 – What are the First Two Pages Visited?
Workshop 15 – What are the First Two Pages Visited?
Workshop 16 – Advanced - First Two Pages Visited?
Answer to Workshop 16 Advanced - First Two Pages Visited?
Workshop 17 – Can You Clean Up the Results?
Answer to Workshop 17 – Can You Clean Up the Results?
Answer to Workshop 17 – Format the Date
Workshop 18 – Build a Churn Table
Workshop 18 – Run the Query Before Building to Test
Workshop 18 – A Better Example
Answer to Workshop 18 – Build a Basic Churn Table
Workshop 18 – Create the Churn Table with a Better Example
The Multi-Case Function in Nexus
The Multi-Case Function Mixing and Matching
The Multi-Case Function Mixing and Matching
SQL-MapReduce Examples - cFilter
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - Linear Regression (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - Linear Regression (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - cFilter (Continued)
SQL-MapReduce Examples - cFilter (Continued)
CFILTER in Action with Bank_Web_Clicks
Date, Time, and Timestamp Keywords
Add or Subtract Days from a date
A Summary of Math Operations on Dates
Using a Math Operation to find your Age in Years
Find What Day of the week you were Born
EXTRACT with DATE and TIME Literals
EXTRACT of the Month on Aggregate Queries
A Side Title example with Reserved Words as an Alias
Implied Extract of Day, Month and Year
DATE_TRUNC Function using TIME
Chapter 9 – Aster Windows Functions
Cumulative Sum - Major and Minor Sort Key(s)
The ANSI CSUM – Getting a Sequential Number
The ANSI OLAP – Reset with a PARTITION BY Statement
PARTITION BY only Resets a Single OLAP not ALL of them
ANSI Moving Sum is Current Row and Preceding n Rows
How ANSI Moving SUM Handles the Sort
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Moving SUM every 3-rows vs. a Continuous Sum
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Quiz – How is that 4th Row Calculated?
Answer to Quiz – How is that 4th Row Calculated?
Partition By Resets an ANSI OLAP
Moving Difference using ANSI Syntax
Moving Difference using ANSI Syntax with Partition By
RANK Defaults to Ascending Order
Getting RANK to Sort in DESC Order
You can use Window Functions in Expressions
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
COUNT OVER for a Sequential Number
Quiz – What caused the COUNT OVER to Reset?
Answer to Quiz – What caused the COUNT OVER to Reset?
MAX OVER with PARTITION BY Reset
Answer to Quiz – Fill in the Blank
Quiz – How did the Row_Number Reset?
Quiz – How did the Row_Number Reset?
FIRST_VALUE After Sorting by the Highest Value
Chapter 10 – The Fundamental SQL Commands That Work on Aster
BETWEEN Works for Character Data
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
LIKE command Underscore is Wildcard for one Character
GROUP BY Vs. DISTINCT – Good Advice
The Five Aggregates of Aster Data
GROUP BY when Aggregates and Normal Columns Mix
GROUP BY Delivers one row per Group
GROUP BY Dept_No or GROUP BY 1 the same thing
Limiting Rows and Improving Performance with WHERE
WHERE Clause in Aggregation limits unneeded Calculations
Keyword HAVING tests Aggregates after they are Totaled
Keyword HAVING is like an Extra WHERE Clause for Totals
Getting the Average Values per Column
Getting the Average Values per Column
Average Values per Column for All Columns in a Table
A two-table join using Non-ANSI Syntax
A two-table join using Non-ANSI Syntax with Table Alias
Aliases and Fully Qualifying Columns
A two-table join using ANSI Syntax
Both Queries have the same Results and Performance
Quiz – Can You Finish the Join Syntax?
Answer to Quiz – Can You Finish the Join Syntax?
Quiz – Can You Find the Error?
Answer to Quiz – Can You Find the Error?
Quiz – Which rows from both tables Won’t Return?
Answer to Quiz – Which rows from both tables Won’t Return?
LEFT OUTER JOIN Brings Back All Rows in the Left Table
RIGHT OUTER JOIN Brings Back All Rows in the RIGHT Table
FULL OUTER JOIN Brings Back All Rows in All Tables
Which Tables are the Left and which are the Right?
Answer - Which Tables are the Left and which are the Right?
INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional WHERE Clause
OUTER JOIN with Additional WHERE Clause
OUTER JOIN with Additional AND Clause
Results from OUTER JOIN with Additional AND Clause
Quiz – Why is this considered an INNER JOIN?
Result Set of the DREADED Product Join
The Horrifying Cartesian Product Join
The ANSI Cartesian Join will ERROR
Quiz – Do these Joins Return the Same Answer Set?
Answer – Do these Joins Return the Same Answer Set?
How would you Join these two tables?
How would you Join these two tables? You Can’t Yet!
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the 3-Table Join?
Answer to Quiz – Can you Write the 3-Table Join?
Quiz – Can you Write the 3-Table Join to ANSI Syntax?
Answer – Can you Write the 3-Table Join to ANSI Syntax?
Quiz – Can you Place the ON Clauses at the End?
Answer – Can you Place the ON Clauses at the End?
The 5-Table Join – Logical Insurance Model
Quiz - Write a Five Table Join Using ANSI Syntax
Answer - Write a Five Table Join Using ANSI Syntax
Quiz - Write a Five Table Join Using ANSI Syntax
Answer - Write a Five Table Join Using ANSI Syntax
Quiz - Write a Five Table Join Using Non-ANSI Syntax
Answer - Write a Five Table Join Using Non-ANSI Syntax
Quiz –Re-Write this putting the ON clauses at the END
Answer –Re-Write this putting the ON clauses at the END
The Nexus Query Chameleon Writes the SQL for Users
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
The Final Answer Set from the Subquery
Quiz- Answer the Difficult Question
Answer to Quiz- Answer the Difficult Question
Should you use a Subquery or a Join?
Answer to Quiz- Write the Subquery
Quiz- Write the More Difficult Subquery
Answer to Quiz- Write the More Difficult Subquery
Quiz- Write the Subquery with an Aggregate
Answer to Quiz- Write the Subquery with an Aggregate
Quiz – Write the Triple Subquery
Answer to Quiz – Write the Triple Subquery
CHARACTER_LENGTH AND OCTET_LENGTH
The TRIM Command trims both Leading and Trailing Spaces
Trim and Trailing is Case Sensitive
Trim and Trailing works if Case right
How SUBSTRING Works with NO ENDING POSITION
Using SUBSTRING to move Backwards
How SUBSTRING Works with a Starting Position of -1
How SUBSTRING Works with an Ending Position of 0
An Example using SUBSTRING, TRIM and CHAR Together
SUBSTRING and SUBSTR are equal, but use different syntax
The POSITION Command finds a Letters Position
The Basics of CAST (Convert and Store)
Some Great CAST (Convert and Store) Examples
Some Great CAST (Convert and Store) Examples
Combining Searched Case and Valued Case