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.
Amazon Redshift Architecture and SQL
Duration
3 Days
Course
AMRS-105-NZ
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Amazon Redshift Architecture and SQL course is designed to provide students with a deeper knowledge and understanding of the Amazon Redshift Architecture and SQL and how to write it. Students will learn the Amazon Redshift Architecture and SQL starting at the most basic level and going to the most advanced level with many examples.Objectives
Upon completion of the Amazon Redshift Architecture and SQL course, students will be able to:
- Identify and apply basic SQL functions
- Understand the WHERE clause
- Contrast Distinct vs. Group by
- Recognize and construct functions, such as:
- Aggregation, Join, Date, OLAP, View, Sub-query and Statistical Aggregate
- Recognize and produce Temporary Tables
- Define and distinguish Strings
- Interrogate the Data
- Set Operators
- Demonstrate knowledge of the Data Manipulation Language (DML)
Audience
- Anyone who has a desire to learn the Amazon Redshift Architecture and SQL from beginners to an advanced audience.
Prerequisites
- None
Topics
- What is Parallel Processing?
- The Basics of a Single Computer
- Data in Memory is Fast as Lightning
- Parallel Processing Of Data
- Each Parallel Process Organizes the Rows inside a Data Block
- Why Columnar?
- Row Based Blocks vs. Columnar Based Blocks
- As Row-Based Tables Get Bigger, the Blocks Split
- Data Blocks Are Processed One at a Time Per Unit
- Columnar Tables Store Each Column in Separate Blocks
- Visualize the Data – Rows vs. Columns
- The Architecture of Redshift
- Distribution Styles
- Distribution Key Where the Data is Unique
- Another Way to Create A Table
- Distribution Key Where the Data is Non-Unique
- Distribution Key is ALL
- Even Distribution Key
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Fact and Dimension Table Distribution Key Designs
- Improving Performance By Defining a Sort Key
- Sort Keys Help Group By, Order By and Window Functions
- Each Block Comes With Metadata
- How Data Might Look On A Slice
- The ANALYZE Command Collects Statistics
- Redshift Automatically ANALYZES Some Create Statements
- What is a Vacuum?
- The VACUUM Command Grooms a Table
- Database Limits
- Creating a Database
- Creating and Dropping a User
- Inserting Into a Table
- Renaming, Adding, and Dropping a Table or a Column
- Converting Table Structures to Redshift
- Best Practices for Designing Tables
- Choose the Best Sort Key
- Each Block Comes With Metadata
- Creating a Sort Key
- Sort Keys Help Group By, Order By and Window Functions
- Choose a Great Distribution Key
- Distribution Key Where the Data is Unique
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Define Primary Key and Foreign Key Constraints
- Primary Key and Foreign Key Examples
- Use The Smallest Column Size When Creating Tables
- Use Date/Time Data Types for Date Columns
- Specify Redundant Predicates on the Sort Column
- Setting the Statement_Timeout to Abort Long Queries
- Amazon Redshift System Tables
- Trouble Shooting Catalog Table pg_table_def
- Seeing the System Tables in your Nexus Tree
- Catalog Table pg_table_def
- Checking Tables for Skew (Poor Distribution)
- Checking All Statements That Used the Analyze Command
- Checking Tables for Skew (Poor Distribution)
- Checking For Details About the Last Copy Operation
- Checking When a Table Has Last Been Analyzed
- Checking For Column Information on a Table
- System tables for troubleshooting data loads
- Determining Whether a Query is Writing to Disk
- Compression Types
- Byte Dictionary Compression
- Delta, LZO, Mostly, Runlength Encoding
- Text255 and Text32k Encodings
- ANALYZE COMPRESSION
- Copy
- Create Table Syntax
- Temporary Table Examples
- Table Limits and CTAS
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy By Creating a Temp Table and Truncating Original
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in The Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example With A Different Column Aliasing Style
- Column Aliasing Can Default For Normal Columns
- CREATING A Derived Table using the WITH Command
- Our Join Example With The WITH Syntax
- WITH Statement That Uses a SELECT *
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- Connecting To Redshift Via Nexus
- Three Ways to Run an EXPLAIN
- EXPLAIN – Steps, Segments and Streams
- EXPLAIN Terms For Scans and Joins
- EXPLAIN Terms For Aggregation and Sorts
- EXPLAIN Terms For Set Operators and Miscellaneous Terms
- EXPLAIN Example and the Cost, Rows, and Width
- Simple EXPLAIN Example and the Costs
- EXPLAIN Join Example Using DS_BCAST_INNER
- EXPLAIN Join Example Using DS_DIST_NONE
- EXPLAIN Showing DS_DIST_NONE Visually
- EXPLAIN With a Warning
- EXPLAIN For Ordered Analytics Such as CSUM
- EXPLAIN For Scalar Aggregate Functions
- EXPLAIN For HashAggregate Functions
- EXPLAIN Using Limit, Merge and Sort
- EXPLAIN Using a WHERE Clause Filter
- EXPLAIN Using the Keyword Distinct
- EXPLAIN for Subqueries
- Finding the Current Schema on the Leader Node
- Getting Things Setup in Your Search Path
- Five Details You Need To Know About The Search_Path
- Introduction
- SELECT * (All Columns) in a Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- Sort the Data with the ORDER BY Keyword
- ORDER BY Defaults to Ascending
- Use the Name or the Number in your ORDER BY Statement
- Two Examples of ORDER BY using Different Techniques
- NULL Values sort First in Ascending Mode (Default)
- NULL Values sort Last in Descending Mode (DESC)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- A Missing Comma can by Mistake become an Alias
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- A Great Technique for Comments to Look for SQL Errors
- Using Limit to bring back a Sample
- Using Limit With an Order By Statement
- The WHERE Clause limits Returning Rows
- Using a Column ALIAS throughout the SQL
- 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
- NULL means UNKNOWN DATA so Equal (=) won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- NULL is UNKNOWN DATA so NOT Equal won’t Work
- Using Greater Than Or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting OR, Character Data
- Using Different Columns in an AND Statement
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- The IN List is an Excellent Technique
- IN List vs. OR brings the same Results
- Using a NOT IN List
- A Technique for Handling Nulls with a NOT IN List
- Another Technique for Handling Nulls with a NOT IN List
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE command Underscore is Wildcard for one Character
- LIKE Command Works Differently on Char Vs Varchar
- Introducing the TRIM Command
- Numbers are Right Justified and Character Data is Left
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- Use the TRIM command to remove spaces on CHAR Data
- Like and Your Escape Character of Choice
- Like and the Default Escape Character
- Similar To Operators
- Multiple Occurrences Must Be Consecutive
- The Distinct Command
- Distinct vs. GROUP BY
- TOP Command
- TOP Command is brilliant when ORDER BY is Used!
- What is the Difference Between TOP and LIMIT?
- The 3 Rules of Aggregation
- There are Five Aggregates
- Troubleshooting Aggregates
- 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
- 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
- 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
- OUTER JOIN with Additional AND Clause Results
- The DREADED Product Join
- The Horrifying Cartesian Product Join
- The ANSI Cartesian Join will ERROR
- The CROSS JOIN
- The Self Join
- The Self Join with ANSI Syntax
- How would you Join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- The 5-Table Join – Logical Insurance Model
- Current_Date
- TIMEOFDAY()
- SYSDATE Returns a Timestamp With Microseconds
- GETDATE Returns a Timestamp Without Microseconds
- Add or Subtract Days from a date
- The ADD_MONTHS Command
- Dateadd Function And Add_Months Function are Different
- The EXTRACT Command
- EXTRACT from DATES and TIME
- EXTRACT with DATE and TIME Literals
- EXTRACT of the Month on Aggregate Queries
- The Datediff command
- The Date_Part Function Using a Date
- The Date_Part Function Using a Time
- Date_Part Abbreviations
- The to_char command
- Conversion Functions
- Formatting A Date
- A Summary of Math Operations on Dates
- Date Related Functions
- A Side Title example with Reserved Words as an Alias
- Implied Extract of Day, Month and Year
- DATE_PART Function
- DATE_TRUNC Function
- MONTHS_BETWEEN Function
- ANSI TIME
- ANSI TIMESTAMP
- Redshift TIMESTAMP Function
- Redshift TO_TIMESTAMP Function
- Redshift NOW() Function
- Redshift TIMEOFDAY Function
- Redshift AGE Function
- Time Zones
- Intervals for Date, Time and Timestamp
- Using Intervals
- Troubleshooting The Basics of a Simple Interval
- Interval Arithmetic Results
- The OVERLAPS Command
- 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)
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- ANSI Moving Window is Current Row and Preceding n Rows
- How ANSI Moving SUM Handles the Sort
- Moving SUM every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- Moving Average
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Moving Average every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK() OVER and PARTITION BY
- RANK() OVER And LIMIT
- PERCENT_RANK() OVER
- COUNT OVER for a Sequential Number
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- The Row_Number Command
- Standard Deviation Functions Using STDDEV / OVER
- STDDEV / OVER Example
- VARIANCE / OVER Syntax
- Variance Functions Using VARIANCE / OVER
- Using VARIANCE with PARTITION BY Example
- Using FIRST_VALUE and LAST_VALUE
- Using FIRST_VALUE
- Using LAST_VALUE
- Using LAG and LEAD
- Using LEAD
- Using LAG
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in The Derived Table
- Visualize This Derived Table
- Multiple Ways to Alias the Columns in a Derived Table
- Column Aliasing Can Default For Normal Columns
- CREATING A Derived Table using the WITH Command
- Our Join Example With The WITH Syntax
- WITH
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Create Table Syntax
- Temporary Table Examples
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy By Creating a Temp Table and Truncating Original
- An IN List Ignores Duplicates
- The Subquery
- The Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Should you use a Subquery of a Join?
- The Basics of a Correlated Subquery
- The Top Query always runs first in a Correlated Subquery
- Correlated Subquery Example vs. a Join with a Derived Table
- How the Double Parameter Subquery Works
- More on how the Double Parameter Subquery Works
- How to handle a NOT IN with Potential NULL Values
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The TRIM Command trims both Leading and Trailing Spaces
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- 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
- The POSITION Command finds a Letters Position
- Using the SUBSTRING to Find the Second Word On
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Declaring a Cursor
- The NULLIFZERO Command
- The ZEROIFNULL Command
- Answer to the ZEROIFNULL Question
- The COALESCE Command
- The Basics of CAST (Convert And Store)
- The Basics of the CASE Statements
- Valued Case Vs. A Searched Case
- When an ELSE is present in CASE Statement
- When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- Nested Case
- Put a CASE in the ORDER BY
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Creating a View to Join Tables Together
- You Select From a View
- Basic Rules for Views
- An ORDER BY Example Inside of a View
- An ORDER BY Inside of a View That is Queried Differently
- Creating a View With Ordered Analytics
- Creating a View With The TOP Command
- Creating a View With The LIMIT Command
- Altering A Table
- A View that Errors After An ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Rules of Set Operators
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- An Equal Amount of Columns in both SELECT List
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY (a Number)
- Great Trick: Place your Set Operator in a Derived Table
- UNION vs. UNION ALL
- A Great Example of how EXCEPT works
- The Stats Table
- STDDEV
- Casting STDDEV_SAMP and SQRT (VAR_SAMP)
- The STDDEV_POP Function
- A STDDEV_POP Example
- The STDDEV_SAMP Function
- A STDDEV_SAMP Example
- The VAR_POP Function
- A VAR_POP Example
- The VAR_SAMP Function
- A VAR_SAMP Example
Related Courses
-
Amazon Redshift Basics (Architecture) and SQL
AMRS-100- Duration: 5 Days
- Delivery Format: Classroom Training, Online Training
- Price: 3,825.00 USD
-
Advanced Architecting on AWS
AWS-131- Duration: 3 Days
- Delivery Format: Classroom Training, Online Training
- Price: 2,025.00 USD
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??
??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