Superset database migration: from SQLite to MySQL

Introduction

Superset is a popular data exploration and visualization web application, originally developed by Airbnb and being an Apache Incubator project now. Superset supports a variety of databases and ships bundled with a SQLite database, superset.db. Because superset.db is connected by Superset by default, it’s common to run the Superset server with superset.db at the beginning. However, as Superset becomes more important, you shall consider to migrate the local SQLite database to a MySQL cluster to to avoid potential data loss. For this data migration, two requirements must be met.

1. Superset works with the new MySQL database.
2. All data in superset.db are completely dumped to the new MySQL database.

Migration procedure

Although SQLite and MySQL are two extremely popular database systems, it seems that no free conversion tool is available to directly dump the data from a SQLite database to a MySQL database. Below is a feasible migration procedure of switching from a SQLite database to a MySQL database.

Dump the data of superset.db to corresponding SQL statements.

SQLite has provided a built-in command dump to make this work easy. With the sqlite3 superset.db .dump > superset_statements.sql, the superset_statements.sql contains all the SQL statements to rebuild superset.db. The superset_statements.sql looks like below.

Transform SQLite-compatible statements to MySQL-compatible statements.

Due to some syntax difference between SQLite and MySQL, it may not be able to directly use the dumped SQL statements of a SQLite database to create a corresponding MySQL database. In view of this, we need to make the following changes in superset_statements.sql for MySQl-compatibility.

• Change PRAGMA foreign_keys=OFF; to SET FOREIGN_KEY_CHECKS = 0; and BEGIN TRANSACTION; to START RANSACTION;.
• Remove double quotes of the table field in insert statements. For example, change INSERT INTO "user" VALUES (1, "userA"); to INSERT INTO user VALUES (1, "userA");.
• Run sed -i ‘/\\"/\\\\"/g' superset_sqls.sql for JSON strings with \".
• Remove table creation statements. Because of the syntax difference in table creation, I choose not to modify the table creation statements. Instead, I create the new MySQL database first (CREATE DATABASE superset CHARACTER SET utf8 COLLATE utf8_bin ) and then use Superset command to create the tables (superset db upgrade && superset load_examples). If you’re a MySQL newbie like me, remember to specify COLLATE utf8_bin when creating the database for case-sensitive storage.

Load the data to the new MySQL database via MySQL-compatible statements

With the new database and SQL statements, it is easy to load the data by running mysql -h <mysql_endpoint> -u <username> -p -D superset < superset_statements.sql where mysql is a mysql-client command. When the data has been populated into the new database, you can write a script to verify that each table in MySQL database superset and the corresponding table in superset.db has the same record count. If all go right, modify the configuration file config.py for Superset to make it connect to the new MySQL database and then run the superset server superset runserver -d to do the final confirmation.

Summary

If you’re experienced with SQLite and MySQL, data migration from SQLite to MySQL shall be easy. If you’re a newbie like me, hope this article is helpful.