EmbulkでSQL ServerからMySQLにデータを移行する


業務でSQL ServerのデータをMySQLに移行する必要があったため、復習も兼ねて記事にしました。
実行環境はDockerで構築しています。
リポジトリは[こちら](https://github.com/pae26/test-embulk)。

# 動作確認
まず実際に動かしてみて流れをつかんでおきます。
↓やりたいことはこんな感じ
1. docker-compose実行でSQL ServerとMySQLを初期化（SQLServerには移行対象のデータがinsertされており、MySQLにはデータは存在しません）
2. docker build & docker runでembulkを実行し、MySQLにデータを移行できているか確認する

#### docker-compose実行
まずdocker-composeを実行して、テーブルがちゃんとセットアップできているか確認します。
```shell
# DBのセットアップ 
$ docker-compose up -d

Creating network "migrate-network" with the default driver
Creating volume "test-embulk_sqlserver" with default driver
Creating volume "test-embulk_mysql" with default driver
Creating migrate_mysql     ... done
Creating migrate_sqlserver ... done

# 立ち上げたコンテナを確認
$ docker-compose ps
      Name                    Command             State                 Ports              
-------------------------------------------------------------------------------------------
migrate_mysql       docker-entrypoint.sh mysqld   Up      0.0.0.0:3306->3306/tcp, 33060/tcp
migrate_sqlserver   /init/entrypoint.sh           Up      0.0.0.0:1433->1433/tcp
```
`exec`でテーブルを確認します。
まずはMySQLから見ていきましょう。

```shell
# コンテナに入る
$ docker exec -it migrate_mysql /bin/bash

# MySQL接続
bash-4.2# mysql -uuser_mysql -ppassword_mysql db_mysql

# テーブル確認
> show tables;
+--------------------+
| Tables_in_db_mysql |
+--------------------+
| users_mysql        |
+--------------------+
1 row in set (0.00 sec)

# データ確認
> select * from users_mysql;
Empty set (0.01 sec)
```
まだ移行前なのでデータは入っていませんね。
次はSQL Serverを見てみます。

```shell
# コンテナに入る
$ d exec -it migrate_sqlserver /bin/bash

# SQL Server接続
root@351027:/# /opt/mssql-tools/bin/sqlcmd -U SA -P Passw0rd -S 127.0.0.1

# DB選択
1> use db_sqlserver;
2> go
Changed database context to 'db_sqlserver'.

# データ確認
1> select * from users_sqlserver;
2> go
id_sqlserver name_sqlserver                 gender_sqlserver
------------ ------------------------------ ----------------
           1 hoge                                          1
           2 fuga                                          3
           3 piyo                                          2

(3 rows affected)

```
移行対象のデータが入っています。
ちなみにSQL Serverでは`gender`を数値で表現していますが、今回はこれを`male`、`female`、`others`という文字列に変換して移行してみます。
DBのセットアップが確認できたところで、Embulkでデータ移行を行っていきます。

#### Embulkでデータ移行
Embulkを実行した後、MySQLのデータを確認してみます。
```shell
# Embulk実行
$ docker build -t test-embulk ./migrate --no-cache
$ docker run --network migrate-network --env-file ./migrate/env.local -t test-embulk

# MySQLのデータを確認
$  mysql -uuser_mysql -ppassword_mysql db_mysql
> select * from users_mysql;
+----------+------------+--------------+
| id_mysql | name_mysql | gender_mysql |
+----------+------------+--------------+
|        1 | hoge       | male         |
|        2 | fuga       | others       |
|        3 | piyo       | female       |
+----------+------------+--------------+
3 rows in set (0.00 sec)
```
無事移行できました！
`gender`もちゃんと文字列に変換されてますね。
では簡単にファイルの中身を見ていきましょう。

# ディレクトリ構造
```
test-embulk
├── docker
│   ├── mysql
│   │   ├── init
│   │   │   └── init.sql
│   │   └── my.cnf
│   └── sqlserver
│       └── init
│           ├── entrypoint.sh
│           └── init.sql
├── docker-compose.yml
└── migrate
    ├── Dockerfile
    ├── entrypoint.sh
    ├── env.local
    └── migrate-sqlserver-to-mysql.yml.liquid
```

## DBのセットアップ
#### docker-compose.yml
SQL ServerとMySQLのボリュームや環境変数を定義しています。
SQL Serverのパスワードは`password`とかだと[パスワードポリシー](https://learn.microsoft.com/ja-jp/sql/relational-databases/security/password-policy?view=sql-server-ver16#:~:text=%E3%82%8C%E3%81%BE%E3%81%9B%E3%82%93%E3%80%82-,%E3%83%91%E3%82%B9%E3%83%AF%E3%83%BC%E3%83%89%E3%81%AE%E8%A4%87%E9%9B%91%E6%80%A7,-%E3%83%91%E3%82%B9%E3%83%AF%E3%83%BC%E3%83%89%E3%81%AE%E8%A4%87%E9%9B%91)に引っかかってしまうので注意が必要です。
```yaml:docker-compose.yml
version: "3.8"
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: migrate_sqlserver
    volumes:
      - ./docker/sqlserver/init:/init
      - sqlserver:/var/opt/mssql/data
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=Passw0rd
      - TZ=Asia/Tokyo
    networks:
      - migrate-network
    ports:
      - 1433:1433
    entrypoint: /init/entrypoint.sh
  
  mysql:
    image: mysql:5.7.41
    container_name: migrate_mysql
    volumes:
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./docker/mysql/init:/docker-entrypoint-initdb.d
      - mysql:/var/lib/mysql
    tmpfs:
      - /var/lib/mysql,mode=755
      - /var/log/mysql,mode=755  
    environment:
      - MYSQL_DATABASE=db_mysql
      - MYSQL_USER=user_mysql
      - MYSQL_PASSWORD=password_mysql
      - MYSQL_ROOT_PASSWORD=root_password_mysql
      - TZ=Asia/Tokyo
    networks:
      - migrate-network
    ports:
      - 3306:3306

networks:
  migrate-network:
    name: migrate-network

volumes:
  sqlserver:
  mysql:
```

#### MySQLセットアップ
簡単なテーブルを`CREATE`しています。
INSERTはしていないので中身は空です。
```sql:mysql/init/init.sql
CREATE TABLE `users_mysql` (
    `id_mysql` INT PRIMARY KEY,
    `name_mysql` VARCHAR(100) NOT NULL,
    `gender_mysql` ENUM('male', 'female', 'others') NOT NULL
)
```

```
+--------------+--------------------------------+------+-----+---------+-------+
| Field        | Type                           | Null | Key | Default | Extra |
+--------------+--------------------------------+------+-----+---------+-------+
| id_mysql     | int(11)                        | NO   | PRI | NULL    |       |
| name_mysql   | varchar(100)                   | NO   |     | NULL    |       |
| gender_mysql | enum('male','female','others') | NO   |     | NULL    |       |
+--------------+--------------------------------+------+-----+---------+-------+
```


#### SQL Serverセットアップ
MySQLと同様、簡単なテーブルをCREATEしています。
こちらは移行用のデータをいくつかINSRETしています。
```sql:docker/sqlserver/init/init.sql
USE master
GO

CREATE DATABASE db_sqlserver
GO

USE db_sqlserver
GO

CREATE TABLE users_sqlserver(
    id_sqlserver INT NOT NULL,
    name_sqlserver VARCHAR(30) NOT NULL,
    gender_sqlserver INT NOT NULL
) ON [PRIMARY]
GO

INSERT INTO users_sqlserver(id_sqlserver, name_sqlserver, gender_sqlserver) VALUES(1, 'hoge', 1)
INSERT INTO users_sqlserver(id_sqlserver, name_sqlserver, gender_sqlserver) VALUES(2, 'fuga', 3)
INSERT INTO users_sqlserver(id_sqlserver, name_sqlserver, gender_sqlserver) VALUES(3, 'piyo', 2)
GO
```

また、MySQLとは異なりシェルスクリプトで初期化する必要があるため、以下の`entrypoint.sh`を`docker-compose up`時に実行しています。
```sh:docker/sqlserver/init/entrypoint.sh
#!/bin/bash
/opt/mssql/bin/sqlservr &
pid=$!

echo "SQL Serverをセットアップします"
command="/opt/mssql-tools/bin/sqlcmd -U SA -P ${MSSQL_SA_PASSWORD} -S 127.0.0.1 -i <(cat /init/init.sql)"
until eval "$command"; do
    echo "SQL Serverを起動しています..."
    sleep 1
done

echo "SQL Serverを停止します $pid"
kill $pid
wait $pid
exec /opt/mssql/bin/sqlservr
```

#### Embulkの設定
まずはDockerfileから見ていきます。
`embulk gem`で`embulk-input-sqlserver`プラグインと`embulk-output-mysql`プラグインをインストールしています。
こちらでもコンテナ起動時にentrypoint.shを実行していますね。
```dockerfile
FROM openjdk:8

RUN curl -o /usr/local/bin/embulk --create-dirs -L "https://dl.embulk.org/embulk-latest.jar" && \
    chmod +x /usr/local/bin/embulk

RUN embulk gem install embulk-input-sqlserver
RUN embulk gem install embulk-output-mysql

RUN apt update && apt install -y mariadb-client && rm -rf /var/lib/apt/lists/*

WORKDIR /root

COPY migrate-sqlserver-to-mysql.yml.liquid .
COPY entrypoint.sh .

CMD [ "./entrypoint.sh" ]
```

```sh:migrate/entrypoint.sh
#!/bin/bash
/usr/local/bin/embulk run migrate-sqlserver-to-mysql.yml.liquid
```

そしてEmbulk実行時に渡しているファイルが本記事の主人公、`migrate-sqlserver-to-mysql.yml.liquid`です。

```yaml:migrate/migrate-sqlserver-to-mysql.yml.liquid
in:
  type: sqlserver
  host: '{{env.SQLSERVER_HOST}}'
  port: '{{env.SQLSERVER_PORT}}'
  user: '{{env.SQLSERVER_USER}}'
  password: '{{env.SQLSERVER_PASSWORD}}'
  database: '{{env.SQLSERVER_DB}}'
  query: |
    SELECT
      id_sqlserver AS id_mysql,
      name_sqlserver AS name_mysql,
      (SELECT CASE gender_sqlserver
      WHEN 1 THEN 'male'
      WHEN 2 THEN 'female'
      WHEN 3 THEN 'others'
      END) AS gender_mysql
    FROM
      users_sqlserver
out:
  type: mysql
  host: '{{env.MYSQL_HOST}}'
  port: '{{env.MYSQL_PORT}}'
  user: '{{env.MYSQL_USER}}'
  password: '{{env.MYSQL_PASSWORD}}'
  database: '{{env.MYSQL_DB}}'
  table: users_mysql
  column_options:
    id_mysql:
      type: int
    name_mysql:
      type: VARCHAR(100)
    gender_mysql:
      type: ENUM('male', 'female', 'others')
  options: {connectTimeout: 20000}
  mode: insert
```
sqlserverで抽出したカラムをAS句でmysqlのカラムに対応させることでEmbulkが移行データを判別します。
`gender_sqlserver`カラムに関しては、CASE句を使ってINTからmale、female、othersに変換しています。

このように移行元とは異なるカラム名や型であっても、簡単に変換して移行先に格納することができます。