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

# はじめに

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

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

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

# サンプルデータの準備

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

```perl: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 にテーブルを作り、インポートします。
数値より文字列データを集計することの方が多そうなので、中身は数値ですが型を変えています。

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

```sql
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

```sql
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エンジンを使うのがおすすめです。


```sql
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 でデータのソート順を指定する必要があります（重複があっても問題ありません）

```sql
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 エンジンでの測定結果です。

```sql
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～~~2038~~2105年までになります。
こちらも範囲外のデータがある場合、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 | |