Aster Data Basics Training

In this Aster Data Basics training class, participants will learn the basics of Aster Data with a focus on what's most important from a user's perspective.

Goals
  1. Gain the knowledge to be able to make strategic decisions regarding your Aster Data environment.
Outline
  1. The Aster Data Architecture
    1. What is Parallel Processing?
    2. The Aster Data Architecture
    3. The Queen Node
    4. The Worker Node
    5. The Loader Node
    6. The Backup Node
    7. The Aster Architecture Interconnect
    8. Aster Data Provides Four Fundamental Hardware Strengths
    9. Replication Failover
    10. Aster Allows a Hybrid of SQL and MapReduce
    11. MapReduce History
    12. What is MapReduce?
    13. What is SQL-MR?
    14. Sessionize: An Example of SQL-MR
    15. Support for Mixed Workload Management and Prioritization
  2. Fact and Dimension Tables
    1. Fact Table
    2. A More Detailed Look at the Fact Table Distribution
    3. Dimension Table are Replicated
    4. A Dimension Table is often Replicated across vworkers
    5. Aster Data has Fact and Dimension Tables
    6. Distribution Key Rules
    7. Aster Data Uses a Hash Formula
    8. The Hash Formula, Hash Map and vworker
    9. Placing rows on the vworker
    10. Placing rows on the vworker Continued
    11. A Review of the Hashing Process
    12. Like Data Hashes to the Same vworker
    13. Distribution Key Data Types
    14. Run ANALYZE to COLLECT STATISTICS on a Table
    15. Some Examples of ANALYZE
    16. What Columns to Analyze
  3. How Aster Processes Data
    1. The Table Header and the Data Rows
    2. Data Block
    3. Full Table Scan
    4. The "Achilles Heel", or Slowest Process
    5. Distribution Key
    6. Query Using the Distribution Key Using a Single vworker
    7. As Rows are Added, a Data Block will Eventually Split
    8. Distribution Key Query Using One vworker
    9. Many Blocks for a Single Table
  4. Four Options for Aster Data Table Design
    1. Four Options to Aster Table Design
    2. Straight up Distribute
    3. Partition Table with Logical Partitioning
    4. Partitioned Table Sorting
    5. Single Partition
    6. Partition a Table by Range or by List
    7. A Partitioned by List Example with Three Tactical Queries
    8. Aster Data Multi-Level Partitioning
    9. Aster Allows for Multi-Level Partitioning
    10. SQL Commands for Logical Partitioning as One Table
    11. Columnar Tables
    12. Comparison of Data for Normal Vs. Columnar
    13. Columnar Table is best for Queries with Few Columns
  5. How Joins Work Inside the Aster Engine
    1. The Joining of Two Tables
    2. Joining Rows to the Same vworker
    3. Join Rule: Dimension Tables Replicated
    4. Two Different Philosophies for Table Join Design
    5. Two Tables Joined 1000 Times a Day?
    6. Fact and Dimension Tables Hashed by the Same Key
    7. Joining Two Tables with the same PK/FK Distribution Key
    8. A Join With Co-Location
    9. A Performance Tuning Technique for Large Joins
    10. The Joining of Two Tables with an Additional WHERE Clause
    11. Aster Performs Joins Using Three Different Methods
    12. The Hash Join
    13. The Merge Join
    14. Nested Loop Joins
  6. Temporary and Analytic Tables
    1. Three Types of Data
    2. Permanent Table Using Create Table AS (CTAS)
    3. Logically Partitioned Table and Populate It
    4. Create a Temporary Table with using Create Table AS (CTAS)
    5. A Temporary Table in Action
    6. A Temporary Table That Uses an Insert/Select
    7. Create an Analytic Table Using an Insert/Select
    8. Create an Analytic Table Using CREATE TABLE AS (CTAS)
    9. Operations that Invalidate an Analytic Table
    10. If an Analytic Table is Invalid
    11. Tera-Tom History
  7. Aster Modeling Rules
    1. Modeling Rules for Aster Data
    2. Three Principles that Govern the Modeling Rules
    3. Modeling Rule 1: Dimensionalize your Model
    4. "Star Schema"
    5. Reading a Data Block by Moving the Block to Memory
    6. A Dimensional Model Moves Less Mass into Memory
    7. Which Move from Disk to Memory Would You Choose?
    8. Transfering Fact Table into Memory in Parallel
    9. Modeling Rule 2: Use Columnar
    10. Which Move From Disk to Memory Would You Choose?
    11. Modeling and Joins at the Simplest Level
    12. Modeling and Joins at the Simplest Level
    13. Joins at the Simplest Level
    14. Modeling Rule 3: Distribute your Tables Based on Joins
    15. The Two Different Philosophies for Table Join Design
    16. Joining Two Tables with the same PK/FK Primary Index
    17. A Join with No Redistribution or Duplication
    18. Aster Hates Joining Tables with a Different Distribution Key
    19. Aster Hates to Redistribute by Hash to Join Tables
    20. Modeling Rule 4: Replicate Dimension Tables
    21. Modeling Rule 5: Partition Your Tables
    22. Modeling Rule 6: Make Fact Tables Skinny
    23. Modeling Rule 6: Make Fact Tables Skinny Example
    24. Modeling Rule 7: Index Your Tables
    25. The B-Tree Index
    26. Which Columns Might You Create an Index?
    27. Modeling Rule 8: Denormalize based on Your Environment
    28. Modeling Rule 8: Denormalize based on Your Environment
  8. Tera-Tom's Top Tips
    1. Tera-Tom's Top Tips
    2. When the GROUP BY Column Is NOT the Distribution Key
    3. Example of GROUP BY Column is NOT the Distribution Key
    4. Query Plan and Estimates
    5. Explain Plan Showing a Hash Join
    6. Explain Plan Showing a Merge Join
    7. Explain Plan Showing a Nested Loop Join
  9. Indexes
    1. There are Only Three Types of Scans
    2. Guidelines for Indexes
    3. An Index Syntax Example
    4. The B-Tree Index
    5. Which Columns Might You Create an Index?
    6. A Visual of an Index (Conceptually)
    7. A Query Using an Index Uses All vworkers
    8. Multicolumn indexes
    9. A NUSI BITMAP Theory
    10. A NUSI Bitmap in Action
    11. Indexes on Expressions
    12. Indexes on Extracts of Dates
    13. GiST Indexes
    14. Five Operational Tips for Efficient Indexing
    15. REINDEX
    16. createCompressedIndexOnCompressedTableByDefault Flag
  10. Aster Windows Functions
    1. Cumulative Sum
    2. Cumulative Sum: Major and Minor Sort Key(s)
    3. The ANSI CSUM: Getting a Sequential Number
    4. The ANSI OLAP: Reset with a PARTITION BY Statement
    5. PARTITION BY only Resets a Single OLAP not ALL of them
    6. ANSI Moving Sum is Current Row and Preceding n Rows
    7. How ANSI Moving SUM Handles the Sort
    8. Moving SUM every 3-rows vs. a Continuous Sum
    9. Moving Average
    10. Partition By Resets an ANSI OLAP
    11. Moving Average Using BETWEEN
    12. Moving Difference using ANSI Syntax
    13. Moving Difference using ANSI Syntax with Partition By
    14. RANK Defaults to Ascending Order
    15. Getting RANK to Sort in DESC Order
    16. You can use Window Functions in Expressions
    17. RANK() OVER and PARTITION BY
    18. DENSE_RANK() OVER
    19. PERCENT_RANK() OVER
    20. PERCENT_RANK() OVER with 14 rows in Calculation
    21. PERCENT_RANK() OVER with 21 rows in Calculation
    22. RANK With ORDER BY SUM()
    23. COUNT OVER for a Sequential Number
    24. The MAX OVER Command
    25. MAX OVER with PARTITION BY Reset
    26. The MIN OVER Command
    27. The Row_Number Command
    28. NTILE
    29. NTILE Using a Value of 10
    30. NTILE With a Partition
    31. CUME_DIST
    32. CUME_DIST With a Partition
    33. LEAD
    34. LEAD With Partitioning
    35. LAG
    36. LAG with Partitioning
    37. FIRST_VALUE
    38. FIRST_VALUE After Sorting by the Highest Value
    39. FIRST_VALUE with Partitioning
    40. LAST_VALUE
    41. NTH_VALUE
    42. NTH_VALUE With Partition
    43. SUM(SUM(n))
  11. SQL-MapReduce
    1. MapReduce History
    2. What is MapReduce?
    3. What is SQL-MapReduce?
    4. SQL-MapReduce Input
    5. SQL-MapReduce Output
    6. Subtle SQL-MapReduce Processing
    7. Aster Data Provides an Analytic Foundation
    8. Path Analysis
    9. Text Analysis
    10. Statistical Analysis
    11. Segmentation (Data Mining)
    12. Graph Analysis
    13. Transformation of Data
    14. Sessionize
    15. Tokenize
    16. SQL-MapReduce Function… nPath
    17. nPath SELECT Clause
    18. nPath ON Clause
    19. nPath PARTITION BY Expression
    20. nPath DIMENSION Expression
    21. nPath ORDER BY Expression
    22. nPath MODE Clause has Overlapping or NonOverlapping
    23. nPath PATTERN Clause
    24. Pattern Operators
    25. Pattern Operators Order of Precedence
    26. Matching Patterns Which Repeat
    27. nPath SYMBOLS Clause
    28. nPath RESULTS Clause
    29. Adding an Aggregate to nPath Results
    30. Adding an Aggregate to nPath Results (Continued)
    31. SQL-MapReduce Examples: Use Regular SQL
    32. SQL-MapReduce Examples: Create Objects
    33. SQL-MapReduce Examples: Subquery
    34. SQL-MapReduce Examples: Query as Input
    35. SQL-MapReduce Examples: Nesting Functions
    36. SQL-MapReduce Examples: Functions in Derived Tables
    37. SQL-MapReduce Examples: SMAVG
    38. SQL-MapReduce Examples: Pack Function
    39. SQL-MapReduce Examples: Pack Function (Continued)
    40. SQL-MapReduce Examples: Pivot Columns
  12. Time and Date
    1. Date, Time, and Timestamp Keywords
    2. Add or Subtract Days from a date
    3. The to_char command
    4. A Summary of Math Operations on Dates
    5. Using a Math Operation to find your Age in Years
    6. Find What Day of the week you were Born
    7. Date Related Functions
    8. The EXTRACT Command
    9. EXTRACT from DATES and TIME
    10. EXTRACT with DATE and TIME Literals
    11. EXTRACT of the Month on Aggregate Queries
    12. A Side Title example with Reserved Words as an Alias
    13. Implied Extract of Day, Month and Year
    14. DATE_PART Function
    15. DATE_TRUNC Function
    16. DATE_TRUNC Function using TIME
    17. Aster NOW() Function
  13. How Rows are Handled
    1. Aster Rowid, CreateXid, DeleteXid
    2. An Update of Multiple Rows
    3. A Delete Example
    4. A Delete Example Query
    5. An Insert Example
    6. An Insert Example Query
  14. The Fundamental SQL Commands That Work on Aster
    1. BETWEEN is Inclusive
    2. BETWEEN Works for Character Data
    3. LIKE uses Wildcards Percent and Underscore
    4. LIKE command Underscore is Wildcard for one Character
    5. GROUP BY Vs. DISTINCT: Good Advice
    6. The Five Aggregates of Aster Data
    7. GROUP BY when Aggregates and Normal Columns Mix
    8. GROUP BY Delivers one row per Group
    9. GROUP BY Dept_No or GROUP BY 1 the same thing
    10. Limiting Rows and Improving Performance with WHERE
    11. WHERE Clause in Aggregation limits unneeded Calculations
    12. Keyword HAVING tests Aggregates after they are Totaled
    13. Keyword HAVING is like an Extra WHERE Clause for Totals
    14. Getting the Average Values per Column
    15. Getting the Average Values per Column
    16. Average Values per Column for All Columns in a Table
    17. A two-table join using Non-ANSI Syntax
    18. A two-table join using Non-ANSI Syntax with Table Alias
    19. Aliases and Fully Qualifying Columns
    20. A two-table join using ANSI Syntax
    21. Both Queries have the same Results and Performance
    22. LEFT OUTER JOIN
    23. LEFT OUTER JOIN Brings Back All Rows in the Left Table
    24. RIGHT OUTER JOIN
    25. RIGHT OUTER JOIN Brings Back All Rows in the RIGHT Table
    26. FULL OUTER JOIN
    27. FULL OUTER JOIN Brings Back All Rows in All Tables
    28. Which Tables are the Left and which are the Right?
    29. INNER JOIN with Additional AND Clause
    30. ANSI INNER JOIN with Additional AND Clause
    31. ANSI INNER JOIN with Additional WHERE Clause
    32. OUTER JOIN with Additional WHERE Clause
    33. OUTER JOIN with Additional AND Clause
    34. Results from OUTER JOIN with Additional AND Clause
    35. The DREADED Product Join 357
    36. Result Set of the DREADED Product Join
    37. The Horrifying Cartesian Product Join
    38. The ANSI Cartesian Join will ERROR
    39. How would you Join these two tables?
    40. How would you Join these two tables? You Can't Yet!
    41. An Associative Table is a Bridge that Joins Two Tables
    42. The 5-Table Join: Logical Insurance Model
    43. The Nexus Query Chameleon Writes the SQL for Users.
    44. An IN List is much like a Subquery
    45. An IN List Never has Duplicates: Just like a Subquery
    46. An IN List Ignores Duplicates
    47. The Subquery
    48. How a Basic Subquery Works
    49. The Final Answer Set from the Subquery
    50. Should you use a Subquery or a Join?
    51. CHARACTER_LENGTH AND OCTET_LENGTH
    52. The TRIM Command trims both Leading and Trailing Spaces
    53. Trim and Trailing is Case Sensitive
    54. Trim and Trailing works if Case right
    55. The SUBSTRING Command
    56. How SUBSTRING Works with NO ENDING POSITION
    57. Using SUBSTRING to move Backwards
    58. How SUBSTRING Works with a Starting Position of:1
    59. How SUBSTRING Works with an Ending Position of 0
    60. An Example using SUBSTRING, TRIM and CHAR Together
    61. SUBSTRING and SUBSTR are equal, but use different syntax
    62. The POSITION Command finds a Letters Position
    63. Concatenation
    64. The Basics of CAST (Convert and Store)
    65. Some Great CAST (Convert and Store) Examples
    66. Some Great CAST (Convert and Store) Examples
    67. Combining Searched Case and Valued Case
    68. A Trick for getting a Horizontal Case
    69. Nested Case
    70. Put a CASE in the ORDER BY
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

Training for your Team

Length: 3 Days
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors

What people say about our training

The SQL course was well-laid out and easy to understand. The instructor was very knowledgeable and helpful.
Michael Arzadon
SchoolsFirst Federal Credit Union
The instructor was amazingly knowledgeable and accessible.
Kevin Glover
Emory University
I really enjoyed this class. Well worth the money and time.
Emily Michel
Stone Carlie
The training environment is intimate and relaxed, and the Webucator experience seems very complete, from the attentive prep work pre-class to course completion.
Cody Wilson
Mississippi Valley Surgery Center

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

60,501

Students who have taken Instructor-led Training

11,680

Organizations who trust Webucator for their Instructor-led training needs

100%

Satisfaction guarantee and retake option

9.28

Students rated our trainers 9.28 out of 10 based on 28,431 reviews

Contact Us or call 1-877-932-8228