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 comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Feb 13 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 comment
    10|10000 characters needed characters exceeded

  • Feb 13 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 comment
    10|10000 characters needed characters exceeded