Search

Updates and Deletes in ClickHouse

It has been few years already when clickhouse development team written blog post about how to update data in ClickHouse. Older version of ClickHouse only supports monthly partitions and changeable structures. We are waiting for easy approach to change the data and finally now we can do Updates and Deletes in ClickHouse. In this blog I will tell you how to do it.


Test Data


Load some test data into the ClickHouse table.

We will show how updates will work as Delete is going to be easy after that.


How it works


The sentence structure for updates and deletes is non-standard SQL. ClickHouse team needed to direct the modification from old-style SQL: new updates and deletes are batch actions, made asynchronously. It is even termed 'mutations'. Routine syntax highpoints the modification.


ALTER TABLE <table_name> DELETE WHERE <filter>;


and


ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>;


In our test table there is column description.


Let's study that status 16 was a fault and we want to fix it. This is just 96% of the data, but without DELETE or UPDATE, we would have to load again the table.

It returns instantly, but the update is asynchronous, so we do not tell if data has been updated or not. Let's crisscross:



Appears to be working. The status of the update action can be observed up in system.mutations table:


Alternative exciting awareness gives system.parts table:

It displays that every part has been affected by update. But it has been fast for small test dataset.

How Delete works


Delete is simple to do.


Conclusion


ClickHouse lastly provisions UPDATE/DELETE functionality, however in its own tradition way. There are some restrictions, but it does the work when used for occasional actions. It makes ClickHouse GDPR submissive, since data can be deleted at all time.

9 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa