In order to complete the recipes in this chapter, you will need a SQL client of your choice to access the Amazon Redshift cluster (for example, MySQL Workbench).

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

Getting ready

To complete this recipe, you will need the following:

• Access to any SQL interface such as a SQL client or query editor
• An Amazon Redshift cluster endpoint

How to do it…

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

1. We will create a new database called qa in the Amazon Redshift cluster. To do this, use the following code:

   CREATE DATABASE qa
   WITH 
   OWNER awsuser 
   CONNECTION LIMIT 50; 

2. To view the details of the database, you will query the PG_DATABASE_INFO, as shown in the following code snippet:

   SELECT datname, datdba, datconnlimit 
   FROM pg_database_info
   WHERE datdba > 1;

   This is the expected output:

   datname datdba  datconnlimit
   qa 100 UNLIMITED

   This query will list the databases that exist in the cluster. If a database is successfully created, it will show up in the query result.

3. To make changes to the database—such as database name, owner, and connection limit—use the following command, replacing <qauser> with the respective Amazon Redshift username:

   /* Change database owner */
   ALTER DATABASE qa owner to <qauser>;
   /* Change database connection limit */
   ALTER DATABASE qa CONNECTION LIMIT 100;
   /* Change database name */
   ALTER DATABASE qa RENAME TO prod;

4. To verify that the changes have been successfully completed, you will query the system table pg_database_info, as shown in the following code snippet, to list all the databases in the cluster:

   SELECT datname, datdba, datconnlimit 
   FROM pg_database_info
   WHERE datdba > 1;

   This is the expected output:

   datname datdba datconnlimit
   prod 100 100

5. You can connect to the prod database using the connection endpoint, as follows:

   <RedshiftClusterHostname>:<Port>/prod

   Here, prod refers to the database you would like to connect to.

6. To delete the previously created database, execute the following query:

   DROP DATABASE prod;

   Important note

   It is best practice to have only one database in production per Amazon Redshift cluster. Multiple databases could be created in a development environment to enable separation of functions such a development/unit testing/quality assurance (QA). Within the same session, it is not possible to access objects across multiple databases, even though they are present in the same cluster. The only exception to this rule is database users and groups that are available across the databases.

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 also logically groups the objects within a cluster. 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 access to any SQL interface such as a SQL client or query editor.

How to do it…

1. Users can create a schema using the CREATE SCHEMA 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. Create finance_grp, audit_grp, and finance_admin_user groups using the following command:

   create group finance_grp;
   create group audit_grp;
   create user finance_admin_usr with password '<PasswordOfYourChoice>'; 

3. Create a schema named finance with a space quota of 2 terabytes (TB), with a finance_admin_usr schema owner:

   CREATE schema finance authorization finance_admin_usr QUOTA 2 TB;

   You can also modify an existing schema using ALTER SCHEMA or DROP SCHEMA.

4. For the finance schema, grant access privileges of USAGE and ALL to the finance_grp group. Further, grant read access to the tables in the schema using a SELECT privilege for the audit_grp group:

   GRANT USAGE on SCHEMA finance TO GROUP finance_grp;
   GRANT USAGE on SCHEMA finance TO GROUP audit_grp;
   GRANT ALL ON schema finance to GROUP finance_grp;
   GRANT SELECT ON ALL TABLES IN SCHEMA finance TO GROUP audit_grp;

5. You can verify that the schema and owner group have been created by using the following code:

   select nspname as schema, usename as owner
   from pg_namespace, pg_user
   where pg_namespace.nspowner = pg_user.usesysid
   and pg_namespace.nspname ='finance';

6. Create a foo table (or view/database object) within the schema by prefixing the schema name along with the table name, as shown in the following command:

   CREATE TABLE finance.foo (bar int); 

7. Now, in order to select the foo table from the finance schema, you will have to prefix the schema name along with the table name, as shown in the following command:

   select * from finance.foo; 

   The preceding SQL code will not return any rows.

8. Assign a search path to conveniently reference the database objects directly, without requiring the complete namespace of the schema qualifier. The following command sets the search path as finance so that you don't need to qualify the schema name every time when working with database objects:

   set search_path to '$user', finance, public;

   Important note

   The search path allows a convenient way to access the database objects without having to specify the target schema in the namespace when authoring the SQL code. The search path can be configured using the search_path parameter with a comma-separated list of schema names. When referencing the database object in a SQL when no target schema is provided, the database object that is in the first available schema list is picked up. You can configure the search path by using the SET search_path command at the current session level or at the user level.

9. Now, executing the following SELECT query without the schema qualifier automatically locates the foo table in the finance schema:

   select * from foo;

   The preceding SQL code will not return any rows.

Now, the new finance schema is ready for use and you can keep creating new database objects in this schema.

Important note

A database is automatically created by default with a PUBLIC schema. Identical database object names can be used in different schemas of the database. For example, finance.customer and marketing.customer are valid table definitions that can be created without any conflict, where finance and marketing are schema names and customer is the table name. Schemas serve the key purpose of easy management through this logical grouping—for example, you can grant SELECT access to all the objects at a schema level instead of individual tables.

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 a SQL client, or you can use the Amazon Redshift query editor.

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 customer_number, first_name, last_name, and date_of_birth related attributes:

   CREATE TABLE finance.customer 
   (
     customer_number   INTEGER,
     first_name        VARCHAR(50),
     last_name         VARCHAR(50),
     date_of_birth     DATE
   );

   Note

   The key ingredient when creating a customer table is to define columns and their corresponding data types. Amazon Redshift supports data types such as numeric, character, date, datetime with time zone, boolean, geometry, HyperLogLog, and super.

2. We will now insert 10 records into the customer table using a multi-value insert statement:

   insert into finance.customer values
   (1, 'foo', 'bar', '1980-01-01'),
   (2, 'john', 'smith', '1990-12-01'),
    (3, 'spock', 'spock', '1970-12-01'),
    (4, 'scotty', 'scotty', '1975-02-01'),
    (5, 'seven', 'of nine', '1990-04-01'),
    (6, 'kathryn', 'janeway', '1995-07-01'),
    (7, 'tuvok', 'tuvok', '1960-06-10'),
    (8, 'john', 'smith', '1965-12-01'),
    (9, 'The Doctor', 'The Doctor', '1979-12-01'),
    (10, 'B Elana', 'Torres', '2000-08-01');

3. You can now review the information on the customer table using the svv_table_info system view. Execute the following query:

   select "schema", table_id, "table", encoded, diststyle, sortkey1,  size, tbl_rows
   from svv_Table_info
   where "table" = 'customer'
   and "schema" = 'finance';

   This is the expected output:

   schema table_id table encoded diststyle sortkey1 size tbl_rows
   finance 167482 customer Y AUTO(ALL) AUTO(SORTKEY) 14 10

   Table_id is the object ID and the number of records in the table is 10 rows. The encoded column indicates the table is compressed. Amazon Redshift stores columns in 1 megabyte (MB) immutable blocks. The size of the table is 14 MB. Let's dive into the terminology and concept of diststyle and sortkey. The customer table is created with default sort key of AUTO, where Amazon Redshift handles the distribution style of the table on the computer nodes.

   • diststyle is a table property that dictates how that table's data is distributed throughout the cluster.
   • KEY: The value is hashed, and the same value goes to same location (slice) on the compute node.
   • ALL: The full table data goes to the first slice of every compute node.
   • EVEN: Round-robin across all the compute nodes.
   • AUTO: When the table is small, it starts with an AUTO style, and when it becomes larger in size, Amazon Redshift converts it to an EVEN style.

Further information about distribution styles can be found at the following link:

https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html

1. Let's run a query against the customer table to list customers who were born before 1980:

   select *
   from finance.customer
   where extract(year from date_of_birth) < 1980;

2. You can also create a copy of the permanent table using create table as (CTAS). Let's execute the following query to create another table for a customer born in 1980:

   create table finance.customer_dob_1980 as 
   select *
   from finance.customer
   where extract(year from date_of_birth) = 1980 ;

3. You can also create temporary tables—for example, to generate IDs in a data loading operation. The temporary tables can only be queried during the current session and are automatically dropped when the session ends. The temporary tables are created in the session-specific schema and are not visible to any other user. You can use a create temporary table command to do this. Execute the following three queries in single session:

   create temporary table #customer(custid integer IDENTITY(1,1), customer_number integer);
   insert into #customer (customer_number) values(1);
   select * from #customer;

   This is the expected output:

   custid  customer_number
   1 1

4. Reconnect to the Amazon Redshift cluster using the SQL client. Reconnecting will create a new session. Now, try to execute the following query against the #customer temporary table. You will get an ERROR: 42P01: relation "#customer" does not exist error message as the temporary tables are only visible to the current session:

   select * from #customer;

How it works…

When you create a table in Amazon Redshift, it stores the data on disk, column by column, on 1 MB blocks. Amazon Redshift by default compresses the columns, which reduces the storage footprint and the input/output (I/O) when you execute a query against the table. Amazon Redshift provides different distribution styles to spread the data across all the compute nodes, to leverage the MPP architecture for your workload. The metadata and the table summary information can be queried using the catalog table and summary view.

Amazon Redshift stores metadata about the customer table. You can query the pg_table_def catalog table to retrieve this information. You can execute the following query to view the table/column structure:

select * from pg_table_def where schemaname = 'finance';. 

Important note

When data is inserted into a table, Amazon Redshift automatically builds, in memory, the metadata of the minimum and maximum values of each block. This metadata, known as a zone map, is accessed before a disk scan in order to identify which blocks are relevant to a query. Amazon Redshift does not have indexes; it does, however, have sort keys. Sort key columns govern how data is physically sorted for a table on disk and can be used as a lever to improve query performance. Sort keys will be covered in depth in the performance-tuning best practices chapter.

View database objects allow the result of a query to be stored. In Amazon Redshift, views run each time a 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 in 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 access to any SQL interface such as a SQL client or query editor.

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.customer_vw 
   AS
   SELECT customer_number,
          first_name,
          last_name,
          EXTRACT(year FROM date_of_birth) AS year_of_birth
   FROM finance.customer;

2. To verify that a view has been created, you can use the following command:

   SELECT table_schema as schema_name,
          table_name as view_name,
          view_definition
   FROM information_schema.views
   WHERE table_schema not in ('information_schema', 'pg_catalog')
   ORDER by schema_name,
            view_name;

   Note

   This script will provide an output of the views created under a particular schema and the SQL script for the view.

3. We can now select directly from the finance.customer_vw view, just like with any another database object, like so:

   SELECT * from finance.customer_vw limit 5;

   Note

   Here, the finance.customer_vw view abstracts the date_of_birth personally identifiable information (PII) from the underlying table and provides the user an abstracted view of only the essential data for that year to determine the age group.

   This is the expected output:

   outputcustomer_number,first_name,last_name,year_of_birth 
   1 foo bar 1980
   2 john smith 1990
   3 spock spock 1970
   4 scotty scotty 1975
   5 seven of nine 1990 

4. To delete the previously created view, you can use the following command:

   DROP VIEW finance.customer_vw ;