Alexey Milovidov alexey-milovidov

ClickHouse co-founder and CTO

alexey-milovidov / example.txt

Created June 16, 2016 20:48

Example of sequenceMatch aggregate function usage in ClickHouse.

	$ clickhouse-client
	ClickHouse client version 0.0.53720.
	Connecting to localhost:9000.
	Connected to ClickHouse server version 1.1.53981.

	:) CREATE TABLE test.funnel (userID UInt64, eventType Enum8('A' = 1, 'B' = 2), EventTime Date, Age UInt8) ENGINE = Memory

	CREATE TABLE test.funnel
	(
	userID UInt64,

alexey-milovidov / nested.txt

Created June 17, 2016 21:15

Example of Nested data type in ClickHouse.

	:) CREATE TABLE test.nested (EventDate Date, UserID UInt64, Attrs Nested(Key String, Value String)) ENGINE = MergeTree(EventDate, UserID, 8192)

	CREATE TABLE test.nested
	(
	EventDate Date,
	UserID UInt64,
	Attrs Nested(
	Key String,
	Value String)
	) ENGINE = MergeTree(EventDate, UserID, 8192)

alexey-milovidov / rounding_dates.txt

Created June 20, 2016 20:57

Example of using date and datetime functions in ClickHouse.

	:) SELECT toMonday(EventDate) AS k, count(), uniq(UserID) FROM hits_layer WHERE CounterID = 29761725 AND EventDate >= '2016-05-01' GROUP BY k ORDER BY k

	SELECT
	toMonday(EventDate) AS k,
	count(),
	uniq(UserID)
	FROM hits_layer
	WHERE (CounterID = 29761725) AND (EventDate >= '2016-05-01')
	GROUP BY k
	ORDER BY k ASC

alexey-milovidov / rounding_time.txt

Created June 20, 2016 21:00

Example of rounding time to 5-second interval in ClickHouse.

	:) SELECT toDateTime(intDiv(toUInt32(EventTime), 5) * 5) AS k, count(), uniq(UserID) FROM hits_layer WHERE CounterID = 29761725 AND EventDate = today() AND EventTime >= now() - 600 GROUP BY k ORDER BY k

	SELECT
	toDateTime(intDiv(toUInt32(EventTime), 5) * 5) AS k,
	count(),
	uniq(UserID)
	FROM hits_layer
	WHERE (CounterID = 29761725) AND (EventDate = today()) AND (EventTime >= (now() - 600))
	GROUP BY k
	ORDER BY k ASC

alexey-milovidov / paste.txt

Created June 21, 2016 17:05

Check for constants in primary key.

	$ clickhouse-client
	ClickHouse client version 0.0.53720.
	Connecting to localhost:9000.
	Connected to ClickHouse server version 1.1.53981.

	:) create table hoba (naber Date) ENGINE = MergeTree(naber, (1, naber), 8192)

	CREATE TABLE hoba
	(
	naber Date

alexey-milovidov / rank.txt

Created July 22, 2016 00:03

Example of emulation of rank function with arrayEnumerateUniq.

	milovidov@milovidov-xenial:~$ clickhouse-client
	ClickHouse client version 1.1.53996.
	Connecting to localhost:9000.
	Connected to ClickHouse server version 1.1.53996.

	:) CREATE TABLE IF NOT EXISTS `employee` (
	:-] `empid` Int32,
	:-] `deptid` Int32,
	:-] `salary` Int64
	:-] ) ENGINE = Memory;

alexey-milovidov / graphite_sizes.txt

Created July 27, 2016 01:20

Example of column sizes in graphite table.

	:) SELECT name, formatReadableSize(sum(bytes)) FROM system.columns WHERE table = 'graphite_impl' AND database = 'default' GROUP BY name ORDER BY sum(bytes) DESC

	SELECT
	name,
	formatReadableSize(sum(bytes))
	FROM system.columns
	WHERE (table = 'graphite_impl') AND (database = 'default')
	GROUP BY name
	ORDER BY sum(bytes) DESC

alexey-milovidov / distributed_group_by_no_merge example

Created January 13, 2017 19:40

	:) SELECT uniq(UserID) FROM hits_all WHERE EventDate = today()

	SELECT uniq(UserID)
	FROM hits_all
	WHERE EventDate = today()

	┌─uniq(UserID)─┐
	│ 321993104 │
	└──────────────┘

alexey-milovidov / mysql_escape_characters

Created January 26, 2017 20:36

	Welcome to the MySQL monitor. Commands end with ; or \g.
	Your MySQL connection id is 113
	Server version: 5.5.50-0ubuntu0.14.04.1 (Ubuntu)

	Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

	Oracle is a registered trademark of Oracle Corporation and/or its
	affiliates. Other names may be trademarks of their respective
	owners.

alexey-milovidov / star_schema.txt

Created April 15, 2017 03:02

	:) SELECT C_REGION, C_NATION, count(*) FROM lineorder_x3 GLOBAL ANY INNER JOIN ( SELECT C_REGION, C_NATION, C_CUSTKEY AS LO_CUSTKEY FROM customer_x3 ) USING (LO_CUSTKEY) WHERE (toYear(LO_ORDERDATE) > 1993) AND (LO_DISCOUNT >= 1) AND (LO_QUANTITY < 25) GROUP BY C_REGION, C_NATION;

	SELECT
	C_REGION,
	C_NATION,
	count(*)
	FROM lineorder_x3
	GLOBAL ANY INNER JOIN
	(
	SELECT