Page view statistics for WikimediaをMySQLにインポートする

MySQLのベンチマークをとるときには巨大なデータセットが必要になります。
[mysqlslap](https://dev.mysql.com/doc/refman/5.6/ja/mysqlslap.html)を何回か実行して、データを作ってもいいのですがいちいち作るのも手間なので既存のデータを探してみたところ、Perconaのブログで[Sample datasets for benchmarking and testing](https://www.percona.com/blog/2011/02/01/sample-datasets-for-benchmarking-and-testing/)というそのものズバリなエントリが。


# Page view statistics for Wikimedia
軽く読んでみたところ、記事に載っているデータセットのうち[Page view statistics for Wikimedia](http://dumps.wikimedia.org/other/pagecounts-raw/)がなかなか取り回しが良さそうな感じなので、MySQLにインポートしてみることに。

「Page view statistics for Wikimedia」はその名の通り、Wikipediaとその関連プロジェクトのページビューを１時間ごとに集計したファイルです。

![](https://qiita-image-store.s3.amazonaws.com/0/20100/a1efa44c-2f26-0259-e470-1b7d12e14df2.png)

ファイルの中身は

```
fr.b Special:Recherche/Achille_Baraguey_d%5C%27Hilliers 1 624
fr.b Special:Recherche/Acteurs_et_actrices_N 1 739
fr.b Special:Recherche/Agrippa_d/%27Aubign%C3%A9 1 743
fr.b Special:Recherche/All_Mixed_Up 1 730
fr.b Special:Recherche/Andr%C3%A9_Gazut.html 1 737
```

のような感じで、右から「project name」「title」「number of requests」「size of the content」のフィールドが空白区切りで並んでいます。

# 手動でインポート

まず、インポートするテーブルを作ります。

```sql
CREATE TABLE `page_view_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  `project_name` varchar(255) NOT NULL,
  `title` text NOT NULL,
  `requests` int(11) NOT NULL,
  `size` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```

そして、適当なファイルをダウンロードして

```bash
wget http://dumps.wikimedia.org/other/pagecounts-raw/2015/2015-12/pagecounts-20151201-000000.gz
```

解凍して先頭に日時を付加。

```bash
gunzip pagecounts-20151201-000000.gz
sed -i.bak 's/^/2015-01-01-00 /' pagecounts-20151201-000000
```

[mysqlimport](https://dev.mysql.com/doc/refman/5.6/ja/mysqlimport.html)でインポート。

```bash
mv pagecounts-20151201-000000 page_view_statistics.txt
mysqlimport -u root -L --fields-terminated-by ' ' -c time,project_name,title,requests,size wikimedia page_view_statistics.txt
```

```rb
mysql> show table status \G
*************************** 1. row ***************************
           Name: page_view_statistics
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6432695
 Avg_row_length: 114
    Data_length: 738197504
Max_data_length: 0
   Index_length: 99237888
      Data_free: 7340032
 Auto_increment: 6815641
    Create_time: 2015-12-20 12:21:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
```

# wikimedia-pagecounts-import

１時間単位の集計なので結構なファイル数になるので、手間を省くためインポート用のスクリプトを作りました。（mysqlimportを使っているため、ローカルのDBにしかインポートできません）

```rb
#!/usr/bin/env ruby
require 'optparse'
require 'optparse/time'
require 'tmpdir'
require 'net/http'
require 'uri'
require 'open3'
require 'shellwords'

Version = '1.0.0'

DOWNLOAD_URL = 'http://dumps.wikimedia.org/other/pagecounts-raw/%<year>04d/%<year>04d-%<month>02d/pagecounts-%<year>04d%<month>02d%<day>02d-%<hour>02d00%<sec>02d.gz'

DATABASE = 'wikimedia'
CREATE_DATABASE = "CREATE DATABASE IF NOT EXISTS `#{DATABASE}` DEFAULT CHARACTER SET utf8"

TABLE = 'page_view_statistics'
CREATE_TABLE = <<-EOS
CREATE TABLE `#{TABLE}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  `project_name` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `requests` int(11) NOT NULL,
  `size` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOS

COLUMNS = 'time,project_name,title,requests,size'

options = {
  host: 'localhost',
  user: 'root',
}

ARGV.options do |opt|
  begin
    #opt.on('-h', '--host HOST')          {|v| options[:host] = v     }
    #opt.on('-P', '--port PORT', Integer) {|v| options[:port] = v     }
    opt.on('-u', '--user USER')          {|v| options[:user] = v     }
    opt.on('-p', '--password PASSWORD')  {|v| options[:password] = v }
    opt.on('-s', '--start DATE', Time)   {|v| options[:start] = v    }
    opt.on('-e', '--end DATE', Time)     {|v| options[:end] = v      }
    opt.on('-t', '--trunc')              {    options[:trunc] = true  }
    opt.parse!

    if options.values_at(:host, :user, :start).any?(&:nil?)
      puts opt.help
      exit 1
    end
  rescue => e
    $stderr.puts(e.message)
    exit 1
  end
end

options[:end] ||= options[:start]

def run(cmd, args)
  cmd_with_args = args.reject {|arg|
    if arg.is_a?(Array)
      arg.last.nil?
    else
      arg.nil?
    end
  }.flat_map {|arg|
    Array(arg).map(&:to_s)
  }

  cmd_with_args.unshift(cmd.to_s)
  cmd_with_args = Shellwords.join(cmd_with_args)
  out, err, status = Open3.capture3(cmd_with_args)
  raise err unless status.success?

  unless out.empty?
    out = out.each_line.map {|i| i.chomp.split("\t") }
    head = out.shift
    out = out.map {|i| Hash[head.zip(i)] }
  end

  [out, err]
end

def mysql(sql, options)
  args = [
     '-A',
    ['-h', options[:host]],
    ['-P', options[:port]],
    ['-u', options[:user]],
    ['-e', sql],
    options[:database],
  ]

  args << "-p#{options[:password]}" if options[:password]

  run(:mysql, args)
end

def mysqlimport(path, options)
  args = [
    ['-u', options[:user]],
    ['-c', COLUMNS],
    ['--fields-terminated-by', ' '],
    DATABASE,
    path,
  ]

  args << "-p#{options[:password]}" if options[:password]

  run(:mysqlimport, args)
end

def download(url)
  url = URI.parse(url)

  Net::HTTP.start(url.host, url.port) {|http|
    http.get(url.path)
  }
end

def proc_file(time, offset = 0, &block)
  time += offset
  url = DOWNLOAD_URL % {year: time.year, month: time.month, day: time.day, hour: time.hour, sec: time.sec}
  file = File.basename(url)

  Dir.mktmpdir do |dir|
    File.chmod(0777, dir)
    path = File.join(dir, file)
    puts "Downloading: #{url}"
    res = download(url)

    case res
    when Net::HTTPSuccess
      open(path, 'wb') {|f| f << res.body }
      block.call(dir, file)
    when Net::HTTPNotFound
      if offset >= 60
        res.value
      else
        puts 'Not Found'
        proc_file(time + 1, &block)
      end
    else
      res.value
    end
  end
end

def create_database(options)
  mysql(CREATE_DATABASE, options)
end

def create_table(options)
  options = options.merge(database: DATABASE)

  if options[:trunc]
    mysql("DROP TABLE IF EXISTS `#{TABLE}`", options)
  end

  out, err = mysql(<<-EOS, options)
    SELECT COUNT(1) AS cnt FROM INFORMATION_SCHEMA.TABLES
      WHERE table_schema = '#{DATABASE}'
        AND table_name = '#{TABLE}'
  EOS

  count = out.first['cnt'].to_i

  if count.zero?
    mysql(CREATE_TABLE, options)
  end
end

def init_database(options)
  puts 'Initializing database'
  create_database(options)
  create_table(options)
end

def main(options)
  init_database(options)

  start_time = options[:start]
  end_time = options[:end]

  (start_time.to_i..end_time.to_i).step(3600) do |time|
    time = Time.at(time)

    proc_file(time) do |dir, file|
      puts "Importing: #{file}"

      orig_file = File.join(dir, file)
      import_file = File.join(dir, "#{TABLE}.txt")

      run(:gunzip, [orig_file])
      orig_file.gsub!(/\.gz\z/, '')

      run(:sed, ['-i.bak', "s/^/#{time.strftime('%Y-%m-%d-%H:00:00')} /", orig_file])

      File.rename(orig_file, import_file)

      mysqlimport(import_file, options)
    end
  end
end

main(options)
```

とりあえず`2014/01/01 00:00:00`〜`2014/01/01 05:00:00`までのデータをインポートしてみます。

```bash
$ time wikimedia-pagecounts-import -s 2014/01/01 -e 2014/01/01-05:00 -t
[ec2-user@ip-10-0-246-208 ~]$ time ./wikimedia-pagecounts-import -s 2014/01/01 -e 2014/01/01-05:00 -t
Initializing database
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-000000.gz
Importing: pagecounts-20140101-000000.gz
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-010000.gz
Importing: pagecounts-20140101-010000.gz
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-020000.gz
Importing: pagecounts-20140101-020000.gz
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-030000.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-030001.gz
Importing: pagecounts-20140101-030001.gz
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040000.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040001.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040002.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040003.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040004.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040005.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040006.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040007.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-040008.gz
Importing: pagecounts-20140101-040008.gz
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-050000.gz
Not Found
Downloading: http://dumps.wikimedia.org/other/pagecounts-raw/2014/2014-01/pagecounts-20140101-050001.gz
Importing: pagecounts-20140101-050001.gz

real    9m18.371s
user    0m30.856s
sys     0m40.312s
```

```sql
mysql> select * from page_view_statistics limit 5;
+----+---------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+
| id | time                | project_name | title                                                                                                                                         | requests | size   |
+----+---------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+
|  1 | 2014-01-01 00:00:00 | AR           | %D9%86%D9%82%D8%A7%D8%B4_%D8%A7%D9%84%D9%85%D8%B3%D8%AA%D8%AE%D8%AF%D9%85:%D8%B9%D8%A8%D8%A7%D8%AF_%D8%AF%D9%8A%D8%B1%D8%A7%D9%86%D9%8A%D8%A9 |        1 | 230243 |
|  2 | 2014-01-01 00:00:00 | De           | %C3%84sthetik                                                                                                                                 |        3 | 194742 |
|  3 | 2014-01-01 00:00:00 | De           | Cholesterin                                                                                                                                   |        1 |      0 |
|  4 | 2014-01-01 00:00:00 | De           | Steroide                                                                                                                                      |        1 |      0 |
|  5 | 2014-01-01 00:00:00 | De           | Vippetangen                                                                                                                                   |        3 |  26119 |
+----+---------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+
5 rows in set (0.00 sec)
```

```sql
mysql> show table status \G
*************************** 1. row ***************************
           Name: page_view_statistics
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 31374255
 Avg_row_length: 97
    Data_length: 3073376256
Max_data_length: 0
   Index_length: 551550976
      Data_free: 4194304
 Auto_increment: 37944766
    Create_time: 2015-12-20 12:27:24
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
```