36uWiyPA

1-- For example we want to emulate the 
2-- UPDATE A SET value=5 WHERE answer_id=42;`
3--
4-- in ClickHouse that doesnt have an UPDATE operation
5-- and the updated dataset is pretty big (around a million of rows).
6-- The previous value of `value` column considered "0".
7
8
9SELECT count() FROM A WHERE answer_id=42;
10-- 938258
11
12
13DROP TABLE IF EXISTS B;
14CREATE TABLE IF NOT EXISTS B 
15ENGINE = MergeTree(event_date, intHash32(answer_id), (answer_id, session_uuid, event_date), 8192) AS
16SELECT * FROM A WHERE answer_id=42;
17-- 0 rows in set. Elapsed: 3.677 sec. Processed 1.16 million rows, 2.07 GB (314.85 thousand rows/s., 563.95 MB/s.)
18SELECT count() FROM B;
19-- 938258
20
21
22ALTER TABLE B DROP COLUMN Sign;
23ALTER TABLE B ADD COLUMN Sign Int8 DEFAULT -1 AFTER value;
24ALTER TABLE B DROP COLUMN event_datetime_created;
25ALTER TABLE B ADD COLUMN event_datetime_created DateTime DEFAULT 1476297037 AFTER event_date;
26SELECT count(), Sign FROM B GROUP BY Sign;
27-- 938258 │ -1
28SELECT count(), event_datetime_created FROM B GROUP BY event_datetime_created;
29-- 938258 │ 2016-10-12 21:30:37
30INSERT INTO `A` SELECT * FROM B FORMAT TabSeparatedWithNamesAndTypes;
31-- 0 rows in set. Elapsed: 9.448 sec. Processed 938.26 thousand rows, 1.68 GB (99.30 thousand rows/s., 177.47 MB/s.)
32SELECT count(), Sign FROM A WHERE answer_id=42 GROUP BY Sign;
33-- 938258 │  1 
34-- 825956 │ -1 
35
36
37ALTER TABLE B DROP COLUMN Sign;
38ALTER TABLE B ADD COLUMN Sign Int8 DEFAULT 1 AFTER value;
39ALTER TABLE B DROP COLUMN event_datetime_created;
40ALTER TABLE B ADD COLUMN event_datetime_created DateTime DEFAULT 1476297037 AFTER event_date;
41ALTER TABLE B DROP COLUMN value;
42ALTER TABLE B ADD COLUMN value UInt8 DEFAULT 5 AFTER event_datetime_created;
43SELECT count(), Sign FROM B GROUP BY Sign;
44-- 938258 │ 1 
45SELECT count(), event_datetime_created FROM B GROUP BY event_datetime_created;
46-- 938258 │ 2016-10-12 21:30:37
47INSERT INTO `A` SELECT * FROM B FORMAT TabSeparatedWithNamesAndTypes;
48-- 0 rows in set. Elapsed: 4.700 sec. Processed 938.26 thousand rows, 1.68 GB (199.65 thousand rows/s., 356.60 MB/s.)
49SELECT count(), Sign, value FROM A WHERE answer_id=42 GROUP BY Sign, value;
50-- 883651 │    1 │                5 │
51-- 938258 │    1 │                0 │
52-- 938258 │   -1 │                0 │
53
54SELECT sum(Sign) FROM A WHERE answer_id=42;
55-- 883651
56SELECT count(), Sign, value FROM A FINAL WHERE answer_id=42 GROUP BY Sign, value;
57-- 938258 │    1 │ 5 │
58-- 234150 │    1 │ 0 │
59
60
61DROP TABLE IF EXISTS B;
62
63
64
65---------- AFTER SOME TIME (5-10 minutes) -----------------
66SELECT count(), Sign, value FROM A WHERE answer_id=42 GROUP BY Sign, value;
67-- 938258 │    1 │                5 │
68-- 938258 │    1 │                0 │
69-- 938258 │   -1 │                0 │