Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Readonly Databases not Supported (eg Dremio or lower permissioned connections) #4916

Closed
malsmith opened this issue May 1, 2018 · 15 comments
Labels

Comments

@malsmith
Copy link

@malsmith malsmith commented May 1, 2018

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

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

Superset version

0.24

Expected results

I should be able to add a table from any database connection. Note that when I add more database connections I can use them just fine in the SQL Lab UI - just can't add tables from them in the Tables function - so I can't actually use them in charts or dashboards.

It seems like the Superset system is only designed to use a single database at a time and requires read/write access to that database, which would block my planned use of the system where I planned to have a small postgresql database for metadata and management and a large readonly database (Dremio in my case) as the analytical datastore.

Actual results

When adding a new Table item in the UI the system gets confused and queries the target database to check if the "tables" table already contains the new entry - rather than checking the main database connection (sqlite in my case). The system returns an Unknown error from database Flash message and the query itself fails in the Superset system logs (table "Tables" does not exist)

Steps to reproduce

Install the system as is with the Sqlite datasource/database as the primary database (load samples etc). Then Add a new Database in addition to the SQLLite one. In my case add a database where new tables cannot be created (although this seems to be beside the point).
Attempt to add a new Table that is known to exist in the secondary database.
Watch the system fail to add the new table (generating the unknown database flash message)

Appears to be a problem in (or leading up to)

superset/connectors/sqla/views.py line 236 pre_add method 

This seems to grab the wrong database sqla engine value and implications are that the system needs to know which is the database engine that holds the Superset metadata and which is the system where the new table is to be added from.

SQL Trace

2018-04-30 20:14:20,274 INFO sqlalchemy.engine.base.Engine SELECT EXISTS (SELECT 1 
FROM tables 
WHERE tables.table_name = ? AND tables.schema = ? AND tables.database_id = ?) AS anon_1
2018-04-30 20:14:20,274:INFO:sqlalchemy.engine.base.Engine:SELECT EXISTS (SELECT 1 
FROM tables 
WHERE tables.table_name = ? AND tables.schema = ? AND tables.database_id = ?) AS anon_1
2018-04-30 20:14:20,275 INFO sqlalchemy.engine.base.Engine (u'sf_incidents_clean', u'', 3)
2018-04-30 20:14:20,275:INFO:sqlalchemy.engine.base.Engine:(u'sf_incidents_clean', u'', 3)
2018-04-30 20:14:20,275:INFO:root:Database.get_sqla_engine(). Masked URL: dremio+pyodbc://malsmith:XXXXXXXXXX@mydremio
2018-04-30 20:14:20,562 INFO sqlalchemy.engine.base.Engine ROLLBACK
2018-04-30 20:14:20,562:INFO:sqlalchemy.engine.base.Engine:ROLLBACK
2018-04-30 20:14:20,564:INFO:werkzeug:127.0.0.1 - - [30/Apr/2018 20:14:20] "POST /tablemodelview/add HTTP/1.1" 302 -
@xrmx

This comment has been minimized.

Copy link
Contributor

@xrmx xrmx commented May 1, 2018

You may want to raise debugging level to see more interesting things. Superset works perfectly fine with multiple databases. It looks like it's just matter of ironing out the compatibility with dremio.

@mistercrunch

This comment has been minimized.

Copy link
Member

@mistercrunch mistercrunch commented May 1, 2018

I think you're misinterpreting the logs. Superset does not require writing to target databases. SELECT EXISTS is against your Superset metadata database as it should as it tries to add a new entry on the Superset side to store the table's metadata from Dremio.

My guess it that the call that tries to extract the metadata from Dremio here
https://github.com/apache/incubator-superset/blob/master/superset/models/core.py#L809
isn't working properly in the dialect.

Some database don't have a direct notion or schema, or sometimes have 2 levels of namespacing (Postgres has database and schema/owner) and that doesn't always fits the SQLAlchemy model perfectly. Dialects implementation have to be careful to line things up properly.

@tomasienrbc

This comment has been minimized.

Copy link

@tomasienrbc tomasienrbc commented Jun 23, 2018

@malsmith how did you connect Dremio to Superset? Trying to do the same and don't see it supported, was considering trying to use the half baked Apache Drill connected for SQL Alchemy possibly but would love to see what you're doing.

@jkgenser

This comment has been minimized.

Copy link

@jkgenser jkgenser commented Jul 1, 2018

@malsmith I'm very interested in connecting Dremio to Superset as well! Any advice welcome

@zero0nee

This comment has been minimized.

Copy link

@zero0nee zero0nee commented Jul 15, 2018

@malsmith Same here! Would love any advice. 👍

@mistercrunch

This comment has been minimized.

Copy link
Member

@mistercrunch mistercrunch commented Jul 15, 2018

Looks like Dremio has an ODBC driver and a SQLAlchemy dialect (https://github.com/sqggles/sqlalchemy_dremio), which means something like: dremio+pyodbc://malsmith:XXXXXXXXXX@mydremio may work.

You may have to add a new entry to configure Dremio in superset/db_engines_sepc.py, and depending on the maturity of the SQLA dialect you may need to tweak that as well.

@tomasienrbc

This comment has been minimized.

Copy link

@tomasienrbc tomasienrbc commented Jul 15, 2018

@mistercrunch thanks Maxime! I'm going to try to get the SQL Alchemy dialect working, the team that built it abandoned the project BUT I think left it in a working or semi working state. If anyone else would rather try it out please let me know, I'm only able to work on this on the weekends but if I get it working I'll @ everyone who was interested here

@mistercrunch

This comment has been minimized.

Copy link
Member

@mistercrunch mistercrunch commented Jul 16, 2018

Writing/tuning a SQLAlchemy dialect shouldn't be too hard, good luck!

@mgoel1190

This comment has been minimized.

Copy link

@mgoel1190 mgoel1190 commented Sep 25, 2018

@mistercrunch thanks Maxime! I'm going to try to get the SQL Alchemy dialect working, the team that built it abandoned the project BUT I think left it in a working or semi working state. If anyone else would rather try it out please let me know, I'm only able to work on this on the weekends but if I get it working I'll @ everyone who was interested here

I started working on connecting Dremio to Apache Superset. Want to check if you were able to connect it using SQLAlchemy dialect. Any advice will be helpful as I am new to Superset & dremio.

@tomasienrbc

This comment has been minimized.

Copy link

@tomasienrbc tomasienrbc commented Sep 25, 2018

@mgoel1190 and @mistercrunch - I'm on another thread with someone who has revived the abandoned project and gotten it working, will @ him in here once I know his Github handle (he emailed me directly)

@tomasienrbc

This comment has been minimized.

Copy link

@tomasienrbc tomasienrbc commented Sep 25, 2018

Ah! It's @timkessler

@mgoel1190

This comment has been minimized.

Copy link

@mgoel1190 mgoel1190 commented Sep 25, 2018

@mgoel1190 and @mistercrunch - I'm on another thread with someone who has revived the abandoned project and gotten it working, will @ him in here once I know his Github handle (he emailed me directly)

@tomasienrbc Thank you for your quick reply. Will check with Tim.

@mgoel1190

This comment has been minimized.

Copy link

@mgoel1190 mgoel1190 commented Sep 25, 2018

@timkessler I am very much interested in connecting Dremio with Apache superset using SQL alchemy dremio dialect. Can you please provide any guidance on how to do it.
Is this repo https://github.com/timkessler/incubator-superset-dremio includes SQL alchemy dremio dialect in superset?

@timkessler

This comment has been minimized.

Copy link

@timkessler timkessler commented Sep 26, 2018

@mgoel1190 I just started that repo this week. I haven't had time to commit changes yet. so, it's just a copy of of incubator-superset presently. For now, you can take a look here at my notes on how I got it working: #4192

@stale

This comment has been minimized.

Copy link

@stale stale bot commented Apr 10, 2019

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

@stale stale bot added the inactive label Apr 10, 2019
@stale stale bot closed this Apr 17, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
8 participants
You can’t perform that action at this time.