In order to complete all the recipes in this chapter, you will need an Amazon Redshift data warehouse (either serverless or provisioned cluster) deployed, and a SQL client of your choice to access Amazon Redshift (for example, Amazon Redshift Query Editor V2, DBeaver, SQL Workbench/J, DataGrip, etc.).

Amazon Redshift consists of at least one database, and it is the highest level in the namespace hierarchy for the database objects. This recipe will guide you through the steps needed to create and manage a database in an Amazon Redshift data warehouse.

Getting ready

To complete this recipe, you will need everything mentioned in the Technical requirements section at the start of the chapter.

How to do it…

Let’s now set up and configure a database on Amazon Redshift. Use the SQL client to connect to it and execute the following commands:

1. We will create a new database called qa in the Amazon Redshift data warehouse that has the owner awsuser and accepts a maximum of 50 connections. To do this, use the following code:

   CREATE DATABASE qa
   WITH
   OWNER awsuser
   CONNECTION LIMIT 50;
   

OWNER and CONNECTION LIMIT are optional parameters. You can find the complete list of options available for...

In Amazon Redshift, a schema is a namespace that groups database objects such as tables, views, stored procedures, and so on. Organizing database objects in a schema is good for security monitoring, and it also means objects are logically grouped. In this recipe, we will create a sample schema that will be used to hold all the database objects.

Getting ready

To complete this recipe, you will need the following:

• Access to any SQL interface, such as Query Editor V2 or a local SQL client
• An Amazon Redshift data warehouse (serverless or provisioned cluster) endpoint
• Log in as a superuser into an Amazon Redshift cluster

How to do it…

1. Users can create a schema using the CREATE SCHEMA (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_SCHEMA.html) command. The following steps will enable you to set up a schema with the name finance and add the necessary access to the groups:
2. User groups...

In Amazon Redshift, you can create a collection of tables within a schema with related entities and attributes. Working backward from your business requirements, you can use different modeling techniques to create tables in Amazon Redshift. You can choose a star or snowflake schema by using normalized, denormalized, or data vault data modeling techniques.

In this recipe, we will create tables in the finance schema, insert data into those tables, and cover the key concepts to leverage the massively parallel processing (MPP) and columnar architecture.

Getting ready

To complete this recipe, you will need everything mentioned in the Technical requirements section at the start of the chapter.

How to do it…

Let’s explore how to create tables in Amazon Redshift:

1. Let’s create a customer table in the finance schema with the customer_number, first_name, last_name, and date_of_birth attributes:

   CREATE TABLE...

View database objects allow the result of a query to be stored. In Amazon Redshift, views run each time the view is mentioned in a query. The advantage of using a view instead of a table is that it can allow access to only a subset of data on a table, join more than one table into a single virtual table, and act as an aggregated table, and it takes up no space on the database since only the definition is saved, hence making it convenient to abstract complicated queries. In this recipe, we will create views to store queries for the underlying tables.

Getting ready

To complete this recipe, you will need everything mentioned in the Technical requirements section at the start of the chapter.

How to do it…

Let’s create a view using the CREATE VIEW command. We will use the following steps to create a view:

1. Create a finance.customer_vw view based on the results of the query on finance.customer:

   CREATE VIEW finance...

A materialized view is a database object that persists the results of a query to disk. In Amazon Redshift, materialized views allow frequently used complex queries to be stored as separate database objects, allowing you to access these database objects directly, and enabling faster query responses.

Employing materialized views is a common approach to powering repeatable queries in a business intelligence (BI) dashboard, and avoids expensive computation each time. Furthermore, materialized views allow an incremental refresh of the results, using the underlying table data. In this recipe, we will create a materialized view to query the tables and also to persist the results for a faster fetch.

Getting ready

To complete this recipe, you will need everything mentioned in the Technical requirements section at the start of the chapter.

How to do it…

Let’s create a materialized view using the CREATE MATERIALIZED...

Stored procedures in Amazon Redshift are user-created objects using the Procedural Language/PostgreSQL (PL/pgSQL) procedural programming language. Stored procedures support both data definition language (DDL) and data manipulation language (DML). Stored procedures can take in input arguments but do not necessarily need to return results. PL/pgSQL also supports conditional logic, loops, and case statements. Stored procedures are commonly used to build reusable extract, transform, load (ETL) data pipelines and serve the database administrator (DBA) to automate routine administrative activities—for example, periodically dropping unused tables.

The SECURITY attribute controls who has privileges to access certain database objects.

Stored procedures can be created with security definer controls to allow the execution of a procedure without giving access to underlying tables—for example, they can drop a table created by another...

Scalar UDF functions in Amazon Redshift are routines that are able to take parameters, perform calculations, and return the results. UDFs are handy when performing complex calculations that can be stored and reused in a SQL statement. Amazon Redshift supports UDFs that can be authored using either Python or SQL. In addition, Amazon Redshift also supports AWS Lambda UDFs, which opens up further possibilities to invoke other AWS services.

For example, let’s say the latest customer address information is stored in AWS DynamoDB—you can invoke an AWS Lambda UDF to retrieve this using a SQL statement in Amazon Redshift.

Getting ready

To complete this recipe, you will need the following:

• Everything mentioned in the Technical requirements section
• Access to create an Identity and Access Management (IAM) role that can invoke AWS Lambda and attach it to Amazon Redshift
• Access to create an AWS Lambda function