LoginSignup
0
0

More than 3 years have passed since last update.

@Recuerdame(Aiko Kishi)

CentOS7でSupersetのDBをsqliteからMySQLにお引越し

Posted at

経緯

CentOS7にSupersetを入れて、可視化していた。
Superset設定格納DBにMySQLを使っていたつもりがsqliteを使っていたので乗り換えた。
その手順を日本語で解説してるサイトが無かったのとアジア語特有の落とし穴があったのでメモ

読者前提

CentosにSuperset入れてそのまま使ってた人
可視化ツール使いたいだけでサーバコマンドは苦手な人

所要時間

30分くらい？
Superset単品用途でDBを作って使ってた人(デフォルト設定)は
特に難しくない

手順

（前提：参考にしたのはこのサイト
https://medium.com/@acmicrox/superset-database-migration-from-sqlite-to-mysql-c882e5ab9349）

目次
1.MySQLを入れてDBとユーザを作る
2.sqliteのDBから再構築用SQLを作る
3.Superset_config.pyファイルを作って設定
4.MySQL上でdb upgradeしてSQL実行
5.SqliteとMySQLでデータ比較
6.Superset設定再読み込み
7.Supersetの稼働確認

1.MySQLを入れてDBとユーザを作る

元々MySQL入ってる人はそのままで良い。

1-1.MySQL公式サイト↓よりLinux 7版のrpm名を見つけてコピー

OS名の下にある、mysql*.rpmがそれです。
https://dev.mysql.com/downloads/repo/yum/

1-2.rpmファイルのダウンロード

$ yum localinstall http://dev.mysql.com/get/さっきコピーしたrpm名

1-3.rpmファイルの展開

$ yum install -y mysql-community-server

1-4.自動起動設定して起動

$ systemctl enable mysqld
$ systemctl start mysqld

1-5.ユーザ登録してログイン

デフォルトパスワードを取得

$ cat /var/log/mysqld.log | grep 'temporary password' | awk -F ': ' '{print $NF}'

ログイン。パスワードを聞かれるので、一つ前で取得したパスワードを入力

$ mysql -u root -p

mysqlが起動できたら、パスワードを変更
ついでに権限付与、DB作成、文字コードの確認。

> SET PASSWORD = PASSWORD('8文字以上英大小文字数字記号込のパスワード')
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'   IDENTIFIED BY '↑と同じパスワード' WITH GRANT OPTION;
> SHOW VARIABLES LIKE "chara%";

文字コードは、Supersetを扱うときは下記の2つ以外はutf8に統一するのが無難。
character_set_filesystem
character_sets_dir

utf8mb4ではdb upgradeがエラーになった。
(が、文字コードが原因とは書いてないので突き止めるのに時間かかった)

1-6.文字コード変える(必要な人だけ)

NGの人はmysqlから一旦出る

> quit;

mysqlの設定ファイルを書き換える

$ vi /etc/my.cnf

以下をファイルの末尾に追記。
大文字G入力でファイル末尾に移動、$で行末尾に移動、iで入力モードにして
Enterで改行し、↓の1行を必要なだけコピペ追加。
character_1-5でutfじゃなかったやつ=utf8

で、追加と編集し終わったら、Escキー押して:キー押したらwqと入力してEnterでファイル閉じる。
そして↑で追記した内容をrestartでmysqldに反映して起動。

$ systemctl restart mysqld 
$ mysql

1-7.DBを作る

ここでトチ狂ってutf8mb4で作ってはいけない。
superset db upgradeがtoo long keyのエラーになり実行できなくなる。
またfabmanagerでユーザを作ってしまうとあとあとalembic_versionのエラーになり詰まるので自分では作らないこと。

>CREATE DATABASE superset CHARACTER SET utf8 COLLATE utf8_bin;
>quit;

2.sqliteのDBから再構築用SQLを作る

2-1.sqliteからSQLを作成

必要に応じて# locate superset.dbで、sqliteのsuperset.dbの位置を確認。
ほにゃらら/.superset/superset.dbになってるはず。
なので↓のコマンドでsqlを作る。この時点のsqlはsqlite仕様。

$ sqlite3 ほにゃらら/.superset/superset.db .dump > superset_statements.sql

2-2.SQLをMySQL仕様に編集する。
先ほど作ったsqlを編集する。

$ vi superset_statements.sql

↓の5文を1行ずつ実行する

:%s/PRAGMA foreign_keys=OFF;/SET FOREIGN_KEY_CHECKS = 0;/g
:%s/BEGIN TRANSACTION;/START RANSACTION;/g
:%s/INSERT INTO "/INSERT INTO /
:%s/" VALUES (/ VALUES (/g
:%s/\\"/\\\\"/g

CREATE *のSQLは、superset自身に正しく構築させるため
sqlから削除する。INSERTだけになるようにする。
↓の文でfindを行い、表示された行をDeleteで消す。

/CREATE

上方向検索なら?CREATE,次を探すならnキー、前を探すならNキーで探せる。

SQLが準備できました。

3.Superset_config.pyファイルを作って設定

デフォルトではsuperset_config.pyは存在しない。
元々のsuperset/config.pyは直接いじっちゃいけない。
設定を上書きするには作ってファイルの在りかをsupersetにわかる変数に設定してあげる必要がある。
一旦↓ではusr/localに置いてるけど、何処でもいい。

$ vi /usr/local/superset_config.py

iで編集モードにして
以下の1文を設定する。mysql://ユーザ名:パスワード@ホスト名/データベース名の表記ルール。

SQLALCHEMY_DATABASE_URI = 'mysql://root:1-5で作ったパスワード@localhost/superset'

書き終えたら、Escキー→：キー→wq記入→Enterキーでファイルを閉じ、
supersetの分かる変数にファイルの置き場所を指定してあげる。
PYTHONPATHという変数なら分かるとのこと。

$ export PYTHONPATH="/usr/local/:$PYTHONPATH"

またサーバ再起動ごとに設定する手間を省くため、
起動時に読み込む設定を記載する.bash_profileファイルに変数を追記する。

$ vi ~/.bash_profile

iキー→末尾に行を追加し↓の１文をコピペ→Escキー→：キー→wq記入→Enterキーでファイルを閉じる

export PYTHONPATH="/usr/local/:$PYTHONPATH"

4.MySQL上でdb upgradeしてSQL実行

cd ほにゃららでsupersetを起動して↓のコマンドを実行する
configファイルから読み込み直してDBを整備してくれる。

$ superset db upgrade

普通は10秒くらいで終わる。私は最初に文字コードを間違えエラーになった。→末尾のエラー1参照
次に↓を実行。

$ cd
$ mysql -h ホスト名 -u root -p -D superset < superset_statements.sql

普通はうまくいくらしい。私はエラーになった。→末尾のエラー２参照

5.SqliteとMySQLでデータ比較

Sqliteから全テーブル全行の値を抽出しMySQLと比較する。

5-1.件数比較。飛ばしてデータ比較でも良い。

$mysql superset
>select 'テーブル名',count(*) from テーブル名 UNION
...;
>select 'テーブル名',count(*) from テーブル名;

で取得。

>quit;

sqliteは

$sqlite3 ほにゃらら/.superset/supserset.db
>mysqlで実行したcountSQLと同じ物を実行

>.quit

で、比較する。一致していれば完璧。差分が有れば原因を突き止め、想定内かを確認
数が合っていてもデータの項目との配置ズレが起きていたら動かない。
なので、面倒でもデータ差分を確認する。

5-2.データを比較

書き疲れたのでちょっと待って。。
あとで編集します

6.Superset設定再読み込み

systemctlでsupersetを管理している人は↓でリスタート。

$ systemctl restart superset

そうでない人はcdでsupersetの実行環境に入り↓を実行

$superset run server -d

7.Supersetの稼働確認

supersetのグラフが正しく表示されていることを確認する。
データの数や内容は、sql上で確認しているので描画されるか、だけ確認しておけばいい。

Appendix.陥ったエラー群書き溜めメモ

エラー1:superset db upgradeでToo long keyというエラーが出る

mysqlの文字型が問題と思われる。
supersetに使うdbについてはutf8mb4は諦めて、
既存との共存ではなく新dbをCREATEするといい。CREATE時の設定は本文参照。

作っちゃったのを変えたい人は、面倒でも↓のsqlを作って実行するか、
中に入ってるデータを退避してdbを再作成する。再作成の方がおすすめ。

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE table_name CONVERT TO character SET utf8 COLLATE utf8_bin;
ALTER TABLE table_name CHANGE column_name column_name 型桁 CHARACTER SET utf8 COLLATE utf8_bin;

もちろん、テーブルalter文はテーブルの分だけ、alter項目文は項目の分だけ作る。

エラー２：sqlの実行で項目数が合わないというエラーが出る

sqliteとMySQLのSupersetメタデータ用テーブルのレイアウトを比較。

1.MYSQLから取得

$ mysql superset

>select TABLE_NAME, COLUMN_NAME, Null, Key, Default from information_schema.COLUMNS where TABLE_SCHEMA = 'projectdb';

2.sqliteから取得

$sqlite3 ほにゃらら/.superset/superset.db

>.schema

3.出てきた内容をExcelなりGoogleスプレッドシートなりでテーブル名でkey合わせして比較してみる。
4.出てきた差分に合わせてINSERT SQLを改修してやる。
　例えばvi superset_statement.sqlで↓のようなコマンドを実行する。

:%s/INSERT INTO テーブル名 VALUES/INSERT INTO テーブル名 (項目名群) VALUES/g

普通はここで転ぶことはないらしい、が、
Superset v0.28.0利用者の私は転んだ。
sliceテーブルのdatasource_idとslice_idの項目がMySQL側に表示されているが
Sqlite側に無い・・Null許可やKey設定、SupersetPGMのPythonコードを見る限り
datasource_idもslice_idもNullで良さげ。
なので、INSERT文へINSERT項目名を載せ直して再実行した。
OSS使うと、こういう謎な動作をある程度自分で解析せねばならんところが面倒。
ん？いやOSSだけじゃねぇな。製品でも同じだな。よくバグ引いてベンダーとギャアギャァ喧嘩してたな。
頻度の違いだな。OSSはまだ内部構造が自分で見えるぶん直せるが、製品は詰む。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0