Importing Ashley Madison CreditCardTransactions CSVs to MySQL

アシュレイ・マディソンのクレジットカード取引CSV を MySQL にインポートする

各CSVの頭にあるヘッダはこうなっている。カード番号は、下4桁のみ。

Each CSV files have the following header. It contains last 4-digits of credit card number.

ACCOUNT,ACCOUNT NAME,AMOUNT,AUTH CODE,AVS,BRAND,CARD ENDING,CVD,FIRST NAME,LAST NAME,MERCHANT TRANS. ID,OPTION CODE,DATE,TXN ID,CONF. NO.,ERROR CODE,AUTH TYPE,TYPE,TXT_CITY%2CTXT_COUNTRY%2CTXT_EMAIL%2CTXT_PHONE%2CTXT_STATE%2CTXT_ADDR1%2CTXT_ADDR2%2CZIP%2CCONSUMER_IP,

これに対応したテーブルを作る。フィールド長がわからないので、適当に varchar(255) にしておく。インデックスも、使いそうなフィールドに適当に付けておく。

I created the corresponding table below. Each field length is unknown, so use varchar(255). Additionally, I added some indexes to maybe useful fields.

drop table if exists creditcard_transactions;
create table creditcard_transactions (
  account integer,
  account_name varchar(255),
  amount float,
  auth_code varchar(255),
  avs varchar(255),
  brand varchar(255),
  card_ending  varchar(255),
  cvd  varchar(255),
  first_name varchar(255),
  last_name varchar(255),
  merchant_trans_id varchar(255),
  option_code varchar(255),
  date datetime,
  txn_id varchar(255),
  conf_no varchar(255),
  error_code varchar(255),
  auth_type varchar(255),
  type varchar(255),
  txt_city varchar(255),
  txt_country varchar(255),
  txt_email varchar(255),
  txt_phone varchar(255),
  txt_state varchar(255),
  txt_addr1 varchar(255),
  txt_addr2 varchar(255),
  zip varchar(255),
  consumer_ip varchar(255),
  index (account),
  index (account_name),
  index (brand),
  index (first_name),
  index (last_name),
  index (date),
  index (auth_type),
  index (type),
  index (txt_city),
  index (txt_country),
  index (txt_email),
  index (txt_phone),
  index (txt_state),
  index (txt_addr1),
  index (txt_addr2),
  index (zip),
  index (consumer_ip)
) default character set=utf8;

mysqlimport コマンドでインポートする。ただしCSVファイルの数が多いので、スクリプトにする。mysqlimport は、CSVファイル名とテーブル名が一致することを求めるから、ln でシンボリックリンクを張りながら 1つひとつインポートしていく。

You can import these CSV files by mysqlimport command. But you have a large number of CSV files. So I created a tiny shell script. mysqlimport requires that the file name of CSV file and the table name are the same. I use symbolic links to meet to this restriction.

#!/bin/sh

for f in *_downloaded.csv
do
  echo $f
  ln -sf $f creditcard_transactions.csv
  mysqlimport \
    --ignore-lines=3 \
    --fields-terminated-by=, \
    --fields-optionally-enclosed-by='"' \
    --columns='account,account_name,amount,auth_code,avs,brand,card_ending,cvd,first_name,last_name,merchant_trans_id,option_code,date,txn_id,conf_no,error_code,auth_type,type,txt_city,txt_country,txt_email,txt_phone,txt_state,txt_addr1,txt_addr2,zip,consumer_ip' \
    --local \
    -uroot \
    creditcard \
    /home/horiuchi/data/dmps/CreditCardTransactions/creditcard_transactions.csv
done

AWS の m4.large に MySQL をインストールしてデフォルトの設定のままで上記を実行したときに、確か5-6時間かかっている。なお、分析に使わないフィールドは update をかけて消しておきましょう。。。

If I remember correctly, it took 5-6 hours on AWS m4.large instance on which I installed MySQL using default configuration. I recommend you to run UPDATE SQL and remove unnecessary fields.