はじめに
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型にする、年月日でカラムを分割して数値としてコピーする、などが必要かもしれません。
| 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 |