Last active
April 17, 2019 10:34
-
-
Save paparaka/84ae9e566b31ecdce02b3350b5c49a41 to your computer and use it in GitHub Desktop.
ClickHouse replacing Merge Tree engine inner workings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE tmp_test_replacing; | |
-- Create a table with the ReplacingMergeTree Engine | |
CREATE TABLE IF NOT EXISTS tmp_test_replacing ( | |
ts Date, | |
sort_var1 String, | |
sort_var2 String, | |
version_var UInt64, | |
indep_var UInt64 | |
) | |
ENGINE = ReplacingMergeTree(version_var) | |
PARTITION BY (ts) | |
ORDER BY (sort_var1, sort_var2); | |
-- Add some data. | |
INSERT INTO tmp_test_replacing FORMAT CSV | |
2019-01-01, Amy, Apples, 1, 1 | |
2019-01-01, Bob, Apples, 1, 1 | |
2019-01-01, Clara, Apples, 1, 1 | |
2019-01-01, Denise, Apples, 1, 1 | |
; | |
-- Check the table data | |
SELECT * FROM tmp_test_replacing; | |
-- Change the versioning var | |
INSERT INTO tmp_test_replacing FORMAT CSV | |
2019-01-01, Amy, Apples, 2, 1 | |
2019-01-01, Bob, Apples, 2, 1 | |
2019-01-01, Clara, Apples, 1, 1 | |
2019-01-01, Denise, Apples, 1, 1 | |
; | |
OPTIMIZE TABLE tmp_test_replacing; | |
SELECT * FROM tmp_test_replacing; | |
-- Change the independent var | |
INSERT INTO tmp_test_replacing FORMAT CSV | |
2019-01-01, Amy, Apples, 2, 2 | |
2019-01-01, Bob, Apples, 2, 2 | |
2019-01-01, Clara, Apples, 1, 1 | |
2019-01-01, Denise, Apples, 1, 1 | |
; | |
OPTIMIZE TABLE tmp_test_replacing; | |
SELECT * FROM tmp_test_replacing; | |
-- Change the partition var | |
INSERT INTO tmp_test_replacing FORMAT CSV | |
2019-01-02, Amy, Apples, 2, 2 | |
2019-01-02, Bob, Apples, 2, 2 | |
2019-01-01, Clara, Apples, 1, 1 | |
2019-01-01, Denise, Apples, 1, 1 | |
; | |
OPTIMIZE TABLE tmp_test_replacing; | |
SELECT * FROM tmp_test_replacing; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment