Issue installing on Redshift

petter-memrise commented 8 years ago

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 commented 8 years ago

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 commented 8 years ago

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 commented 8 years ago

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

petter-memrise commented 8 years ago

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 commented 6 years ago

@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 commented 6 years ago

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.

apache / superset