embulk-output-mysqlを使ったymlの設定だけで主キーやインデックスを追加する方法

完全フルリモートで、自宅で鬼筋トレしたり、晴れの日はテント出したり、割と引きこもり生活を楽しんでいるプレイライフのエンジニアの合原です。

昨晩、久々（？）にembulkを使って`mysqlへデータ投入時、特定のカラムをPKにしたり、インデックス貼ったり`といったことができることに今更ながら気づいたので、まとめます。
（ドキュメント読めば書いてあるのですが、まぁ、わかりずらい...ので、まとめた次第です）


# 前提
下記は既に済みであることを想定して話を進めます。

* embulkインストール
* mysqlインストール

# embulkの構成
基本的には、liquidファイルを使って、同じ設定などの共通化をしています。
詳しくは、[これ](https://qiita.com/hiroysato/items/e7987e71a8112584b748#%E3%81%BE%E3%81%A8%E3%82%81)とか、[これ](https://qiita.com/play-life/items/c194405512c4cc0fcbdd#rails%E7%92%B0%E5%A2%83%E4%B8%8B%E3%81%A7%E4%BD%BF%E3%81%86embulk%E3%81%AE%E6%A7%8B%E6%88%90)を見ていただけますと。

## liquidファイルの構成
```sh
├── playlife
│   ├── Gemfile
│   ├── Gemfile.lock
│   ├── _filters.yml.liquid
│   ├── _in.yml.liquid
│   ├── _out.yml.liquid
│   ├── src
│   └── vendor
└── playlife_import.yml.liquid
```

## 使っているGems
```sh
source 'https://rubygems.org/'

gem 'embulk'

gem 'embulk-parser-jsonl'
gem 'embulk-filter-add_time'
gem 'embulk-filter-ruby_proc'
gem 'embulk-filter-column'
gem 'embulk-filter-eval'
gem 'embulk-formatter-sprintf'
gem 'embulk-output-mysql' # 今回は主にこれの使い方の話です
```

### それまでは...
ちょっと話がずれますが、それまでは、embulkでmysqlにデータ投入する際は、事前にターゲットとなるテーブルを作成してから行っていました。
つまり、事前に`primary key`や`index key`の設定をした上でのインポートを行っていました。
..がこれそもそも、embulkでできないか？確かいろいろオプションあったはず。。。
ということでやってみました。

# やりたいこと
下記のようなテーブル作成＆データ投入をembulkだけでやる

```sql
CREATE TABLE `target_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_no` bigint(20) NOT NULL COMMENT 'ホテルID',#
  `hotel_name` varchar(256) COLLATE utf8mb4_bin NOT NULL,#
  :
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`), #
  UNIQUE KEY `index_hoge` (`hotel_no`),# 
  KEY `index_shop_name_on_hoge_hotel` (`hotel_name`),#
  KEY `index_telephone_on_hoge_hotel` (`telephone_no`)#
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
```

上記の`#`のある各種カラムの定義やインデックスキーの設定をどうやるのか...？？

# create_table_constraint等オプションを使えばいい
もう下記が答えそのものなのですが、create_table_constraint等オプションを下記のように使うとできます。

> 例 `_out.yml
※[embulk-output-mysql](https://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-mysql)


```yml
# 
out:
  type: mysql
  host: {{ db_host }}
  database: {{ db_name }}
  user: {{ db_user }}
  password: {% if env.RAILS_ENV == 'development' %} '' {% else %}{{ db_password }}{% endif %}
  table: target_table
  options: {connectTimeout: 20000}
  mode: replace
  options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}
  default_timezone: '+0900'
  create_table_constraint: 'PRIMARY KEY (`id`),UNIQUE KEY `index_affiliate_id_on_target_table` (`id`),KEY `index_name_on_target_table` (`hotel_name`),KEY `index_telephone_on_target_table` (`telephone_no`)'
  create_table_option: 'auto_increment=1'
  column_options:
    id: { type: 'int(11) NOT NULL AUTO_INCREMENT' }
    hotel_no: { type: 'bigint(20) NOT NULL COMMENT "ここにコメント設定できます"' }
    plan_url: { type: 'text COLLATE utf8mb4_bin' }
    dp_plan_list_url: { type: 'text COLLATE utf8mb4_bin' }
    :
    latitude: { type: 'double DEFAULT NULL' }
    longitude: { type: 'double DEFAULT NULL' }
    address1: { type: 'text COLLATE utf8mb4_bin' }
    address2: { type: 'text COLLATE utf8mb4_bin' }
    telephone_no: { type: 'varchar(256) COLLATE utf8mb4_bin NOT NULL' }
    created_at: { value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S'}
    updated_at: { value_type: string, timestamp_format: '%Y-%m-%d %H:%M:%S'}
```

上記の通り、キモは`create_table_constraint`です。
ここにプライマリーキーやインデックスの設定が可能です。

## ちなみに、公式の説明だけだとわかりづらい

[embulk-output-mysql](https://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-mysql)の説明を見ると...

オプションの指定について、
<img width="916" alt="スクリーンショット 2020-04-07 11.19.00.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/578823/8d25f1c8-64e9-5246-c041-e1d705d907d6.png">

とあるだけで、実にわかりづらい。。。

具体的には、上記のキーの設定はどう設定するんだ？って感じでした...
結果的にわかったのが、`普通のテーブル定義の内容をそのまま文字列で設定すればいいだけ`でした。実にわかりづらい...（わかる人にはわかるんですかね？

# create_table_constraintの使い方

例えば、

```sql
CREATE TABLE `target_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_no` bigint(20) NOT NULL COMMENT 'ホテルID',#
  `hotel_name` varchar(256) COLLATE utf8mb4_bin NOT NULL,#
  :
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`), #
  UNIQUE KEY `index_affiliate_id_on_hoge_travel_hotel` (`hotel_no`),# 
  KEY `index_shop_name_on_hoge_travel_hotel` (`hotel_name`),#
  KEY `index_telephone_on_hoge_travel_hotel` (`telephone_no`)#
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
```

という、テーブル定義の場合、

```sql
PRIMARY KEY (`id`), #
UNIQUE KEY `index_affiliate_id_on_hoge_travel_hotel` (`hotel_no`),# 
KEY `index_shop_name_on_hoge_travel_hotel` (`hotel_name`),#
KEY `index_telephone_on_hoge_travel_hotel` (`telephone_no`)#
```

をデータインポート時に設定するには、

```yml
:
create_table_constraint: 'PRIMARY KEY (`id`),UNIQUE KEY `index_affiliate_id_on_target_table` (`id`),KEY `index_name_on_target_table` (`hotel_name`),KEY `index_telephone_on_target_table` (`telephone_no`)'
:
```

とすればできます。

# 各種カラムの型やNOT NULL制約も付けたい場合
`column_options`に下記のように設定すればOK。

```yml
column_options:
  id: { type: 'int(11) NOT NULL AUTO_INCREMENT' }
  hotel_no: { type: 'bigint(20) NOT NULL COMMENT "ここにコメント設定できます"' }
  :
```

と言った感じです。

# つまり、普通に`文字列で定義すればいい`
`事前にテーブル作ったテーブル定義（show create tableした結果とか）から、上述のように、
create_table_constraintやcolumn_options
にそのまま、文字列としてコピペしていけばいい`ってことです。
置換すればいいだけなので、割とすぐできるかと思います。

# まとめ
ドキュメントだけだとわかりづらかったので、今回、個人的に大きな発見でした。
これでテーブル定義も`yml`に設定を集約できるので、コード管理もしやすくなりそうですし、ETL基盤等あるなら、なおのこと。（うちはないですがw）
とはいえ、必ずしも事前にテーブル定義まで設定することがベストとは限らないので、適宜、使い分けて行こうかと思います。