Increase superset userbase by allowing upload csv out of the box

[mrinterestfull]

Currently there is no way to upload csv and start analyzing using superset with default installation, even by changing one config file.

• I think feature of csv analysis is 'out of the box' with every Business intelligence software: tableau, powerbi
• I think allowing csv manipulation/xlsx should work as well in superset. Superset will get immediately a boost of new users talking and using superset to analyze data.
• I think right now the alternative is use libreoffice or download paid version of tableau (no linux support).

To make this feature available with ease we have one optimal options:

1. Allow user to add a new mydata.db by going to cd ~/.superset && sqlite3 mydata.db (.quit to exit)
   and modify setting that currently prevents user from connecting to sqlite and make it default. (optimal)

An error occurred while fetching databases: {"sqlalchemy_uri":["SQLiteDialect_pysqlite cannot be used as a data source for security reasons."]}
set:
PREVENT_UNSAFE_DB_CONNECTIONS = False

Why this is optimal solution:

• Production professionals will be managing database connections, they don't need to be handhold by the setting.
• Security prevents you from using superset on your local machine. It's up to the user to select if he wants to connect to database, so forcing secure connection for first time users removes 100s of new users causing the project to not grow.
• It's a double-standard. It's ok for superset to use 'not secure database', but its not ok for users? aka superset.db is by default categorized as unsecure, and does not prevent config changes in UI. I think outcome should be that as long as superset allows sqlite3 superset.db then superset should also allow user to add new database that are also file system sqllite based, and edit their settings.
• We are missing out on whole new set of functionality that https://litestream.io/getting-started/ provides with sqlite3
• These changes would prevent user like myself spending 3hours just to get here on 1st install. The effort of getting superset to explore csv is not worth the time needed to come up with working solution.
• UI for upload csv and xls is already built, but this settings handicaps the power of superset.

Not optimal solutions:
2. Allow main database "superset.db" to allow uploads. (not optimal)
3. create a new database mydata.db and make it default to upload csv. (not optimal)(in production this would mean we need to remove this database on each install)

[rusackas]

This does require having some sort of datasource connected (i.e. a database), to which you would upload these CSV resources. While Superset does ship with a metadata database (for storing internal objects, e.g. dashboards/charts), it does not ship with a default analytical database. Typically it'd be an anti-pattern to overload the metadata database as an analytical database, and typically anyone deploying superset will have their own business reasons to choose from a whole range of possible analytical databases.

So that's essentially why this isn't a default thing... it would add to the maintenance footprint, as well as just being a bit of a sidecar that most people would likely override with their own DB anyway, which puts us back in the current state of affairs.

That said, there are commercial offerings of Superset (e.g. Preset) that do include such a feature - Preset lets you upload a CSV directly upon signup, for exactly the reasons you propose here, namely that it's the first thing many users do to "kick the tires" on the product.

[betodealmeida]

I totally agree, this is a big friction point in getting people to use Superset.

At Preset we did two things to improve this, but both are not trivial to set up:

1. I created a pseudo-database that stores files encrypted in S3. When a CSV file is uploaded we convert it to Parquet, upload to S3, and give it a table name. Users can then query those tables via S3 Select using a modified version of this Shillelagh adapter. To the user the experience is the same as using a database: they write queries like SELECT * FROM table, and can even drop tables if they want (and the file gets deleted from S3).
2. I recently added file uploading capabilities to the GSheets DB engine spec: feat(gsheets): file upload #24921. Unfortunately this requires setting up a service account, which is very complicated. Once we implement [SIP-85] OAuth2 for databases #20300 it should be a much smoother experience, requiring the user to just add a GSheets database before they can upload files.

Additionally, I have a PR open (since April 2021) to create a "native Superset database": #14225. I wonder if this could become a standard database that is installed by default, and it could be used to handle file uploads, in addition to other functionality (like connecting Superset to APIs, like Google Calendar).

These are all overengineered (and complex) solutions, though. I think a good solution would be to have the default installation contain 2 SQLite databases: examples and a database where users can upload CSVs. That would be easy to implement, though we'd have to change the default value of PREVENT_UNSAFE_DB_CONNECTIONS, which might be controversial. I'll give it some thought if we can make SQLite more secure, so that we can have it on by default.

[sebastianliebscher]

I'm just guessing and might be completely wrong. Why not just define another PostgreSQL database service in docker-compose-non-dev.yml (as a user-friendly default) and automatically add it as a database entry in Superset? This could then serve as an ad-hoc db for CSV uploads and the LOAD_EXAMPLES data. If this is documented properly, a user can easily decide during initial setup if they want to keep this default db or remove it from the compose file.

[sfirke]

This sounds great to me and to the point made by @sebastianliebscher in #25546 we could also put the examples in that separate Postgres database, in the existing Postgres container. Like @dpgaspar asked, I'm not sure what that means for users doing pip install.

I'd be fine with having this superior (IMO) experience for docker compose even if we couldn't match it for pip, as long as it didn't introduce excessive maintenance load. And if we could use a new Postgres db to solve this problem, it would also enable #25546 which would be a 2-for-1.

Edited upon realizing we wouldn't need a 2nd Postgres container, just a 2nd db & user in that same container.

[dpgaspar]

I agree this can potentially be a friction point, I personally like @betodealmeida reference to Gsheets file upload and future smooth/easy OAuth2 databases setup. @sebastianliebscher proposal is an easy win, I guess it depends on how most of our users do their first Superset install, do they mainly use pip install or docker-compose-non-dev ?

One of the main problems of allowing SQLLite db's is that they allow users to create a database that is a file on the web server, this can potentially cause multiple problems some identified some not identified. We do aim for a Secure by default Superset.

[mrinterestfull]

I'm most concern with expansion of superset usage. Goal: Bring another 10,000 new users of superset within 6 months. User story: Lucas S. Has received import data in CSV. He wants to install superset on his Linux desktop, and analyze the data. He wants to be ready to analyze within 5 min. He understand that if data is valuable he will refer to production installation guide to setup production ready version. He wants out of the box working system that is open source and has a lot of features, rather then closed. To accomplish this: I think current default of sqllite is sufficient just need write access enabled. If the analytics part also need database,I think sqllite is also sufficient. Thanks Lucas

…

On Fri, Oct 6, 2023, 6:21 PM Sam Firke ***@***.***> wrote: This sounds great to me and to the point made by @sebastianliebscher <https://github.com/sebastianliebscher> in #25546 <#25546> we could also put the examples in that separate Postgres container. Like @dpgaspar <https://github.com/dpgaspar> asked, I'm not sure what that means for users doing pip install. I'd be fine with having this superior (IMO) experience for docker compose even if we couldn't match it for pip, as long as it didn't introduce excessive maintenance load. And if we could use a new container to solve this problem, it would also enable #25446 <#25446> which would be a 2-for-1. — Reply to this email directly, view it on GitHub <#23399 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAL4YEKDHQZQMDJW334NC2DX6CHABAVCNFSM6AAAAAAV5RN73OVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TEMJUGIZTQ> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[sfirke]

Relevant to this thread: the Superset community voted to remove support for SQLite as a metadata database, this is scheduled to happen at the next major release (4.0). But I think @lszyba1 your goal of .csv upload supported out-of-the-box is just as possible without SQLite.

At that point everyone installing Superset will have to use a more robust metadata DB. That won't change with docker-compose, the fastest way to get up and running; it already uses a Postgres container. I guess for pip install deployment, folks will need to supply a Postgres or MySQL database, either running on the same machine or elsewhere?

Either way, given that requirement, could we just put examples in a separate DB in the same service as the metadata DB? It could hold examples and accept ad-hoc .csv uploads.

[mrinterestfull]

Hello. Thanks. Can you send me to the vote details. Yes, I don't necessarily advocating sqllite, but I do think requiring CSV write and building analysis on a file out of the box is a must. I also don't think docer requirements are inline. I think installing superset via pip install or brew install should be level 1. Installing docer with Postgres level 2. Installing app, separate server for database, HIPPA, Must,CO IT, PII, compliant installation level 3. I don't think we need to combine levels of security and therefore sacrifice product's ease of use. If we can separate the level /use cases or even better add a new parameter during installation or configuration that turns on level 1 out of box analysis and CSV writting capabilities that could work as well. Thanks Lucas

…

On Mon, Oct 9, 2023, 8:28 AM Sam Firke ***@***.***> wrote: Relevant to this thread: the Superset community voted to remove support for SQLite as a metadata database <#8874>, this is scheduled to happen at the next major release <https://github.com/orgs/apache/projects/292/views/1?pane=issue&itemId=40454778> (4.0). But I think @lszyba1 <https://github.com/lszyba1> your goal of .csv upload supported out-of-the-box is just as possible without SQLite. At that point everyone installing Superset will have to use a more robust metadata DB. That won't change with docker-compose, the fastest way to get up and running; it already uses a Postgres container. I guess for pip install deployment, folks will need to supply a Postgres or MySQL database, either running on the same machine or elsewhere? Either way, given that requirement, could we just put examples in a separate DB in the same service as the metadata DB? It could hold examples and accept ad-hoc .csv uploads. — Reply to this email directly, view it on GitHub <#23399 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAL4YEJBO34FGTAVCRM5KQDX6P3Y3AVCNFSM6AAAAAAV5RN73OVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TEMZQGUZDC> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[sfirke]

Sure the votes are here: https://lists.apache.org/[email protected]:2020-1:sip-33 There was minimal discussion.

I appreciate your perspective on pip install being the easiest way to install. Right now there's no documentation here about creating a database: https://superset.apache.org/docs/installation/installing-superset-from-scratch/ Is that because this method creates a SQLite database and handles that for the user? In which case we are going to want some documentation on that page re: how to configure the DB, maybe providing a sample script the way e.g., OpenMetadata does.

[mrinterestfull]

Hello I want to be upfront. The choice of database for me is indifferent but "easy to use" for a user who might be: "aspiring data scientist, aspiring programmer, or just a medium advanced data user" needs to be at level1 = easy. If we/superset can provide a writable database out of the box, then that solves the problem because all features work out of the box. I went over the reason in #8874 <#8874> which really comes down to people who are serious about superset will start with sqlite and grow and never move to a proper database causing more issues during upgrades. I agree, if you are serious maybe increasing entries to the proper database is correct to make sure you don't fail later. So how can we allow users to use superset in "easy" mode. pip install superset, done Maybe that page: https://superset.apache.org/docs/installation/installing-superset-from-scratch/ should be modified right now to use postgre, and one should be able to configure superset in under 4min with helper commands? Sometimes when you can't solve a problem, we can use "Charlie Munger" brain trick and ask the reverse question: Mental exercise here: How can we make sure superset doesn't grow in userbase?: - Make the process of installation so complicated that more than 33% of users fail to get it up and running. - Make the process so that even if they install it, not all features are enabled, and they need to spend hours to figure out how. - Make sure the process requires sudo, or requires company admin to approve the installation. - Not allowing him to control his environment. (Offer a magic in a box solution similar to docker, where for sure a user that is serious will not know how it was configured, nor how it works.) - Not provide easy steps to go from "demo to prod v1, to 55 users to 144, to 233 users). How do we solve this so that we can gain 10,000 more users in 6 months? It seems to me if SIP33 fastest incremental value to end user would be to add documentation on : I can't speak of sqllite support as I don't have enough info, but maybe if everyone one has gone through "how to get started" and followed proper database setup, then there would be no need to remove it. (So who has a postgre requirement? and what can we learn from them during installation? (wordpress?)) - Under installing from scratch <https://superset.apache.org/docs/installation/installing-superset-from-scratch> modify section to talk about using postgre <https://superset.apache.org/docs/databases/db-connection-ui> So I guess the process might look like : 1. pip install superset 2. decide what database you want...follow docs [here]: or pick our recommended postgre for new users. 3. Give superset your selection of database you will use. 4. enter admin username/password in our installation process. (or refer to quick docs how to install postgre on linux for first time users) Thanks Lucas

…

On Tue, Oct 10, 2023 at 9:39 AM Sam Firke ***@***.***> wrote: Sure the votes are here: ***@***.***:2020-1:sip-33 There was minimal discussion. I appreciate your perspective on pip install being the easiest way to install. Right now there's no documentation here about creating a database: https://superset.apache.org/docs/installation/installing-superset-from-scratch/ Is that because this method creates a SQLite database and handles that for the user? In which case we are going to want some documentation on that page re: how to configure the DB, maybe providing a sample script the way e.g., OpenMetadata does <https://github.com/open-metadata/OpenMetadata/blob/main/docker/postgresql/postgres-script.sql> . — Reply to this email directly, view it on GitHub <#23399 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAL4YEKUBOSQW6VPRDQPUVDX6VMZJAVCNFSM6AAAAAAV5RN73OVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TENBSGAZDK> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- http://wwwhww.news/u/cwSNkeLNXf <http://lucasmanual.com/blog/>