Amazon Redshift Create Table Command: A Comprehensive Guide 101

The Amazon Redshift Create Table command allows you to create new tables for your Amazon Redshift instance.

With most companies adopting cloud as their primary choice of storing data, the need for having a powerful and robust cloud data warehouse is on the rise. One of the most popular cloud-based data warehouses that meets all these requirements is Amazon’s Redshift data warehouse. It allows users to store potentially TBs of data with ease and provides fast query processing abilities along with robust integration support for a diverse set of data analytics and business intelligence tools. The first step of using a data warehouse such as Amazon Redshift is creating tables to store data.

This article aims at providing you with a step-by-step solution to help you create Amazon Redshift tables using the Amazon Redshift Create Table command. Upon a complete walkthrough of content, you will be able to create tables for your Amazon Redshift instance using various clauses/parameters such as “Default”, “Like”, etc. along with the Amazon Redshift Create Table command.

What is Amazon Redshift?

Amazon Redshift is a petabyte-scale, fully managed data warehouse service that stores data in the form of clusters that you can access with ease. It supports a multi-layered architecture that provides robust integration support for various business intelligence tools and a fast query processing functionality. Apart from business intelligence tools, you can also connect Amazon Redshift to SQL-based clients. It further allows users and applications to access the nodes independently.

Being a fully-managed warehouse, all administrative tasks associated with Amazon Redshift, such as creating backups, security, etc. are taken care of by Amazon.

Prerequisites

• Working knowledge of Amazon Redshift.
• An Amazon Redshift account.

How to Create Tables in Amazon Redshift?

It requires users to specify a name for the table and columns, along with their data types. You can use the Amazon Redshift Create Table command with the following syntax:

CREATE TABLE table_name (
column_name1 data_type1,
column_name2 data_typ2
)

For example, if you want to create a table named “Employees” having columns such as employee_id, first_name, last_name, hire_date, and salary, you can do so, using the following command:

CREATE TABLE Employees
(
employee_id      integer(30),
first_name       varchar(30),
last_name        varchar(30),
hire_date        date,
salary         integer
);

Amazon Redshift also allows users to modify their commands using parameters/keywords such as “Like”, “As”, “Default”, etc. to access or load their Amazon Redshift data with ease.

7 Use Cases of Redshift Create Table Command

You can learn more about using the Amazon Redshift Create Table command with these parameters in the following sections:

• Using Default Column Values
• Using the If Not Exists Clause
• Creating a Temporary Table
• Using the AS Clause
• Using the Like Clause
• With IDENTITY Column
• With Primary Key

1) Using Default Column Values

Amazon Redshift allows users to set default values for their columns while creating a table in their Amazon Redshift instance using the default keyword. The default keyword helps prevent issues related to NULL values and uses the following syntax:

column_name data_type DEFAULT Default_Value 

With default values in place, in case the user doesn’t provide any input for that column, the default value will be used automatically.

For example, if you want to create a table named “Employees” having columns such as employee_id, first_name, last_name, hire_date, and salary with default values for each column, you can use the following command:

For example:
CREATE TABLE Employees
(
employee_id      integer(30),
first_name       varchar(30) default 'Antony',
last_name        varchar(30) default 'John',
hire_date        date default '2020-01-01',
salary         integer default 3000
);

This is how you can use the default keyword with Amazon Redshift Create Table command.

2) Using the If Not Exists Clause

To avoid any duplicate tables from being created, Amazon Redshift allows users to make use of the “If not exists” clause/parameter in their Amazon Redshift Create Table commands. With the “If not exists” clause/parameter in place, Amazon Redshift will first check if the table already exists or not. If it does, it will give you an error message for the same.

This is how you can use the “If not exists” clause/parameter with Amazon Redshift Create Table command.

3) Creating a Temporary Table

Amazon Redshift allows users to create temporary tables by making use of the temp or temporary keyword while creating new tables for their Amazon Redshift instance. The temporary tables exist only for the duration of the session, and once the session ends, they automatically discard the data.

To create a temporary table, you can either make use of the temp keyword or use a # symbol just before the tables’ name. You can create a temporary table in Amazon Redshift using either of the following three ways:

Using the # symbol to create a temporary table in Amazon Redshift

Using the temporary keyword to create a temporary table in Amazon Redshift

Using the temp keyword to create a temporary table in Amazon Redshift

This is how you can create temporary tables with Amazon Redshift Create Table command.

4) Using the AS Clause

With Amazon Redshift’s AS clause/parameter, you can create new tables in Amazon Redshift based on the result of a particular select statement. By using the AS clause/parameter, you will be able to inherit the table structure and data from the SQL query, however, it doesn’t support inheriting constraints, default values, etc.

For example, if you want to create a table named “Employees_backup” based on the output obtained from the “Select * from Employees” query, you can use the following command:

CREATE TABLE Employees_backup AS Select * from Employees;

This is how you can use the AS clause/parameter with Amazon Redshift Create Table command.

5) Using the Like Clause

With Amazon Redshift‘s Like clause/parameter, you can create new tables in Amazon Redshift based on the structure of another table. By using the Like clause/parameter, you will be able to inherit the table structure and attributes of the parent table, however, it doesn’t support inheriting the primary and foreign key constraints.

The Like clause and parent table name must be there in your command if you want to leverage this functionality. You can also inherit the default values by using the INCLUDING DEFAULTS clause.

For example, if you want to create a table named “Employees_backup” that has the same structure and default column values as the “Employees table”, you can use the following command:

CREATE TABLE Employees_backup (LIKE Employees INCLUDING DEFAULTS);

This is how you can use the Like clause/parameter with Amazon Redshift Create Table command.

6) With IDENTITY Column

An IDENTITY column in Redshift works in a similar manner as the KEY field in a relational database. It helps in auto-generating unique values for a column. The syntax for defining an IDENTITY column is:

IDENTITY(seed, step)

The starting value is the seed, and the increment value is the step. The data type of a column must be an INT or BIGINT in order to be defined as an IDENTITY column.

The table below shows how to create a Redshift table with an IDENTITY Column. Table name, column name, data type, and the keyword IDENTITY are required at a minimum.

CREATE TABLE employees
(
employee_id      int(30)       identity(0,1),
first_name       varchar(30)   default 'Mary',
last_name        varchar(30)   default 'Cooper',
email            varchar(60)   default 'mary.cooper@xyz.com',
phone            varchar(15)   default '000-000-0000',
hire_date        date          default '2000-01-01',
user_id         integer       default 0
);

7) With Primary key

The Redshift primary key uniquely identifies all records in a relational database. Primary Key constraints in Redshift tables are only for informational purposes and are not enforced.

Primary Key constraints can be applied at the column or table level. If only one column needs to be set as a Primary Key, you can do it at the column level. You can set it at the table level if you need multiple columns to form a composite Primary Key.

Primary Key: Column Level

CREATE TABLE employees
(
employee_id      int(30)     PRIMARY KEY,
first_name       varchar(30) default 'Mary',
last_name        varchar(30) default 'Keller',
email            varchar(60) default 'mary.keller@xyz.com',
phone            varchar(15) default '000-000-0000',
hire_date        date        default '1990-12-12',
user_id         integer     default 0
);

Primary Key: Table Level

CREATE TABLE employees
(
employee_id      int(30),
first_name       varchar(30) default 'Mary',
last_name        varchar(30) default 'Keller',
email            varchar(60) default 'mary.keller@xyz.com',
phone            varchar(15) default '000-000-0000',
hire_date        date        default '1990-12-12',
user_id         integer     default 0, 
primary key (last_name,hire_date)
);

Limitations of creating tables using the Amazon Redshift Create Table command

• Amazon Redshift imposes a limit on the number of tables that you can create in a cluster by node type. 
• An Amazon Redshift table cannot have more than 1,600 columns. 

Datatypes Supported by Amazon Redshift

• SMALLINT (INT2)
• INTEGER (INT, INT4)
• BIGINT (INT8)
• DECIMAL (NUMERIC)
• REAL (FLOAT4)
• DOUBLE PRECISION (FLOAT8)
• BOOLEAN (BOOL)
• CHAR (CHARACTER)
• VARCHAR (CHARACTER VARYING)
• DATE
• TIMESTAMP
• TIMESTAMPTZ

Conclusion

This article teaches you how to successfully use the Amazon Redshift Create Table command to create new tables in Amazon Redshift. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. Using Amazon Redshift to draw crucial insights about your business, requires you to bring in data from a diverse set of sources by setting up various ETL pipelines. 

Visit our Website to Explore Hevo

Setting up ETL pipelines can be challenging especially for a beginner & this is where Hevo saves the day. Hevo Data, a No-code Data Pipeline, helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo, with its strong integration with 150+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Tell us about your experience of using the Amazon Redshift Create Table command! Share your thoughts in the comments section below.