Closed jgbolger closed 6 years ago
Hi,
If it helps, I have dockerised this so it should hopefully be straightforward to reproduce.
Also - I can confirm that this is all hanging together and that when I use the standard SQL Lite database for the superset db, the db upgrade works and I can log in as admin and add the Oracle database as a data source and query it successfully.
Finally, it is worth mentioning that I am running my Docker instance on Windows.
The setup is as follows:
FROM amancevice/superset
USER root
RUN pip install cx_Oracle --upgrade
RUN apt-get install libaio1
COPY instantclient_11_2 /lib/oracle_instantclient_11_2
ENV LD_LIBRARY_PATH="/lib/oracle_instantclient_11_2:${LD_LIBRARY_PATH}"
USER superset
Important: Before you build that image, you need to download the Oracle instant client (11G version) from the Oracle website. I downloaded the zip and unzipped into the same folder as my docker file. URL for download is: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html File name is: instantclient-basiclite-linux-x86-64-11.2.0.2.0.zip
version: '3'
services:
oracledb:
container_name: oracledb
image: wnameless/oracle-xe-11g
volumes:
- ./data/oracle:/docker-entrypoint-initdb.d
ports:
- "49161:1521"
- "49160:22"
- "8081:8080"
environment:
- "ORACLE_ENABLE_XDB=true"
- "ORACLE_ALLOW_REMOTE=true"
superset:
container_name: superset
image: awp/superset
volumes:
- ./data/etc/superset:/etc/superset
ports:
- 8088:8088
links:
- oracledb
ROW_LIMIT = 5000
SUPERSET_WORKERS = 4
SUPERSET_WEBSERVER_PORT = 8088
SECRET_KEY = '\2\1thisismyscretkey\1\2\e\y\y\h'
SQLALCHEMY_DATABASE_URI = 'oracle://XDB:xdb@oracledb:1521/XE'
WTF_CSRF_ENABLED = True
DRUID_IS_ACTIVE = False
DEBUG = True
I notice that in line 77 of env.py it states the following:
if engine.name in ('sqlite', 'mysql'):
kwargs = {
'transaction_per_migration': True,
'transactional_ddl': True,
}
https://github.com/apache/incubator-superset/blob/master/superset/migrations/env.py
I therefore modified that if statement to include 'oracle' but that caused all sorts of errors. I changed it so that for oracle it only sets 'transactional_ddl': True and it got past the "Materialising permissions" script successfully
if engine.name in ('sqlite', 'mysql'):
kwargs = {
'transaction_per_migration': True,
'transactional_ddl': True,
}
if engine.name in ('oracle'):
kwargs = {
'transactional_ddl': True,
}
but failed further down:
INFO [alembic.runtime.migration] Running upgrade 4fa88fe24e94 -> c3a8f8611885, Materializing permission INFO [alembic.runtime.migration] Running upgrade c3a8f8611885 -> f0fbf6129e13, Adding verbose_name to tablecolumn INFO [alembic.runtime.migration] Running upgrade f0fbf6129e13 -> 956a063c52b3, adjusting key length INFO [alembic.runtime.migration] Running upgrade 956a063c52b3 -> 1226819ee0e3, Fix wrong constraint on table columns WARNI [root] Could not find or drop constraint on
columns
INFO [alembic.runtime.migration] Running upgrade 1226819ee0e3 -> d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric INFO [alembic.runtime.migration] Running upgrade d8bc074f7aad -> 27ae655e4247, Make creator owners INFO [alembic.runtime.migration] Running upgrade 27ae655e4247 -> 960c69cb1f5b, add dttm_format related fields in table_columns INFO [alembic.runtime.migration] Running upgrade 960c69cb1f5b -> f162a1dea4c4, d3format_by_metric INFO [alembic.runtime.migration] Running upgrade f162a1dea4c4 -> ad82a75afd82, Update models to support storing the queries. Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) cx_Oracle.DatabaseError: ORA-00904: : invalid identifierThe above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/local/bin/superset", line 15, in
manager.run() File "/usr/local/lib/python3.6/site-packages/flask_script/init.py", line 412, in run result = self.handle(sys.argv[0], sys.argv[1:]) File "/usr/local/lib/python3.6/site-packages/flask_script/init.py", line 383, in handle res = handle(*args, config) File "/usr/local/lib/python3.6/site-packages/flask_script/commands.py", line 216, in call return self.run(*args, kwargs) File "/usr/local/lib/python3.6/site-packages/flask_migrate/init.py", line 244, in upgrade command.upgrade(config, revision, sql=sql, tag=tag) File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 254, in upgrade script.run_env() File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 425, in run_env util.load_python_file(self.dir, 'env.py') File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file module = load_module_py(module_id, path) File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 64, in load_module_py module_id, path).load_module(module_id) File " ", line 399, in _check_name_wrapper File " kw) File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 330, in run_migrations step.migration_fn(*kw) File "/usr/local/lib/python3.6/site-packages/superset/migrations/versions/ad82a75afd82_add_query_model.py", line 44, in upgrade sa.PrimaryKeyConstraint('id') File "", line 823, in load_module File " ", line 682, in load_module File " ", line 251, in _load_module_shim File " ", line 675, in _load File " ", line 655, in _load_unlocked File " ", line 678, in exec_module File " ", line 205, in _call_with_frames_removed File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 107, in run_migrations_online() File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 100, in run_migrations_online context.run_migrations() File " ", line 8, in run_migrations File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 836, in run_migrations self.get_context().run_migrations( ", line 8, in create_table File " args, kw) File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 118, in _exec return conn.execute(construct, *multiparams, **params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl compiled File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception exc_info File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, 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 186, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: : invalid identifier [SQL: '\nCREATE TABLE query (\n\tid INTEGER NOT NULL, \n\tclient_id VARCHAR2(11 CHAR) NOT NULL, \n\tdatabase_id INTEGER NOT NULL, \n\ttmp_table_name VARCHAR2(256 CHAR), \n\ttab_name VARCHAR2(256 CHAR), \n\tsql_editor_id VARCHAR2(256 CHAR), \n\tuser_id INTEGER, \n\tstatus VARCHAR2(16 CHAR), \n\tname VARCHAR2(256 CHAR), \n\tschema VARCHAR2(256 CHAR), \n\tsql CLOB, \n\tselect_sql CLOB, \n\texecuted_sql CLOB, \n\tlimit INTEGER, \n\tlimit_used SMALLINT, \n\tselect_as_cta SMALLINT, \n\tselect_as_cta_used SMALLINT, \n\tprogress INTEGER, \n\trows INTEGER, \n\terror_message CLOB, \n\tstart_time NUMERIC(20, 6), \n\tchanged_on DATE, \n\tend_time NUMERIC(20, 6), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(database_id) REFERENCES dbs (id), \n\tFOREIGN KEY(user_id) REFERENCES ab_user (id), \n\tCHECK (limit_used IN (0, 1)), \n\tCHECK (select_as_cta IN (0, 1)), \n\tCHECK (select_as_cta_used IN (0, 1))\n)\n\n']", line 3, in create_table File "/usr/local/lib/python3.6/site-packages/alembic/operations/ops.py", line 1120, in create_table return operations.invoke(op) File "/usr/local/lib/python3.6/site-packages/alembic/operations/base.py", line 318, in invoke return fn(self, operation) File "/usr/local/lib/python3.6/site-packages/alembic/operations/toimpl.py", line 101, in create_table operations.impl.create_table(table) File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 194, in create_table self._exec(schema.CreateTable(table)) File "/usr/local/lib/python3.6/site-packages/alembic/ddl/oracle.py", line 22, in _exec result = super(OracleImpl, self)._exec(construct,
Looking at that SQL for creating the query table - the issue is that it is attempting to use a reserved word "rows" as a column name.
You can tell SQLAlchemy to add double quotes to columns that it does not know to be reserved words by including the quote=True attribute on the column definition
sa.Column('rows', sa.Integer(), nullable=True, quote=True),
I did this and reran the db upgrade and it now completes all of the migration scripts but fails with the following output:
INFO [alembic.runtime.migration] Context impl OracleImpl. 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 INFO [alembic.runtime.migration] Running upgrade b4456560d4f3 -> 4fa88fe24e94, owners_many_to_many INFO [alembic.runtime.migration] Running upgrade 4fa88fe24e94 -> c3a8f8611885, Materializing permission INFO [alembic.runtime.migration] Running upgrade c3a8f8611885 -> f0fbf6129e13, Adding verbose_name to tablecolumn INFO [alembic.runtime.migration] Running upgrade f0fbf6129e13 -> 956a063c52b3, adjusting key length INFO [alembic.runtime.migration] Running upgrade 956a063c52b3 -> 1226819ee0e3, Fix wrong constraint on table columns WARNI [root] Could not find or drop constraint on
columns
INFO [alembic.runtime.migration] Running upgrade 1226819ee0e3 -> d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric INFO [alembic.runtime.migration] Running upgrade d8bc074f7aad -> 27ae655e4247, Make creator owners INFO [alembic.runtime.migration] Running upgrade 27ae655e4247 -> 960c69cb1f5b, add dttm_format related fields in table_columns INFO [alembic.runtime.migration] Running upgrade 960c69cb1f5b -> f162a1dea4c4, d3format_by_metric INFO [alembic.runtime.migration] Running upgrade f162a1dea4c4 -> ad82a75afd82, Update models to support storing the queries. INFO [alembic.runtime.migration] Running upgrade ad82a75afd82 -> 3c3ffe173e4f, add_sql_string_to_table INFO [alembic.runtime.migration] Running upgrade 3c3ffe173e4f -> 41f6a59a61f2, database options for sql lab INFO [alembic.runtime.migration] Running upgrade 41f6a59a61f2 -> 4500485bde7d, allow_run_sync_async INFO [alembic.runtime.migration] Running upgrade 4500485bde7d -> 65903709c321, allow_dml INFO [alembic.runtime.migration] Running upgrade 41f6a59a61f2 -> 33d996bcc382 INFO [alembic.runtime.migration] Running upgrade 33d996bcc382, 65903709c321 -> b347b202819b, empty message INFO [alembic.runtime.migration] Running upgrade b347b202819b -> 5e4a03ef0bf0, Add access_request table to manage requests to access datastores. INFO [alembic.runtime.migration] Running upgrade 5e4a03ef0bf0 -> eca4694defa7, sqllab_setting_defaults INFO [alembic.runtime.migration] Running upgrade eca4694defa7 -> ab3d66c4246e, add_cache_timeout_to_druid_cluster INFO [alembic.runtime.migration] Running upgrade eca4694defa7 -> 3b626e2a6783, Sync DB with the models.py. WARNI [root] (cx_Oracle.DatabaseError) ORA-01418: specified index does not exist [SQL: '\nDROP INDEX table_name'] INFO [alembic.runtime.migration] Running upgrade 3b626e2a6783, ab3d66c4246e -> ef8843b41dac, empty message INFO [alembic.runtime.migration] Running upgrade ef8843b41dac -> b46fa1b0b39e, Add json_metadata to the tables table. INFO [alembic.runtime.migration] Running upgrade b46fa1b0b39e -> 7e3ddad2a00b, results_key to query INFO [alembic.runtime.migration] Running upgrade 7e3ddad2a00b -> ad4d656d92bc, Add avg() to default metrics INFO [alembic.runtime.migration] Running upgrade ad4d656d92bc -> c611f2b591b8, dim_spec INFO [alembic.runtime.migration] Running upgrade c611f2b591b8 -> e46f2d27a08e, materialize perms INFO [alembic.runtime.migration] Running upgrade e46f2d27a08e -> f1f2d4af5b90, Enable Filter Select INFO [alembic.runtime.migration] Running upgrade e46f2d27a08e -> 525c854f0005, log_this_plus INFO [alembic.runtime.migration] Running upgrade 525c854f0005, f1f2d4af5b90 -> 6414e83d82b7, empty message INFO [alembic.runtime.migration] Running upgrade 6414e83d82b7 -> 1296d28ec131, Adds params to the datasource (druid) table INFO [alembic.runtime.migration] Running upgrade 1296d28ec131 -> f18570e03440, Add index on the result key to the query table. INFO [alembic.runtime.migration] Running upgrade f18570e03440 -> bcf3126872fc, Add keyvalue table INFO [alembic.runtime.migration] Running upgrade f18570e03440 -> db0c65b146bd, update_slice_model_json INFO [alembic.runtime.migration] Running upgrade db0c65b146bd -> a99f2f7c195a, rewriting url from shortner with new format INFO [alembic.runtime.migration] Running upgrade a99f2f7c195a, bcf3126872fc -> d6db5a5cdb5d, empty message INFO [alembic.runtime.migration] Running upgrade d6db5a5cdb5d -> b318dfe5fb6c, adding verbose_name to druid column INFO [alembic.runtime.migration] Running upgrade d6db5a5cdb5d -> 732f1c06bcbf, add fetch values predicate INFO [alembic.runtime.migration] Running upgrade 732f1c06bcbf, b318dfe5fb6c -> ea033256294a, empty message INFO [alembic.runtime.migration] Running upgrade b318dfe5fb6c -> db527d8c4c78, Add verbose name to DruidCluster and Database INFO [alembic.runtime.migration] Running upgrade db527d8c4c78, ea033256294a -> 979c03af3341, empty message INFO [alembic.runtime.migration] Running upgrade 979c03af3341 -> a6c18f869a4e, query.start_running_time INFO [alembic.runtime.migration] Running upgrade a6c18f869a4e -> 2fcdcb35e487, saved_queries INFO [alembic.runtime.migration] Running upgrade 2fcdcb35e487 -> a65458420354, add_result_backend_time_logging INFO [alembic.runtime.migration] Running upgrade a65458420354 -> ca69c70ec99b, tracking_url Loaded your LOCAL configuration at [/etc/superset/superset_config.py] 2017-09-15 13:56:33,818:INFO:root:Syncing role definition 2017-09-15 13:56:33,818:INFO:root:Creating database reference Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into ("XDB"."DBS"."ID")The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/local/bin/superset", line 15, in
manager.run() File "/usr/local/lib/python3.6/site-packages/flask_script/init.py", line 412, in run result = self.handle(sys.argv[0], sys.argv[1:]) File "/usr/local/lib/python3.6/site-packages/flask_script/init.py", line 383, in handle res = handle(*args, config) File "/usr/local/lib/python3.6/site-packages/flask_script/commands.py", line 216, in call return self.run(*args, *kwargs) File "/usr/local/lib/python3.6/site-packages/superset/cli.py", line 28, in init security.sync_role_definitions() File "/usr/local/lib/python3.6/site-packages/superset/security.py", line 202, in sync_role_definitions get_or_create_main_db() File "/usr/local/lib/python3.6/site-packages/superset/security.py", line 101, in get_or_create_main_db db.session.commit() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 157, in do return getattr(self.registry(), name)(args, kwargs) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 906, in commit self.transaction.commit() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 461, in commit self._prepare_impl() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl self.session.flush() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2171, in flush self._flush(objects) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2291, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2255, in _flush flush_context.execute() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute uow File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements execute(statement, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception exc_info File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, 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 186, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (cx_Oracle.IntegrityError) ORA-01400: cannot insert NULL into ("XDB"."DBS"."ID") [SQL: 'INSERT INTO dbs (created_on, changed_on, verbose_name, database_name, sqlalchemy_uri, password, cache_timeout, select_as_create_table_as, expose_in_sqllab, allow_run_sync, allow_run_async, allow_ctas, allow_dml, force_ctas_schema, extra, perm, created_by_fk, changed_by_fk) VALUES (:created_on, :changed_on, :verbose_name, :database_name, :sqlalchemy_uri, :password, :cache_timeout, :select_as_create_table_as, :expose_in_sqllab, :allow_run_sync, :allow_run_async, :allow_ctas, :allow_dml, :force_ctas_schema, :extra, :perm, :created_by_fk, :changed_by_fk) RETURNING dbs.id INTO :ret_0'] [parameters: {'created_on': datetime.datetime(2017, 9, 15, 13, 56, 33, 831803), 'changed_on': datetime.datetime(2017, 9, 15, 13, 56, 33, 831810), 'verbose_name': None, 'database_name': 'main', 'sqlalchemy_uri': 'oracle://XDB:XXXXXXXXXX@oracledb:1521/XE', 'password': b'I5GyQ4mhCkC7uNGFmrwXLw==', 'cache_timeout': None, 'select_as_create_table_as': 0, 'expose_in_sqllab': 1, 'allow_run_sync': 1, 'allow_run_async': 0, 'allow_ctas': 0, 'allow_dml': 0, 'force_ctas_schema': None, 'extra': '{\n "metadata_params": {},\n "engine_params": {}\n}\n', 'perm': None, 'created_by_fk': None, 'changed_by_fk': None, 'ret_0': }]
I think the SQLAlchemy dialect has a list of reserved words it needs to quote as well. It's probably the best place to put this into.
Thanks for that.
In the latest stack trace, the issue is that Oracle does not auto increment primary key columns. See http://docs.sqlalchemy.org/en/latest/dialects/oracle.html
From reading this, it suggests that you should use a sequence. This looks like it might be a quite invasive change to implement as there are quite a number of superset table definitions that would require update.
Can I therefore ask if supporting use of the Oracle database for the superset tables is something you want and plan to do, or is the recommendation to use the standard SQL Lite db?
Cheers, John
We recommend MySQL, MariaDB or Postgres. SQLLite isn't production worthy.
Ideally Oracle support would come through SQLAlchemy with no customization on our end. I think people have made it work, it may require creating sequences manually. If that's the case, either SQLAlchemy/Alembic should take care of that, or we can document it here in this issue or in a section of the docs "Setting up against Oracle".
In any case the core dev team doesn't really care about Oracle specifically and our take is to use at your own risk. We can enable documentation and hooks and things like that, but should be minimal and/or live somewhere else. We're not in the business of db engine compatibility for our metadata db.
Keep in mind that there's no way for us to build or run our continuous integration against Oracle, so that means we cannot make any guarantees. To make any guarantees we'd have to have someone else make these guarantees, maybe in the way of our release process which we'd rather keep simple as much as possible.
But it seems like any somewhat modern environment should have an easy way to pop a MySQL or Postgres instance, I'd go with that option.
I'm not sure Oracle can be used without code changes - I think you need to tell SQLAlchemy to use sequences for id generation, otherwise it expects there to be auto increment behaviour. I am not that familiar with it (coming from a Java background) so I can't say I'm 100% confident of that.
Apart from the sequence issue, to make Oracle work you also need to:
Regarding support of Oracle - I understand that you cannot expect to support all databases and it would lead to a high overhead for you. Can I therefore suggest that you call out the list of supported / tested databases in the documentation for installation & configuration?
Thanks, John
The knowledge as to what database work well as a backend database should be community contributed as all we know is around what we can test/build against. We should add a section about that in the installation istructions.
Agreed
Notice: this issue has been closed because it has been inactive for 214 days. Feel free to comment and request for this issue to be reopened.
I´m also fighting to get superset working with oracle. I created sequences for every table on the model, added rows as reserved word, edited the line
engine.name in ('oracle'):
but now I´m having this issue:
step.migration_fn(**kw)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/superset/migrations/versions/e68c4473c581_allow_multi_schema_metadata_fetch.py", line 24, in upgrade
default=True,
File "<string>", line 8, in add_column
File "<string>", line 3, in add_column
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/alembic/operations/ops.py", line 1565, in add_column
return operations.invoke(op)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/alembic/operations/base.py", line 319, in invoke
return fn(self, operation)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/alembic/operations/toimpl.py", line 123, in add_column
schema=schema
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/alembic/ddl/impl.py", line 172, in add_column
self._exec(base.AddColumn(table_name, column, schema=schema))
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/alembic/ddl/oracle.py", line 22, in _exec
result = super(OracleImpl, self)._exec(construct, *args, **kw)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/alembic/ddl/impl.py", line 118, in _exec
return conn.execute(construct, *multiparams, **params)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
compiled
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/opt/analytics/anaconda3/envs/superset/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00972: identifier is too long [SQL: 'ALTER TABLE dbs ADD allow_multi_schema_metadata_fetch SMALLINT'] (Background on this error at: http://sqlalche.me/e/4xp6)
Anyone knows a good way to go around this identifier length problem? oracle only allows 30.
It should be noted that these issues only occur when trying to use Oracle as an OLTP backend. Most likely, that's not necessary. Just use MySQL, Postgres, or Sqlite for Superset's user/dashboard management and target Oracle from within Superset.
Make sure these boxes are checked before submitting your issue - thank you!
Superset version
0.19.1
Expected results
"superset db upgrade" command completes
Actual results
Error running one of the migration scripts
It is worth noting that this works fine when I use the default SQL Lite instance. It is only when I change to use Oracle.
Steps to reproduce
1. Set db connection in superset_config.py SQLALCHEMY_DATABASE_URI = 'oracle://user:password@hostname:1521/XE'
Oracle db instance is Oracle XE 11g.
**2. Run superset db upgrade
Log error: