Generate Redshift DDL using System Tables

Amazon Redshift is a fully managed, distributed relational database on the AWS cloud. Amazon will manage the hardware’s and your only task is to manage databases that you create as a result of your project. In this article, we will check one of the administrator tasks, generate Redshift view or table DDL using System Tables.

Generate Redshift DDL using System Tables

Amazon Redshift support many system tables, that will make your life easier as a database administrator. All the activity information’s such as executed query, DDL, memory information, etc. are stored in the system table.

Redshift System STL_DDLTEXT Table

The STL_DDLTEXT system table stored information about the DDL queries that you execute on Redshift database. You can query this table for a given time period and get required DDL for your application or backup purpose.

The STL_DDLTEXT table captures the following DDL statements that were run on the system.

These DDL statements include the following queries and objects:

• CREATE SCHEMA, TABLE, VIEW
• DROP SCHEMA, TABLE, VIEW
• ALTER SCHEMA, TABLE

How to get Required DDL from STL_DDLTEXT Table?

Use the STARTTIME and ENDTIME columns to find out which statements were logged during a given time period. Long blocks of SQL query text are broken into lines 200 characters long; the SEQUENCE column identifies fragments of text that belong to a single statement.

This table is global and visible to all users in database by default. However, Superusers can see all rows; regular users can see only their own data.

Reconstructing Stored SQL

To reconstruct the SQL stored in the text column of the STL_DDLTEXT, run a SELECT statement to create SQL from 1 or more parts in the text column. You should replace all (\n) with a newline character to get proper structured query.

Get DDL from Redshift System Table

For Example, You can use below query to display DDL text for queries that are executed on Redshift.

SELECT starttime, 
       sequence, 
       Substring(text, 1, 20) AS text 
FROM   stl_ddltext 
ORDER  BY xid DESC, 
          sequence 
LIMIT  10; 

         starttime          | sequence |         text
----------------------------+----------+----------------------
 2019-09-01 17:28:58.870316 |        0 | drop function  get_u
 2019-09-01 17:27:41.15912  |        0 | create or replace fu
 2019-09-01 17:27:41.15912  |        1 | nge(1, len(arr)+1):\
 2019-09-01 17:15:12.631951 |        0 | drop view dual;
 2019-09-01 16:59:57.912569 |        0 | CREATE VIEW DUAL AS
 2019-09-01 16:59:44.929566 |        0 | drop table dual;
 2019-09-01 16:52:06.806867 |        0 | CREATE TABLE DUAL (
 2019-09-01 16:51:55.575681 |        0 | drop table dual;
 2019-09-01 16:49:41.358245 |        0 | CREATE TABLE DUAL (
 2019-09-01 16:48:13.342484 |        0 | drop view dual; 

Hope this helps 🙂