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 Databse Administration
Duration
3 Days
Course
ASTR-115
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Aster Data Database Administration course is designed to provide students with a deeper knowledge and understanding of the Aster Data Database Administration in order to administer the system. Students will learn the Aster Data Database Administration starting at the most basic level and going to the most advanced level with many examples.Objectives
Upon completion of the Aster Data Database Administration course, students will be able to:
- Identify the Aster Data Architecture
- Define Administrative Operations
- Apply Fact and Dimension Tables
- Analyze the AMC in Detail
- Properly restart the Aster Database
- Name how Aster Processes Data
- Discover four options for Aster Data Table Design
- Monitor Events with the Event Engine
- Understand how Joins Work Inside the Aster Engine
- Back up the System
- Configure the Aster Database Connector
- Memorize guidelines for indexes
- Differentiate Three Principles that Govern the Modeling Rules
- Display the HCatalog
- Demonstrate Workload Management
Audience
- Anyone who has a desire to learn the Aster Data Database Administration 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
- The Aster Data Architecture
- The Queen, Worker, Loader, Backup Node
- The Aster Architecture Interconnect
- The Aster Architecture has Spare Nodes
- The Aster Architecture Allows Flexibility based on Need
- Aster Data Provides Four Fundamental Hardware Strengths
- Replication Failover
- Data is Compressed on Data Transfers
- Aster Utilizes Dual Optimizers
- Aster Allows a Hybrid of SQL and MapReduce
- MapReduce History
- What is MapReduce?
- What is SQL-MR?
- Sessionize – An Example of SQL-MR
- Support for Mixed Workload Management and Prioritization
- Cluster Management
- Cluster Administration
- Database Administration
- Bulk Utilities
- Aster Database Management Console (AMC)
- Aster Database Event Engine
- nCluster Command Line Interface (ncli)
- Aster nCluster Terminal (ACT)
- ODBC and JDBC Connections to Third Party Tools
- Aster Database Loader
- Aster Database Backup
- Logging Into the AMC
- Overview of the AMC:
- The Dashboard Tab
- The Processes Tab
- The Nodes Tab
- The Admin Tab
- Aster Tables are defined as Fact or Dimension when Created
- Fact Table
- 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
- Distribution Key Rules
- The Hash Formula, Hash Map and vworker
- Placing rows on the vworker
- A Review of the Hashing Process
- Like Data Hashes to the Same vworker
- Distribution Key Data Types
- Run ANALYZE to COLLECT STATISTICS on a Table
- Some Examples of ANALYZE
- What Columns to Analyze
- Dashboard Tab
- Processes Tab
- Nodes Tab:
- Node Overview
- Hardware Stats
- Hardware Config
- Admin Tab:
- Cluster Management
- Events
- Executables
- Logs
- Configuration>Cluster Settings
- Configuration>Workload>Service Classes
- Configuration>Roles and Privileges
- Setting up IP Pools
- Remove Nodes
- Check Node Hardware Configuration
- Configure Cluster Settings
- Cluster Settings
- Sparkline Graph Scale Units
- Graph Scaling
- Internet Access Settings
- Aster Support Settings
- QoS Concurrency Threshold Configuration
- Roles and Privileges
- View the List of Available AMC User Privileges
- Create an AMC User
- Edit AMC Privileges
- Restarting Your Aster Database
- Procedure for Restarting the Aster Database
- Activate Aster Database
- Situations that Require Activation
- Activate Aster Database: The Procedure
- Balance Data
- Balance Process
- Cluster Management from the Command Line
- Check Cluster Status
- Soft Shutdown
- Soft Startup
- Free Space Occupied by Defunct Vworkers
- Passwordless Root SSH
- When a Table is Created, a Table Header is Created
- Every vworker has the Exact Same Tables
- All Aster Tables are spread across All vworkers
- The Table Header and the Data Rows are Stored Separately
- A vworker Stores the Rows of a Table inside a Data Block
- To Read Rows, a vworker Moves the Data Block into Memory
- A Full Table Scan Means All vworkers must Read All Rows
- The “Achilles Heel”, or Slowest Process, is Block Transfer
- Each Table has a Distribution Key
- A Query Using the Distribution Key uses a Single vworker.
- As Rows are Added, a Data Block will Eventually Split
- A Full Table Scan Means All vworkers Read All Blocks
- Distribution Key Query uses One vworker
- Each vworker Can Have Many Blocks for a Single Table
- A Full Table Scan Means All vworkers Read All Blocks
- 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
- When to use a Columnar Table
- Monitor Events with the Event Engine
- Event Engine Overview
- Manage Event Subscriptions
- Create or Edit Event Subscriptions
- Upgrades of Event Engine
- View Event Subscriptions
- Supported Events
- Remediations
- Automatic Cluster Shutdown on Disk Full Event
- Event Engine Best Practices
- Test the Event Engine
- Troubleshoot Event Engine Issues
- Monitor the Aster Database with SNMP
- 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
- 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 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, Nested Loop Joins
- 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
- 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
- Tera-Tom History
- Manage Backups
- Add a New Backup Manager to the AMC
- Start a Backup
- Backup Manager Commands
- Monitor and Manage Backups - Starting and Stopping
- Monitor and Manage Backups - Status and Availability
- Recovering the Queen Node - Queen Replacement
- Recovering the Queen Node - Queen Replacement Script
- Queen Replacement Best Practices
- Set up Host Entries for all Nodes
- Configure Hosts
- Setting up the Connector
- Networking
- Client Software
- Performance
- load_from_teradata Syntax
- load_to_teradata Syntax
- Cancelling load_to_teradata
- Procedure for Setting START_INSTANCE
- Troubleshooting the Connector
- Running Joins in Aster to Teradata
- Building Remote Views
- Create Table in Aster Example
- Modeling Rules for Aster Data
- Three Principles that Govern the Modeling Rules
- Modeling Rule 1 – Dimensionalize your Model
- A Dimensional Model is called a 'Star Schema'
- To Read a Data Block, a vworker Moves the Block to Memory
- A Dimensional Model Moves Less Mass into Memory
- Which Move From Disk to Memory Would You Choose?
- Vworkers transfer their Fact Table into Memory in Parallel
- Modeling Rule 2 – Use Columnar
- Which Move From Disk to Memory Would You Choose?
- Let's Discuss Modeling and Joins at the Simplest Level
- Modeling Rule 3 – Distribute your Tables Based on Joins
- The Two Different Philosophies for Table Join Design
- Facts are Hashed and most often the Dimension is Replicated
- Fact and Dimension Tables can be Hashed by the same Key
- Joining Two Tables with the same PK/FK Primary Index
- A Join With No Redistribution or Duplication
- Aster Hates Joining Tables with a Different Distribution Key
- Aster Hates to Redistribute by Hash to Join Tables
- Modeling Rule 4 – Replicate Dimension Tables
- Modeling Rule 5 – Partition Your Tables
- Modeling Rule 6 – Make Fact Tables Skinny
- Modeling Rule 7 – Index Your Tables
- The B-Tree Index
- Which Columns Might You Create an Index?
- Modeling Rule 8 – Denormalize based on Your Environment.
- Tera-Tom's Top Tips
- Tera-Tom's Top Tips # 2
- Tera-Tom's Top Tips #3
- Tera-Tom's Top Tips # 3 Rewritten
- 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
- An Index Syntax Example
- The B-Tree Index
- 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
- 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
- Introduction to SQL-H
- Configuring SQL-H Aster
- Aster 5.10 or Earlier SQL-H Configuration
- Loading from HCatalog -Syntax
- Displaying the HCatalog
- Using SQL-H to Create Views
- Using SQL-H to Create Views
- Eliminating Partitions in SQL-H Views
- Conversions
- Tips for Working with SQL-H
- Troubleshooting SQL-H
- Introduction to Workload Management
- Admission Control
- Managing Concurrency Using ncli
- Configuring Admission Limits with AMC
- Resource Management
- Workload Settings via the Command Line
- Priority and Weight
- Resource Allocation
- Memory Soft Limit Percent
- Memory Hard Limit Percent
- Automatic Query Cancellation
- Workload Policies
- Setting Up Workload Rules
- Workload Predicates
- Monitoring Queries
- Best Practices
- Troubleshooting
- Cumulative Sum
- 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
- 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
- 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
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- The Row_Number Command
- NTILE
- CUME_DIST
- LEAD
- LAG
- FIRST_VALUE
- LAST_VALUE
- NTH_VALUE
- MapReduce History
- What is MapReduce?
- What is SQL-MapReduce?
- SQL-MapReduce Input
- SQL-MapReduce 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
- Pattern Operators Order of Precedence
- 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
- nPath in Action
- Operators at their Simplest
- Pattern
- Accumulate
- Accumulate With All Pages
- Accumulate – nPath with a WHERE Clause
- 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
- Another nPath Example
- Finding Out What Functions You Have Installed
- Workshop #’s 1 – 18
- The Multi-Case Function
- The Multi-Case Function in Nexus
- The Multi-Case Function Mixing and Matching
- SQL-MapReduce Examples - cFilter
- 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 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_PART Function
- DATE_TRUNC Function
- DATE_TRUNC Function using TIME
- Aster NOW() Function
- Cumulative Sum
- 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
- 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
- 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
- MAX OVER with PARTITION BY Reset
- 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
- 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?
- How would you Join these two tables? You Can’t Yet!
- 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 is much like a Subquery
- An IN List Never has Duplicates – Just like a Subquery
- An IN List Ignores Duplicates
- The Subquery
- How a Basic Subquery Works
- The Final Answer Set from the 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
- 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.