Mastering Amazon Redshift and Serverless for Data Engineers

Launch Amazon Redshift, a fast data warehouse, by creating a cluster in the web console, explore built-in tables, run simple queries to validate data, and perform basic CRUD operations.

Learn to create a free trial Amazon Redshift cluster, name it retail, and explore the sample Tickit data with two fact tables and five dimensions, preparing for query access.

Connect to a Redshift database using the query editor, select a cluster, dev database, and aws user, and run queries to interact with tables.

Learn to list existing tables in a Redshift cluster by querying information_schema.tables in the query editor. Connect to the TICKIT database and filter for the public schema.

Learn to run queries against Redshift tables via the query editor, preview data, and view table schemas. Explore selecting fields, counting rows, and validating data in a Redshift database.

Insert data into Redshift tables using single-row or multi-row inserts, with or without column lists. Redshift does not enforce primary key, foreign key, or unique constraints, and has no indexes.

Update data in redshift tables using the update statement with set and where clauses, demonstrated on the myusers table; preview with select and convert names to lowercase with lower.

Delete data from Redshift tables using delete with a where clause or delete all rows as part of CRUD. Compare truncate as a cleanup option and review DDL versus DML.

learn how to save and retrieve redshift queries using the query editor, including mandatory query name and description, saving to saved queries, loading, and running single queries.

Delete a Redshift cluster from the dashboard and create a final snapshot to retain data. Use snapshots to recreate the cluster after deletion if you want to preserve your data.

Restore a Redshift cluster from a final manual snapshot with a unique name, customize cluster identifier, node type, number of nodes, database name, and port, then validate tables.

Explore copying data from S3 to Redshift using the copy command, configure an IAM role with S3 permissions, and customize the command to match your data characteristics while validating results.

Clone the retail_db dataset from GitHub, clean up the .git folder, copy data into the itv-retail s3 bucket, and prepare for the Redshift copy command.

Create a new Redshift database retail_db and an orders table with a primary key, then configure an IAM user to run copy commands from S3 into Redshift.

Create an IAM user with programmatic access and full S3 permissions to enable copying data from S3 into Redshift using the COPY command.

Discover how to copy data from s3 into the Redshift orders table using the copy command, configure credentials, and troubleshoot with stl_load_errors.

Troubleshoot Redshift copy command failures by inspecting the load query, error reason 1205, and adjust the order date to a date time type before reloading from S3.

Copy data from S3 to Redshift tables by adjusting the order date to datetime, dropping and recreating the orders table, and validating success with queries.

Validate redshift data loaded from s3 using the copy command by previewing orders, counting rows, and grouping by status to confirm 68,883 rows and nine statuses.

Master the Redshift copy command to load data from s3 into Redshift tables, manage credentials or iam roles, and handle csv, json, and parquet formats.

Create a customizable IAM role with S3 full access, attach it to Redshift cluster, and use it to copy data from S3 into Redshift tables via copy command, without credentials.

Attach the IAM role to the Redshift cluster and load S3 data into the order_items table with a COPY command using IAM_ROLE and a delimiter or CSV, then validate.

Learn to prepare json data in S3 and load it into Redshift using the copy command, including cloning the retail_db_json repository, uploading to S3, and reviewing the json data.

Copy json data from s3 into redshift tables using the copy command with json auto, use iam role, and validate by querying the first 10 records.

Explore how to connect to a Redshift cluster from external tools and work with its tables, using Python, SQL Workbench, or psql to develop applications against Redshift databases.

Learn to connect to redshift clusters from the external world by allocating an elastic ip, mapping the endpoint to it, and making the cluster publicly accessible while monitoring cost.

Enable public access to the Redshift cluster by mapping the endpoint to an Elastic IP via the cluster's modify publicly accessible setting, then restart the cluster and expect downtime.

Learn to open a Redshift cluster to external access by updating security group inbound rules for port 5439, using telnet tests, and configuring my IP or a custom port.

Learn how to connect to a Redshift cluster from outside, set host, port, database, user, and password, create retail_db and retail_user, and grant all permissions on the database.

Connect to a Redshift cluster from an external source using psql, validating host and port 5439, database retail_db, and user retail_user, while addressing permissions and exploring SQL Workbench.

Change the ownership of retail_db tables orders and order_items from aws_user to retail_user using the Redshift query editor and CLI, then validate by running select queries against retail_db.

Connect to a Redshift cluster with SQL Workbench by creating a new connection using the Amazon Redshift driver, entering endpoint, port 5439, and database retail_db; test connection and preview data.

Create and activate a Python virtual environment, install psycopg2-binary, and optionally boto3 and Jupyter Lab to connect to Redshift cluster databases from Python applications.

Learn to connect to a Redshift database from Python using psycopg2. Create a connection and cursor, then run a simple select query and fetch results.

Learn how to manage Redshift data with Python by truncating the order_items table, then copy data from S3 into Redshift using a copy statement and proper cursor and connection handling.

Learn to copy data from s3 into Redshift tables using Python by creating an iam user with programmatic access, obtaining access key and secret key, and using the copy command.

Validate IAM user access by configuring AWS credentials in the OS environment, creating a boto3 S3 client, and listing buckets and objects under itv-retail/retail_db_json to prepare copying data to Redshift.

Execute a Python Redshift copy command to load S3 data into a table using an IAM user’s access keys and psycopg2, then validate with count and preview queries.

Explore diststyles, distkeys, and sortkeys in Amazon Redshift, and learn how these distribution and sorting concepts guide table design, architecture, and multi-node data loading.

Explore the Redshift architecture, including leader and compute nodes, node slices, and how client applications connect via JDBC or ODBC, with reference to the official diagrams.

Create a multi-node Redshift cluster using three dc2.large nodes and review distribution styles, distribution keys, and sort keys, then delete to avoid charges.

Connect to a Redshift cluster using the query editor to run queries against dev database with temporary credentials or Secrets Manager, and explore distribution styles, dist keys, and sort keys.

Create the retail_db database in Redshift, establish the initial retail schema for subject areas, and use the awsuser admin account to create the user and schemas.

Learn to create a Redshift database user, assign a password, and verify permissions by connecting to retail_db, creating and dropping tables, and exploring schemas.

Create retail_ods schema in retail_db and authorize retail_user, then validate access by connecting to create a table. Understand one-to-many relation between database and schemas for subject areas and ETL pipelines.

Explore distribution styles, dist keys, and sort keys for Redshift tables, comparing default diststyle auto with explicit settings, and access metadata via svv_table_info and pg_table_def.

Explore Redshift table metadata and access control, understanding distribution styles, dist keys, and sort keys, and learn to grant select on pg_catalog to a Redshift user.

Learn to configure the Redshift search path to include retail_ods so queries against pg_table_def succeed, using show search_path, set, custom parameter groups, and a cluster reboot.

Explore Redshift table design by examining distribution styles, dist keys, and sort keys using the orders table in retail_ods, including auto diststyle behavior and copy from S3 with IAM role.

Restore a Redshift cluster from a snapshot, assign IAM roles, and configure a custom parameter group to include retail_ods in the search_path. Validate by querying pg_table_def against retail_ods.

Review the Redshift cluster architecture and node slices to understand memory, CPU, and storage, and explain how dist keys and distribution styles drive data distribution for parallel loading.

Discover Amazon Redshift distribution styles—all, even, key, and auto—and how dist keys shape data distribution across cluster nodes. Learn the syntax and semantics for creating tables and loading data.

Apply redshift distribution strategies—all, even, key, and auto—across the retail six-table schema, configure dist keys for orders and order items, and plan for sort keys and data loading.

Create Redshift tables with distribution style all for three dimension tables: departments, categories, and products in the retail_ods schema, and copy data from S3 while validating with pg_table_def and svv_table_info.

Troubleshoot Redshift copy errors when using diststyle all by inspecting stl_load_errors for varchar length issues, adjust column size, and reload from S3; explore diststyle auto for customers and verify metadata.

Create a Redshift table with distribution style auto, which may switch to even or key as data grows, and load customers data from S3 using copy.

Discover how to create redshift tables with distribution styles, applying dist style key on orders and order items to optimize joins and copy data from s3 into retail_ods.

Delete an Amazon Redshift cluster only after taking a manual snapshot, then recreate the cluster from that snapshot to review distribution styles, distkeys, and sort keys.

Learn how Redshift federated queries let you query RDS databases like Postgres, MySQL, and Aurora from a Redshift cluster. Spectrum enables querying S3 data via blue catalogue or etna catalogue.

Learn how federated queries connect Redshift to RDS databases like Postgres and MySQL, enabling you to run cross-system queries via external schemas and feed a Redshift data warehouse.

Create an IAM role for Redshift to enable federated and spectrum queries. Attach S3 full access and configure the redshift customizable role in the AWS console.

Set up a Postgres RDS server and create retail_db with a user and tables. Enable Redshift federated queries to access these external tables.

Enable federated queries on Redshift clusters by building a Postgres-based retail_db, creating a retail_user, granting permissions, and executing a script to create six tables and load JSON data.

Create and manage secrets for a Postgres database using AWS Secrets Manager to enable federated queries from Redshift. Use Python to access these secrets for loading data and developing apps.

Access secret details with python and boto3 through aws secrets manager to retrieve retail1.secrets. Parse the secret’s json to extract credentials, engine, host, and db details for a connection url.

Load json data into a pandas dataframe using read_json with lines=True, then prepare to populate Postgres tables by building file paths from a base directory and table name.

Use json_to_df to convert json files into a pandas dataframe and write it to existing database tables with to_sql, using a SQLAlchemy connect string built from Secrets Manager.

Create and attach a secrets manager policy to a Redshift federated query role, enabling external schema access for Postgres data and federated queries using the secret ARN.

Restore a Redshift cluster from snapshot, associate an IAM role with a secret policy, and enable federated queries to a Postgres retail_db within the same VPC.

Create the external schema retail_pg in redshift to run federated queries against a Postgres database, configure the IAM role, secret, and endpoint, and troubleshoot connectivity issues.

Explore federated queries with a redshift cluster by linking an external retail_pg schema to a postgres source, enable vpc routing, and run queries and etl workflows for analytics.

Perform etl using redshift federated queries to join transactional orders from retail_ods with dimensional customers from retail_pg (postgres), count orders per customer for reporting.

clean up resources used for federated queries, including deleting the redshift cluster, postgres rds, and secret, then introduce spectrum for querying s3 data with a catalog.

Update the IAM role with policies to grant Glue Data Catalog access to a Redshift cluster for Spectrum, enabling queries against S3 via the catalog.

Restore a Redshift cluster from a snapshot and associate the ITVRedshift Federated and Spectrum Demo IAM role to enable queries using Spectrum, ensuring S3 and Glue Data Catalog access.

Review how to access Redshift Spectrum via Glue Data Catalog, expose databases and tables as external schema, and query json data stored in S3 using Redshift.

Create an external schema using glue data catalog and redshift spectrum to query S3 data, exposing glue databases and tables as external tables and validating with sample queries.

Learn to run queries using Redshift Spectrum to join Redshift cluster tables with Glue Data Catalog tables, exposing external schemas for data lake and data warehouse reporting.

Learn how to clean up a Redshift cluster after demos, including federated queries and spectrum considerations, deleting the cluster, and managing snapshots to avoid unnecessary AWS costs.

Create a Redshift serverless workgroup and namespace in the AWS console, configure VPC, security groups, and subnets, set the base RPU, and define admin credentials and an IAM role.

Explore how Amazon Redshift serverless decouples compute from database objects using namespaces and workgroups. Learn how RPUs drive costs and how databases, security, and encryption fit within a namespace.

Preview Amazon Redshift Serverless dashboard, review namespaces and workgroups, inspect queries metrics, credits, and RPU, and learn how to run queries against the created workgroup.

Validate your Amazon Redshift Serverless workgroup by connecting to the Redshift Query Editor V2 and running a sample query against information_schema.tables to confirm query execution.

Enable public accessibility for the Redshift serverless workgroup, adjust the security group inbound rules, and verify connectivity via telnet on port 5439 before connecting with psql.

Learn how Redshift serverless workgroups incur costs based on RPUs, view and adjust the base RPU capacity from 32 to 512, and understand automatic scaling during and after queries.

Set up a Redshift Spectrum database for access to S3 data via Glue catalog database and tables, then validate and practice basic and advanced SQL on Redshift Serverless.

Set up s3 data for glue catalog and redshift spectrum by creating a bucket and copying retail_db_json with aws cli and cloud shell.

Delete all tables, the database, and the crawler in the AWS Glue Console to reset the retail_db and prepare for recreating the catalog and tables for redshift spectrum.

Create a Glue crawler to build a Glue catalog database and six tables from S3 folders, running on demand and using a new IAM role to populate retail_db.

Set up an AWS Glue catalog database and tables for Redshift Spectrum using an AWS Glue crawler on S3 data, validate six tables, and enable serverless Redshift Spectrum access.

Create a redshift serverless workgroup and namespace for retail, then configure redshift spectrum database and run sample queries against the dev database using information schema.

Learn to create the retail_dm database and retail_user, then grant all permissions on retail_dm to retail_user using the Redshift query editor, with notes on notebook workflows.

Create the retail_dm database and retail_user, configure database_URL, and connect via Redshift query editor or SQL extension; prepare the external schema for Glue catalog.

Create an external schema in Redshift pointing to the Glue data catalog, verify permissions, attach the appropriate IAM role to the namespace, and run the create external schema statement.

Validate external schema setup by running select queries on Glue Data Catalog tables via Redshift Spectrum and Redshift Serverless; note external tables are not stored in Redshift, and DDL differs.

Master filtering, aggregations, and joins with AWS Redshift SQL, using nested subqueries on the retail_db data model (orders, order_items, customers, products, categories, departments).

Learn how the with clause creates named subqueries (ctes) in Redshift sql, use union all to generate test data, and query against temporary aliases for clearer, modular queries.

Create or replace views in Redshift sql queries to persist complex definitions, compare with the with clause, and query views directly for counting and distinct course ids.

Learn to filter data with AWS Redshift SQL using the retail_db.orders table, applying where clauses and distinct values, using limit for display, and counting records to analyze order_status.

Use boolean and to filter orders by status and date in Redshift SQL, applying a where clause to select complete orders placed on a specific date.

Learn to filter data with the like operator in Redshift SQL, using pattern matching and the where clause to query dates like 2014-01% and 2014-01-01.

Master boolean or and in operator usage in Redshift SQL to filter orders by status (complete, pending, pending payment) using where, like, and count.

Explore total aggregations in AWS Redshift SQL by using count, sum, min, max, and average on a sales view, including distinct counts, and distinguish measures from dimensions.

Learn to compute the total average in Redshift SQL using sum and count or the AVG function on sale_amount, with aliasing like avg_sale_amount.

Learn to perform total aggregations with conditions in Redshift SQL, applying sum, avg, min, max, and count using where clauses on course_id in the sales dataset.

Learn to compute counts and count(distinct) on real world datasets using Redshift sql, exploring orders and order items in the retail_db schema and identifying major fields for aggregation.

Learn to perform aggregations on the order_items dataset using sum and average on measures like order_item_quantity and order_item_subtotal, to compute revenue, item counts, and explore min, max, and rounding.

Learn to perform grouped aggregations in Redshift SQL by using group by with aggregate functions like sum and count to compute course revenue and counts across keys.

Learn to filter aggregated results with having on group by, using sums or counts for revenue or course count. Avoid where for aggregates and use having with aliases or functions.

Understand the SQL order of execution from the from clause through group by, having, and order by, and why where cannot filter aggregated results while having can.

Learn to join tables in Redshift using inner, left outer, right outer, and full outer joins, with practical examples and setup steps to connect to a serverless workgroup.

Prepare an AWS EMR cluster for Amazon Redshift integration by provisioning an EC2 elastic IP and an EC2 key pair, then associate IP with master node and connect via ssh.

Create a bootstrap script to install boto3 on all emr cluster nodes via s3-hosted bootstrap, enabling redshift integration and secrets manager credentials retrieval.

Create an AWS EMR cluster integrated with Amazon Redshift, using Spark with the Glue data catalog, and configure scaling, bootstrap script, key pair, and storage.

Attach an elastic IP to the EMR master node and validate SSH connectivity using the PEM key, ensuring stable access and preparing EMR for Redshift integration.

Set up a VS Code development workspace on EMR master node, clone the mastering redshift repo, and install pylance to enable a Python project for AWS EMR and Redshift integration.

Validate the redshift serverless workgroup and namespace named aigithub-wg and aigithub-ns, then verify connectivity from the emr master node to the workgroup endpoint using telnet and psql.

Validate connectivity to a redshift serverless workgroup from an EMR master node using the PostgreSQL client psql, installing postgresql-devel, and connecting with endpoint, port 5439, user admin, and database dev.

Create an Amazon Redshift database github_dm and a user github_user in Amazon Redshift Serverless, grant all permissions, and verify connectivity from EMR via Spark.

Install the psycopg2 binary on the EMR master node to enable Python connections to Redshift serverless, validate installation, and prepare to establish the connection in the next lecture.

Validate Redshift connectivity from the EMR master node using Python and psycopg2, running a notebook to set up a connection, cursor, and query against a serverless Redshift workgroup.

Create and validate the ghrepos table in github_dm public schema using Redshift editor, set owner to github_user, and confirm access with a Python query in the AWS EMR PySpark workflow.

Learn to create and store Redshift credentials in AWS Secrets Manager for serverless Redshift, enable EMR access with proper IAM permissions, and validate secret access via boto3.

Validate that boto3 is installed on the EMR master node via the bootstrap action, then verify access in Python and notebooks, ensuring boto3 is available on all cluster nodes.

Retrieve Redshift credentials from AWS Secrets Manager using Python boto3, create a Secrets Manager client, fetch secret value, parse JSON, and access host, username, and password.

Fetch Redshift credentials from Secrets Manager using boto3 and psycopg2 to validate connectivity from the AWS EMR master node, and preview a spark-based approach with pyspark to write data.

Launch the pyspark cli on the EMR master node with redshift jars to connect Spark to Redshift and validate connectivity using spark-redshift, jdbc, spark-avro, and minimal-json jars.

Explore connecting to Redshift from Spark on an EMR cluster using PySpark, credentials, JDBC URL, and IAM role, validating data access and loading into a dataframe.

Validate spark and Redshift integration by running a notebook-based spark session on EMR, configuring pyspark modules, and loading a Redshift dataframe to verify schema and row count.

Set up gharchive github activity data in s3 by downloading 2022-06-18 to 2022-06-20, uploading 72 files to s3, and preparing to read into a data frame for redshift processing.

Leverage pyspark to read json data from s3, create a spark session with redshift jars, filter create events for repositories, and prepare the new_repos dataframe for loading into Redshift.

Load the new_repos dataframe into a Redshift table by staging avro files in a temporary location with a jdbc url, secrets manager credentials, and an IAM role.

Validate the spark load of json data into Amazon Redshift by checking row counts and data integrity, and fix column name mismatches through aliases or renaming.

Learn how Spark loads data from an S3 tempdir into Redshift, ensuring DataFrame columns match the Redshift table to prevent duplicates, and review lifecycle policies that auto-clean temp files.

Explore the final steps of integrating AWS EMR with Amazon Redshift, including deploying Spark applications with Redshift integration, developing and loading data, and managing cluster termination and serverless workgroup usage.