Skip to main content
All CollectionsForward & Reverse Engineering
Amazon Redshift- Direct Connect and Reverse Engineer
Amazon Redshift- Direct Connect and Reverse Engineer

Direct connect to AWS Redshift to obtain and update schema DDL

Written by Serge Gershkovich
Updated over a week ago

Redshift projects can use direct connect to reverse engineer entire schemas in seconds. Please refer to the instructions below to configure and perform a direct connection to your Redshift account.

Direct Connect

To establish a direct connection to Redshift, you will require the following details

Using these details, follow the highlighted steps to establish the connection (or get more details in the related articles at the end of this post):

  1. Navigate to the Reverse Engineering screen

  2. Click the "Connect to DW" button

  3. Enter your connection details from the section above

  4. Connect and select the schema to import

Obtaining Redshift parameters

To obtain the connection parameters for your Redshift instance, head to the "Provisioned clusters dashboard" page in the AWS console

Click on the cluster you wish to connect to in order to see its related properties.

In the "Endpoint," you will find the connection string that contains the server, port, and database in the following format:

  • server:port/database

Required database permissions

To obtain DDL, SqlDBM direct connect runs several queries that reference metadata in the information_schema and pg_catalog. Access to the database schemas/catalogs and their contents is not required.

The connecting user will require read access to:

  • pg_catalog

  • information_schema

For reference, the following SQL is run to obtain the DDL:

--Get schema list

SELECT table_schema as schema_name
FROM information_schema.tables
WHERE table_schema not in ('pg_internal', 'pg_catalog', 'information_schema')
AND table_catalog = @database


--Create schema ddl

WHEN nspowner > 100
+ ';' AS ddl
FROM pg_catalog.pg_namespace as pg_namespace
LEFT OUTER JOIN pg_catalog.pg_user pg_user
ON pg_namespace.nspowner=pg_user.usesysid
WHERE nspName = any(@schemas)
ORDER BY nspname


--Create table ddl

SELECT 'SHOW TABLE ' + quote_ident(t.table_schema) + '.' + quote_ident(t.table_name) + ';' as sqlText
FROM information_schema.tables t
WHERE t.table_schema = any(@schemas)
AND t.table_type = 'BASE TABLE'

IP allowlisting and private networking

Corporate networking and security policies may interfere with connectivity between SqlDBM and Databricks. If you are unable to connect despite following the guidelines in this article, please make sure that IP blocking or private networking is not interfering.

An easy way to diagnose this type of issue is by attempting to connect to your Databricks account from a home computer, without using a VNP or any corporate software.

For instructions on allowing SqlDBM through the firewall or private network, please see the related article at the bottom of this page.

See also:

Did this answer your question?