Redshift CREATE DATABASE Command 101: Syntax & Usage Simplified

AWS Redshift is a Cloud Data Warehousing solution provided by Amazon. Redshift can scale to store huge volumes of data running up to petabytes. It uses the AWS Infrastructure. Redshift also comes with several tools that you can use to analyze your data and draw meaningful insights for decision-making. Redshift allows its users to create all types of database objects. Examples of these objects include Databases, Stored Procedures, Tables, and Views.

 A Database lets you create tables, load your data, and execute queries. A single Redshift cluster can have multiple databases. You can easily build a new database by running the Redshift Create Database command.

In this article, you will learn how to create a database using the Redshift Create Database command.

What is Amazon Redshift? 

Amazon Redshift is a Fully Managed, Petabyte-Scale Cloud Data Warehousing Platform that makes the larger part of the AWS Cloud Platform. Amazon Redshift provides its users with a platform where they can store all their data and analyze it to extract deep business insights. 

Traditionally, businesses had to make Sales Predictions and other forecasts manually. Amazon Redshift does the largest part of the work of analyzing the data to give you time to focus on something else. It also allows you to analyze your business data using the latest predictive analytics. This way, you can make smart decisions that can drive the growth of your business. For more information, you can check out the Official Amazon Redshift Documentation. 

Learn how to speed up query performance in Redshift with indexing techniques, which help in creating efficient access paths for your queries.

How to use Redshift Create Database Command?

After creating an AWS Redshift cluster, you can go ahead to create your database. It is in the database where you will create tables, views, and run queries against them. You can create more than one database in a single cluster. For example, you can create ORDERSDB and PAYMENTSDB in the same cluster. 

To create a database in Redshift, you should use the Redshift Create Database command. To Understand the Redshift Create Database command, let’s go through the following aspects:

• Redshift Create Database command Syntax
• Redshift Create Database command Examples

1) Redshift Create Database command Syntax

The Redshift Create Database command has the following syntax:

CREATE DATABASE db_name [ WITH ]
[ OWNER [=] database_owner ]
[ CONNECTION LIMIT {limit | UNLIMITED} ]
[ COLLATE CASE_SENSITIVE |COLLATE CASE_INSENSITIVE ]

The parameters used in the Redshift Create Database command are described below:

• db_name: This is the name of the database. Ensure that there is no other database with the same name within the cluster. 
• WITH: This is an optional keyword. 
• OWNER: This keyword specifies the owner of the database. 
• database_owner: The username of the database owner. 
• [ CONNECTION LIMIT {limit | UNLIMITED} ]: The maximum number of users allowed to connect to the database concurrently. However, this limit cannot be enforced for superusers. The UNLIMITED keyword will help you to allow for a maximum number of concurrent users to connect to the database. You may also set a limit for the maximum number of connections per user using the CREATE USER command. To see the current connections to the database, you only have to query a system view named STV_SESSIONS. 
• [ COLLATE CASE_SENSITIVE |COLLATE CASE_INSENSITIVE ]: This clause specifies whether a string comparison or search will be case-sensitive or case-insensitive. By default, the string comparison and search are case-sensitive. 

You can also use the Redshift Create Database command to create a database from a Datashare to share data within an AWS account. In such a case, the Redshift Create Database command will take the following syntax:

CREATE DATABASE db_name
FROM DATASHARE datashare_name OF [ ACCOUNT id ] 
NAMESPACE namespace_guid

The above parameters are described below:

• FROM DATASHARE: This keyword specifies the location of the datashare. 
• datashare_name: The name of the Datashare on which the database was created. 
• Namespace_guid: This value specifies the producer namespace to which the datashare belongs. 
• ACCOUNT id: This value specifies the producer account to which the datashare belongs. 

2) Redshift Create Database command Examples

In this section, you will learn how to use the Redshift Create Database command via a few practical examples.

•  Create a New database and assign it to an owner.

create database companyabc
with owner abcuser1;

In the above query, we have used the Redshift Create Database command to create a database named companyabc and assigned it to a database user named abcuser1. 

The details of all Redshift databases are stored in the PG_DATABASE_INFO catalog table. You can query it as follows:

select datname, datdba
from pg_database_info
where datdba > 1;

Note that we have only selected two fields from the table. 

•  Create a Database from a Datashare

This example demonstrates creating a database named stock_db from a datashare named stockshare:

CREATE DATABASE stock_db FROM DATASHARE stockshare OF NAMESPACE '14c8833e-27a6-5f17-9de3-2c018d5ed00c';

You can also create a database and make it case-insensitive. This means that string searches and comparisons involving the database will not be case sensitive. 

The following query demonstrates this:

create database mydb collate case_insensitive;

In the above query, a database named mydb is created and the collate keyword is used to make it case insensitive. 

After creating a Redshift database, you will want to use it, probably to create a table and insert data into it. To use a particular database, you must connect to it. This is possible using the CONNECT TO command. For example, to connect to the database named mydb, we can use the following query:

CONNECT TO mydb;

You can then start to use the database by creating objects in it. That is how the Redshift Create Database command works. 

Learn More About:

Amazon Redshift CREATE Schema Command

How to use CREATE DATABASE with a datashare?

The following syntax describes the CREATE DATABASE command used to create databases from a datashare for sharing data within the same AWS account.

CREATE DATABASE database_name
[ [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid

Parameters for using CREATE DATABASE with a datashare

• FROM DATASHARE: A keyword that indicates where the datashare is located.
• datashare_name: The name of the datashare that the consumer database is created on.
• WITH PERMISSIONS: This clause specifies that the database created from the data share requires object-level permissions to access individual database objects. Without this clause, users or roles granted the USAGE permission on the database will automatically have access to all database objects.
• NAMESPACE namespace_guid: A value that specifies the producer namespace that the datashare belongs to.
• ACCOUNT account_id: A value that specifies the producer account that the datashare belongs to.

How to Use Create databases to receive results of zero-ETL integrations?

To create a database using a zero-ETL integration identity, specify the integration_id in your CREATE DATABASE command.

CREATE DATABASE destination_db_name FROM INTEGRATION 'integration_id';

For example, first, retrieve the integration ids from SVV_INTEGRATION and then use one of the integration ids retrieved to create the database that receives zero-ETL integrations.

SELECT integration_id FROM SVV_INTEGRATION;
CREATE DATABASE sampledb FROM INTEGRATION 'a1b2c3d4-5678-90ab-cdef-EXAMPLE11111';

Learn more about Redshift Functions through a detailed blog.

CREATE DATABASE limits

• Maximum of 60 user-defined databases per cluster.
• Maximum of 127 bytes for a database name.
• A database name can’t be a reserved word.

These are the limits enforced by Redshift.

To master Redshift commands, it’s essential to get familiar with Redshift’s Dynamic SQL , which allows you to perform operations like filtering and sorting efficiently.

Conclusion

In this article, you have learned how to effectively use the Redshift Create Database Command to create a new database. Amazon Redshift runs on the AWS infrastructure, which allows it to scale massively. After creating an AWS Redshift cluster, you can go ahead to create a database within it. It is within the database where you will create objects like tables and views for data storage. Redshift Create Database is a single line command to easily Create a completely New Database as well as create a database from a Datashare.

After you have created your database, you can start loading and processing data for further business analysis. For a more In-depth and Holitisic Analysis of Business Performance and Financial Health, it is important to consolidate data from all the applications used across your business.

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of sources to a Data Warehouse like Amazon Redshift or a Destination of your choice to be visualized in a BI Tool. It is a reliable, completely automated, and secure service that doesn’t require you to write any code! Sign up for 14-day free trial today.

FAQ on Redshift CREATE DATABASE Command

If you are using Amazon Redshift as your Cloud Data Warehousing and Analysis  Platform and searching for a Stress-Free Alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 150+ sources & BI tools(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of using the Redshift Create Database Command! Share your thoughts with us in the comments section below.