HW23bpiv

1DROP TABLE tmp_test_replacing;
2
3-- Create a table with the ReplacingMergeTree Engine 
4CREATE TABLE IF NOT EXISTS tmp_test_replacing (
5  ts Date, 
6  sort_var1 String,
7  sort_var2 String,
8  version_var UInt64,
9  indep_var UInt64
10)
11ENGINE = ReplacingMergeTree(version_var)
12PARTITION BY (ts)
13ORDER BY (sort_var1, sort_var2);
14
15-- Add some data. 
16INSERT INTO tmp_test_replacing  FORMAT CSV
172019-01-01, Amy, Apples,	1, 1
182019-01-01, Bob, Apples,	1, 1
192019-01-01, Clara, Apples,	1, 1
202019-01-01, Denise, Apples,	1, 1
21;
22
23-- Check the table data
24SELECT * FROM tmp_test_replacing;
25
26-- Change the versioning var
27INSERT INTO tmp_test_replacing  FORMAT CSV
282019-01-01, Amy, Apples,	2, 1
292019-01-01, Bob, Apples,	2, 1
302019-01-01, Clara, Apples,	1, 1
312019-01-01, Denise, Apples,	1, 1
32;
33OPTIMIZE TABLE tmp_test_replacing;
34SELECT * FROM tmp_test_replacing;
35
36-- Change the independent var
37INSERT INTO tmp_test_replacing  FORMAT CSV
382019-01-01, Amy, Apples,	2, 2
392019-01-01, Bob, Apples,	2, 2
402019-01-01, Clara, Apples,	1, 1
412019-01-01, Denise, Apples,	1, 1
42;
43OPTIMIZE TABLE tmp_test_replacing;
44SELECT * FROM tmp_test_replacing;
45
46-- Change the partition var
47INSERT INTO tmp_test_replacing  FORMAT CSV
482019-01-02, Amy, Apples,	2, 2
492019-01-02, Bob, Apples,	2, 2
502019-01-01, Clara, Apples,	1, 1
512019-01-01, Denise, Apples,	1, 1
52;
53OPTIMIZE TABLE tmp_test_replacing;
54SELECT * FROM tmp_test_replacing;