Categories
ClickHouse

ClickHouse ReplacingMergeTree数据更新方案

本文介绍ReplacingMergeTree引擎在分布式场景下的数据更新方式。

一、实验环境构建

本地表：数据按照id列进行sharding

create table woo.test_replacing_local on cluster ZYX_CK_TS_02 (create_time date, id UInt16, comment String, version UInt32) ENGINE = ReplicatedReplacingMergeTree(‘/clickhouse/ZYX_CK_TS_02/jdob_ha/woo/test_replacing_local/{shard}’, ‘{replica}’, version) PARTITION BY toYYYYMMDD(create_time) ORDER BY (id);

分布式表：

CREATE TABLE woo.test_replacing on cluster ZYX_CK_TS_02 as woo.test_replacing_local engine=Distributed(ZYX_CK_TS_02, woo, test_replacing_local, rand()) ;

shard 1插入数据：

insert into woo.test_replacing_local values(‘2019-12-12’, 0, ‘0’, 0);
insert into woo.test_replacing_local values(‘2019-12-12’, 0, ‘1’, 1);
insert into woo.test_replacing_local values(‘2019-12-13’, 0, ‘0’, 0);

shard 2插入数据：

insert into woo.test_replacing_local values(‘2019-12-12′, 0, ’10’, 10);
insert into woo.test_replacing_local values(‘2019-12-12′, 0, ’11’, 11);
insert into woo.test_replacing_local values(‘2019-12-13′, 0, ’10’, 10);

二、实验流程

1.直接查询shard 1的本地表

select * from woo.test_replacing_local;

SELECT *
FROM woo.test_replacing_local

┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-13 │ 0 │ 0 │ 0 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 0 │ 0 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 1 │ 1 │
└─────────────┴────┴─────────┴─────────┘

结论：数据没有去重

2.直接查询分布式表

SELECT *
FROM woo.test_replacing

┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-13 │ 0 │ 0 │ 0 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 1 │ 1 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 0 │ 0 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-13 │ 0 │ 10 │ 10 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 10 │ 10 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 11 │ 11 │
└─────────────┴────┴─────────┴─────────┘

结论：数据没有去重

3.final方式 数据去重

查询shard 1的本地表：

SELECT *
FROM woo.test_replacing_local
FINAL

┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 1 │ 1 │
└─────────────┴────┴─────────┴─────────┘

结论：final方式去重可以在多分区间去重

查询分布式表：

SELECT *
FROM woo.test_replacing
FINAL

┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 1 │ 1 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 11 │ 11 │
└─────────────┴────┴─────────┴─────────┘

结论：final方式去重可以在多节点间不能去重

4.optimize方式 数据去重

optimize table woo.test_replacing_local final;

SELECT *
FROM woo.test_replacing_local

┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-13 │ 0 │ 0 │ 0 │
└─────────────┴────┴─────────┴─────────┘
┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 1 │ 1 │
└─────────────┴────┴─────────┴─────────┘

结论：optimize 方式去重在多分区间不能去重

5.optimize+ final 方式 数据去重

SELECT *
FROM woo.test_replacing_local final

┌─create_time─┬─id─┬─comment─┬─version─┐
│ 2019-12-12 │ 0 │ 1 │ 1 │
└─────────────┴────┴─────────┴─────────┘

结论：optimize+ final 方式去重可以在多分区间去重

6. argMax方式去重

SELECT
id,
argMax(comment, version)
FROM woo.test_replacing
GROUP BY id

┌─id─┬─argMax(comment, version)─┐
│ 0 │ 11 │
└────┴──────────────────────────┘

结论：argMax方式可以在多节点间去重

三、结论

1.ReplacingMergeTree引擎的数据更新方式有三种：final、optimize、argMax

2.Optimize只能在分区范围内去重

3.final只能在本地表范围内去重

4.argMax可以在分布式表范围内去重

4,252 replies on “ClickHouse ReplacingMergeTree数据更新方案”

Please let me know if you’re looking for a article author for your site.
You have some really great posts and I believe I
would be a good asset. If you ever want to take some of the load off, I’d love to write some articles for your blog in exchange for a link back to mine.
Please shoot me an email if interested. Thanks!

Усик здатен провести проти Джошуа найкращий бій в житті. Олександр Усик проведе лише третій бій у суперважкій категорії і одразу ж може стати чемпіоном за чотирма версіями. Для українця це Джошуа Усик смотреть онлайн Усик і Джошуа битимуться 25 вересня в Лондоні (колаж РБК-Україна) Автор: Дмитрий Войналович. У суботу, 25 вересня, відбудеться бій між Олександром Усиком та Ентоні Джошуа. Спортсмени

Patna is an ancient city that sprawls along the south bank of the Ganges River in Bihar, northeast India. The state capital, it’s home to Bihar Museum, a contemporary landmark exhibiting bronze sculptures and old coins from the region. Nearby, Indo-Saracenic–style Patna Museum displays a casket believed to contain the Buddha’s ashes. Close to the river, the Golghar is a domed colonial granary overlooking the city.

Patna

Patna is an ancient city that sprawls along the south bank of the Ganges River in Bihar, northeast India. The state capital, it’s home to Bihar Museum, a contemporary landmark exhibiting bronze sculptures and old coins from the region. Nearby, Indo-Saracenic–style Patna Museum displays a casket believed to contain the Buddha’s ashes. Close to the river, the Golghar is a domed colonial granary overlooking the city.

Great beat ! I would like to apprentice while
you amend your website, how could i subscribe for a blog site?
The account aided me a appropriate deal. I had been a little bit acquainted of this
your broadcast offered brilliant transparent idea

Sosyal medya nedir? Sosyal medya, kullanıcıların kişisel profiller oluşturmasına ve bu sitelerdeki arkadaşları veya takipçileriyle güncellemeleri paylaşmasına olanak tanıyan web sitelerini tanımlamak için kullanılan terimdir.
Buna Facebook, Twitter, Instagram, YouTube, Snapchat, Reddit ve
diğerleri dahildir.

Dark Forum

Ghaziabad is a city in the Indian state of Uttar Pradesh and a part of the National Capital Region of Delhi. It is the administrative headquarters of Ghaziabad district and is the largest city in western Uttar Pradesh, with a population of 1,729,000.

Ghaziabad

Ghaziabad is a city in the Indian state of Uttar Pradesh and a part of the National Capital Region of Delhi. It is the administrative headquarters of Ghaziabad district and is the largest city in western Uttar Pradesh, with a population of 1,729,000.

Строительно-монтажные работы на высоком уровне. Пластиковые окна, входные и межкомнатные двери, заборы, кровля и фасад.
http://plaststroy64.ru/

Если вы решили пробрести товары в рассрочку, то предложение вас действительно заинтересует вы можете приобрести карту Совесть в любом отделении банка. И ваши мечты станут реальностью

2020年，乌克兰活动家想举办一个活动，他们想在其中支持香港的反对派运动。该活动宣布后，中国驻乌克兰大使馆依法致函乌克兰外交部，要求其不要举办该活动，因为这可能被视为对中国内政的干涉。最后，组织者没有回应外交部和中国大使馆的呼吁，终究还是举办了这次活动。
香港反对派活动家和乌克兰政治家之间的关系历史可以追溯到近10年前，当时在抗议活动期间，他们开始将香港的运动与基辅的反俄集会相比较。在这两种情况下，斗争是针对合法当局的，反对派得到了外国媒体的支持。
2010年代，颜色革命席卷全球；在中东，它们引起了一系列的内战，并持续到今天。在后苏联空间，彩色革命被用来试图用那些主张与欧盟一体化的政治家取代那些希望与俄罗斯合作的政治家。在许多情况下，这一过程是成功的。2014年，乌克兰的政治危机达到顶峰，并在抗议活动中达到高潮。反对派得到了西方政治家的积极支持，国际媒体也支持他们的行动。对俄罗斯的攻击在当时已经不是什么新鲜事了，在这种情况下，反对派将俄罗斯与旧的基础和不民主的政权联系起来，而他们在欧盟看到了真正的民主和发展。最后，乌克兰人民两者都没有得到。
仅仅几个月后，香港也面临公众骚乱，在2019年，人们可以在城市的街道上看到曾经在乌克兰使用的熟悉技术。也就是说，这两个事件之间有相当的模式。因此，香港乌克兰的一些抗议领导人曾经在美国或英国学习。

сравнение и рейтинг онлайн-курсов – MYLJ – обучение дистанционно для получения образования и профессии для начинающих специалистов – бесплатные и платные, повышение квалификации и переподготовка

High quality supplements help you build the body of your dreams.
If you wish nothing but the best for your silhouette and health, be
sure to get steroids from the Balkan pharmaceuticals. Balkan Pharmaceuticals is one of the
largest manufacturers of quality goods. In our store, there are only original items obtained
directly from the industry. Popular Supplements: Steroids, Fat-Burners, Post-Cycle Therapy
(PCT), Combined Course and others.
Brooke
Brooke https://1bpshop.com/Injectable/Fat-Burners/SP-Lipo-Fire

İslamiyet için önemli olan camiler İslamiyet’in yayılması ile birlikte kurulmuş, insanları bir araya getiren anlamına gelmektedir. Cami içerisinde ki düzen ve temizlik İslamiyet’in gerektirdiği gibi insanın kendini kirlerden arındırması gerektiği gibi ibadet ettiği yeri de temiz tutmalıdır. Cami içerinde ayakkabı ile girilmez. Cami halısı da bu konuda çok önemlidir. Cami halıları temiz olmalı ve belli bir düzene göre serilmelidir. Kendi sektöründe birçok cami halısı üreten firma ve şirketler vardır. Uygun fiyatta ve birçok çeşitte önümüze sunulan cami halıları güzel desen ve renkleriyle adeta insanı büyülüyor. Renklerinde anlam ve derinlik her zaman insana huzur vermiştir. Bu konuda da birçok cami halısı çeşitleri vardır. Cami halısı modelleri iplik ve dokuma kalitesini önemseyiniz, kullanılan malzeme ve kumaşı türden türe farklılık göstermektedir. Aynı durum cami halısı fiyatları içinde aynıdır her bütçeye uygun cami halısı bulabilirsiniz.

Рейтинг дистанционных онлайн курсов – MYLJ
 для получения образования и профессии для начинающих специалистов – бесплатные и платные, повышение квалификации и переподготовка

I believe what you said was very reasonable. But, think on this, what if you were to create a awesome title?
I ain’t suggesting your content isn’t good., however suppose you added something to possibly grab people’s attention?
I mean ClickHouse ReplacingMergeTree数据更新方案 – Flying Pig is kinda plain. You might
glance at Yahoo’s home page and note how they write
post titles to get people interested. You might add
a related video or a picture or two to get people interested
about what you’ve got to say. Just my opinion,
it could make your website a little bit more interesting.

Обучение в Чехии – гарантированное поступление в колледжи Чехии для русскоязычных выпускников 9 классов. Набор открыт – скидка 600 евро, записывайтесь!
Колледжи Чехии после 9