den-crane

| => mvn test
[INFO] Scanning for projects...
[INFO]
[INFO] ----------------< ru.yandex.clickhouse:clickhouse-jdbc >----------------
[INFO] Building clickhouse-jdbc 0.3.1-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- ph-javacc-maven-plugin:4.1.4:javacc (jjc) @ clickhouse-jdbc ---
[INFO] Skipping - all parsers are up to date
[INFO]

den-crane

drop table if exists testA;
drop table if exists testB;
drop table if exists testB_mv;
create table testA(A Int64) Engine=MergeTree order by A;
create table testB(c Int64) Engine = MergeTree order by tuple();
create MATERIALIZED view testB_mv to testB as  select count() c, max(throwIf(A = 9500000)) from testA;
system stop merges testA;
system stop merges testB;

den-crane

CREATE TABLE test  (
    k1 int,
    d date not null,
    some_col int,
    val int  )
PARTITION BY (d); 


CREATE PROJECTION test_p1 (  k1 ENCODING RLE,  d, some_col, val )
AS SELECT * FROM test  ORDER BY k1

den-crane

rocksDB is faster than MergeTree on Key/Value queries because MergeTree primary key index is sparse.
Probably it's possible to speedup MergeTree by reducing index_granularity.

The main feature of rocksDB is instant updates.
You can update row instantly (microseconds):

select * from rocksDB where A=15645646;
┌────────A─┬─B────────────────────┐
│ 15645646 │ 12517841379565221195 │
└──────────┴──────────────────────┘

den-crane

DROP TABLE IF EXISTS test_ttl_delete_01763;

CREATE TABLE test_ttl_delete_01763
(
    `key_a` UInt32,
    `key_b` UInt32,
    `ts` DateTime,
    `value` UInt32
)
ENGINE = MergeTree()

den-crane

set allow_experimental_map_type=1

create table b( A Int64, K Array(String), V Array(String), KV Map(String, String) ) Engine=MergeTree order by A;    

insert into b select number x, 
                     arrayMap( i -> toString(i), range(20) ) K,
                     arrayMap( i -> toString(cityHash64(x,i)), range(20) ) V,
                     (K,V)
from numbers(10000000);      

den-crane

drop database uniq_test;
create database uniq_test;
USE uniq_test;

create table events(ts DateTime, uid UInt64, site_id UInt32, browser LowCardinality(String))
Engine=MergeTree partition by toYYYYMM(ts) order by (site_id, toStartOfHour(ts));

CREATE MATERIALIZED VIEW uniq_aggr
ENGINE = AggregatingMergeTree
partition by toYYYYMM(day)  order by (site_id, browser, day)

den-crane

select anyResample(100, 190, 30)(t, t),
       avgResample(100, 190, 30)(v, t)
FROM
(
select 
      [100, 110, 120, 130, 140, 150, 160, 170, 180, 190] time,
      [ 1 ,  1 ,  1 ,  2 ,  2 ,  2 ,  3 ,  3 ,  3 ,  3 ] values
) array join time as t, values as v

┌─anyResample(100, 190, 30)(t, t)─┬─avgResample(100, 190, 30)(v, t)─┐

den-crane

create table t1 (A Int64, B Int64, X Array(Float64)) Engine = Memory;
insert into t1 values(1,1,[1.0,2.0]), (1,2,[1.0,2.0]), (2,1,[1.0,2.0]), (2,2,[1.0,2.0]);


select A, avgMerge(X2) from (
   select A, B, avgMergeState(X1) X2 from (
         select A, B, arrayReduce('avgState', X) X1 from t1) 
    group by A, B)
group by A

den-crane

create table pass (i UInt64,  S String, P Array(UInt8)) Engine=MergeTree order by i;
insert into pass select number, arrayStringConcat(arrayMap(i -> char(i*number%255), range(10)),''), arrayMap(i -> i*number%255, range(30)) from numbers(10000000);

┌─database─┬─table─┬─column─┬─compressed─┬─uncompressed─┐
│ dw       │ pass  │ i      │ 38.19 MiB  │ 76.29 MiB    │
│ dw       │ pass  │ P      │ 10.30 MiB  │ 362.40 MiB   │
│ dw       │ pass  │ S      │ 3.72 MiB   │ 104.90 MiB   │
└──────────┴───────┴────────┴────────────┴──────────────┘