Amazon Redshift Copy Table: 3 Easy Methods

Vishal Agrawal • Last Modified: September 20th, 2023

Amazon Redshift Copy Table

Companies widely use Data Warehouses to run their business operations efficiently and use data-driven business strategies to increase their revenue. Amazon Redshift is one of the majorly used Cloud Data Warehouse that offers a plethora of features to help businesses find new opportunities. 

A Data Warehouse writes many records every day and many time a large set of data remain unsorted in the table. Companies also need to restructure the table data. All this can be achieved via the Redshift Copy Table where a copy of the table will be created and then the data get sorted.

Deep Copy makes it easier for users to get Amazon Redshift Copy Table and it automatically sorts the table in the bulk insertion of data. In this article, you learn about Amazon Redshift Copy Table and different methods to perform Amazon Redshift Copy Table via deep copy.

Table of Contents

Introduction to Amazon Redshift

Amazon Redshift Logo
Image Source

Amazon Redshift is a Cloud-based serverless Data Warehouse provided by Amazon as a part of Amazon Web Services. It is a fully managed and cost-effective Data Warehouse solution. Amazon Redshift is designed to store petabytes of data and perform real-time analysis to generate insights.

Amazon Redshift is a column-oriented database that stores the data in a columnar format compared to traditional Databases stored in a row format. Amazon Redshift has its own compute engine to perform computing and generate critical insights. 

To know more about Amazon Redshift, follow the official documentation here.

Amazon Redshift Architecture

Amazon Redshift has straightforward Architecture. It contains a leader node and cluster of compute nodes that perform Analytics on data. The below snap depicts the schematics of Amazon Redshift architecture: 

Architecture - Amazon Redshift Copy Table
Image Source

Amazon Redshift offers JDBC connectors to interact with client applications using major programming languages like Python, Scala, Java, Ruby, etc.

Key Features of Amazon Redshift

  • Amazon Redshift allows users to write queries and export the data back to Data Lake.
  • Amazon Redshift can seamlessly query the files like CSV, Avro, Parquet, JSON, ORC directly with the help of ANSI SQL.
  • Amazon Redshift has exceptional support for Machine Learning, and developers can create, train and deploy Amazon Sagemaker models using SQL.
  • Amazon Redshift has an Advanced Query Accelerator (AQUA) which performs the query 10x faster than other Cloud Data Warehouses.
  • Amazon Redshift’s Materialistic view allows you to achieve faster query performance for ETL, batch job processing, and dashboarding.
  • Amazon Redshift has a petabyte scalable architecture, and it scales quickly as per need.
  • Amazon Redshift enables secure sharing of the data across clusters.
  • Amazon Redshift provides consistently fast performance, even with thousands of concurrent queries.
Simplify Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Get Started with Hevo for Free

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensures that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Check out why Hevo is the Best:

  1. Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  2. Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  3. Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  4. 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.
  5. 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.
  6. Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, E-Mail, and support calls.
  7. 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!

Amazon Redshift Deep Copy

Deep copy is a process followed by Redshift users that recreate the table using a systemic approach of using bulk inserts in the table. This process repopulates the table and automatically sorts the table.

While performing Deep Copy for Amazon Redshift Copy Table, the user should keep in mind that they should not make concurrent updates during the operation; otherwise, it will be difficult to track the deltas.

Steps to Perform Amazon Redshift Copy Table

There are three ways to perform Deep Copy for Redshift Copy Table. Let’s have a detailed look at all three approaches listed below:

1) Using the Original Table & DDL Command

This method is useful when the Original DDL of the table is available. This method is one of the fastest and preferred methods to perform Deep copy for the Amazon Redshift Copy Table.

With the help of Create Table DDL available, you can create a new table along with all the columns and attributes, including primary and foreign keys.

Steps to Create Deep Copy for Amazon Redshift Copy Table

  1. Using the CREATE Table DDL, create a copy of the parent table.
  2. Use the INSERT command to insert the data from the parent table to the newly created table. 
  3. Drop the parent table.
  4. Use the ALTER TABLE command to rename the new table with the original name.

Let’s understand the above steps with a real-time example. Consider a table of sales that contains the sales records of the products as shown in the code below:

CREATE TABLE SALES_COPY (
sales_id int,
order_id int,
transaction_id int,
sales_date datetime,
sales_transaction double);

INSERT INTO SALES_COPY (SELECT * FROM SALES) 
  • Once the data is completely copied to the SALES_COPY table, drop the original table as shown in the code below:
DROP TABLE SALES;
  • Rename the new table with the old table as shown in the code below:
ALTER TABLE SALES_COPY RENAME TO SALES;

2) Using CREATE TABLE LIKE Command

This method is useful when the create table DDL is not available. This method uses the CREATE TABLE LIKE command that inherits the encoding, distribution key, sort key, etc., from the old table to the new table. It doesn’t inherit the old table’s primary and foreign keys.

Steps to create Deep Copy for Amazon Redshift Copy Table

  1. Using the CREATE TABLE LIKE command, create a copy of the parent table.
  2. Use the INSERT command to insert the data from the parent table to the newly created table. 
  3. Drop the parent table.

Let us understand the above steps with a real-time example. Consider a table sales that contains the sales records of the products. 

CREATE TABLE SALES_COPY (
sales_id int,
order_id int,
transaction_id int,
sales_date datetime,
sales_transaction double);

INSERT INTO SALES_COPY (SELECT * FROM SALES) 
  • Once the data is completely copied to the SALES_COPY table, drop the original table as shown in the code below:
DROP TABLE SALES;
  • Rename the new table with the old table as shown in the code below:
ALTER TABLE SALES_COPY RENAME TO SALES;

3) Amazon Redshift Copy Table: Using a Temporary Table & Truncating Original Table

This method is useful when dependencies are in the parent table and cannot be deleted. Using the temporary table approach improves the performance, but also there is a risk of losing data.

The temporary table is session-based. Hence the temporary table will automatically drop when the current session ends. Therefore this method produces a risk of losing data if anything goes wrong. 

Steps to perform Deep Copy for Amazon Redshift Copy Table

  • Use CREATE TABLE AS SELECT command (CTAS) to create a temporary table similar to the parent table. 
  • Truncate the parent table.
  • Use the INSERT command to insert the data from the temporary table to the parent table.

Let us understand the above steps with a real-time example. Consider a table of sales that contains the sales records of the products. The code is shown below:

CREATE TEMP TABLE SALES_TEMP AS (SELECT * FROM SALES) 
TRUNCATE SALES;
INSERT INTO SALES (SELECT * FROM SALES_TEMP);
DROP TABLE SALES_TEMP;

Conclusion

In this article, You learnt about Amazon Redshift Copy Table via the Deep Copy method that efficiently performs the deep copy of the table and automatically sorts the data. It becomes essential to maintain the data in the sorted form so that users can make quick queries and operations on the data.

Visit our Website to Explore Hevo

Amazon Redshift stores data from multiple sources and every source follows a different schema. Instead of manually writing scripts for every source to perform the ETL (Extract Transform Load) process, one can automate the whole process. Hevo Data is a No-code Data pipeline solution that can help you transfer data from 100+ sources to Amazon Redshift or other Data Warehouse of your choice. Its fault-tolerant and user-friendly architecture fully automates the process of loading and transforming data to destination without writing a single line of code. 

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

Share your experience of learning about the Amazon Redshift Copy Table in the comments section below!

No-code Data Pipeline For your Amazon Redshift