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

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

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

前提

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

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

embulkの構成

基本的には、liquidファイルを使って、同じ設定などの共通化をしています。
詳しくは、これとか、これを見ていただけますと。

liquidファイルの構成

├── playlife
│   ├── Gemfile
│   ├── Gemfile.lock
│   ├── _filters.yml.liquid
│   ├── _in.yml.liquid
│   ├── _out.yml.liquid
│   ├── src
│   └── vendor
└── playlife_import.yml.liquid

使っているGems

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だけでやる

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

# 
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の説明を見ると...

オプションの指定について、

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

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

create_table_constraintの使い方

例えば、

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;

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

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`)#

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

:
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。

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）
とはいえ、必ずしも事前にテーブル定義まで設定することがベストとは限らないので、適宜、使い分けて行こうかと思います。