Issue installing on Redshift

[petter-memrise]

Make sure these boxes are checked before submitting your issue - thank you!

• [ x ] I have checked the caravel logs for python stacktraces and included it here as text if any
• [ x ] I have reproduced the issue with at least the latest released version of caravel
• [ x ] I have checked the issue tracker for the same issue and I haven't found one similar

I am trying to initialize Caravel and connect to an Amazon Redshift DB.

Caravel version

0.11.0

Expected results

Server should start up and use the database SQLALCHEMY_DATABASE_URI points to

Actual results

Stack trace:
(caravel_test) Petters-MacBook-Pro:~ petter$ caravel runserver -p 8088
/Users/petter/.virtualenvs/caravel_test/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.script is deprecated, use flask_script instead.
.format(x=modname), ExtDeprecationWarning
/Users/petter/.virtualenvs/caravel_test/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.sqlalchemy is deprecated, use flask_sqlalchemy instead.
.format(x=modname), ExtDeprecationWarning
/Users/petter/.virtualenvs/caravel_test/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.sqlalchemy._compat is deprecated, use flask_sqlalchemy._compat instead.
.format(x=modname), ExtDeprecationWarning
/Users/petter/.virtualenvs/caravel_test/lib/python2.7/site-packages/flask_cache/init.py:152: UserWarning: Flask-Cache: CACHE_TYPE is set to null, caching is effectively disabled.
warnings.warn("Flask-Cache: CACHE_TYPE is set to null, "
/Users/petter/.virtualenvs/caravel_test/lib/python2.7/site-packages/flask/exthook.py:71: ExtDeprecationWarning: Importing flask.ext.cache is deprecated, use flask_cache instead.
.format(x=modname), ExtDeprecationWarning
2016-10-27 14:11:59,051:INFO:flask_appbuilder.security.sqla.manager:Security DB not found Creating all Models from Base
2016-10-27 14:12:00,358:ERROR:flask_appbuilder.security.sqla.manager:DB Creation and initialization failed: (psycopg2.NotSupportedError) SQL command "CREATE SEQUENCE ab_user_id_seq" not supported.
[SQL: 'CREATE SEQUENCE ab_user_id_seq']

Steps to reproduce

I have followed the steps here: http://airbnb.io/caravel/installation.html and am able to connect to the default DB. Next I tried adding a file caravel_config.py and point my PYTHONPATH to it's library, and change the SQLALCHEMY_DATABASE_URI to point to my server. The only things I've overridden from this file: https://github.com/airbnb/caravel/blob/master/caravel/config.py is this section:

SECRET_KEY = 'RANDOM_LONG_STRING'
SQLALCHEMY_DATABASE_URI = 'redshift+psycopg2://USR:PASSWD@HOST:PORT/DB' (actual values but this format)

I've also tried replacing 'redshift+psycopg2' with 'postgresql' with similar results. I'm running on a Macbook OS Capitan 10.11.6, Python version Python 2.7.10 in a virtual environment with caravel and both sqlacademy-redshift and psycopg2 installed.

I've Google for the error above but with almost no results. Any help would be greatly appreciated, and please let me know if there's any further info I can provide. Thanks!

[xrmx]

It looks like sequences are not supported on redshift:
http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html

Are you sure the stacktrace is from when you were using redshift+psycopg2 dialect?

[petter-memrise]

Thanks for the response! Yep, the stacktrace looks the same to me in both cases in fact, This is from when I ran it again just now:

From caravel_config.py:
SQLALCHEMY_DATABASE_URI = 'redshift+psycopg2://readonly:PASSWD@memrise-prod-vpc.clk9kuenajso.us-east-1.redshift.amazonaws.com:5439/memrise'

Error part of stacktrace:

2016-10-27 14:55:21,786:ERROR:flask_appbuilder.security.sqla.manager:DB Creation and initialization failed: (psycopg2.NotSupportedError) SQL command "CREATE SEQUENCE ab_user_id_seq" not supported.
[SQL: 'CREATE SEQUENCE ab_user_id_seq']

From reading online, it looks like it should be possible to use a Redshift DB with Caravel; is this sequence feature required for it to work? Thanks!

[xrmx]

Maybe people are installing it locally on mysql / sqlite and just using redshift dbs as datasources?

[petter-memrise]

Thanks xrmx, I tried just omitting that file and was able to add the Redshift DB as a data source in the web UI; I hadn't understood that this was an option at first.. Cheers!

[niks-git]

@petter-memrise regarding your comment above:
"Thanks xrmx, I tried just omitting that file and was able to add the Redshift DB as a data source in the web UI; I hadn't understood that this was an option at first.. Cheers!"
Can you explain how were you able to do it?

My requirement is that i want to bypass the default sqlite impl (superset.db) and select the postgresql on redshift. Is it possible to do that? I am able to edit the DB record in UI that exists for default DB, but I cannot see my changes getting reflected anywhere on the Redshift DB.

Modifying the URL in config file doesn't help. It throws the same error for me too:
ERROR:flask_appbuilder.security.sqla.manager:DB Creation and initialization failed: (psycopg2.NotSupportedError) SQL command "CREATE SEQUENCE ab_user_id_seq" not supported.
[SQL: 'CREATE SEQUENCE ab_user_id_seq']

[mistercrunch]

Wait @niks-git, looks like you're trying to use Redshift as the metadata database for Superset. This won't work as Redshift is not a proper OLTP database. Redshift should be used as a database you query, not as a metadata backend for the app.