Edited at

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

More than 1 year has passed since last update.


はじめに

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