Different Methods to Create Redshift Tables

In my other posts, I have discussed various methods to create Redshift table from Spark DataFrame, Redshift Temporary tables, creating an index on Redshift tables, etc. In this article, we will check different methods and approach to create Amazon Redshift tables. We will also check differences between various methods to create tables in Redshift.

During development, developer create a table either permanently or temporarily as per the requirement. Developers usually create tables using DDL such as “CREATE TABLE” statement. But, sometimes you may need to use different methods such as creating a copy of an existing table or creating table similar to another table, etc.

Well, following are some of the different methods to create table in Amazon Redshift.

Create Redshift Table using DDL
CREATE TABLE AS Method to Create Redshift Table
Use SELECT INTO to Create Redshift Table
CREATE TABLE LIKE Method to Create Redshift Table

Create Redshift Table using DDL

Using table DDL is the most common method to create tables in Amazon Redshift.

Typically, DDL in Redshift looks something like following example.

CREATE TABLE RedshiftTestTable
 (
 id INT NOT NULL identity(1,1),
 name VARCHAR(50),
 Age INT NOT NULL 
 )
 DISTSTYLE AUTO
 DISTKEY (id)
 SORTKEY (id);

CREATE TABLE AS Method to Create Redshift Table

CREATE TABLE AS (CTAS) is another common method to creates a new table based on a query. This method is usually used to create table with data. The query is mandatory to use CTAS.

The following statement can create table in Redshift using CTAS.

CREATE TABLE RedshiftCTAS as 
(
SELECT 
	ID,
	name,
	age
FROM RedshiftTestTable
);

The new table is loaded with data defined by the query in the command. The table columns have names and data types associated with the output columns of the query.

Redshift CREATE TABLE AS (CTAS) with DISTSTYLE, DISTKEY and SORTKEY

You can also specify the DISTSTYLE, DISTKEY and SORTKEY while using CTAS. However, DISTSTYLE EVEN/ALL/NONE is not compatible with a DISTKEY.

For example,

CREATE TABLE RedshiftCTAS2 
 DISTSTYLE KEY
 DISTKEY (id)
 SORTKEY (id)
AS 
(
SELECT 
	ID,
	name,
	age
FROM RedshiftTestTable
);

Use SELECT INTO to Create Redshift Table

SELECT INTO is another way to create new tables. The same method is available in SQL server to create table out of the query. SELECT INTO facilitates fast bulk data copy to a new tables from a query.

Following statement uses SELECT INTO to create a new table RedshiftTestTable3 in Amazon Redshift.

SELECT 
	ID,
	name,
	age
INTO RedshiftTestTable3
FROM RedshiftTestTable;

Note that, you cannot specify the DISTSTYLE, DISTKEY and SORTKEY while using SELECT INTO.

CREATE TABLE LIKE Method to Create Redshift Table

The CREATE TABLE LIKE method creates a new table by inheriting the encoding, distkey, sortkey, and not null attributes of the parent table.

The following statement creates a “RedshiftTestTable_bkp” table by copying settings from the exiting “RedshiftTestTable” table.

CREATE TABLE RedshiftTestTable_bkp (LIKE RedshiftTestTable);

CREATE TABLE LIKE method creates the structure by copying settings from the parent table. However, it does not copy the data into a new table.

Difference Between Redshift CREATE TABLE LIKE and CREATE TABLE AS (CTAS)

Following table provide the simple difference between CREATE TABLE LIKE and CREATE TABLE AS (CTAS)

CREATE TABLE LIKE	CREATE TABLE AS (CTAS)
Inherits table settings such as DISTSTYLE, DISTKEY, SORTKEY and NOT NULL Settings	You have to specify the DISTSTYLE, DISTKEY and SORTKEY while using CTAS
Only table structure is created. Does not copy data	You can control the data copy while using CTAS
CREATE TABLE LIKE does not inherits default constraint and identity	CTAS does not inherits default constraint and identity