Amazon Redshift Create Table Command: A Comprehensive Guide 101

on Data Warehouse, Tutorials • October 15th, 2020 • Write for Hevo

Redshift Create Table

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.

Table of Contents

What is Amazon Redshift?

Redshift Create Table Blog: Redshift Logo.

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.

Hevo Data: A smart alternative to load data in your desired destination

Hevo Data, a No-code Data Pipeline, helps to transfer data from 100+ sources to Amazon Redshift. Hevo is fully managed and completely automates the process of not only exporting data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis. 

Get Started with Hevo for Free

Check out what makes Hevo amazing:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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:

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.

Using If not exists clause in Redshift Create Table Command.

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

Redshift Create Table Blog: An example for creating a temporary table with # symbol

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

Creating a temporary table with the temporary keyword.

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

Redshift Create Table: An example of creating a temporary table with the temp keyword.

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 100+ 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.

No-code Data Pipeline Solution For Redshift