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.