Updated March 8, 2023
Introduction to Redshift create schema
Redshift create schema is the process of creating and defining the structure of the current database in Redis Database Management System. We have to be very careful while creating and structuring the schema because the way in which the data is stored has a lot of impact on the management and manipulation of the data. In this article, we will have a look at the syntax of the create schema syntax inside redshift and also will have a detailed discussion over the arguments that are specified in the command. Further, we will understand the demonstration of the create schema command along with the help of some examples.
Syntax:
The syntax of the Redis Create Schema command is shown below:
CREATE SCHEMA [IF NOT EXISTS] name of the schema [AUTHORIZATION name of user] [QUOTA {amount of memory or quota [TB | GB |MB] | UNLIMITED}] [elements of schema [ …]
CREATE SCHEMA AUTHORIZATION name of user [ QUOTA {amount of memory or quota [TB | GB |MB] | UNLIMITED}] [elements of schema] ]
The parameters of arguments that are used in the above syntax are explained one by one in the below section containing its description –
- Name of schema – This is the schema name that cannot be of PUBLIC privilege. The schema name needs to be a valid alphanumeric string which are the same rules followed while declaring the identifiers and names.
- IF NOT EXISTS – This is the optional clause that can be specified when we want to make sure that error does not occur when the schema with the same name exists in the database and that is when the system should not do any changes to an existing schema and return a message saying that the schema with same already exists in the database. This optional clause is most importantly used inside scripts that can be run again and again and if the schema is created then it should not be modified. This makes sure that the execution of the script does not stops if there is an error while creating the schema.
- QUOTA – This is the amount of memory that will be used at max by the schema that is being created. Disk space utilization is an important factor in a database. This can help in the optimization of disk space. This quota includes the views that are created materialistically, permanent tables, and also the cloned objects which are duplicates of all the specified tables and that are created at each of the individual nodes for computation. Note that the quota memory amount does not include the memory consumed by the temporary tables, schema, and namespace.
We can check the amount of memory kept for the schema in parameter STL_SCHEMA_QUOTA_STATE. In case if you want to check for which tables the quota is exceeded, you can check the parameter value of STL_SCHEMA_QUOTA_VIOLATIONS.
By default, if you do not specify the quota unit, GB that is gigabytes are taken into consideration. Internally, whatever unit you specify and whatever amount of quota is mentioned is converted to megabytes.
- AUTHORIZATION – This helps in specifying the privileges and ownership for the user which is specified in the command.
- Name of the user – This is the name of the owner of the schema.
- Schema elements – These are the objects to be created inside the schema and their definition.
- UNLIMITED – When we specify the quota as unlimited which specifies that there should not be any limit imposed on the size of the schema is created.
Note: There is a limit enforced on the schemas per database in Redshift by Amazon which is 9900 schemas per individual database.
Examples
Let us consider some of the examples to understand how we can make the use of CREATE SCHEMA command in the Amazon redshift database.
Example #1
Suppose we want to create one schema with the name customers_details whose ownership and access need to be assigned to the user having the name educba_user. For this, we will make use of the below command –
create schema customers_details authorization educba_user
The output of executing the above command is as shown below –
To confirm if the user has been assigned the ownership of the created schema, you can fire the following command –
select nspname as schema, usename as owner from pg_namespace, pg_user
where pg_namespace.nspowner = pg_user.usesysid and pg_user.usename ='{{username}}';
The output of the above command is as shown below –
Example #2
Let us consider one example where we will create the schema which has a certain space or memory quota specified right when it is being created. Suppose, we have to create the schema with the name customers_details with a quota amount of 25 GB, then we can make the use of the below query statement –
create schema customers_details authorization educba_user QUOTA 25 GB;
The output of the execution of the above query statement is as shown below –
To confirm the existence of the created schema, we can make the select query on the catalog table named PG_NAMESPACE using the following query –
select nspname as schema, usename as owner
from pg_namespace, pg_user
where pg_namespace.nspowner = pg_user.usesysid
and pg_user.usename 'educba_user';
The output of the execution of the above query statement is as shown below showing the name of the schema along with its user to which we have assigned the ownership –
Example #3
Now, we will try to execute the CREATE SCHEMA query with one of its optional parameters which is IF NOT EXISTS which can either return the message saying schema already exists or will return the success message of schema creation depending on the existence of the customers_details schema in the current database. Our query statement is as shown below –
create schema if not exists customers_details;
The output of the execution of the above command in my case is as shown below –
Conclusion
The use of CREATE SCHEMA command is done when you want to create a new schema that defines the objects and elements and also specifies the ownership and quota-related details of the schema to be created. We saw that how we can verify the creation of the schema from catalog tables.
Recommended Articles
This is a guide to Redshift create schema. Here we discuss Introduction, syntax, parameters, examples with code implementation respectively. You may also have a look at the following articles to learn more –
