unable to connect to AWS Redshift

[khushhalm]

Describe the bug My application is hosted on AWS elasticbeanstalk so every time I deploy the application local db is removed. So I tried using redshift to store the data.

Using Postgresql:

I tried using Postgres to connect to my redshift database but it gave the following error:

sqlalchemy.exc.NotSupportedError: (psycopg2.errors.FeatureNotSupported) Column "endpoint.id" has unsupported type "serial".

[SQL: 
CREATE TABLE "Endpoint" (
        id SERIAL NOT NULL, 
        name VARCHAR(250) NOT NULL, 
        monitor_level INTEGER, 
        time_added TIMESTAMP WITHOUT TIME ZONE, 
        version_added VARCHAR(100), 
        last_requested TIMESTAMP WITHOUT TIME ZONE, 
        PRIMARY KEY (id), 
        UNIQUE (name)
)]

Since redshift doesn't support SERIAL.

Using MS-SQL:

So I moved to use MS-SQL but it gave the following warning:

....lib/python3.7/site-packages/sqlalchemy/connectors/pyodbc.py:79: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "

and when I tried reaching localhost:5000/dashboard it gave following error:

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5)

To Reproduce For Postgres:

[database]
TABLE_PREFIX=fmd
DATABASE=postgresql://<user>:<pass>@<host>:5439/<db_name>

For MS-SQL:

TABLE_PREFIX=fmd
DATABASE=mssql://<user>:<pass>@<host>:5439/<db_name>
Driver={Amazon Redshift (x64)}; Server=<host> Database=<db_name>; UID=<user>; PWD=<pass>; Port=5439

Expected behavior How can I use Redshift with Flask-monitoringDashboard?

Desktop (please complete the following information):

• OS: MacOS 10.15.5
• Browser: chrome, safari, Brave
• FMD Version: 2.1.4

[khushhalm]

@FlyingBird95 @kloostert @bogdanp05 @mircealungu Any help on this would be appreciated.

[FlyingBird95]

Hey,

we don't officially support redshift databases, but I think that following posts might help you:

• https://stackoverflow.com/questions/35004936
• https://pypi.org/project/sqlalchemy-redshift/

Note that the we directly load the database configuration into sqlalchemy, so what you specify as the DATABASE=<your-value> parameter will be used for create_engine(<your-value>).

Hopefully this helps for you.

Regards,

Patrick

[khushhalm]

Status update: (In case someone came looking here who also wants to use AWS Redshift to store the fmd monitoring data.)

I tried the method suggested by @FlyingBird95 but achieved no luck in using AWS Redshift because the queries that are formed using the libraries are not supported by AWS redshift. AWS Redshift doesn't support all the functions of Postgres so that is a bummer.

Hopefully, I'll try to work on this in the future and make a pull request to improved this wonderful project.