MySQL
ClickHouse

MySQLのデータをClickHouseで高速に集計する

はじめに

ClickHouse に、MySQL に直接接続する機能が追加されました。
MySQL のテーブルを、直接ClickHouse から利用することが可能です。

ClickHouse は、集計が高速なデータベースで、GROUP BY を使うようなクエリは MySQL 上で普通に集計するよりも、ClickHouse 側からMySQLのデータを使って集計する方が高速です。

以下は手元の仮想環境でテストした記録です。
仮想環境なので厳密なテストではなく、参考程度に見てください。

サンプルデータの準備

適当に1000万件程度のデータを用意してテストします。

makedata.pl
#!/usr/bin/perl

use strict;
use warnings;

srand(0);

my $RECORD_COUNT = 10000000;
for(my $i = 1; $i <= $RECORD_COUNT; $i++) {
    my @field;
    push(@field, $i);
    push(@field, sprintf("%08d\@example.com", $i));
    push(@field, int(rand(5))+1);
    push(@field, int(rand(10))+1);
    push(@field, int(rand(100000))+1);
    push(@field, int(rand(1000000))+1);
    push(@field, int(rand(10000000))+1);
    print join("\t", @field), "\n";
}

実行してサンプルデータを作ります。500MBくらいになりました。

$ ./makedata.pl > data.tsv
$ ls -lh data.tsv 
-rw-rw-r-- 1 mikage mikage 512M Jan 18 07:44 data.tsv
$ head -2 data.tsv 
1   00000001@example.com    1   8   9638    870466  5773036
2   00000002@example.com    4   7   36877   873905  7450951

MySQL での測定

MySQL にテーブルを作り、インポートします。
数値より文字列データを集計することの方が多そうなので、中身は数値ですが型を変えています。

その後、適当に集計してみます。

mysql> CREATE TABLE testdata (
  id    INT NOT NULL PRIMARY KEY,
  email BLOB NOT NULL,
  data1 INT NOT NULL,
  data2 VARCHAR(100) NOT NULL,
  data3 BLOB NOT NULL,
  data4 BLOB NOT NULL,
  data5 BLOB NOT NULL
) ENGINE=InnoDB;

mysql> LOAD DATA LOCAL INFILE 'data.tsv' INTO TABLE testdata;
Query OK, 10000000 rows affected (44.98 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT data1, COUNT(*) FROM testdata GROUP BY data1;
+-------+----------+
| data1 | COUNT(*) |
+-------+----------+
|     1 |  2000453 |
|     2 |  1999993 |
|     3 |  1998988 |
|     4 |  1999013 |
|     5 |  2001553 |
+-------+----------+
5 rows in set (3.22 sec)

mysql> SELECT data2, COUNT(*) FROM testdata GROUP BY data2;
+-------+----------+
| data2 | COUNT(*) |
+-------+----------+
| 1     |  1000530 |
| 10    |   999424 |
| 2     |  1000648 |
| 3     |  1000357 |
| 4     |   998349 |
| 5     |   998889 |
| 6     |   999786 |
| 7     |   998774 |
| 8     |  1001805 |
| 9     |  1001438 |
+-------+----------+
10 rows in set (4.01 sec)

mysql> SELECT data3, COUNT(*) FROM testdata GROUP BY data3;
-- 結果省略
100000 rows in set (3 min 32.82 sec)

mysql> SELECT data1, COUNT(DISTINCT data5) FROM testdata GROUP BY data1;

+-------+-----------------------+
| data1 | COUNT(DISTINCT data5) |
+-------+-----------------------+
|     1 |               1813005 |
|     2 |               1812503 |
|     3 |               1812072 |
|     4 |               1811674 |
|     5 |               1814106 |
+-------+-----------------------+
5 rows in set (3 min 3.56 sec)

BLOB型でGROUPBYをするとかなり遅いようです。

ClickHouse を使って測定

ClickHouse 上で、テーブル名の代わりに、以下のような形で指定すれば、MySQL のデータを利用できます。

mysql('host:port', 'database', 'table', 'user', 'password)

ClickHouse のクライアントを起動してテストします。
起動時に --multiline オプションをつけないと、複数行のクエリをかけないのでつけておきます。

$ clickhouse-client --multiline

SELECT 
    data1, 
    COUNT(*)
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 
GROUP BY data1

┌─data1─┬─COUNT()─┐
     4  1999013 
     3  1998988 
     2  1999993 
     5  2001553 
     1  2000453 
└───────┴─────────┘

5 rows in set. Elapsed: 2.685 sec. Processed 10.00 million rows, 40.00 MB (3.72 million rows/s., 14.90 MB/s.) 

SELECT 
    data2, 
    COUNT(*)
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 
GROUP BY data2

┌─data2─┬─COUNT()─┐
 6       999786 
 8      1001805 
 9      1001438 
 3      1000357 
 2      1000648 
 4       998349 
 5       998889 
 10      999424 
 1      1000530 
 7       998774 
└───────┴─────────┘

10 rows in set. Elapsed: 2.692 sec. Processed 10.00 million rows, 101.00 MB (3.71 million rows/s., 37.52 MB/s.) 

SELECT 
    data3, 
    COUNT(*)
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 
GROUP BY data3

-- 結果省略

100000 rows in set. Elapsed: 5.236 sec. Processed 10.00 million rows, 138.89 MB (1.91 million rows/s., 26.52 MB/s.) 

SELECT 
    data1, 
    uniqExact(data5)
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 
GROUP BY data1

┌─data1─┬─uniqExact(data5)─┐
     4           1811674 
     3           1812072 
     2           1812503 
     5           1814106 
     1           1813005 
└───────┴──────────────────┘

5 rows in set. Elapsed: 12.944 sec. Processed 10.00 million rows, 198.89 MB (772.55 thousand rows/s., 15.37 MB/s.) 

-- ClickHouse には、概算でユニーク数を求める機能もあるので、そちらで計算してみます。

SELECT 
    data1, 
    uniq(data5)
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 
GROUP BY data1

┌─data1─┬─uniq(data5)─┐
     4      1812684 
     3      1811607 
     2      1817432 
     5      1815763 
     1      1815300 
└───────┴─────────────┘

5 rows in set. Elapsed: 6.026 sec. Processed 10.00 million rows, 198.89 MB (1.66 million rows/s., 33.00 MB/s.) 

MySQL では非常に時間がかかったクエリも、ごく短時間で処理できます。

繰り返し集計するときは、データを一度ClickHouseにコピーしてしまう方がよいかもしれません。
以下のようにコピーしてしまえば、その後のクエリはさらに速くなります。

とりあえずStripeLogエンジンを使うのがおすすめです。

CREATE TABLE testdata
ENGINE = StripeLog AS
SELECT *
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 

Ok.

0 rows in set. Elapsed: 9.823 sec. Processed 10.00 million rows, 917.66 MB (1.02 million rows/s., 93.42 MB/s.) 

主キーがある場合、MergeTree テーブルを使うのもよいかもしれません。
こちらがClickHouseで一番主に使われるエンジンです。
ORDER BY でデータのソート順を指定する必要があります(重複があっても問題ありません)

CREATE TABLE testdata
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('localhost', 'mikage', 'testdata', 'mikage', '') 

Ok.

0 rows in set. Elapsed: 20.466 sec. Processed 10.00 million rows, 917.66 MB (488.61 thousand rows/s., 44.84 MB/s.) 

先ほどのクエリを実行してみます。
以下は StripeLog エンジンでの測定結果です。

SELECT 
    data1, 
    COUNT(*)
FROM testdata 
GROUP BY data1

┌─data1─┬─COUNT()─┐
     4  1999013 
     3  1998988 
     2  1999993 
     5  2001553 
     1  2000453 
└───────┴─────────┘

5 rows in set. Elapsed: 0.071 sec. Processed 10.00 million rows, 40.00 MB (141.13 million rows/s., 564.53 MB/s.) 

SELECT 
    data2, 
    COUNT(*)
FROM testdata 
GROUP BY data2

┌─data2─┬─COUNT()─┐
 6       999786 
 8      1001805 
 9      1001438 
 3      1000357 
 2      1000648 
 4       998349 
 5       998889 
 10      999424 
 1      1000530 
 7       998774 
└───────┴─────────┘

10 rows in set. Elapsed: 0.177 sec. Processed 10.00 million rows, 101.00 MB (56.34 million rows/s., 569.05 MB/s.) 

SELECT 
    data3, 
    COUNT(*)
FROM testdata 
GROUP BY data3

-- 結果省略

100000 rows in set. Elapsed: 0.779 sec. Processed 10.00 million rows, 138.89 MB (12.84 million rows/s., 178.29 MB/s.) 


SELECT 
    data1, 
    uniqExact(data5)
FROM testdata 
GROUP BY data1

┌─data1─┬─uniqExact(data5)─┐
     5           1814106 
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
     1           1813005 
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
     4           1811674 
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
     3           1812072 
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
     2           1812503 
└───────┴──────────────────┘

5 rows in set. Elapsed: 1.725 sec. Processed 10.00 million rows, 198.89 MB (5.80 million rows/s., 115.32 MB/s.) 

SELECT 
    data1, 
    uniq(data5)
FROM testdata 
GROUP BY data1

┌─data1─┬─uniq(data5)─┐
     4      1812684 
     3      1811607 
     2      1817432 
     5      1815763 
     1      1815300 
└───────┴─────────────┘

5 rows in set. Elapsed: 0.285 sec. Processed 10.00 million rows, 198.89 MB (35.03 million rows/s., 696.62 MB/s.) 

まとめ

時間をまとめると以下のようになります。
(1回ずつ、VM環境での測定なので誤差は大きいと思います)

左から、MySQLでの時間、ClickHouseがMySQLからデータを読み込んで処理する場合の時間、ClickHouse上にコピー済みのデータを処理する時間です。

クエリ MySQL処理時間(秒) MySQL->ClickHouse処理時間(秒) ClickHouse処理時間(秒)
groupby(data1) 3.22 2.685 0.071
groupby(data2) 4.01 2.692 0.177
groupby(data3) 212.82 5.236 0.779
groupby(data1)+uniq(data5) 183.56 12.944 1.725
groupby(data1)+uniq(data5)概算 (機能なし) 6.026 0.285

(追記)型の対応

MySQLのデータを参照した時、ClickHouse側に対応する型がない場合は、String型になるようです。
Decimal型などは対応したものがないため、String型になってしまいます。

数値として扱いたい場合は、精度は変わってしまいますが、MySQL側でDouble型にしておくなどが必要そうです。

また、Date型、DateTime型も、MySQLとClickHouseでは範囲が異なります。
ClickHouseでは、どちらも1970~20382105年までになります。
こちらも範囲外のデータがある場合、String型にする、年月日でカラムを分割して数値としてコピーする、などが必要かもしれません。

該当箇所のソースコード:
https://github.com/yandex/ClickHouse/blob/9965f5e357f1be610608a51dc7a41f89c2321275/dbms/src/TableFunctions/TableFunctionMySQL.cpp#L37

MySQL の型 ClickHouse の型 備考
tinyint UInt8 / Int8
smallint UInt16 / Int16
int / mediumint UInt32 / Int32
bigint UInt64 / Int64
float Float32
double Float64
date Date 表現できる範囲差あり
datetime DateTime 表現できる範囲差あり
timestamp DateTime
binary FixedString
上記以外 String