Amazon Redshift CREATE Schema Command: 2 Critical Aspects

The essence of having a Database is to be able to manage your data efficiently so you can easily store information, query records, and increase the overall productivity and profitability of your organization. Not having the right knowledge or right tools to do this can become an impediment to achieving success and ensuring the long-term survival of your company.

There are various tools in the market today that programmers use in the management of Databases and Amazon Redshift is one of the popular ones as it is used by a wide variety of organizations.

This article will introduce you to Amazon Redshift. It will also help you understand how to use Amazon Redshift CREATE Schema Command. It will also provide the syntax and usage of Amazon Redshift CREATE Schema Command.

What is Amazon Redshift?

Amazon Redshift is a petabyte-scale Data Warehousing Solution from Amazon Web Services (AWS). It is also used for migrating large Databases, making Data Management straightforward.

Amazon Redshift has a Massively Parallel Processing (MPP) architecture. Its databases are based on a Column-Oriented Database designed to connect to SQL-based clients and Business Intelligence (BI) tools.

This makes data (structured and unstructured) easily available to users at all times and helps them run Complex Analytical queries. Amazon Redshift also connects with standard ODBC and JDBC connections.

Each Amazon Redshift Data Warehouse contains a collection of computing resources organized in a cluster and each of the clusters runs its Amazon Redshift engine which contains at least one Database.

What is Amazon Redshift Schema?

A Database may contain multiple objects such as Tables, Views, Functions, Triggers, Dexes, Stored Procedures, etc., and schemas can be regarded as a collection of these Database objects. A Schema in SQL is a collection of Database objects linked with a particular Database with a username. It can also be defined as a list of logical structures of data.

Therefore, a Schema is a helpful tool used to segregate Database objects for different applications, access rights, and to manage the security administration of Databases.

In Amazon Redshift, each schema in a Database contains Tables and other kinds of named objects. Database objects can be grouped under a common name using Schemas as they are similar to file system directories but Schemas can not be nested.

Also, identical object names can be used in different schemas in the same Database without issues, for example, a table named TITLE can be in different Schemas in the same Database, and there will be no conflict. Objects across multiple schemas within a database can be accessed by users with the necessary privileges.

Schemas can help you with the following:

• Allows many people to work in the same Database without interfering with one another.
• Helps in managing and organizing Database objects into logical groups for easy accessibility.
• Applications can put objects into different Schemas so that their names do not conflict with the names of objects used by other applications.

Syntax and Usage of Amazon Redshift CREATE Schema Command

Amazon Redshift CREATE Schema is used to define a new schema for the current Database. The following is the syntax of Amazon Redshift CREATE Schema:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION username ] 
           [ QUOTA {quota [MB | GB | TB] | UNLIMITED} ] [ schema_element [ ... ] 
 
CREATE SCHEMA AUTHORIZATION username[ QUOTA {quota [MB | GB | TB] | UNLIMITED} ] [ schema_element [ ... ] ]

The parameters found in the syntax of Amazon Redshift CREATE Schema are described below:

• IF NOT EXISTS: This is used to indicate that if the specified Schema already exists, the command should make no changes and return a message that the Schema exists instead of outputting an error message.
  • This is most useful when scripting so the script will not fail when CREATE Schema tries to create an already existing Schema.
• schema_name: This is the name of the new Schema.
• AUTHORIZATION: This clause gives ownership to a specified user.
• username: This is the name of the Schema owner.
• schema_element: This is the definition for one or more objects to be created within the Schema.
• QUOTA: This tells the maximum amount of disk space that the specified Schema can use.
• UNLIMITED: Amazon Redshift enforces a maximum of 9900 Schemas per database. It does not have an imposing limit to the growth of the total size of your Schema.

An example of Amazon Redshift CREATE Schema is GEN_SALES with ownership to the user STOREUSER and quota is set at 50GB is shown below.

CREATE SCHEMA gen_sales AUTHORIZATION STOREUSER QUOTA 50 GB;

ALTER Command in Amazon Redshift CREATE Schema

The ALTER command can be used to change the definition of an existing schema by renaming it or changing the owner of a schema. The syntax for ALTER is shown below.

ALTER SCHEMA schema_name
{
RENAME TO new_name |
OWNER TO new_owner |
QUOTA { quota [MB | GB | TB] | UNLIMITED }
}

The parameters found in the above syntax are:

• schema_name: This is the name of the Schema to be altered.
• RENAME TO: This is a clause that renames the Schema.
• new_name: This is the new name of the Schema.
• OWNER TO: A clause that changes the owner of the schema.
• new_owner: This is the owner of the Schema.
• QUOTA: This is the maximum amount of disk space that the specified Schema can use. This is a collective of the size of all the tables found in the specified Schema and Amazon Redshift converts all the selected values to megabytes.

An example of the ALTER Command of Amazon Redshift CREATE Schema is shown below. This code will rename SALES to DECEMBER_SALES.

ALTER SCHEMA sales
RENAME TO december_sales;

GRANT Command in Amazon Redshift CREATE Schema

GRANT Command in Amazon Redshift CREATE Schema is used to define privileges for a user or a user group. These privileges may include access to read in tables, write data, create and drop tables, etc. This command is used to give specific privileges for a Table, Database, Schema, Function, Procedure, Language, or Column. This article will only dwell on Schemas.

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]

The parameters found in the syntax above are:

• CREATE: Depending on the Database object, you can grant the following privileges to users or user groups on CREATE.
  • On Databases, CREATE can be used to allow users to create schemas within a Database. 
  • CREATE allows users to create objects within a Schema, rename an object. 
  • CREATE also allows users to create objects within a Schema, rename an object, but the user must have the CREATE privilege and own the object to be renamed.
• USAGE: It grants USAGE privilege on specific Schemas to users and makes objects in that Schema accessible to them. Although, specific actions like SELECT or UPDATE privileges on tables must be granted separately. By default, all users have CREATE and USAGE privileges on the PUBLIC Schema.
• ON SCHEMA schema_name: Grants the specified privileges on a Schema.
• PUBLIC: Grants privileges specified to all users, including users created later. PUBLIC represents a group that always includes all users.

Privileges of Amazon Redshift CREATE Schema

The privileges of and permissions Amazon Redshift CREATE Schema are determined by the owner of the Schema and users with necessary privileges can have unrestricted access to objects found across multiple Schemas in a Database. The following explains the privileges enjoyed by users.

• By default, every user has CREATE and USAGE privileges on the PUBLIC schema of a Database, though this can be removed by using the REVOKE command to erase such privileges.
• If the USAGE privilege is not granted to users by the object owners, the users can not access any object in Schemas they do not own.
• Users granted access to the CREATE privilege in a Schema created by another user can also create objects in that Schema.

Conclusion

In conclusion, the CREATE SCHEMA command in Amazon Redshift is a powerful tool for organizing your database objects and improving data management. By allowing you to group tables, views, functions, and other objects into distinct namespaces, it enhances clarity and control within your data warehouse. Whether you’re working in a multi-user environment or managing complex projects, creating schemas helps maintain order and security. With just a few simple commands, you can structure your Redshift environment to be more efficient and scalable, making it easier to manage your growing data needs.

Hevo Data supports 150+ data sources and helps you transfer your data from these sources to Data Warehouses like Amazon Redshift, Google BigQuery, Snowflake, etc.

Sign up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Frequently Asked Questions