title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Aster Data SQL and MapReduce
Duration
3 Days
Course
ASTR-120
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Aster Data SQL and MapReduce course is designed to provide students with a deeper knowledge and understanding of the Aster Data Architecture, SQL and MapReduce functions and how to write them. In this course, students will learn the Aster Data SQL and MapReduce starting at the most basic level and going to the most advanced level with many examples.Objectives
Upon completion of the Aster Data SQL and MapReduce course, students will be able to:
- Recognize the Aster Data Architecture
- Discover four options for Aster Data Table Design
- Assemble Temporary and Analytic Tables
- Reproduce SQL-MapReduce
- Apply Pattern
- Operate SQL-MapReduce Examples
- Path Generator
- Naive Bayes
- Linear Regression
- Examine nPath in action
- Understand and employ functions such as:
- Sub-query, multi-case, Aster Windows, Fundamental SQL commands
- Recall Tera-Tom’s Top Tips
Audience
- Anyone who has a desire to learn the Aster Data SQL and MapReduce from beginners to an advanced audience.
Prerequisites
- none
Topics
- What is Parallel Processing?
- 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
- Fact Table
- A More Detailed Look at the Fact Table Distribution
- Dimension Tables
- 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
- Distribution Key Rules
- The Hash Formula, Hash Map and vworker
- Placing rows on the vworker
- A Review of the Hashing Process
- Distribution Key Data Types
- Run ANALYZE to COLLECT STATISTICS on a Table
- What Columns to Analyze
- There are Four Options to Aster Table Design
- Straight up Distribute by Hash
- 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
- When to use a Columnar Table
- The Joining of Two Tables
- 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
- Fact and Dimension Tables can be Hashed by the same Key
- Joining Two Tables with the same PK/FK Distribution Key
- A Join With Co-Location
- A Performance Tuning Technique for Large Joins
- The Joining of Two Tables with an Additional WHERE Clause
- Aster Performs Joins Using Three Different Methods
- The Hash, Merge, and Nested Loop Join
- Aster has Three Types of Data
- 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 in Action
- A Temporary Table That Uses an Insert/Select
- Creating an Analytic Table Using an Insert/Select or CREATE TABLE AS (CTAS)
- Operations that Invalidate an Analytic Table
- Tera-Tom History
- Tera-Tom's Top Tips
- Tera-Tom's Top Tips # 2
- Tera-Tom's Top Tips #3
- Tera-Tom's Top Tips #4
- 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 #5
- Tera-Tom's Top Tips #6 – Use EXPLAIN
- Query Plan and Estimates
- 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
- Guidelines for Indexes
- The B-Tree Index
- Which Columns Might You Create an Index?
- A Visual of an Index (Conceptually)
- A Query Using an Index Uses All vworkers
- Multicolumn indexes
- A NUSI BITMAP Theory
- A NUSI Bitmap in Action
- Indexes on Expressions
- Indexes on Extracts of Dates
- GiST Indexes
- Five Operational Tips for Efficient Indexing
- REINDEX
- createCompressedIndexOnCompressedTableByDefault Flag
- MapReduce History
- What is MapReduce?
- What is SQL-MapReduce?
- SQL-MapReduce Input and Output
- Subtle SQL-MapReduce Processing
- Aster Data Provides an Analytic Foundation
- Path, Text, and Statistical Analysis
- Segmentation (Data Mining)
- Graph Analysis
- Transformation of Data
- Sessionize
- Tokenize
- SQL-MapReduce Function… nPath
- nPath SELECT Clause
- nPath ON Clause
- nPath PARTITION BY Expression
- nPath DIMENSION Expression
- nPath ORDER BY Expression
- nPath MODE Clause has Overlapping or NonOverlapping
- nPath PATTERN Clause
- Pattern Operators
- Matching Patterns Which Repeat
- nPath SYMBOLS Clause
- nPath RESULTS Clause
- Adding an Aggregate to nPath Results
- Adding an Aggregate to nPath Results (Continued)
- SQL-MapReduce Examples:
- Use Regular SQL
- Create Objects
- Subquery
- Query as Input
- Nesting Functions
- Functions in Derived Tables
- SMAVG
- Pack Function
- Pivot Columns
- Workshop: Create This Table
- Login to your GNOME Terminal, Linux
- 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
- Operators at their Simplest
- Pattern
- Accumulate
- SQL-MapReduce Examples:
- Path Generator
- Linear Regression
- Naive Bayes
- Join Aster, Teradata and Hadoop Tables; feed into MapReduce
- Run Both of these Examples Together and Compare
- Run this nPath Map Reduce Function
- nPath in Action
- Finding Out What Functions You Have Installed
- Workshop #’s 1 -18
- Multi-Case
- The Multi-Case Function
- SQL-MapReduce Examples:
- cFilter
- Linear Regression
- CFILTER in Action with Bank_Web_Clicks
- CFILTER using Nexus
- nPath Error
- Date, Time, and Timestamp Keywords
- Add or Subtract Days from a date
- The to_char command
- 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
- Date Related Functions
- The EXTRACT Command
- EXTRACT from DATES and TIME
- 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_PART Function
- DATE_TRUNC Function
- Aster NOW() Function
- 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
- Moving SUM every 3-rows vs. a Continuous Sum
- Moving Average
- Partition By Resets an ANSI OLAP
- Moving Average Using BETWEEN
- Moving Difference using ANSI Syntax
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- You can use Window Functions in Expressions
- RANK() OVER and PARTITION BY
- DENSE_RANK() OVER
- PERCENT_RANK() OVER
- RANK With ORDER BY SUM()
- COUNT OVER for a Sequential Number
- The MAX OVER Command
- The MIN OVER Command
- The Row_Number Command
- NTILE
- CUME_DIST
- LEAD
- LAG
- FIRST_VALUE
- LAST_VALUE
- NTH_VALUE
- SUM(SUM(n))
- BETWEEN is Inclusive
- 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
- Average Values per Column for All Columns in a Table
- A two-table join using Non-ANSI Syntax
- Aliases and Fully Qualifying Columns
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- Which Tables are the Left and which are the Right?
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause and WHERE Clause
- OUTER JOIN with Additional WHERE Clause and AND Clause
- Results from OUTER JOIN with Additional AND Clause
- The DREADED Product Join
- Result Set of the DREADED Product Join
- The Horrifying Cartesian Product Join
- The ANSI Cartesian Join will ERROR
- How would you Join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- The 5-Table Join – Logical Insurance Model
- The Nexus Query Chameleon Writes the SQL for Users.
- An IN List Never has Duplicates – Just like a Subquery
- The Subquery
- How a Basic Subquery Works
- The Final Answer Set from the Subquery
- Should you use a Subquery or a Join?
- CHARACTER_LENGTH AND OCTET_LENGTH
- The TRIM Command trims both Leading and Trailing Spaces
- Trim and Trailing is Case Sensitive
- The SUBSTRING Command
- 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
- Concatenation
- The Basics of CAST (Convert and Store)
- Combining Searched Case and Valued Case
- A Trick for getting a Horizontal Case
- Nested Case
- Put a CASE in the ORDER BY
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
Exam Terms & Conditions
??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.