Redshift Alter Table Command 101: Syntax and Usage Simplified

With the increasing data collection, Traditional Data Warehouses have failed to keep up with the desired processing pace of organizations. However, Amazon introduced Redshift, a Cloud-Based Data Warehouse, that enables Data Analysts to query their data faster with parallel processing and data compression. It also supports various Business Intelligence (BI) tools to provide real-time updates on the dashboard. To streamline the entire process of data storage and retrieval, Redshift uses the power of Structured Query Language (SQL), making it simple for companies to leverage Redshift.  

This article gives an overview of the Redshift Alter Table command. As Redshift uses SQL, the article also introduces readers to the fundamentals of SQL commands. Moreover, the syntax, parameters, and examples of the Redshift Alter Table command for various use cases are also discussed.

Table of Contents

• Introduction to Redshift
• Introduction to SQL Commands
• Syntax and Parameters for Redshift Alter Table Command
  • Redshift Alter Table Syntax
  • Redshift Alter Table Parameters
• Examples of Redshift Alter Table Command
  • Alter Internal Table
  • Alter External Table 
  • ADD and DROP Columns
• Conclusion

Introduction to Redshift

Today, one of the core elements for effective Business Intelligence in organizations is Data Warehousing. However, as the data grows, organizations are required to expand their On-Premise Data Warehouses to enhance usability. This not only increases the cost but also reduces productivity. However, Amazon rolled out Redshift in 2012 as a direct alternative to encounter several challenges of traditional Data Warehouses.

Redshift is a fully-managed, petabyte-scale, Cloud-Based Data Warehouse Service powered by Amazon. It delivers lightning-fast performance in data processing without any massive investment in infrastructure. With several SQL-based clients, Redshift also facilitates connections with various data sources and business intelligence tools.

Introduction to SQL Commands

As text or CSV files cannot handle big data due to the absence of processing speed, companies require a database that allows the secure storage of huge amounts of data. As a result, databases were introduced to store and retrieve information with the help of SQL. And due to the simplicity of SQL, it is now termed as the language of databases. Consequently, it has become the fundamental building block of modern database architecture. 

There are five basic types of SQL commands, namely:

• Data Definition Language (DDL): DDL commands are used to create, modify, and remove database objects. These commands are primarily governed by database administrators that revolve around four primary commands i.e. create, drop, alter, and truncate.
• Data Control Language (DCL): DCL commands consist of ‘grant’ and ‘revoke’ commands. It mainly deals with rights, permissions, and the authority of data within the database.
• Data Manipulation Language (DML): DML commands are used to retrieve, insert and modify database information. These commands include insert, update and delete. DML commands are not auto-committed, which means changes done in the database using DML commands can be rolled back.
• Data Query Language (DQL): DQL commands are used for querying data within database schemas. It consists of a ‘select’ command to pick desired attributes.
• Transaction Control Language (TCL): TCL commands are used to perform a set of tasks (commands), resulting in the success or failure of an event. These commands include commit, rollback, and savepoint and operate using DML commands.

Syntax and Parameters for Redshift Alter Table Command

Redshift Alter Table is a DDL command used to add, delete or modify columns in a table. It mainly deals with the table’s structure, as the choice of data keeps changing according to business requirements.

A) Redshift Alter Table Syntax

ADD	ALTER TABLE table_name ADD column column_name;
DROP	ALTER TABLE table_name DROP column column_name;
MODIFY	ALTER TABLE table_name MODIFY column column_name data_type;

Redshift Alter Table command updates the values and properties set by CREATE TABLE or CREATE EXTERNAL TABLE. However, a developer cannot run a Redshift Alter Table command on an external table within a transaction block. 

Below is the syntax for all the possible use cases of the Redshift Alter Table command:

ALTER TABLE table_name 
{
ADD table_constraint 
| DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] 
| OWNER TO new_owner 
| RENAME TO new_name 
| RENAME COLUMN column_name TO new_name            
| ALTER COLUMN column_name TYPE new_data_type
| ALTER COLUMN column_name ENCODE new_encode_type     
| ALTER COLUMN column_name ENCODE encode_type, 
| ALTER COLUMN column_name ENCODE encode_type, .....;      
| ALTER DISTKEY column_name 
| ALTER DISTSTYLE ALL       
| ALTER DISTSTYLE EVEN
| ALTER DISTSTYLE KEY DISTKEY column_name 
| ALTER DISTSTYLE AUTO             
| ALTER [COMPOUND] SORTKEY ( column_name [,...] ) 
| ALTER SORTKEY AUTO 
| ALTER SORTKEY NONE
| ALTER ENCODE AUTO
| ADD [ COLUMN ] column_name column_type
  [ DEFAULT default_expr ]
  [ ENCODE encoding ]
  [ NOT NULL | NULL ] |
| DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ] }

where table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] )  
| PRIMARY KEY ( column_name [, ... ] ) 
| FOREIGN KEY (column_name [, ... ] )
   REFERENCES  reftable [ ( refcolumn ) ]}

If you want to use the Redshift Alter Table command for external tables (tables in other databases), follow the below syntax:

SET LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' } 
| SET FILE FORMAT format |
| SET TABLE PROPERTIES ('property_name'='property_value') 
| PARTITION ( partition_column=partition_value [, ...] ) 
  SET LOCATION { 's3://bucket/folder' |'s3://bucket/manifest_file' } 
| ADD [IF NOT EXISTS] 
    PARTITION ( partition_column=partition_value [, ...] ) LOCATION { 's3://bucket/folder' |'s3://bucket/manifest_file' }
    [, ... ]
| DROP PARTITION ( partition_column=partition_value [, ...] )  

Amazon also supports a combination of Redshift Alter Table commands as follows:

ALTER TABLE tablename ALTER SORTKEY (column_list), ALTER DISTKEY column_Id;
ALTER TABLE tablename ALTER DISTKEY column_Id, ALTER SORTKEY (column_list);
ALTER TABLE tablename ALTER SORTKEY (column_list), ALTER DISTSTYLE ALL;
ALTER TABLE tablename ALTER DISTSTYLE ALL, ALTER SORTKEY (column_list);

B) Redshift Alter Table Parameters

Following are the list of Redshift Alter Table Parameters:

• table_name: Every table is given a name, and table_name refers to the name of the table to alter. One can either specify the table’s name or use the format schema_name.table_name for a specific schema. As longer names are truncated to 127 bytes, a table name cannot be more than 127 bytes.
• ADD table_constraint: It is a clause that adds the specified constraint to the table.
• DROP CONSTRAINT constraint_name: A clause that drops specified constraints from the table. While using this drop constraint, specify the constraint name, not the constraint type.
• RESTRICT: It is a clause that removes only the specified constraint. RESTRICT is an alternative for DROP CONSTRAINT, and it cannot be used with CASCADE.
• CASCADE: If a user wants to remove a specified constraint and other dependent constraints, the CASCADE clause is used. It is used as an option for DROP CONSTRAINT.
• OWNER TO new_owner: With this clause, you can change the owner of a table.
• RENAME TO new_name: This clause is used to rename a table to a specified value, where the table name’s maximum length is 127 bytes. Not only can you rename an external table, but also you cannot rename a permanent table with ‘#,” as it indicates a temporary table.
• ALTER COLUMN column_name TYPE new_data_type: A clause that can change the size of a column (e.g., VARCHAR data type). Below are a few limitations:
  • You cannot alter a column with compression encodings BYTEDICT, RUNLENGTH, TEXT255, or TEXT32K.
  • The maximum size of existing data cannot be less than the existing data.
  • Columns having default values cannot undergo alter operation.
  • Columns defined as UNIQUE, PRIMARY KEY, or FOREIGN KEY cannot have the alter operation.
  • You cannot alter columns within a transaction block (BEGIN … END).
• There are many such parameters and clauses utilized in combination with internal (table inside Redshift) alter table commands. Below are a few parameters useful when dealing with external tables:
  • SET LOCATION { ‘s3://bucket/folder/’ | ‘s3://bucket/manifest_file’ }: It shows the path to the Amazon S3 folder, containing the manifest file having an Amazon S3 object path. These buckets should be in the same AWS Region as the Amazon Redshift cluster.
  • SET FILE FORMAT format: This clause is used to set file formats for external data files. Valid formats include AVRO, PARQUET, RCFILE, SEQUENCEFILE, TEXTFILE, etc.
  • SET TABLE PROPERTIES ( ‘property_name’=’property_value’): A clause that defines properties of an external table, for example:
    • ‘numRows’=’row_count’: A property used to set the numRows value in a table. Users can indicate the size of the table because it explicitly updates the statistics of the external table.
    • ‘skip.header.line.count’=’line_count’: A property that sets the number of rows to skip at the beginning of each source file.

Examples of Redshift Alter Table Command

A) Alter Internal Table

Below are various cases where the Redshift Alter Table command can be combined to perform desired tasks:

• Rename Table and Column: For simplicity and relevance, a table can be renamed. For instance, if we want to rename the ‘USERS’ table to ‘USERS_BKUP,’ use the below query:

alter table users
rename to users_bkup;

Similarly, we can also rename the column ‘VENUESEATS’ in the VENUE table to VENUESIZE using the following Redshift Alter Table query:

alter table venue
rename column venueseats to venuesize;

• Change Owner of Table and View: Organizations often change the owner of a table, considering the data security. Suppose we want to make DWUSER the owner of the VENUE table, use the following query:

alter table venue
owner to dwuser;

Many times, database administrators create a view (set of desired columns) from a table and then change its owner as shown below:

create view vdate as select * from date;
alter table vdate owner to vuser;

• Drop Table Constraint: While dropping table constraints, such as — primary key, foreign key, or unique constraint, it becomes necessary to find the internal name of the constraint. Redshift Alter Table command can then be used to drop specified constraint as shown below:

select constraint_name, constraint_type 
from information_schema.table_constraints 
where constraint_schema ='public'
and table_name = 'category';

constraint_name | constraint_type
----------------+----------------
category_pkey   | PRIMARY KEY    

alter table category
drop constraint category_pkey;

• Varchar: Varchar is an SQL data type that acquires a minimum size for your data to conserve storage. However, to accommodate long strings, one can alter tables and increase the column size. 

alter table event alter column eventname type varchar(300);

B) Alter External Table

You can even alter an external table. The following command will set the number of rows to 170,000 for spectrum.sales external table:

alter table spectrum.sales 
set table properties ('numRows'='170000');

One can even change the location of an external table using the below query:

alter table spectrum.sales 
set location 's3://awssampledbuswest2/tickit/spectrum/sales/';

To change the file storage type format to parquet, use the below query:

alter table spectrum.sales 
set file format parquet;

C) ADD and DROP Columns

We can perform Redshift ALTER TABLE to add and then drop a basic table column with a dependent object.

alter table users
add column feedback_score int
default NULL;

If we want to drop feedback_score from the user’s table, use the below query:

alter table users drop column feedback_score;

For more examples and tips on the Redshift Alter Table Command, you can check out the Amazon Documentation.

Conclusion

In this article, you learned how to use the Redshift Alter Table Command. With the easy-to-understand Syntax, you can start working with Redshift Alter Table Command for adding, deleting, or modifying columns in a table. Using this command you can alter the structure of both internal and external tables for your varying business needs. 

With traditional data warehouse technology, organizations battle in querying large datasets and experience delays in achieving desirable results. To query data, Amazon adopted Redshift, a simple SQL at the backend while connecting with BI tools for displaying critical parameters on the dashboard. Redshift also connects with other AWS Products and Services, enabling analysts to provide secure, cost-effective, and scalable solutions for businesses.

Now, as your business grows, tremendous volumes of data associated with your Products, Services, and Customers is generated. Constantly updating and maintaining the data transfer from various applications across your enterprise into Redshift Data Warehouse is a tedious task. You would be required to invest a section of your engineering bandwidth to Integrate, Clean, Transform and Load your data to Redshift for further business analysis. All of this can be efficiently automated by a Cloud-Based ETL tool like Hevo Data.

Visit our Website to Explore Hevo

Hevo Data, a No-Code Pipeline seamlessly transfers your data from a collection of sources into a Data Warehouse like Amazon Redshift or a destination of your choice to be visualized in a BI Tool. It is a secure, reliable, and fully automated service that doesn’t require you to write any code! 

If you are using Amazon Redshift as your Data Warehousing and Analytics platform and searching for a stress-free alternative to Manual Data Integration, then Hevo can effectively automate this for you. 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 jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan meets all your business requirements

Share with us your experience of using the Amazon Redshift Alter Table Command. Let us know in the comments section below!