Skip to content

Instantly share code, notes, and snippets.

Avatar
🙀
What's happening?

Denny Crane den-crane

🙀
What's happening?
View GitHub Profile
View gist:2e7e12cecc203efe206d84c909a97afa
create table sess (user_id Int64, session_created_time DateTime) Engine=Memory;
insert into sess select 1, toDateTime(now())+number*2 from numbers(10);
insert into sess select 2, toDateTime(now())+number*5 from numbers(2);
insert into sess select 3, toDateTime(now())+number*6 from numbers(1);
select user_id, arrayReduce('median', arraySlice(arrayDifference(arrayMap(i->toUnixTimestamp(i), (arraySort(groupArray(session_created_time))))),2)) median
from sess
group by user_id
View gist:b56adb6ecc226f1ca6bf9aa41da1df4f
| => 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]
View gist:ef788b4ee94b409e23526c2082c1c74c
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;
View gist:57ae0eaca00e711c3d8924c0f9d266ff
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
den-crane / rocksDB_direct_dictionary.sql
Last active Mar 18, 2021
rocksDB_direct_dictionary
View rocksDB_direct_dictionary.sql
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 │
└──────────┴──────────────────────┘
View TTL_test17303
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()
View Map_benchm
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);
View gist:16b2882bc3d0e24de72f6e9d0cf84603
create table X ( A String, F Int64) Engine=SummingMergeTree order by A;
system stop merges X;
insert into X values(1, 1);
insert into X values(1, 2);
alter table X add column B Int64 default rand64(), modify order by (A, B) ;
alter table X modify column B default rand64();
system start merges X;
optimize table X final;
View gist:b8bec6f58cf091ac5e228bbb6a4a46c6
drop database uniq_test;
create database uniq_test engine=Ordinary;
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)
View resample_example.sql
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)─┐