A developer guide for Amazon Redshift

Name: A developer guide for Amazon Redshift
Rating: 3.9 (94 reviews)

A complete guide to the BI developer who wants to build a datawarehouse on AWS cloud using Redshift.

Created bySravan Konde

Last updated 3/2019

English

What you'll learn

You can master building a datawarehouse on Amazon Redshift
Zero to pro in less than 2 hours.

Course content

11 sections • 35 lectures • 2h 3m total length

About the Trainer0:23
Introduction1:51
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.

DISTINCT Clause in SQL2:39
WHERE predicate in SQL7:30
SORT in database1:32
AGGREGATE functions in database4:51
JOINS in database4:10
UNION, UNION ALL, INTERSECT and EXCEPT3:18
HAVING Clause1:26
SELECT INTO, INSERT INTO, ALTER TABLE4:19
ALTER TABLE and UPDATE1:59
Data Movement in Redshift4:18
Type Casting in Redshift3:29
COALESCE and NVL3:55
Window functions in Redshift4:17
User Defined Functions in Redshift2:08

Introduction to Workload Management1:04
WLM Query Queues2:31
WLM Concurrency1:39
WLM User Groups2:05
WLM Memory percent to Use
WLM Timeout2:33
WLM Query Queue Hopping3:10
Implementing WLM2:05
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;

Requirements

Basic Database/SQL Knowledge

Description

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 an effective data warehouse. This course introduces compression/encoding techniques. You can learn to implement Workload management and monitor and tune long-running queries.

I always noticed most of the data engineers are clueless about how data is retrieved from the database. Writing an optimized query is a must when dealing with big data, understanding the architecture and the movement of the data is very important to write the best query.

Without prior knowledge on SQL, you can master the SQL and become a pro with this short 2 Hour tutorial.

Who this course is for:

Redshift beginners or Data warehousing beginners curious about AWS Redshift

A developer guide for Amazon Redshift

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 2min

Redshift Architecture1 lecture • 4min

Launching Redshift cluster1 lecture • 5min

Columnar database and Row oriented Database1 lecture • 4min

Preparing the data, schema and data in Redshift1 lecture • 4min

SQL Statements14 lectures • 50min

SORT and DIST Keys in Redshift4 lectures • 19min

Constraints and Database Views1 lecture • 4min

Compression/Encoding1 lecture • 12min

Workload Management8 lectures • 15min

Requirements

Description

Who this course is for: