Support for Amazon Redshift database

[schemaspy]

Support for Amazon Redshift database

[drorm]

I just successfully created a schema for redshift.

Instructions in https://gist.github.com/drorm/14ca3ac50d90cada44fa1d8f0e121bd1

[wkasa]

Hi @thebogusman . I've created PR with support for redshift db.
Please verify if everything works.

edit: Remember to download jtds driver and point it in parameters.
Port should be also configured separately (not with host)

[rafalkasa]

@thebogusman PR created by @wkasa was already merged. So you can clone the main repository for your test and verification.

[thebogusman]

@wkasa thanks a lot! I really appreciate your effort.

I did some testing on a moderately large database. The first time it crashed with some NPE while loading/generating info for columns. So I deleted the output folder and run it once again with console output redirected to a file, but this time it finished successfully.

The end result is pretty much usable, but it has some issues:

1. Reported row count for some tables is -1 (affects about 10% of all tables, seems to be random)
2. I was generating docs for schema public, but there was also schema public_bkp present in the database at the time, with almost identical set of tables. This caused all the relations in the docs to be doubled, with one pointing to public.table and the other one to public_bkp.table. Tables from "public_bkp" schema are also present on the diagrams, but not on the table list.
3. Descriptions for columns are mixed up between different columns inside a table. This seems to be connected with the previous issue, as only the tables with lots of relations seems to be affected. (edit: problem on my db)

I'm attaching the command output, as there were some warnings/exceptions showing up along the way. I obfuscated the names of the tables, but hopefully they're not that important.

output_obf.txt

[wkasa]

@thebogusman any chance that you can prepare some test db? Or maybe script to create such database to reproduce this issue? I am not and redshift expert and I've only created simple test db to fix exception with connecting to this db.

[thebogusman]

@wkasa sure, I will come up with something next week.

[thebogusman]

@wkasa actually, I've already got it. Here's the ddl for both schemas:
schemaspy_test_ddl.zip

[wkasa]

@thebogusman thanks! I'll look on it next week.

[wkasa]

@thebogusman
I checked your script and it looks like the problem is deeper. Below is printscreen from SQL Workbench, I used the same jdbc driver like in schemaspy. As you can see even there those tables are somehow related.

[rafalkasa]

@thebogusman based on @wkasa explanation I suppose that's something incorrect in your database.
Could you verify and let us know ?

[wkasa]

@rafalkasa it looks ok on the database - there is no relation between this tables. It looks like some bug in jdbc drivers or something like this.

[rafalkasa]

@wkasa thanks for your detailed explanation so if this is something in jdbc driver it will be not simple to fix... Do you have any suggestions ?

[thebogusman]

@wkasa you're probably right. I can observe the same in DBeaver. I'll post an issue on Amazon's forum regarding that problem.

Amazon provides this view for retreiving foreign keys:
https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_constraint_dependency.sql
It works ok on the schemas I sent you.

[wkasa]

Ok then, let's wait until @thebogusman will receive some feedback from Amazon.

[wkasa]

@thebogusman any update?

[lovegis]

@thebogusman - can you please post the URL of this issue on the Amazon forum? There is great interest to have SchemaSpy work with Redshift and I'd like to add to the conversation.

[seanmuth]

+1 for interest in this.
can help with testing/investigation

[npetzall]

@seanmuth

There is a dbtype for redshift that you can test a provide feedback for.

redshift

[seanmuth]

@npetzall getting a failed to load driver error trying to run with the latest docker snapshot:

Failed to load driver 'com.amazon.redshift.jdbc42.Driver'
This entry doesn't point to a valid file/directory: [/Users/seanmuth/Documents/Redshift/RedshiftJDBC42-1.2.1.1001.jar]

chmod 777 to the jar file to make sure it's not a file permissions thing, no luck

Here's the command I'm running:

docker run schemaspy/schemaspy:snapshot -t redshift -u username -p password -host hostname -db database -port portnum -s public -dp ~/Documents/Redshift/RedshiftJDBC42-1.2.1.1001.jar

Guessing this is obvious but I can't see it

[npetzall]

@seanmuth redshift driver is not included with the docker image. You need to bind mount the driver using -v

[npetzall]

-v "path-to-folder-with-jar:/drivers" add before image

[seanmuth]

Got it thanks @npetzall, I don't have a ton of experience with docker yet, my avatar definitely applies here :-P

[npetzall]

@seanmuth the image and docker is pretty new so the docs hasn't been battle tested. I'll think of an update to improve the docs. I read it now and the -v is quite hidden.

[seanmuth]

no worries, I can help with docs too if you need it.

got through connecting relationships, error'd here:

.ERROR - Application startup failed
java.lang.UnsatisfiedLinkError: /usr/lib/jvm/java-1.8-openjdk/jre/lib/amd64/libfontmanager.so: Error relocating /usr/lib/jvm/java-1.8-openjdk/jre/lib/amd64/libfontmanager.so: AWTFontDefaultChar: symbol not found

[npetzall]

@seanmuth I've noticed some possible improvements and also that I didn't pin the version of the "parent image" so might be an update there that has broken the docker image.

I'll have a look when I get to a computer. Currently in a cabin on the countrysidr with only a cellphone.

If your able to run locally without docker (graphiz installed) you can download the snapshot.jar

The error you get is probably related to fontconfig but there might be changes in newer jdk. So the setup I've doesnt work. I'll investigate and fix, but later... #122

[seanmuth]

Cabin in the countryside sounds great! I'll mess around locally and see where I can get!

[npetzall]

@seanmuth you can join our chat on gitter

[lovegis]

Initial results for Redshift look promising. I need to work on adding all my constraints that exist in my postgres DB to redshift. I did have to research and add this to my command line as this database enforces ssl: -connprops "ssl\=true;sslfactory\=org.postgresql.ssl.NonValidatingFactory" FWIW, the layout of v6 is very nice. I was previously using v5. Will report back after I update the database. Reily

[npetzall]

There is a link to download the latest snapshot in the readme.md so you can skip the download I hope.

https://github.com/schemaspy/schemaspy#latest-release

@rafalkasa we need to change the header to snapshot.

[drorm]

Updated the gist.

When I downloaded the snapshot it had the save version/file name as the version I downloaded earlier. Probably makes sense to have them have different versions names.

[npetzall]

@drorm we have changed the name and also yesterday released 6.0.0.

I failed to ask back then, was the schema looking OK. Do we have any outstanding issues with redshirt?

[drorm]

The schema looked fine

[npetzall]

Closing this.

Create new issues if found.