Amazon Redshift SQL

Commands & Optimization

Table of Contents

Architecture of Redshift 1

Understanding SQL In Redshift 2

● DDL
● DML
● Functions
● Names or Identifiers
● Expressions
● Data Types
● Commands

Query Optimization Tips 32

 1

Redshift is fully managed petabyte data warehouse over the cloud. A Redshift engine is

called as cluster and is built up from one or more nodes. There are 2 types of nodes called

Compute Node and Leader node. Leader node performs multiple roles which include

communicating with JDBC/ODBC client and creating a query execution plan to pass on to

compute node(s). The cluster is incomplete without a Leader node. Compute node

executes the actual query as per the query plan provided by Leader node. The architecture

of Redshift is shown below.

Amazon Redshift SQL - Commands & Optimization

 2

AWS Redshift is a Massively Parallel Platform (MPP) built on top of technology provided by

ParAccel (One of DMS). You can check out our blog to know more about ​Redshift

Architecture​. Redshift follows Columnar DBMS Principle where column data is stored in

chunks of 1 MB blocks. Redshift is based on PostgreSQL 8.0.2. Remember Redshift is

designed for OLAP and BI applications and beautifully supports complex SQL queries

against large data dataset.

Redshift has carried most of the SQL commands available in PostgreSQL 8.0.2 along with

some additional commands introduced e.g. COPY, UNLOAD statements etc. The list of SQL

commands in Redshift is exhaustive. Here, let us cover various important SQL commands

supported in Redshift.

Amazon Redshift SQL - Commands & Optimization

 3

DDL

DDL commands are also called Data Definition Language commands. These commands are

used to create or change the structure of an object i.e. tables, views etc. Remember these

commands are auto-commit commands. You need to hit those commands carefully in

Redshift. The list of major commands are as follows:

CREATE TABLE → This command will create a table in Redshift. This command is mostly

used by a developer or through a Job.

CREATE TABLE AS → This command will create a table from the output of another query in

Redshift. This command is mostly used by a developer or through a Job.

Amazon Redshift SQL - Commands & Optimization

 4
CREATE VIEW​ → This command will create a view in Redshift. This command is mostly

used by a developer.

CREATE GROUP​ ​→​ This command will create a group and add user/users in Redshift. This

command can only be run by a superuser.

→​ Above example will create a group called developers_grp with two users user1 and user2

Above command is quite handy when you want to create respective groups for your

respective team members in your project. For example, a separate group for developers,

testers, Admins, designers etc, When someone will grant/revoke access to a group, it will

automatically be reflected for the respective users in the group.

Amazon Redshift SQL - Commands & Optimization

 5
CREATE USER​ → This command will add user/users in Redshift. This command can be run

only by a superuser.

This command will create a user called “new_user” with password “yourpwd123”.

DROP TABLE/VIEW → ​This command will drop a table or a view in Redshift. You should

have the necessary access to drop table or view for that particular table.

DROP TABLE

DROP VIEW

TRUNCATE TABLE → This command will truncate all the rows from a table. This command

is faster than DELETE operation as this command does not perform the table scan, it just

wipes out entire data from the table and performs the commit.

Amazon Redshift SQL - Commands & Optimization

 6

DML

DML commands are also called Data Modification Language commands. These commands

are used when you want to do some modifications to a particular object in Redshift (i.e.

Insert, Delete, Update etc). The list of major commands are as follows:

INSERT​ → This command will insert a new row into a table in Redshift.

Let us say we have a table called employee with 3 columns, emp_id, dept_id, salary.

If we want to insert rows, we can write the below statement.

Here, 1 will be emp_id, 2 will be dept_id, 300 will be salary.

If you want to insert all the rows from table x into table y, you can write the statement

mentioned below.

→ The number of columns or column datatypes of both the tables should match else this

command could fail.

Amazon Redshift SQL - Commands & Optimization

 7
You can give your command something as below to avoid any failure.

UPDATE → This command will update a column (or columns) in a table in Redshift. You can

apply also apply a join with other table and pick values from another table where you apply

to update a specific column/s of a table.

Direct update without where clause​:

This statement will update table1 and will set column col1 to 123 and col2 to 456. Since

there is no “where” clause in the statement, it will update all the rows of the table1.

Direct update with where clause:

This statement will update table1 and will set col1 to 123 and col2 to 456 for all rows where

col3 is 105. (Here we have where clause on col3, only those rows where col3 is 105 will take

the update into effect)

Amazon Redshift SQL - Commands & Optimization

 8
Update table X using join with table Y:

This update statement will update column col2 with value 456 of table X where there is a

matching row of col2 with table Y and table Y having col3 column having value 123.

DELETE → This command will delete a row/s in a table in Redshift. You can also perform

where clause in a table where you are deleting rows or you can also delete row/s based on

the join condition.

Delete table X without where clause

This command will delete all rows from x

Delete table X with where clause

This command will delete all rows from x where column col1 has value 123.

Amazon Redshift SQL - Commands & Optimization

 9
Delete table X using join with table Y:

This statement will delete all matching rows for column col1 against table Y from table X.

Note: ​When you perform Insert, Update or Delete on a significant number of rows, you

need to perform VACUUM and ANALYZE to remove ghost rows, reclaim space and Update

statistics of the table.

We will be covering the entire list of commands in the later section of this ebook.

Amazon Redshift SQL - Commands & Optimization

 10

FUNCTIONS

Though stored procedures are not supported in Redshift but built in Functions and User

Defined Functions (UDF) are well supported in Redshift.

Functions are a step which accepts a parameter, perform an action based on the

parameter and returns a output as a value.

In-Built Functions (Executed by Leader Node alone)

In Redshift, interestingly there are a few functions which directly perform actions on Leader

node alone and do not pass through to Compute nodes. You need to be very careful when

calling this function. In case this function references any user-created table or Redshift

table, this function will show an error.

Amazon Redshift SQL - Commands & Optimization

 11
In-Built Functions (Executed by Compute Node)

Redshift has few In-built in functions. Remember the function should reference

user-defined table or system catalog table else Redshift will show an error.

Some major built-in functions are mentioned below.

AVG → This function returns average of column argument passed on to the function. This

function works for numeric columns.

Amazon Redshift SQL - Commands & Optimization

 12
COUNT → This function returns the count of a row from a table/view. You can apply a

group by clause to get count based on a specific column. This function is highly useful for

reporting team in the production environment.

Returns the number of rows from sales tables

Returns ​the count per department

MIN​ → This function returns the minimum of a column among the entire set of rows.

MAX​ → This function returns the maximum of a column among the entire set of rows.

Amazon Redshift SQL - Commands & Optimization

 13
SUM​ → This function returns the sum of column values among all rows

RANK → Rank is extremely convenient when you want to assign a rank to row based on

certain criteria. The result will be sorted accordingly.

Amazon Redshift SQL - Commands & Optimization

 14
SUBSTRING → This function will return a substring of a specific varchar column. It can be

used when you want to extract only a subset of a column and not interested in the entire

varchar column.

This ebook only shows a few of the numerous built-in functions related to numeric

columns, for datetime columns, substring, etc. Refer to ​AWS documentation for more

details.

Amazon Redshift SQL - Commands & Optimization

 15

Names or Identifiers

In Redshift Names or Identifiers are standard used to identify Redshift object (Table,

column, user, passwords etc). There are two types of Identifiers in Redshift.

Standard Identifiers

1. Standard Identifiers must start with a single byte alphabetic character or underscore

or any UTF-8 character.

2. The subsequent characters can contain a dollar sign.

3. It does not contain space or quotes.

4. It does not contain reserved SQL keyword.

Quoted or delimited identifiers

1. Delimited identifiers start with double quotes.

2. It can contain space.

3. Delimited identifiers are useful as you can enclose them within quotes to get rid of

reserved SQL keywords.

Amazon Redshift SQL - Commands & Optimization

 16

Expressions

In Redshift, an expression is one or more combination of operations that evaluates to a

value while performing a query.

Amazon Redshift SQL - Commands & Optimization

 17

Types of Expressions

Simple Expressions

Simple Expressions are used to perform simple calculations in Redshift.

Compound Expressions

Compound expressions are used when performing simple expressions queries along with

some arithmetic operations on top of it.

Amazon Redshift SQL - Commands & Optimization

 18
Expression List

Expression List in Redshift is used when you are utilizing a list of values in your query.

It is helpful when you want to get output from the table based on the combination of the

list in where clause.

Amazon Redshift SQL - Commands & Optimization

 19

Data​ ​Types

Redshift has carried a few of the data types from PostgreSQL 8.0.2. Still, some of them are

discarded from PostgreSQL and new data types also added. The list of data types

supported in Redshift are as follows:

Data Type Description

SMALLINT Signed two-byte integer

INTEGER Signed four-byte integer

BIGINT Signed eight-byte integer

DECIMAL Exact numeric of selectable precision

REAL Single precision floating-point number

DOUBLE PRECISION Double precision floating-point number

BOOLEAN Logical Boolean (true/false)

CHAR Fixed-length character string

VARCHAR Variable-length character string with a user-defined limit

DATE Calendar date

TIMESTAMP Date and time (without time zone)

TIMESTAMPTZ Date and time (with time zone)

Amazon Redshift SQL - Commands & Optimization

 20

Commands

Redshift commands are SQL queries which you perform to run the queries. Some of the

commands we have already covered in this ebook earlier. Let us cover the entire

commands once again in detail.

● ABORT/ROLLBACK → ABORT or ROLLBACK command rolls back the entire DML

executed post BEGIN statement in the query state.

Amazon Redshift SQL - Commands & Optimization

 21
● ALTER DATABASE → This command is used to either rename or change a database

name.

● ALTER DEFAULT PRIVILEGES → This command is used to grant/revoke the default

privileges set by superuser from a table owner to other users.

● ALTER GROUP → This command is used to add or remove a user from a group.

● ALTER SCHEMA → This command is used to rename a schema.

● ALTER TABLE → This command is used to alter the table properties/definition.

Amazon Redshift SQL - Commands & Optimization

 22
● ALTER TABLE APPEND → This command appends rows from the source table and

then empties the source table. This table is quite handy as it just moves the entire

block.

Amazon Redshift SQL - Commands & Optimization

 23

● ALTER USER → Only a superuser can execute this command. It is typically used to

change a user property i.e. password etc.

● ANALYZE → This command updates the stats of a table for query planner. This

command is usually used after VACUUM (​to be covered later​).

Amazon Redshift SQL - Commands & Optimization

 24
● ANALYZE COMPRESSION → This command is used to analyze encoding on table

columns and produce a report to suggest encoding types.

● BEGIN → Starts a transaction. Recommended when you query the sequences of

commands so that automatic rollback can happen in case the job fails.

● CANCEL → Cancels a query (​process id is needed​).

Amazon Redshift SQL - Commands & Optimization

 25

● COMMIT/END → Commits a transaction. Once the commits happen, database

updates become permanent.

● COPY → This command loads data from S3, EMR or DynamoDB into Redshift

Kindly refer to our detailed white-paper on ​Redshift commands and query

optimization techniques​ for more details.

● CREATE DATABASE → This command is used to create the database and gives

ownership to a user.

Amazon Redshift SQL - Commands & Optimization

 26
● CREATE EXTERNAL SCHEMA → This command is used to create a schema using a

database pointing to Glue/Athena/EMR catalog.

● CREATE EXTERNAL TABLE → This command creates a table in Redshift external

schema.

Refer to ​Redshift Spectrum’s report​ for more details.

● CREATE FUNCTION → This command is a user-defined function (UDF) in Redshift. To

create a UDF you need to use SQL SELECT or Python program. Refer ​AWS

documentation​ for usage.

Amazon Redshift SQL - Commands & Optimization

 27
● CREATE GROUP → Only a superuser can give this command. This command is used

to create a group.

● CREATE SCHEMA → This command creates a new schema for the current database.

● CREATE TABLE → This command is used to create table.

● CREATE TABLE AS → This command is used to create a table with same DDL as of

other table/sub-table.

● CREATE USER → This command can only be executed by the superuser. This

command creates a new user.

● CREATE VIEW → This command is used to create a view.

Amazon Redshift SQL - Commands & Optimization

 28
● DELETE → This command deletes a row from a table. This is a DML statement.

● DROP DATABASE → This command drops a database.

● DROP FUNCTION → This command drops a UDF.

● DROP SCHEMA → This command drops a schema from the database.

● DROP TABLE → This is a DDL statement. This command drops a table.

● DROP USER → Only a superuser can execute this statement. This command drops a

user from a database.

Amazon Redshift SQL - Commands & Optimization

 29
● DROP VIEW → This command drops a view from a database.

● EXPLAIN → This command is used to see the explain plan of a query without

running the query.

● GRANT → This command is used to grant access privileges to user/group.

● INSERT → This is a DML statement. This command inserts a row into a table.
 30
● LOCK → This command is used to get access lock. This command is effective only

when it is getting executed within Begin...End statement.

● REVOKE → This command is used to revoke access to database object to

user/group.

● SELECT → This command is used to select the data from table/view.

● SELECT INTO → This command is used to get the output from one table and insert

into the new table.

 31
● TRUNCATE → This is a DDL statement and deletes all the rows from a table.

● UNLOAD → This command is used to unload the data from Redshift table into the

S3 bucket. Refer the ​whitepaper​ to know more about it in detail.

● UPDATE → This command is used to update a column of a table.

● VACUUM → This command removes ghost rows from tables. This command

reclaims free space.

Amazon Redshift SQL - Commands & Optimization

 32

Here are a few tips to optimize queries in Redshift:

1. Try to avoid doing a cross join between your tables as it will create a cartesian

product as an output from your table joins. Improper use of cross join might cause

extra storage and performance issues.

2. Redshift is a columnar data warehouse, unlike other data warehouses. Try to avoid

select * unless it is necessary. Select only necessary columns required in your ETL.

3. While doing your ETL, try to restrict your data using WHERE clause unless necessary.

Try to give sortkey on columns where you are applying WHERE clause.

4. If you are using GROUP BY and ORDER BY together in your query, try to give same

order of sequence both in GROUP BY and in ORDER BY.

5. Do not keep unnecessary data or keep tables which will not be in use after

performing the ETL. Drop those data immediately post your ETL.

6. Define Primary and Secondary key in your table only when it is really meant and

satisfies the rule because query planner does statistical computations accordingly. It

assumes that your tables do have Primary and Secondary keys. Please note that

Redshift does not enforce Primary and Secondary key values in your tables. Hence

this might create data integrity issues in your tables. Hence, it is important to design

your tables carefully.

Amazon Redshift SQL - Commands & Optimization

 33
7. Always take full advantage of view svv_table_info. This view contains all the details

including skew_ratio, diststyle, sortkey, skew_rows which will help you analyze the

performance of your table.

8. When using subqueries make sure subquery output returns the lesser number of

columns else it could hamper the performance.

9. Avoid cross-referencing of the same table while executing concurrent queries hitting

the same table in multiple sessions. Be very careful while inserting your data into

your second table when commit from the first query is not yet completed. This

could lead to a serialization isolation error. You can refer to AWS article ​Serialization

Isolation​ for more details.

Amazon Redshift SQL - Commands & Optimization

 34

While Redshift provides an exceptional performance, Data loading is one of the biggest

challenges of Redshift. To perform ETL to Redshift, you would need to invest precious

engineering resources to extract, clean, enrich, and build data pipelines. However, writing

complex scripts to automate all of this is not easy. It gets harder if you want to stream your

data real-time. Data loss becomes an everyday phenomenon due to issues that crop up

with changing sources, unstructured & unclean data, incorrect data mapping at the

warehouse, and more. Using a data integration platform like Hevo can solve all your

Redshift ETL problems. Hevo integrates with a variety of data sources ranging from SQL,

NoSQL, SaaS, File Storage Base, Webhooks, etc. with the click of a button.

Sign up for a free trial​ or ​view a quick video​ on how Hevo can make ETL easy.

Amazon Redshift SQL - Commands & Optimization

Looking for a simple and reliable way to bring
Data from Any Source to AWS Redshift?