将superset的元数据库从sqlite修改为mysql

在使用superset过程中，查看superset看板时经常会出现报错sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked，要多次刷新才能成功查看到看板，在网上查了一下原因，说是sqlite的问题，切换为mysql后不再出现这个问题，所以决定直接切换superset元数据库为mysql。

测试环境

Superset 0.35.1
MySQL 5.7.28
Navicat Premium 12.0.19

导出sqlite中的数据

1.停止superset，使停止向superset.db写入数据（文件位置~/.superset/superset.db），然后将superset.db从安装superset的linux服务器取到本地磁盘。
2.使用Navicat连接Sqlite数据库，参考网址如下：
https://jingyan.baidu.com/article/9989c7463e223ef649ecfe77.html
3.若不预先处理表字段值中的字符\，则后续导入数据到mysql会出现问题，所以找出可能存在字符\的表，先替换为@#$#@（自定义的字符串，不会与数据内容重复即可），后面导入mysql后再替换回来。
4.dashboards表position_json字段和json_metadata字段，keyvalue表value字段，slices表params字段，tables表sql字段，另外query表和logs表也有含有字符\的字段。但是query表是记录的superset SQL Lab的查询历史，不包含保存的查询，保存的查询在saved_query表中，logs是日志数据，我都不导入，所以query表和logs表不进行处理。
5.在sqlite数据库执行命令进行替换：

update dashboards set position_json=replace(position_json,'\','@#$#@'),json_metadata=replace(json_metadata,'\','@#$#@');
update keyvalue set value=replace(value,'\','@#$#@');
update slices set params=replace(params,'\','@#$#@');
update tables set sql=replace(sql,'\','@#$#@');

注意由于mysql与sqite的差异，数据导入mysql后替换回来的sql语句中要使用\\，具体sql语句见后续步骤。
6.使用navicate导出向导，导出SQL脚本文件（点击全选，去除logs表和query表，然后点击全选右侧三角符号，选择导出选择的到相同文件夹），总共37个*.sql文件。
7.将文件上传到mysql客户端所在的linux服务器上（后续会使用source命令导入数据到mysql），执行命令grep -o '\\' /export/* |wc -l，验证字符\是否被替换完了，若结果是0则表示替换完了。

修改导出的*.sql文件

1.因为sqlite3与mysql语法不同，因此直接导入*.sql文件会报错，需要处理一下*.sql文件，对比mysql的命令和sqlite3的命令如下：

-- sqlite3：
INSERT INTO "ab_permission"("id", "name") VALUES (1, 'can_this_form_post');
-- mysql：
INSERT INTO `ab_permission`(`id`, `name`) VALUES (1, 'can_this_form_post');

可以看到两个数据库的insert into命令有一定区别，根据区别（由于query、saved_query、tables等表中有schema等作为字段名，所以INSERT INTO语句中的`符号不能省略），将sqlite3的insert into命令调整为与mysql一致。
2.调整方法如下：
将INSERT INTO “ab_permission”(“id”, “name”) VALUES (替换为INSERT INTO `ab_permission`(`id`, `name`) VALUES (，使用sed命令进行替换，例如将aaa替换为bbb使用：sed -i 's/aaa/bbb/g' test.sql，这里使用命令（注意执行命令前将中文单引号替换为英文单引号，将中文双引号替换为英文双引号，其他地方的命令若有，也按此处理，后续不再说明）：sed -i ‘s/INSERT INTO “ab_permission”(“id”, “name”) VALUES (/INSERT INTO `ab_permission`(`id`, `name`) VALUES (/g’ /export/ab_permission.sql
同理，处理其他36张表，若有*.sql文件为空，则无需进行替换操作。使用命令head -n 1 /export/*，查看*.sql文件，观察是否替换成功。

创建mysql数据库及初始化表结构

1.创建mysql数据库

CREATE DATABASE superset DEFAULT CHARACTER SET utf8;

2.在superset所在的linux服务器上安装mysqlclient，以支持修改superset 数据库配置，若superset安装在linux虚拟环境，则mysqlclient也应该安装到虚拟环境。
在线安装：pip install --default-timeout=100 -i https://mirrors.aliyun.com/pypi/simple mysqlclient
或者离线安装：
先下载mysqlclient-2.0.3.tar.gz（下载地址：https://pypi.org/），上传到目录/home/superset/packages/下，然后执行命令
pip install --no-index --find-links=/home/superset/packages/ mysqlclient
3.修改superset配置文件config.py中的数据库连接地址（文件位置：lib/python3.7/site-packages/superset/config.py），修改前先备份文件。
把下面一行注释掉：

SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(DATA_DIR, 'superset.db')

增加一行配置：xxxx 为密码、IP为IP地址，superset为创建的mysql数据库名

SQLALCHEMY_DATABASE_URI = 'mysql://root:xxxx@IP/superset?charset=utf8'

4.进入安装superset的虚拟环境source venv/bin/activate，使用 superset db upgrade 初始化mysql数据表结构。
5.查看mysql中数据库superset的表，发现ab_role表和alembic_version有数据，清空这两张表：

use superset;
delete from ab_role;
delete from alembic_version;

导入数据到mysql

注意执行source命令导入某些表有可能会报错，可能的报错请看第2步。
1.开启终端模拟器软件（例如：xshell、securecrt等）的日志记录，用于后面查看执行命令后是否有ERROR输出，再执行下面的命令：

-- linux服务器上登录mysql
mysql -uroot -p
-- 禁用外键约束
SET FOREIGN_KEY_CHECKS=0;
-- 查看外键约束状态
SELECT @@FOREIGN_KEY_CHECKS;
use superset;
-- 这里的字符集与将要导入的数据库的字符集一致
set names utf8; 
-- 导入数据前修改数据类型，具体请看第2步
alter table slices modify params longtext;
-- 导入数据，依次导入所有表的数据，并观察是否有ERROR。
source /export/ab_permission.sql; 
source /export/ab_permission_view_role.sql;
...
-- 启用外键约束MySQL
SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;
-- 退出mysql
exit

2.可能会出现的报错
我在执行source命令时，观察到导入slices表、metrics表时有报错如下：
（1）slices表：
ERROR 1406 (22001): Data too long for column ‘params’ at row 1
解决办法：修改数据类型为longtext：
具体解决步骤：

-- 若已经执行过source /export/slices.sql;才需要删除数据
delete from slices;
alter table slices modify params longtext;
-- 重新导入数据
source /export/slices.sql;

（2）metrics表：
ERROR 1062 (23000):Duplicate entry ‘max__usedABCct-325’ for key ‘uq_metrics_metric_name’
查看metrics建表语句，发现有唯一约束UNIQUE KEY `uq_metrics_metric_name` (`metric_name`,`datasource_id`)，经过观察datasource_id对应的数据源确实存在相同的指标名metric_name：max__usedABCct和max__UsedABCct，虽然它们的大小写并不完全相同。
解决办法：不处理报错，最后superset启动后，到相应数据源去修改指标。
3.数据导入mysql后将@#$#@替换回\，sql语句中要使用\\，如下：

update dashboards set position_json=replace(position_json,'@#$#@','\\'),json_metadata=replace(json_metadata,'@#$#@','\\');
update keyvalue set value=replace(value,'@#$#@','\\');
update slices set params=replace(params,'@#$#@','\\');
update `tables` set `sql`=replace(`sql`,'@#$#@','\\');

4.启动superset，访问superset，验证superset是否正常。

其他

上文已经完成了superset的元数据库切换，但是由于步骤较多，可以取生产环境的superset.db文件到测试环境进行处理，然后导出测试环境mysql的superset数据库，导入生产环境的mysql即可，具体步骤如下：
1.停止生产环境的superset，取superset.db文件到测试环境。
2.测试环境按照上文完成处理，停止测试环境的superest，执行命令mysqldump -uroot -ppassword superset > superset.sql导出测试环境mysql的superset数据库，将导出的superset.sql文件上传到生产环境的mysql服务器上。
3.生产环境上执行上文创建mysql数据库及初始化表结构的1 2 3步。
4.生产环境执行命令mysql -uroot -ppassword superset < superset.sql导入数据，启动superset，验证superset是否正常。

参考网址

https://blog.csdn.net/sinat_26809255/article/details/108258630
https://blog.csdn.net/weixin_33815613/article/details/88678725
SQL中的替换函数replace()使用
https://www.cnblogs.com/martinzhang/p/3301224.html
Sqlite数据库字符串处理函数replace
https://www.cnblogs.com/huangtailang/p/5cfbd242cae2bcc929c81c266d0c875b.html

如果对你有帮助请点赞支持，如果文中有错误或疑问请评论指正，谢谢。