Changing from Sqlite to MySQL

[giliredbaron]

Hi all

I'm trying to migrate my local install of superset (that comes by default with sqlite) to MySQL.
I was trying to convert the sqlite data like so:
sqlite3 .superset/superset.db .dump > superset_statements.sql
But, some of the values in the generated script are faulty, like this:
CREATE TABLE IF NOT EXISTS "query" ( id INTEGER NOT NULL, client_id VARCHAR(11) NOT NULL, database_id INTEGER NOT NULL, tmp_table_name VARCHAR(256), tab_name VARCHAR(256), sql_editor_id VARCHAR(256), user_id INTEGER, status VARCHAR(16), schema VARCHAR(256),

So, CREATE TABLE IF NOT EXISTS "query" is wrong because it needs to be like this:
CREATE TABLE IF NOT EXISTS query
And
schema VARCHAR(256),
Scema is a preserved word so I need to change it to schema VARCHAR(256),`

It looks like this is a deep rabbit hole (there are so many of those, also the generated script creates insert values to tables that are created later on this script - this is not working :) )

Is there a better way to install (even from scratch) OR convert superset to use MySQL?

Thanks
Gili

[dosubot[bot]]

Shalom @giliredbaron 🌟 I'm here to help you with your Superset migration to MySQL. Let's work together to figure this out!

To convert Superset to use MySQL instead of SQLite, you should update the SQLALCHEMY_DATABASE_URI in your superset_config.py file to use a MySQL connection string. Here is how you can do it:

1. Install the MySQL client package:

   pip install mysqlclient

2. Update the SQLALCHEMY_DATABASE_URI in your superset_config.py file with the MySQL connection string:

   SQLALCHEMY_DATABASE_URI = 'mysql://<UserName>:<DBPassword>@<Database Host>/<Database Name>'

3. If you are using Docker, ensure that the mysqlclient library is installed in the Superset Docker container. You can do this by adding mysqlclient to a requirements-local.txt file in the docker subdirectory where your docker-compose.yml file is located. This file will be used to install the necessary packages into the container at runtime:

   echo "mysqlclient" >> ./docker/requirements-local.txt

4. Launch Superset with Docker Compose:

   docker compose -f docker-compose-non-dev.yml up

This change will configure Superset to use MySQL as the metadata database instead of SQLite. Make sure to replace <UserName>, <DBPassword>, <Database Host>, and <Database Name> with your actual MySQL database credentials and details. If you encounter any issues, you can troubleshoot by checking the Docker logs for error messages ^[1][2].

To continue talking to Dosu, mention @dosu.