Redshift Temp Tables: Syntax, Commands, & Tips Simplified 101

Venturing into Data Science can be challenging, especially when deciding which tools to use, given the wide array of choices available. In today’s data-driven world, organizations seek ways to efficiently handle and transform the massive amounts of data they produce, often looking to minimize operational costs. The Cloud has become the preferred platform for data transformation because it offers easier management and cost-efficiency compared to traditional on-premise systems.

Data warehousing architectures have evolved, and most leading service providers now offer cloud-based solutions. Companies are increasingly moving to the Cloud due to its lower upfront costs, scalability, and performance advantages. Amazon Redshift is a powerful cloud-based data warehouse solution that addresses many limitations of traditional on-premise data warehouses by offering flexibility, lower costs, and scalability.

One key feature of Amazon Redshift is its support for SQL functionalities, such as creating temporary tables to handle non-permanent data. This allows users to process data dynamically without long-term storage. In this article, you’ll explore Amazon Redshift, its key features, and how to work with Redshift Temp Tables, learning the steps to create and manage them effectively.

What is Amazon Redshift?

Amazon Web Services (AWS) is a subsidiary of Amazon saddled with the responsibility of providing a cloud computing platform and APIs to individuals, corporations, and enterprises. AWS offers high computing power, efficient content delivery, database storage with increased flexibility, scalability, reliability, and relatively inexpensive cloud computing services.

Amazon Redshift, a part of AWS, is a Cloud-based Data Warehouse service designed by Amazon to handle large data and make it easy to discover new insights from them. Its operations enable you to query and combine exabytes of structured and semi-structured data across various Data Warehouses, Operational Databases, and Data Lakes.

Amazon Redshift is built on industry-standard SQL with functionalities to manage large datasets, support high-performance analysis, provide reports, and perform large-scaled database migrations. Redshift has a concept of temporary tables or Redshift Temp tables which exist just for the duration of the session, and they automatically discard the data when the session ends.

Key Features of Amazon Redshift

The key features of Amazon Redshift are as follows:

• Massively Parallel Processing
• Fault Tolerance
• Redshift ML
• Column-oriented Design

1) Massively Parallel Processing (MPP)

Massively Parallel Processing (MPP) is a distributed design approach in which the divide and conquer strategy is applied by several processors to large data jobs. A large processing job is broken down into smaller jobs which are then distributed among a cluster of Compute Nodes. These Nodes perform their computations parallelly rather than sequentially. As a result, there is a considerable reduction in the amount of time Redshift requires to complete a single, massive job.

2) Fault Tolerance

Data Accessibility and Reliability are of paramount importance for any user of a database or a Data Warehouse. Amazon Redshift monitors its Clusters and Nodes around the clock. When any Node or Cluster fails, Amazon Redshift automatically replicates all data to healthy Nodes or Clusters.

3) Redshift ML

Amazon Redshift houses a functionality called Redshift ML that gives data analysts and database developers the ability to create, train, and deploy Amazon SageMaker models using SQL seamlessly.

4) Column-Oriented Design

Amazon Redshift is a Column-oriented Data Warehouse. This makes it a simple and cost-effective solution for businesses to analyze all their data using their existing Business Intelligence tools. Amazon Redshift achieves optimum query performance and efficient storage by leveraging Massively Parallel Processing (MPP), Columnar Data Storage, along with efficient and targeted Data Compression Encoding schemes.

What is Redshift Temp Table?

• Temporary Table Creation: Amazon Redshift allows users to create temporary tables using the TEMP or TEMPORARY keyword. These tables are session-specific and exist only for the duration of the session.
• Automatic Data Deletion: Redshift temp tables automatically delete their data once the session ends, making them ideal for short-term data storage and intermediate steps in complex queries.
• Use in Query Operations: Temp tables are often created for use in join operations later in the session. To optimize performance, it’s critical to configure the table with appropriate column encoding, distribution key, and sort key.
• Session-Scoped Nature: Unlike regular tables, temp tables are session-scoped, meaning they are recreated if used in multiple parts of a process or report. This makes them highly adaptable for dynamic query requirements.
• Comparison with Regular Tables: While temp tables may be rewritten or regenerated during queries in a session, regular tables typically maintain persistent data and are not modified each time they are queried.
• Schema Control and Flexibility: Temp tables offer flexibility in schema design, making it easy to ensure they are structured appropriately for specific session needs without long-term persistence concerns.

Different statements used to create Redshift Temp tables and insert values into them are as follows:

• Redshift Temp Table: CREATE TABLE Statement
• Redshift Temp Table: CREATE TABLE AS Statement
• Redshift Temp Table: CREATE TABLE LIKE Statement
• Redshift Temp Table: INSERT INTO Statement
• Redshift Temp Table: DROP TABLE Statement

1) CREATE TABLE Statement

CREATE is a Data Definition Language (DDL) command in the Amazon Redshift, which is used to create new tables for your Amazon Redshift instance. Users must provide the table and columns a name, as well as the data types that will be used in it. The following syntax can be used with the Amazon Redshift Create Temp Table command:

CREATE TABLE table_name (
column_name1 data_type1,
column_name2 data_typ2
)

For Example:

CREATE TABLE Books
(
book_id      integer(30),
book_name       varchar(50),
author_name        varchar(50),
Publisher_name     varchar(60),
editions             integer
);

Amazon Redshift also allows users to easily access or load their Amazon Redshift data by modifying their instructions with parameters/keywords such as “Like,” “As,” “Default” and so on.

An advanced syntax of Temp Table Redshift Create function will look something like this:

CREATE [ [LOCAL ] { TEMPORARY | TEMP } ] TABLE 
[ IF NOT EXISTS ] table_name
( column_name data type [ column_constraint ] | [ Table Constraint ] |
[ column attribute] | [ table attribute] | LIKE
);

where column_attributes are:
  [ DEFAULT default_expr ]
  [ IDENTITY ( seed, step ) ] 
  [ GENERATED BY DEFAULT AS IDENTITY ( seed, step ) ]             
  [ ENCODE encoding ] 
  [ DISTKEY ]
  [ SORTKEY ]
  [ COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE  ]

and column_constraints are:
  [ { NOT NULL | NULL } ]
  [ { UNIQUE  |  PRIMARY KEY } ]
  [ REFERENCES reftable [ ( refcolumn ) ] ] 

and table_constraints  are:
  [ UNIQUE ( column_name [, ... ] ) ]
  [ PRIMARY KEY ( column_name [, ... ] )  ]
  [ FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ] 

and table_attributes are:
  [ DISTSTYLE { AUTO | EVEN | KEY | ALL } ] 
  [ DISTKEY ( column_name ) ]
  [ [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [,...]) |  [ SORTKEY AUTO ] ]
  [ ENCODE AUTO ]

A) Parameters of CREATE TABLE Command

The parameters of CREATE TABLE Command are as follows:

• Local Clause
• Temporary/Temp Clause
• If Not Exists Clause
• Default Clause
• IDENTITY (Seed/Step) Clause
• PRIMARY KEY Clause
• FOREIGN KEY Clause
• BACKUP Clause
• DISTSTYLE Clause
• DISTKEY Clause
• SORTKEY Clause

I) Local Clause

This is an optional keyword that is accepted in the statement but has no effect on Amazon Redshift.

CREATE [LOCAL ] TABLE table_name

II) Temporary or Temp Clause

Users can build temporary tables in Amazon Redshift by using the Temp or Temporary keyword when creating new tables for their Amazon Redshift instance. In Redshift, the temporary table is only visible for the current session. Redshift Temp Tables are dropped at the end of each session they are generated in and cannot be recovered in any way. The BigQuery Temp Table is generated in a distinct, session-specific Redshift schema, though users cannot give this schema a name. The schema is private and hidden from the user.

Users can either use the temp keyword or a # sign right before the table name to create a temporary table (Redshift Temp Table). In other words, to create a Redshift Temp Table, simply specify the TEMPORARY keyword (or TEMP abbreviation) or # sign in your CREATE TABLE DDL statement.

Syntax 1: 

CREATE TABLE #table_name (
column_name1 data_type1,
column_name2 data_typ2
);

Syntax 2:

CREATE TEMPORARY TABLE table_name (
column_name1 data_type1,
column_name2 data_typ2
);

Syntax 3:

CREATE TEMP TABLE table_name (
column_name1 data_type1,
column_name2 data_typ2
);

The name of the Temporary table might be the same as the name of the Permanent table. Due to the way search paths are set up, it is possible for a temporary table to override a permanent one unless you explicitly specify it by using a schema name in the table name.

• Staging Redshift Temp Table

The staging table is a Redshift Temp Table (temporary table) that stores all of the data that will be needed to perform updates and inserts to the target database.

The staging table and the target table must be joined for a merge operation to work. Set the staging table’s distribution key to the same column as the destination table’s distribution key to collocate the joining rows.

• Global Redshift Temp Table

Users can also construct a globally accessible Redshift Temp Table (temporary table) by placing double hash (##table_name) symbols in front of the table name. When the person or process that generated them is no longer active, global Redshift Temp Tables expire as well. But, comparatively, a global Redshift Temp Table can be accessed by any database user while it is active.

III) If Not Exists Clause

Redshift lets customers employ the “If not exists” clause/parameter in their Amazon Redshift Create Table commands to prevent creating duplicate tables. If the “If not exists” clause/parameter is set, Amazon Redshift will check to see if the table already exists or not. In case it does not, Redshift will give an error message. 

Syntax:

CREATE TABLE 
[ IF NOT EXISTS ] table_name

IV) Default Clause

The default keyword is used to avoid problems with NULL values and has the following syntax:

column_name data_type DEFAULT Default_Value

For example:

author_name varchar(50), default  'Neil Gaiman',

If the user does not give any input for that column with default values in place, the default value will be applied automatically.

• table_name

This is the name of the table that will be created. It can be up to 127 bytes.

• column_name

It is for the name of a column that will be added to the new table. The column name can be up to 127 bytes long.

• data_type

The column’s data type is being created.

V) IDENTITY(seed, step) Clause

The clause identifies the column as an IDENTITY column.

VI) PRIMARY KEY Clause

A Primary Key indicates that this group of columns may be used as a unique identifier for rows in other tables. This constraint will imply that a column or set of columns in a table can only contain non-null values that are unique (non-duplicate). Choosing a collection of columns as the Primary Key also offers information about the schema’s architecture. 

VII) FOREIGN KEY Clause

A Foreign Key constraint indicates that a group of one or more columns in the new table can only have values that match those in the referred column or columns of some row of the referenced table.

Read more about Redshift Primary and Foreign Keys.

VIII) BACKUP { YES | NO } Clause

Whether or not the table should be included in automatic and manual clusters, snapshots are specified by this clause.

IX) DISTSTYLE Clause

The Data Distribution Style for the entire table is defined by this keyword. The rows of a table are distributed among compute nodes by Amazon Redshift according to the table’s distribution style i.e., AUTO, EVEN, and ALL.

X) DISTKEY Clause

It is the constraint that defines the column that will be used as the table’s distribution key.

XI) SORTKEY Clause

This parameter defines one or more sort keys for the table. When data is imported into the table, the columns selected as sort keys are used to sort the data.

2) CREATE TABLE AS Statement

Create Table As (CTAS) instructions are used in Redshift to create tables from select query results. The SQL query’s table structure and data are passed on to the CTAS statement. However, it doesn’t support inheriting constraints, default values, etc. 

Syntax:

CREATE TABLE Old_Books AS Select * from Books;

For the Redshift Temp table (Temporary table):

CREATE TEMP TABLE Old_Books AS Select * from Books;

3) CREATE TABLE LIKE Statement

This statement allows users to define a distinct table whose structure they want to duplicate without copying any data. The difference between the Create Table Like and Create Table As commands is as follows: The Create table LIKE clause creates a table based on a parent table’s structure, whereas the Create table AS (CTAS) clause creates a table based on a SQL query.

Add the “INCLUDING DEFAULTS” clause if you want the column level default values from the parent table transferred across. 

CREATE TABLE Books_backup (LIKE Books INCLUDING DEFAULTS);

If you wish to create a table without column defaults, such as NULL defaults, you may use the “EXCLUDING DEFAULTS” clause or just skip the statement as seen below.

CREATE TABLE Books_backup (LIKE Books);

Or

CREATE TABLE Books_backup (LIKE Books EXCLUDING DEFAULTS);

For creating a temporary table (Redshift Temp Table):

CREATE TEMP TABLE Books_backup (LIKE Books INCLUDING/EXCLUDING DEFAULTS);

4) INSERT INTO Statement

Using the INSERT INTO command and specifying values for all columns is the easiest approach to insert a record in Redshift.

Assuming the users table has only three columns: book_id, Book_name, and author_name, and in that order.

INSERT INTO users Books('0573', 'Anxious People', 'Fredrik Backman');

Users can also specify the column list and use the VALUES keyword in the above syntax.

INSERT INTO users (book_id, Book_name, author_name)
VALUES ('0573', 'Anxious People', 'Fredrik Backman');

The latter is preferable since users don’t have to remember the table’s column order or provide values for all columns, only the ones that are necessary. It is simpler to match a value to the column it is meant for when seeing it in the statement rather than having to look at the table description if there are numerous columns.

Once a column is added or deleted from the table, INSERT statements without a column list are invalidated. As a result, users will need to update their queries to reflect the new or removed column in order to resume their work.The same format is also applicable to a Temporary Table too.

After a table is populated, you can query it using:

SELECT * from table_name;

Users can create Temporary tables directly using Permanent tables and include input values into them. This also implies that every subquery defines and creates a Temporary table.

For Example:

SELECT book_id, book_name, author_name          
INTO #tmp_books
FROM books
WHERE editions > '2'

This will create a Temporary table and insert the book_id, book_name, and author_name of all employees in the physical books table with the books having more than two editions.

• Note: Keep in mind that every time a subquery is performed, it creates a new temporary table which can cause frequent rewriting. If you alter your query to combine the logic into a single, bigger query, the disc writes required in filling the temporary table may be more costly than the reads. Do consider the IO cost of executing writes and then reading the data back into a subsequent query before utilizing temporary tables to aid in process debugging or to improve readability.

5) DROP TABLE Statement

This has the easiest syntax, however, once this command is given, the action cannot be reversed.

DROP TABLE table_name;

There are two possibilities for getting rid of a Redshift Temp Table. The first step is to disconnect from the database where the Redshift Temp Table was created. The other option is to use the command below.

DROP TABLE #table_name; (for Redshift temp table) 

Conclusion

In this article, you have learned about how to use the Amazon Redshift Create Table command to create new permanent and temporary tables in Amazon Redshift successfully. This article also provided information on Amazon Redshift, its key features, and Redshift Temporary Tables.

Hevo Data with its strong integration with 150+ sources (including 60+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice such as Amazon Redshift, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools. 

Explore Hevo’s 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing pricing, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the Amazon Redshift Data Lake Export feature in the comment section below! We would love to hear your thoughts.

FAQs