200 万行を入れて列ファイルの中をのぞく
目標
MergeTreeテーブルに200万行を入れ、パート・グラニュール・列ごとの圧縮率をsystemテーブルから読みます。同じ行数を読んでも、触れた列とソートキーによってコストが変わることを、rows_read・bytes_readで確認します。
なぜ重要なのか
列指向DBでは、クエリのコストは「何行を選んだか」ではなく「どの列を何グラニュール読んだか」で決まります。この感覚がないと、ソートキーの選び方を誤り、SELECT *を習慣のように使い、ディスク容量を元データのサイズで見積もってしまいます。このラボの採点ツールは、書き込まれた数値をそのまま信用しません。サーバーのsystem.parts・system.columns・system.query_logを直接読み、保存されたSELECTを読み取り専用でもう一度実行して、読んだ行数とバイト数を測って突き合わせます。採点ツール自身のクエリはquery_logに残りません。
ステップ
- データベース
colとテーブルcol.eventsを作成してください。列はts DateTime, site LowCardinality(String), user_id UInt64, url String, dur_ms UInt32, country FixedString(2)(この順序)、エンジンはMergeTree、ソートキーはORDER BY (site, ts)です。 /opt/lab/fixtures/columnar/events.sqlを1回だけ実行して、200万行を入れてください。OPTIMIZE TABLE col.events FINALでパートを1つにマージしたあと、そのパートのpart(名前)・rows・marks・part_typeを書き込んでください(保存先: /root/ch/columnar/parts.json)。- system.columnsから、非圧縮サイズと圧縮サイズの比が最も大きい列(
most_compressed)・最も小さい列(least_compressed)・圧縮後バイト数の合計(total_compressed_bytes)を書き込んでください(保存先: /root/ch/columnar/columns.json)。 dur_msの合計だけを求めるクエリ(/root/ch/columnar/q_one.sql)と、url文字列の内容を読むクエリ(/root/ch/columnar/q_url.sql、例:max(url))を作成し、2つのクエリのbytes_readをone_col_bytes・url_bytesとして書き込んでください(保存先: /root/ch/columnar/bytes.json)。site = 'docs.example'の行のdur_msの合計を求めるクエリ(/root/ch/columnar/q_key.sql)と、country = 'KR'の行のdur_msの合計を求めるクエリ(/root/ch/columnar/q_nokey.sql)を作成し、2つのクエリのrows_readをkey_rows_read・nokey_rows_readとして書き込んでください(保存先: /root/ch/columnar/rows.json)。SELECT uniqExact(user_id) FROM col.events WHERE site = 'shop.example'にSETTINGS log_comment = 'chs-col-07'を付けて実行したあと、system.query_logからそのクエリを探し、query_id・read_rows・read_bytes・result_rowsを書き込んでください(保存先: /root/ch/columnar/qlog.json)。col.eventsと同じ列のテーブルcol.smallを作成し、col.eventsの1000行を1回のINSERTで入れてから、2つのテーブルのパート形式をevents・smallとして書き込んでください(保存先: /root/ch/columnar/part_types.json)。
参考
- サーバーはPodの起動時にすでに立ち上がっています(127.0.0.1:9000)。
clickhouse-clientと入力するだけで接続できます。止まっていた場合はch-upを実行してください。 - ファイルからクエリを実行するには
clickhouse-client --queries-file 파일.sqlを使います(プレースホルダーはファイル名です)。結果の末尾の統計は、--format JSONで受け取るとstatisticsに入っています(jq .statistics)。 - 数値をJSONの中で引用符なしで受け取るには、
--output_format_json_quote_64bit_integers 0を指定します。 OPTIMIZE ... FINALは、ラボでパート数を固定するために使うものです。本番環境では、マージはサーバーに任せるのが原則です。- query_logは約1秒ごとにフラッシュされます。今実行したクエリが見えない場合は
SYSTEM FLUSH LOGSを実行します。 - よくある間違い: ステップ2を2回実行して400万行になってしまうことです。MergeTreeは重複を防ぎません。その場合は
TRUNCATE TABLE col.eventsを実行してから、もう一度入れ直します。 - 公式ドキュメント: Table parts・Primary indexes・MergeTree・system.parts・system.columns・system.query_log
ソートキーを持つMergeTreeテーブルを作成する
データベースcolとテーブルcol.eventsを作成してください。列はts DateTime, site LowCardinality(String), user_id UInt64, url String, dur_ms UInt32, country FixedString(2)の順序、エンジンはMergeTree、ソートキーはORDER BY (site, ts)です。
CREATE DATABASEとCREATE TABLEの2文です。ソートキーを書かないMergeTreeは作成できません。キーがそのままディスク上の行の順序になるからです。列名と型を1文字でも違えて書くと、あとのステップの元スクリプトが入りません。
元データ200万行を入れる
/opt/lab/fixtures/columnar/events.sqlを1回だけ実行して、col.eventsに2,000,000行を入れてください。
clickhouse-clientに--queries-fileで渡せば実行できます。このスクリプトはnumbers()で行番号を作り、ハッシュで値を決めるので、何回実行しても同じ行ができます。行数が400万なら、2回入れています。
パートを1つにマージして名前を読む
OPTIMIZE TABLE col.events FINALでパートを1つにマージしたあと、system.partsからそのアクティブなパートのpart(名前)・rows・marks・part_typeを書き込んでください(保存先: /root/ch/columnar/parts.json)。
1回のINSERTが、ブロックサイズによって複数のパートを作ることがあります。マージのあとは名前が変わります(マージレベルが上がります)。active = 1の行だけを見てください。マージ済みの古いパートも、しばらく一覧に残っています。system.partsのname列にpartという別名を付けてJSONEachRowで受け取れば、そのまま保存できます。
列ごとに圧縮率はどれだけ違うのか
system.columnsからcol.eventsの列ごとにdata_uncompressed_bytes / data_compressed_bytesを求め、最も大きい列をmost_compressed、最も小さい列をleast_compressed、圧縮後バイト数の合計をtotal_compressed_bytesとして書き込んでください(保存先: /root/ch/columnar/columns.json)。
argMax(name, 比率)とargMin(name, 比率)を使えば、1つのクエリで済みます。最もよく縮む列は、たいていソートキーの先頭側にある列か、値の種類が数個しかない列です。最も縮まない列は、増え続ける値や乱数に近い値の列です。
同じ行、違う列: 読み取りバイト数
dur_msの合計だけを求めるクエリ(/root/ch/columnar/q_one.sql)と、url文字列の内容を読むクエリ(/root/ch/columnar/q_url.sql)を作成し、2つのクエリを--format JSONで実行して、statistics.bytes_readをone_col_bytes・url_bytesとして書き込んでください(保存先: /root/ch/columnar/bytes.json)。
2つのクエリとも、200万行のすべてを読みます。違うのは、触れる列のサイズです。UInt32の列1つなら、1行あたり4バイトです。url側ではlength(url)を使ってはいけません。その関数は長さだけを持つサブカラムを読むように書き換えられ、内容を読まないからです。max(url)のように、内容を比較する関数を使ってください。
ソートキーで絞り込むときだけスキップされる
site = 'docs.example'の行のdur_msの合計を求めるクエリ(/root/ch/columnar/q_key.sql)と、country = 'KR'の行のdur_msの合計を求めるクエリ(/root/ch/columnar/q_nokey.sql)を作成し、2つのクエリのstatistics.rows_readをkey_rows_read・nokey_rows_readとして書き込んでください(保存先: /root/ch/columnar/rows.json)。
siteはソートキーの先頭の列なので、インデックスがその値を含み得ないグラニュールをスキップします。countryはキーに含まれないため、スキップする根拠がありません。読んだ行数が8192の倍数付近になる理由を考えてみてください。スキップの単位は行ではなくグラニュールです。
query_logから自分のクエリを探す
SELECT uniqExact(user_id) FROM col.events WHERE site = 'shop.example' SETTINGS log_comment = 'chs-col-07'を実行し、system.query_logからそのクエリの終了(type = 'QueryFinish')の記録を探して、query_id・read_rows・read_bytes・result_rowsを書き込んでください(保存先: /root/ch/columnar/qlog.json)。
query_logは約1秒ごとにディスクへフラッシュされます。すぐに探したいなら、先にSYSTEM FLUSH LOGSを実行します。1つのクエリは開始(QueryStart)と終了(QueryFinish)の2行を残しますが、読んだ行数があるのは終了の行だけです。log_commentで絞り込めば、大量のクエリの中から自分のものだけが残ります。
小さなパートはCompactになる
col.eventsと同じ列・エンジンのテーブルcol.smallを作成し、col.eventsの1000行を1回のINSERTで入れてから、2つのテーブルのアクティブなパートの形式(part_type)をevents・smallとして書き込んでください(保存先: /root/ch/columnar/part_types.json)。
CREATE TABLE ... ASに別のテーブルを指定すると、列とエンジンをそのままコピーします。パート形式は、パートのバイト数・行数がテーブル設定のmin_bytes_for_wide_part・min_rows_for_wide_partより小さければCompact、大きければWideです。テーブル設定は変更しないでください。