embulkで本番環境のDBから分析用のDBに匿名化しつつ移動する

embulk

分析用DBに日々

  • 顧客データ
  • マスタ
  • アクセスログ(独自フォーマットのhttpdログ)
  • 広告レポート(CSV)

などなどのデータを取り込んでいる。この取り込みで2つ問題があって、1つ目はこれらを取り込む方法がシェルスクリプト(mysqldump -> 分析環境にscp -> インポート)だったり、更新頻度の低いマスタ系は手動で取り込んだり、CakePHPのシェルで実装されていたり…とバリエーションに富んでいること。つらい。2つ目は、顧客データはmysqldumpで取り込んでいるけど、加工が終わるまでの短期間とはいえ個人情報や機微情報が含まれる瞬間がある。分析に使うことはないし見るべきじゃないし見たくない。ここら辺をembulkでなんとか解決してやろうとしている。

1つ目の問題は愚直にymlを書けばいいので置いておく。2つ目の問題をどう解決したか、未解決な問題は何かをメモしとく。

検証環境

  • CentOS 7.3 on Vagrant
    • mariadb 10.2
    • embulk 0.8.29
      • embulk-input-mysql 0.8.5
      • embulk-filter-eval 0.1.0
      • embulk-filter-hash 0.3.2
      • embulk-output-mysql 0.7.11

サンプルのテーブル、データ

-- 元データ
CREATE DATABASE src DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE src.users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  birthday DATE NOT NULL,
  gender TINYINT NOT NULL,
  tel VARCHAR(30),
  email VARCHAR(200),
  prefecture VARCHAR(4) NOT NULL,
  city VARCHAR(100) NOT NULL,
  address1 VARCHAR(100) NOT NULL,
  address2 VARCHAR(100),
  PRIMARY KEY (id)
)
Engine=InnoDB
;
INSERT INTO src.users (name, birthday, gender, tel, email, prefecture, city, address1, address2) VALUES
('荻野　胡桃', '1985-06-22', 2, '090-2997-6880', 'iogino@efaj.mwg',         '三重県',  '桑名市',    '森忠4-10', NULL),
('金谷　蒼衣', '1969-06-18', 2, NULL,            'aoi6726@nisjeofue.yjd',   '福岡県',  '宗像市',    '朝野1-4-6', 'グランド朝野405 '),
('矢口　正昭', '1971-09-25', 1, '089-002-4488',  'iyaguchi@kcblusladp.mlr', '愛媛県',  '宇和島市',  '三間町黒井地1-13', 'プレイス三間町黒井地409'),
('千葉　絵理', '1976-02-23', 2, '0304112396',    NULL,                      '東京都',  '新宿区',    '二十騎町1-17-5', NULL);

-- 取り込み先
CREATE DATABASE dest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE dest.users (
  id INT NOT NULL,
  birthday DATE NOT NULL,
  gender TINYINT NOT NULL,
  tel VARCHAR(64),
  email VARCHAR(64),
  prefecture VARCHAR(4) NOT NULL,
  city VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
)
Engine=InnoDB
;

※データは疑似個人情報データ生成サービスで生成

作ったファイル

users.yml.liquid

in:
  type: mysql
  host: 127.0.0.1
  user: {{ env.SRC_USER }}
  password: {{ env.SRC_PASSWORD }}
  database: src
  table: users
  select: id, birthday, gender, tel, email, prefecture, city
  column_options:
    birthday: { type: string, timestamp_format: '%Y-%m-%d', timezone: '+0900' }

filters:
  - type: eval
    eval_columns:
      - tel: "value.nil? ? nil : value.gsub(/-/, '')"

  - type: hash
    columns:
    - { name: tel, algorythm: SHA_256 }
    - { name: email, algorythm: SHA_256 }
 
out:
  type: mysql
  host: 127.0.0.1
  user: {{ env.DEST_USER }}
  password: {{ env.DEST_PASSWORD }}
  database: dest
  table: users
  mode: truncate_insert
  options: { characterEncoding: UTF-8 }

何をやったか

  • embulk-input-mysqlのselectに必要なカラムのみ列挙
    • 不要なカラムは取り込み先テーブルには用意しない
  • embulk-filter-hashで、一意性だけ保てればいいカラムのハッシュ化
    • tel / emailは他テーブルと結合条件になる場合があるだけ
    • telはハイフンあり/なしが混在しているためembulk-filter-evalでハイフン除去
      • eval便利だけどなんでもできちゃうので避けたいが
    • SHA-256でハッシュ化すると64文字になるので、取り込み先の当該カラムはサイズを変更

何ができていないか

  • birthdayを匿名化したい
    • ageであれば、例えば10歳刻みの年齢層に置き換えれば良さそうだけど
    • 同一年度内の別日にしたりするのだろうか…不勉強ゆえわからない
  • ハッシュ化する際にsaltつけたい

ハマったこと

embulk-output-mysqlで出力したら日本語が?に化けた。
これはMySQL Connector/Jの仕様によるところで、https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html に

To allow multiple character sets to be sent from the client, use the UTF-8 encoding, either by configuring utf8 as the default server character set, or by configuring the JDBC driver to use UTF-8 through the characterEncoding property.

とある通り、UTF-8を扱うのであればcharacter_set_serverがutf8となっているか、ドライバーのプロパティでcharacterEncoding=UTF-8としてやる必要がある。 前者であれば、/etc/my.cnf.d/server.confあたりに

[server]
character_set_server=utf8

と書いてmariadbを再起動すればいいし、後者であればembulk-output-mysqlのoptionsで{ characterEncoding: UTF-8 }とすればいい。

今後やること

参考