The unique key table in ByteHouse CDW supports partial column update operations. This feature is suitable for scenarios where: data update operations originate from multiple data sources, with each data source updating only a subset of columns in a data row; or ETL operations are performed on existing data, updating only certain columns of the existing data. This article introduces the working principles and provides usage examples for partial column update.

Function overview #

Partial column update uses the functional column_update_columns_ to specify which columns to update.

• The content in _update_columns_ lists the columns to be updated, separated by commas. The engine does not process special characters before or after column names, such as spaces, tabs, or line breaks, and regular expressions are not supported.
• _update_columns_ supports two modes: manual specification and automatic parsing. When manually specifying_update_columns_ as'', it indicates that all columns will be updated.

The update method for the _update_columns_ column follows these rules:

• Non-Map type: If the column is included in _update_columns_, it is updated directly, and updating to the default value is allowed.
• Map type: If partial_update_enable_merge_map = true, keys with existing values are updated, new keys are written directly, and old keys not updated retain their original values; if set to false, the value is replaced directly.
• Nullable type: When using REPLACE_IF_NOT_NULL semantics, writing null retains the original value.

Working principle #

Partial column update includes the following steps:

1. Locate the position of the existing data based on incremental data and the unique key index.
2. Read the relevant columns of the existing data (the red section in the example diagram below).
3. Set the delete bitmap column of the corresponding row in the existing data to 1, marking it as deleted.
4. Generate incremental data.

The following is an example. In this example, the table is unique at the partition level, PK is the partition key, UK is the unique key, and delete bitmap is the column used for marking deletion.

Data with the delete bitmap column set to 1 is marked as deleted. After incremental data is written and partial column update is performed, the latest data is:

2020-10-29，1，m，1001 
2020-10-29，2，n，1002 
2020-10-30，1，z，2001 
2020-10-30，2，k，0

Usage example #

partial_update_enable_merge_map = false #

-- The engine ensures the uniqueness of the unique key within the partition by default 
CREATE TABLE t8 ( 
  k Int32, 
  c1 Int32, 
  c2 Nullable(Float64), 
  c3 Nullable(String), 
  c4 Nullable(Int64), 
  m1 Map(String, Int32), 
  a1 Array(String)) 
ENGINE = CnchMergeTree 
UNIQUE KEY k ORDER BY k SETTINGS enable_unique_partial_update = 1, 
partial_update_enable_merge_map = 0; 

-- This can be set implicitly; the session-level enable_unique_partial_update default value is 1 
SET enable_unique_partial_update = 1; 

-- If _update_columns_ is not specified, automatic parsing fills _update_columns_ as 'k,c1,c2,m1,a1' 
-- Equivalent to INSERT INTO t8 (k, c1, c2, m1, a1, _update_columns_) VALUES (1, 10, 3.14, {'k1':1}, ['hello'], 'k,c1,c2,m1,a1'); 
INSERT INTO t8 (k, c1, c2, m1, a1) VALUES (1, 10, 3.14, {'k1':1}, ['hello']); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1───────┬─a1────────┐ 
│ 1 │ 10 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k1':1} │ ['hello'] │ 
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘ 

-- Manually specify _update_columns_; bold font indicates updated columns 
INSERT INTO t8 (k, c1, m1, a1, _update_columns_) VALUES (1, 20, {'k2':2}, ['world'], 'k,c1,m1,a1'); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1───────┬─a1────────┐ 
│ 1 │ 20 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k2':2} │ ['world'] │ 
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘ 

-- Manually specify _update_columns_. Columns not listed in _update_columns_ will not be updated, even if data is provided 
INSERT INTO t8 (k, c1, m1, a1, _update_columns_) VALUES (1, 200, {'k2':20}, ['world20'], 'k'); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1───────┬─a1────────┐ 
│ 1 │ 20 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k2':2} │ ['world'] │ 
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘ 

-- If _update_columns_ is not specified, it will be automatically analyzed and filled as 'k,c1,c3,m1' 
-- Equivalent to INSERT INTO t8 (k, c1, c3, m1, _update_columns_) VALUES (1, 0, 'foo', {'k3':3}, 'k,c1,c3,m1'); 
INSERT INTO t8 (k, c1, c3, m1) VALUES (1, 0, 'foo', {'k3':3}); 
┌─k─┬─c1─┬───c2─┬─c3──┬───c4─┬─m1───────┬─a1────────┐ 
│ 1 │  0 │ 3.14 │ foo │ ᴺᵁᴸᴸ │ {'k3':3} │ ['world'] │ 
└───┴────┴──────┴─────┴──────┴──────────┴───────────┘ 

-- When _update_columns_ is explicitly specified as '', it means updating all columns 
INSERT INTO t8 (k, c1, c2, c3, c4, m1, a1, _update_columns_) VALUES (1, 10, 31.4, 'goo', 15, {'k4': 4}, ['hello', 'world'], ''); 
┌─k─┬─c1─┬───c2─┬─c3──┬─c4─┬─m1───────┬─a1────────────────┐ 
│ 1 │ 10 │ 31.4 │ goo │ 15 │ {'k4':4} │ ['hello','world'] │ 
└───┴────┴──────┴─────┴────┴──────────┴───────────────────┘ 

-- More flexible usage: specify _update_columns_ at the row level 
INSERT INTO t8 (k, c1, c2, c3, c4, m1, a1, _update_columns_) FORMAT JSONEachRow {"k":"1", "c1": "100", "_update_columns_":"k,c1"} {"k":"1", "c2": "314.0", "_update_columns_":"k,c2"}; 
┌─k─┬──c1─┬──c2─┬─c3──┬─c4─┬─m1───────┬─a1────────────────┐ 
│ 1 │ 100 │ 314 │ goo │ 15 │ {'k4':4} │ ['hello','world'] │ 
└───┴─────┴─────┴─────┴────┴──────────┴───────────────────┘

partial_update_enable_merge_map = true #

-- The engine guarantees the uniqueness of the unique key within the partition by default 
CREATE TABLE t8m ( 
  k Int32, 
  c1 Int32, 
  c2 Nullable(Float64), 
  c3 Nullable(String), 
  c4 Nullable(Int64), 
  m1 Map(String, Int32), 
  a1 Array(String)) 
ENGINE = CnchMergeTree 
UNIQUE KEY k ORDER BY k SETTINGS enable_unique_partial_update = 1, 
partial_update_enable_merge_map = 1; 

-- It can be set implicitly; the session-level default value for enable_unique_partial_update is 1 
SET enable_unique_partial_update = 1; 

-- If _update_columns_ is not specified, it will be automatically analyzed and filled as 'k,c1,c2,m1,a1' 
-- Equivalent to INSERT INTO t8m (k, c1, c2, m1, a1, _update_columns_) VALUES (1, 10, 3.14, {'k1':1}, ['hello'], 'k,c1,c2,m1,a1'); 
INSERT INTO t8m (k, c1, c2, m1, a1) VALUES (1, 10, 3.14, {'k1':1}, ['hello']); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1───────┬─a1────────┐ 
│ 1 │ 10 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k1':1} │ ['hello'] │ 
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘ 

-- Explicitly specify _update_columns_; bold font indicates updated columns 
INSERT INTO t8m (k, c1, m1, a1, _update_columns_) VALUES (1, 20, {'k2':2}, ['world'], 'k,c1,m1,a1'); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1──────────────┬─a1────────┐ 
│ 1 │ 20 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k1':1,'k2':2} │ ['world'] │ 
└───┴────┴──────┴──────┴──────┴─────────────────┴───────────┘ 

-- Explicitly specify _update_columns_; columns not included in _update_columns_ will not be updated even if data is provided 
INSERT INTO t8m (k, c1, m1, a1, _update_columns_) VALUES (1, 200, {'k2':20}, ['world20'], 'k'); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1──────────────┬─a1────────┐ 
│ 1 │ 20 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k1':1,'k2':2} │ ['world'] │ 
└───┴────┴──────┴──────┴──────┴─────────────────┴───────────┘ 

-- If _update_columns_ is not specified, it will be automatically analyzed and filled as 'k,c1,c3,m1' 
-- Equivalent to INSERT INTO t8m (k, c1, c3, m1, _update_columns_) VALUES (1, 0, 'foo', {'k3':3}, 'k,c1,c3,m1'); 
INSERT INTO t8m (k, c1, c3, m1) VALUES (1, 0, 'foo', {'k3':3}); 
┌─k─┬─c1─┬───c2─┬─c3──┬───c4─┬─m1──────────────────────┬─a1────────┐ 
│ 1 │  0 │ 3.14 │ foo │ ᴺᵁᴸᴸ │ {'k1':1,'k2':2,'k3':3} │ ['world'] │ 
└───┴────┴──────┴─────┴──────┴─────────────────────────┴───────────┘ 

-- When _update_columns_ is explicitly specified as '', it means updating all columns 
INSERT INTO t8m (k, c1, c2, c3, c4, m1, a1, _update_columns_) VALUES (1, 10, 31.4, 'goo', 15, {'k4': 4}, ['hello', 'world'], ''); 
┌─k─┬─c1─┬───c2─┬─c3──┬─c4─┬─m1─────────────────────────────┬─a1────────────────┐ 
│ 1 │ 10 │ 31.4 │ goo │ 15 │ {'k1':1,'k2':2,'k3':3,'k4':4} │ ['hello','world'] │ 
└───┴────┴──────┴─────┴────┴────────────────────────────────┴───────────────────┘ 

-- More flexible usage: specify _update_columns_ at the row level 
INSERT INTO t8m (k, c1, c2, c3, c4, m1, a1, _update_columns_) FORMAT JSONEachRow {"k":"1", "c1": "100", "_update_columns_":"k,c1"} {"k":"1", "c2": "314.0", "_update_columns_":"k,c2"}; 
┌─k─┬──c1─┬──c2─┬─c3──┬─c4─┬─m1─────────────────────────────┬─a1────────────────┐ 
│ 1 │ 100 │ 314 │ goo │ 15 │ {'k1':1,'k2':2,'k3':3,'k4':4} │ ['hello','world'] │ 
└───┴─────┴─────┴─────┴────┴────────────────────────────────┴───────────────────┘

REPLACE_IF_NOT_NULL semantics #

-- The engine guarantees the uniqueness of the unique key within the partition by default 
CREATE TABLE t8n ( 
  k Int32, 
  c1 Int32, 
  c2 Nullable(Float64) REPLACE_IF_NOT_NULL, 
  c3 Nullable(String), 
  c4 Nullable(Int64), 
  m1 Map(String, Int32), 
  a1 Array(String)) 
ENGINE = CnchMergeTree 
UNIQUE KEY k ORDER BY k SETTINGS enable_unique_partial_update = 1, 
partial_update_enable_merge_map = 1; 

-- It can be set implicitly; the session-level default value for enable_unique_partial_update is 1 
SET enable_unique_partial_update = 1; 

-- If _update_columns_ is not specified, it will be automatically analyzed and filled as 'k,c1,c2,c3,m1,a1' 
-- Equivalent to INSERT INTO t8n (k, c1, c2, c3, m1, a1, _update_columns_) VALUES (1, 10, 3.14, 'foo', {'k1':1}, ['hello'], 'k,c1,c2,c3,m1,a1'); 
INSERT INTO t8n (k, c1, c2, c3, m1, a1) VALUES (1, 10, 3.14, 'foo', {'k1':1}, ['hello']); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1───────┬─a1────────┐ 
│ 1 │ 10 │ 3.14 │ foo │ ᴺᵁᴸᴸ │ {'k1':1} │ ['hello'] │ 
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘ 

-- If _update_columns_ is not specified, it will be automatically analyzed and filled as 'k,c2,c3,m1,a1' 
-- Writing null to c2, based on REPLACE_IF_NOT_NULL semantics, retains the old value 
-- Writing null to c3, the data column is updated to null; bold font indicates updated columns 
INSERT INTO t8n (k, c2, c3, m1, a1) VALUES (1, null, null, {'k2':2}, ['world']); 
┌─k─┬─c1─┬───c2─┬─c3───┬───c4─┬─m1──────────────┬─a1────────┐ 
│ 1 │ 10 │ 3.14 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ {'k1':1,'k2':2} │ ['world'] │ 
└───┴────┴──────┴──────┴──────┴─────────────────┴───────────┘

Insert on duplicate key syntax #

-- Attention： 
-- 1. Partial column update requires enable_unique_partial_update = 1 
-- 2. The transformation performed by on duplicate action should ensure 
-- The data type remains the same before and after transformation or both columns are numerical types 
:) CREATE TABLE mysql_test_on_duplicate_action ( 
    `a` Int32, 
    `b` Int32 
) 
ENGINE = CnchMergeTree 
ORDER BY a 
UNIQUE KEY a 
SETTINGS enable_unique_partial_update = 1; 

:) insert into mysql_test_on_duplicate_action values (1, 1); 
:) select * from mysql_test_on_duplicate_action; 
┌─a─┬─b─┐ 
│ 1 │ 1 │ 
└───┴───┘ 

:) insert into mysql_test_on_duplicate_action on duplicate key update b=4 values (1, 2), (2, 3); 
:) select * from mysql_test_on_duplicate_action; 
┌─a─┬─b─┐ 
│ 1 │ 4 │ 
│ 2 │ 3 │ 
└───┴───┘