Course duration
- 3 days
Course Benefits
- Learn to gain a deeper knowledge and understanding of the Kognitio Architecture and Kognitio SQL and how to write it.
Course Outline
- Introduction to the Kognitio Architecture
- What is Parallel Processing?
- The Basics of a Single Computer
- Data in Memory is fast as Lightning
- Parallel Processing Of Data
- Kognitio is an In-Memory System
- Kognitio has Three Table Distribution Options
- Kognitio has Linear Scalability
- Nexus is Now Available for Kognitio
- Kognitio Table Structures
- Kognitio has Three Table Distribution Options
- A Table that is distributed via a Round Robin Technique
- Round Robin Technique is the Default
- Random Distribution
- A Table that is distributed by Hash
- Tables that join are excellent candidates for Hashed Tables
- Hash Distribution
- Nexus for Kognitio
- Nexus is Available on the Cloud
- Nexus Queries Every Major System
- How to Use Nexus
- Why is Nexus Special? Visualization and Automatic SQL
- Why is Nexus Special? Cross-System Joins
- Why is Nexus Special? The Amazing Hub System
- Why is Nexus Special? Save Answer Sets as Tables
- Why is Nexus Special? Automated Data Movement
- Why is Nexus Special? Nexus makes the Servers Talk Directly
- What Makes Nexus Special? The Garden of Analysis
- The Garden of Analysis Grouping Sets Tab
- The Basics of SQL
- Introduction
- Setting the Default Schema
- SELECT * (All Columns) in a Table
- Fully Qualifying a Database, Schema and Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- The WHERE Clause
- The WHERE Clause limits Returning Rows
- Double Quoted Aliases are for Reserved Words and Spaces
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don't
- Comparisons against a Null Value
- NULL means Unknown Data so Equal (=) won't return rows
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Distinct, Group By and TOP
- The Distinct Command
- Distinct vs. GROUP BY
- TOP Command
- TOP Command with an ORDER BY Statement
- Just Place the TOP n in front of any Query
- Aggregation
- The 3 Rules of Aggregation
- There are Five Aggregates
- Join Functions
- A Two-Table Join Using Traditional Syntax
- A two-table join using Non-ANSI Syntax with Table Alias
- You Can Fully Qualify All Columns
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- Date Functions
- Current_Date
- Current_Date and Current_Time
- Current_Date and Current_Timestamp
- Current_Timestamp with Milliseconds
- Current_Timestamp with Microseconds
- Current_Timestamp and SYSDATE are Synonyms
- The Now Function
- Adding Days, Weeks and Months to a Date
- Sub-query Functions
- 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
- OLAP Functions
- The Row_Number Command
- Using a Derived Table and Row_Number
- Finding the First Occurrence
- Finding the Last Occurrence
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK OVER and PARTITION BY
- RANK and DENSE RANK
- CSUM
- CSUM The Sort Explained
- CSUM Rows Unbounded Preceding Explained
- CSUM Making Sense of the Data
- CSUM Making Even More Sense of the Data
- CSUM The Major and Minor Sort Key(s)
- Temporary Tables
- There are Two Types of Temporary Tables
- CREATING A Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Multiple Ways to Alias the Columns in a Derived Table
- Strings
- The LENGTH Command Counts Characters
- The LENGTH Command Spaces can Count too
- The LENGTH Command Counts Trailing Spaces
- The LENGTH Command and TRIM
- UPPER and LOWER Commands
- Using the LOWER Command
- Using the UPPER Command
- Non-Letters are Unaffected by UPPER and LOWER
- The CHARACTERS Command Counts Characters
- Interrogating the Data
- The NULLIF Command
- The COALESCE Command Fill In the Answers
- The COALESCE Answer Set
- The COALESCE Command
- Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION ALL Explained Logically
- View Functions
- The Fundamentals of Views
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Basic Rules for Views
- Exception to the ORDER BY Rule inside a View
- Views sometimes CREATED for Formatting
- Table Create and Data Types
- Kognitio Has Three Table Distribution Options
- A Table that is distributed via a Round Robin Technique
- Round Robin Technique is the Default
- A Table that is distributed by Hash
- Tables that join are excellent candidates for Hashed Tables
- A Table that is distributed by Hash by Multiple Columns
- The Reasons for a Multi-Column HASHED Distribution Key
- Creating a Table that is replicated across all Nodes
- The Concept is all about the Joins
- Data Manipulation Language (DML)
- INSERT Syntax # 1
- INSERT example with Syntax 1
- INSERT Syntax # 2
- INSERT example with Syntax 2
- INSERT example with Syntax 3
- Kognitio Explain
- How to See an EXPLAIN Plan
- Seeing an EXPLAIN Plan with Nexus
- The Eight Rules to Reading an EXPLAIN Plan
- Interpreting Keywords in an EXPLAIN Plan
- Interpreting an EXPLAIN Plan
- A Single Segment Retrieve The Fastest Query
- Statistical Aggregate Functions
- The Stats Table
- Numeric Manipulation Functions
- Ceiling Gets the Smallest Integer Not Smaller Than X
- Floor Finds the Largest Integer Not Greater Than X
- The Round Function and Precision
- The STDDEV_POP Function
- A STDDEV_POP Example
Class Materials
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Instructor-led courses are offered via a live Web connection, at client sites throughout Europe, and at our Geneva Training Center.