Superset oracle configuration

Question

I'm trying to configure Superset, but when I try to configure a new database to connect to oracle, I have problems,

Superset uses sqlalchemy for db connection, I define tnsname to connect and pass to superset as say documentation http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#database-urls oracle+cx_oracle://ifn:XXXXXX@DBDESA but produces an error

(TypeError: expecting None or a string)

separatly i try to do a connection with sqlalchemy for test the connection

    Import sqlalchemy as sa
    Eng = sa.create_engine ('oracle + cx_oracle: // ifn: XXXXXX @ DBDESA')
    Eng.execute ('select 1 from dual') fetchall ()

But i get the same error, is there another needed configuration? Encoding or someting for connect to oracle XE? Or aditional params in superset configuration?

Tnsnames.ora

    DBDESA =
            (Description =
                    (Address_list =
            (Address = (protocol = TCP) (host = XXX.XX.X.X) (port = 1524)))
            (Connect_data = (Service_name = dbdesa)))

score 0 · Answer 1 · edited Oct 18 '17 at 05:59

0

I think URI syntax may be wrong.

Please try to set the URI by referring to the example below:

oracle+cx_oracle://dbname:password@ipaddress:port/?service_name=yourtnsname

For example:

oracle+cx_oracle://test_db:test_password@10.22.3.202:1521/?service_name=hoge

edited Oct 18 '17 at 05:59

E.Z

1,958
1
18
27

answered Oct 18 '17 at 05:11

user8669400

39
2
6

neilson · Answer 2 · 2018-05-25T10:06:23.520

Although the documentation says that oracle is supported, you will have a hard time making it work.

For instance:

Superset assumes that the autoincrement is supported by the DB, which is not the case for oracle. You will need to create all the sequences in Oracle and add references to them in your domain classes. You have to do that in superset code superset/models/core.py, as well as in the migrations superset/migrations/* (look for every place where a table is created).
Oracle does not support identifiers (foreign key or column names) with more than 30 characters. You will need to do some renamings.
on superset/migrations/env.py, after:

if engine.name in ('sqlite', 'mysql'):
    kwargs = {
        'transaction_per_migration': True,
        'transactional_ddl': True,
 }

add a similar block for oracle as below:

if engine.name in ('oracle'):
        kwargs = {
            'transactional_ddl': True,
  }

Notice that I removed the transaction_per_migration parameter. And you can also use equals instead of "in" :)

on the dialect site-packages/sqlalchemy/dialects/oracle/base.px add "rows" as a reserved word ./base.py:360:RESERVED_WORDS
Do that again every time you want to upgrade superset.
Give up this mess and use Mysql

Thanks for the tips. The behaviour I'm seeing with vanilla superset is that for oracle 12c it works in SQL editor. It also works for visualising if I select just one row. Trying to select more than one row doesn't show anything. Does that line up with your experience? — eamon1234, Jun 08 '18 at 06:26

Superset oracle configuration

2 Answers2