4.18 out of 5
4.18
51 reviews on Udemy

A developer guide for Amazon Redshift

A complete guide to the BI developer who wants to build a datawarehouse on AWS cloud using Redshift.
Instructor:
Sravan Konde
4,285 students enrolled
English [Auto-generated]
You can master building a datawarehouse on Amazon Redshift

This course covers the Architecture of Redshift, Columnar vs Row oriented databases, Introduce you to the AWS VPC, IAM role and S3 bucket creation. You can master the SQL with best practices and learn a few admin activities which help to build a effective data warehouse. This course introduces the compression/encoding techiniques. You can learn to implement Workload management and monitor and tune long running queries.

Introduction

1
About the Trainer
2
Introduction

After completion of the course, you will understand the Redshift architecture, Launch your own Redshift cluster, Create S3 bucket, create AWS IAM user, create AWS VPC, master writing effective queries and tune queries for better performance.

Redshift Architecture

1
Redshift Architecture

Launching Redshift cluster

1
Creating VPC

Columnar database and Row oriented Database

1
Columnar and Row oriented Databases

Preparing the data, schema and data in Redshift

1
Creating schema and tables in Redshift

SQL Statements

1
DISTINCT Clause in SQL
2
WHERE predicate in SQL
3
SORT in database
4
AGGREGATE functions in database
5
JOINS in database
6
UNION, UNION ALL, INTERSECT and EXCEPT
7
HAVING Clause
8
SELECT INTO, INSERT INTO, ALTER TABLE
9
ALTER TABLE and UPDATE
10
Data Movement in Redshift
11
Type Casting in Redshift
12
COALESCE and NVL
13
Window functions in Redshift
14
User Defined Functions in Redshift

SORT and DIST Keys in Redshift

1
SORT and DIST Keys
2
Table Distribution on Redshift Cluster
3
Distribution styles
4
Implementing Dist keys and Sort keys on a table

Constraints and Database Views

1
Primary Keys and DB Views

Compression/Encoding

1
Compression and Encoding

Workload Management

1
Introduction to Workload Management
2
WLM Query Queues
3
WLM Concurrency
4
WLM User Groups
5
WLM Memory percent to Use
6
WLM Timeout
7
WLM Query Queue Hopping
8
Implementing WLM

SELECT SCHEMA schemaname,


"table" tablename, table_id tableid, size size_in_mb,


CASE WHEN diststyle NOT IN ('EVEN','ALL') THEN 1 ELSE 0 END has_dist_key,


CASE WHEN sortkey1 IS NOT NULL THEN 1 ELSE 0 END has_sort_key,


CASE WHEN encoded = 'Y' THEN 1 ELSE 0 END has_col_encoding,


CAST(max_blocks_per_slice - min_blocks_per_slice AS FLOAT) / GREATEST(NVL (min_blocks_per_slice,0)::int,1) ratio_skew_across_slices,


CAST(100*dist_slice AS FLOAT) /(SELECT COUNT(DISTINCT slice) FROM stv_slices) pct_slices_populated


FROM svv_table_info ti


JOIN (SELECT tbl, MIN(c) min_blocks_per_slice, MAX(c) max_blocks_per_slice, COUNT(DISTINCT slice) dist_slice


FROM (SELECT b.tbl, b.slice, COUNT(*) AS c


FROM STV_BLOCKLIST b


GROUP BY b.tbl, b.slice)


WHERE tbl IN (SELECT table_id FROM svv_table_info)


GROUP BY tbl) iq ON iq.tbl = ti.table_id;

Optimizing performance

1
Optimizing performance
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.2
4.2 out of 5
51 Ratings

Detailed Rating

Stars 5
20
Stars 4
15
Stars 3
14
Stars 2
0
Stars 1
2
940f34538a9261e0b1f1d36789ad04cd
30-Day Money-Back Guarantee

Includes

2 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion