You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2020/03/12 13:34:18 UTC
[GitHub] [incubator-superset] disssid opened a new issue #9294: Superset
with SQL Server as backend
disssid opened a new issue #9294: Superset with SQL Server as backend
URL: https://github.com/apache/incubator-superset/issues/9294
Trying to get Superset working with SQL server (running in a docker container) as backend
### Expected results
Superset UI
### Actual results
1. `docker exec -it superset superset-init`
- Creating an admin account - Successful
(Queried the table to see the user successfully inserted)
- Database upgrade - Failed
(Error: same as the one in below additional logs)
2. Tried manual upgrade using
- `superset db upgrade` - Failed
3. When I tried to manually initialize using
- `superset init`
```
superset@7d839629ab4a:~$ superset init
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
""")
2020-03-12 13:24:30,849:INFO:root:logging was configured successfully
2020-03-12 13:24:31,037:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
2020-03-12 13:24:31,403:DEBUG:asyncio:Using selector: EpollSelector
Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dbs'. (208) (SQLExecDirectW)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/bin/superset", line 31, in <module>
cli()
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__
return self.main(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 586, in main
return super(FlaskGroup, self).main(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main
rv = self.invoke(ctx)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
return callback(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
return callback(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/superset/cli.py", line 51, in init
utils.get_example_database()
File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 976, in get_example_database
return get_or_create_db("examples", db_uri)
File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 960, in get_or_create_db
db.session.query(models.Database).filter_by(database_name=database_name).first()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3228, in first
ret = list(self[0:1])
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__
return list(res)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3330, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3355, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dbs'. (208) (SQLExecDirectW)")
[SQL: SELECT TOP 1 dbs.created_on AS dbs_created_on, dbs.changed_on AS dbs_changed_on, dbs.id AS dbs_id, dbs.verbose_name AS dbs_verbose_name, dbs.database_name AS dbs_database_name, dbs.sqlalchemy_uri AS dbs_sqlalchemy_uri, dbs.password AS dbs_password, dbs.cache_timeout AS dbs_cache_timeout, dbs.select_as_create_table_as AS dbs_select_as_create_table_as, dbs.expose_in_sqllab AS dbs_expose_in_sqllab, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_csv_upload AS dbs_allow_csv_upload, dbs.allow_ctas AS dbs_allow_ctas, dbs.allow_dml AS dbs_allow_dml, dbs.force_ctas_schema AS dbs_force_ctas_schema, dbs.allow_multi_schema_metadata_fetch AS dbs_allow_multi_schema_metadata_fetch, dbs.extra AS dbs_extra, dbs.perm AS dbs_perm, dbs.impersonate_user AS dbs_impersonate_user, dbs.created_by_fk AS dbs_created_by_fk, dbs.changed_by_fk AS dbs_changed_by_fk
FROM dbs
WHERE dbs.database_name = ?]
[parameters: ('examples',)]
(Background on this error at: http://sqlalche.me/e/f405)
```
Additional Logs (when trying to upgrade the database):
```
INFO [alembic.runtime.migration] Context impl MSSQLImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 4e6a06bad7a8, Init
INFO [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -> 5a7bad26f2a7, empty message
INFO [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -> 1e2841a4128, empty message
INFO [alembic.runtime.migration] Running upgrade 1e2841a4128 -> 2929af7925ed, TZ offsets in data sources
INFO [alembic.runtime.migration] Running upgrade 2929af7925ed -> 289ce07647b, Add encrypted password field
INFO [alembic.runtime.migration] Running upgrade 289ce07647b -> 1a48a5411020, adding slug to dash
INFO [alembic.runtime.migration] Running upgrade 1a48a5411020 -> 315b3f4da9b0, adding log model
INFO [alembic.runtime.migration] Running upgrade 315b3f4da9b0 -> 55179c7f25c7, sqla_descr
INFO [alembic.runtime.migration] Running upgrade 55179c7f25c7 -> 12d55656cbca, is_featured
INFO [alembic.runtime.migration] Running upgrade 12d55656cbca -> 2591d77e9831, user_id
INFO [alembic.runtime.migration] Running upgrade 2591d77e9831 -> 8e80a26a31db, empty message
INFO [alembic.runtime.migration] Running upgrade 8e80a26a31db -> 7dbf98566af7, empty message
INFO [alembic.runtime.migration] Running upgrade 7dbf98566af7 -> 43df8de3a5f4, empty message
INFO [alembic.runtime.migration] Running upgrade 43df8de3a5f4 -> d827694c7555, css templates
INFO [alembic.runtime.migration] Running upgrade d827694c7555 -> 430039611635, log more
INFO [alembic.runtime.migration] Running upgrade 430039611635 -> 18e88e1cc004, making audit nullable
INFO [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> 836c0bf75904, cache_timeouts
INFO [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> a2d606a761d9, adding favstar model
INFO [alembic.runtime.migration] Running upgrade a2d606a761d9, 836c0bf75904 -> d2424a248d63, empty message
INFO [alembic.runtime.migration] Running upgrade d2424a248d63 -> 763d4b211ec9, fixing audit fk
INFO [alembic.runtime.migration] Running upgrade d2424a248d63 -> 1d2ddd543133, log dt
INFO [alembic.runtime.migration] Running upgrade 1d2ddd543133, 763d4b211ec9 -> fee7b758c130, empty message
INFO [alembic.runtime.migration] Running upgrade fee7b758c130 -> 867bf4f117f9, Adding extra field to Database model
INFO [alembic.runtime.migration] Running upgrade 867bf4f117f9 -> bb51420eaf83, add schema to table model
INFO [alembic.runtime.migration] Running upgrade bb51420eaf83 -> b4456560d4f3, change_table_unique_constraint
Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'alembic_version'. (208) (SQLExecDirectW)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/bin/superset", line 31, in <module>
cli()
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__
return self.main(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 586, in main
return super(FlaskGroup, self).main(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main
rv = self.invoke(ctx)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
return callback(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
return callback(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/flask_migrate/cli.py", line 134, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/usr/local/lib/python3.6/site-packages/flask_migrate/__init__.py", line 95, in wrapped
f(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/flask_migrate/__init__.py", line 280, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 276, in upgrade
script.run_env()
File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 475, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file
module = load_module_py(module_id, path)
File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 177, in load_module_py
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 678, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 116, in <module>
run_migrations_online()
File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 108, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 839, in run_migrations
self.get_context().run_migrations(**kw)
File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 369, in run_migrations
head_maintainer.update_to_step(step)
File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 595, in update_to_step
self._update_version(from_, to_)
File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 541, in _update_version
== literal_column("'%s'" % from_)
File "/usr/local/lib/python3.6/site-packages/alembic/ddl/mssql.py", line 36, in _exec
result = super(MSSQLImpl, self)._exec(construct, *args, **kw)
File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 134, in _exec
return conn.execute(construct, *multiparams, **params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'alembic_version'. (208) (SQLExecDirectW)")
[SQL: UPDATE alembic_version SET version_num='b4456560d4f3' WHERE alembic_version.version_num = 'bb51420eaf83']
(Background on this error at: http://sqlalche.me/e/f405)
```
### Environment
- superset version: `0.35.2`
- python version: `3.6.10`
- sqlalchemy version - `1.3.6`
- Host OS - `Ubuntu 18.04.4 LTS`
- SQL Server - docker image `mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04`
### Checklist
Make sure these boxes are checked before submitting your issue - thank you!
- [Y] I have checked the superset logs for python stacktraces and included it here as text if there are any.
- [Y] I have reproduced the issue with at least the latest released version of superset.
- [Y] I have checked the issue tracker for the same issue and I haven't found one similar.
### Additional context
- Installed the necessary packages
```
sudo apt-get install unixodbc-dev
pip install pyodbc
```
- Installed
[SQL Server 2017 ODBC driver](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15)
- SQL Alchemy URI: `mssql+pyodbc://{username}:{password}@{hostname}/{dbname}?driver=ODBC Driver 17 for SQL Server`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] disssid commented on issue #9294: Superset
with SQL Server as backend
Posted by GitBox <gi...@apache.org>.
disssid commented on issue #9294: Superset with SQL Server as backend
URL: https://github.com/apache/incubator-superset/issues/9294#issuecomment-599515852
@dpgaspar @willbarrett Thank you for the advice, we'll use either MySQL or Postgres as the metadata DB.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] dpgaspar commented on issue #9294: Superset
with SQL Server as backend
Posted by GitBox <gi...@apache.org>.
dpgaspar commented on issue #9294: Superset with SQL Server as backend
URL: https://github.com/apache/incubator-superset/issues/9294#issuecomment-598190989
I do advise to to use MySQL or Postgres has the **metadata DB**. Note that tests run on these backends, even if you manage to make it work now, it will not be guaranteed on future versions.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] dpgaspar edited a comment on issue #9294:
Superset with SQL Server as backend
Posted by GitBox <gi...@apache.org>.
dpgaspar edited a comment on issue #9294: Superset with SQL Server as backend
URL: https://github.com/apache/incubator-superset/issues/9294#issuecomment-598190989
I do advise you to use MySQL or Postgres has the **metadata DB**. Note that tests run on these backends, even if you manage to make it work now, it will not be guaranteed on future versions.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] willbarrett commented on issue #9294: Superset
with SQL Server as backend
Posted by GitBox <gi...@apache.org>.
willbarrett commented on issue #9294: Superset with SQL Server as backend
URL: https://github.com/apache/incubator-superset/issues/9294#issuecomment-598245432
+1 - to @dpgaspar 's advice. The only two metadata databases supported in Superset are MySQL and Postgres. Providing an abstraction that will work for every possible metadata store is not possible for us at this time.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] disssid closed issue #9294: Superset with SQL
Server as backend
Posted by GitBox <gi...@apache.org>.
disssid closed issue #9294: Superset with SQL Server as backend
URL: https://github.com/apache/incubator-superset/issues/9294
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org