Amazon Redshift Create Table Command: A Comprehensive Guide 101

With the increasing adoption of cloud storage, the demand for powerful cloud data warehouses is growing. Amazon Redshift is a popular choice, offering the ability to store terabytes of data and providing fast query processing and robust integration with various analytics and business intelligence tools. To get started with Redshift, you first need to create tables to store data. 

This article provides a step-by-step guide on using the Amazon Redshift Create Table command, including clauses such as “Default” and “Like,” to help you create tables effectively.

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 data 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, if 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 the 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.

CREATE TABLE IF NOT EXISTS 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 “If not exists” clause/parameter with the 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 table’s name. You can create a temporary table in Amazon Redshift using either of the following three ways:

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

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

Method 2: Using the temporary keyword to create a temporary table in Amazon Redshift

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

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

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

This is how you can create temporary tables with the 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 auto-generate 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. 

Learn More About:

• Redshift Temp Tables
• Redshift ETL Tools
• Redshift FIRST_VALUE and LAST_VALUE functions

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. 

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Tell about your experience of using the Amazon Redshift Create Table command! Share your thoughts in the comments section below.

FAQs