javisantana

https://web.archive.org/web/20190312223043/http://the-witness.net/news/2012/05/the-depth-jam/

javisantana

Clickhouse has a pretty good endpoint /replicas_status which gives information about the, guess what, replication status. When you are working on a cluster in which you use replication to increase the amount of QPS you usually have a load balancer before, something like this:

                                 +--------------+
                                 |              |
                       +-------->+  clickhouse  |
                       |         |              |
                       |         +--------------+
                       |

javisantana

Looks like filters are pushed down when filtering an "UNION ALL". Also an example on how to use EXPLAIN in clickhouse and a different view of seeing what is going on with the traces, this lines show how much data clickhouse is reading:

Selected 1 parts by date, 1 parts by key, 2 marks by primary key, 2 marks to read from 1 ranges
Reading approx. 16384 rows with 1 streams

The example

javisantana

Clickhouse has a powerful feature, JOIN engines, that allows to prepare a table to be joined with better performance that a regular table (MergeTree, Log...). It also allows to use joinGet to get table values using a key.

Somtimes you don't have a JOIN table but you'd like to use something with the joinGet performance. Unfortunately you can't use joinGet with something created on the fly (well, you could create a temporally join table but you need several SQL queries).

So there is a way to do that, using transform:

with (
  select (groupArray(key), groupArray(value)) from my_table
) as key_value

javisantana

clickhouse on osx

These are the steps I followed to compile clickhouse on OSX mojave (10.4.3). These might not be the best way to make it compile on OSX, my knowledge about C++ and build tooling is really limited but they do the work.

clickhouse sha-1: 2ad4df1d6a

compile command (apply the patch below before running cmake)

javisantana

MacBook-Pro-de-javi.local :) create table multiple_keys (tmp Int32, testMap Nested (a Int32, bKey Int32, value Int32)) Engine=SummingMergeTree() order by (tmp);

CREATE TABLE multiple_keys
(
    `tmp` Int32,
    `testMap` Nested(
    a Int32,
    bKey Int32,
    value Int32)
)

javisantana

MacBook-Pro-de-javi.local :) select cityHash64(groupArray(cityHash64(*))) from A a asof inner join (select * from B  where ts<toDateTime('1970-01-01 02:00:00')) b on a.id=b.id and a.ts=b.ts where a.ts<toDateTime('1970-01-01 02:00:00');

SELECT cityHash64(groupArray(cityHash64(*)))
FROM A AS a
ASOF INNER JOIN
(
    SELECT *
    FROM B
    WHERE ts < toDateTime('1970-01-01 02:00:00')
) AS b ON (a.id = b.id) AND (a.ts = b.ts)

javisantana

IMPORTANT, this worked with CH stable (76629e9) version on 2019/02/14

install requirements

from the original osx build doc page https://clickhouse.yandex/docs/en/development/build_osx.html

build clickhouse

Add /usr/local/include to the default path for gcc (for some reason gcc-7 was not using that folder on osx)

javisantana

https://docs.google.com/presentation/d/1vURI9yYJvAm0couPbFeKz09sr8bvQuSzCfcTEBow7ec/edit#slide=id.g5294e95553_0_101

javisantana

/*
FILE ARCHIVED ON 3:09:27 feb 10, 2005 AND RETRIEVED FROM THE
INTERNET ARCHIVE ON 19:57:07 abr 24, 2016.
JAVASCRIPT APPENDED BY WAYBACK MACHINE, COPYRIGHT INTERNET ARCHIVE.

ALL OTHER CONTENT MAY ALSO BE PROTECTED BY COPYRIGHT (17 U.S.C.
SECTION 108(a)(3)).
*/
// Copyright 2005 Google
function ci(vg, Pf, yh) {