Help us understand the problem. What is going on with this article?

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
ymir
独自のテクノロジーで、パフォーマンスを追求したメール配信システムを中心にメッセージングソリューションを提供するSaaSベンダーです。
https://www.ymir.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした