Skip to Content

Reverse Engineer Data Model from Amazon Redshift

First post / new member - apologies if I'm doing this wrong.

I'm looking for a data modelling tool that supports forward and reverse engineering of database tables for Amazon Redshift. SAP PowerDesigner seems to support Redshift, but the Reverse Engineering option is not importing some table options. It gets the table and column information, but misses the Distribution Style/Key, and the Sort style/key. These attributes are all enterable in PowerDesigner under the generic "Extended Attributes" tab, and while they seem to forward engineer okay (the CREATE TABLE SQL includes any values you set), the Reverse Engineer won't pull them from the database.

Here's what I'm doing:

- Open PowerDesigner

- File->Reverse Engineer->Database

- Choose DBMS = Amazon Redshift Database

- Provide ODBC credentials to my Redshift database

- Select my tables from the Database Reverse Engineering list of database tables

- They import

- Open a table and go to the Extended Attributes tab - None of the Redshift attributes are imported

- Go to the Preview tab and inspect the CREATE TABLE statement - It does not include the Distribution or Sort settings.

Is this a known bug/limitation with Redshift reverse engineer?

If so, is there a patch? I need to select a tool for our new data warehouse, and I can't get PowerDesigner over the line without Reverse Engineer capability.

If not, what am I doing wrong?

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Feb 13, 2019 at 05:11 PM

    Hi Ross,

    I can't give you an answer, but maybe I can point you in the right direction. You can extend PD by modifying the XDB files it uses to generate the DDL that are specific to the DBMS you're using (though of course you would want to keep a copy of the original file prior to making any changes).

    Select Database\Edit Current DBMS from any open physical diagram (assuming your active model uses the Redshift XDB, if not, you can get to the same place going to Tools\Resources\DBMS and selecting the Redshift XDB from the list) to open the XDB for editing. Under the Script\Objects\Table node, you would want to create a new text item (right click on the Table node, then new\text item) and write a SQL query that would retrieve the properties of the Redshift table, and include a header with the variables to populate from that query (SqlChckQuery is a good example for setting an extended attribute in this manner, at least in my SQL Server XDB).

    To get your new query to fire during reverse engineering, you will need to add the name of your query\text item to the ReversedQueries list under the Table node (if you don't see a ReversedQueries item, right click on Table, select Add Items and select it from the popup)

    I haven't done this myself (though I will be soon in my quest to support system-versioned temporal tables in SQL Server), but I have modified the XDBs for other purposes and have had success generating (or altering the out of the box) DDL in this manner.

    Good luck,

    Laura

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 13, 2019 at 05:40 PM

    I forgot to mention that you can find the programmatic names for the extended attributes (for use in your SQL query) in the XDB under the Profile\Table\Extended Attributes node.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 22, 2020 at 10:32 AM

    Give a try to ERBuilder Data Modeler, it supports the forward and the reverse engineering of Amazon Redshift. A full trial version is offered for 15 days.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.