You need to enable JavaScript to run this app.
导航
部分列更新
最近更新时间:2024.08.20 15:26:30首次发布时间:2024.08.20 15:26:30

ByteHouse云数仓版支持部分列更新模式。
在行更新模式时,缺省列采用默认值填充。而在列更新模式下,缺省列如果有原值会保留,否则填充默认值。

适用场景

  1. 数据更新操作来自多个数据源,每个数据源更新数据行的一部分列的数据。
  2. 存量数据做 ETL 操作,更新存量数据的一部分列的数据。

使用限制

不支持在表级别唯一时,使用部分列更新模式(参见表级别唯一键)。

使用方法

部分列更新使用 功能列_update_columns_ 来指定希望更新哪些列。

  1. _update_columns_中的内容是需要更新的列名,以逗号分隔
  2. ByteHouse 不会处理列名前后的特殊字符,如空格、Tab、换行符等,且不支持正则表达式。
  3. 当指定_update_columns_为空值''时,表示更新所有列。

_update_columns_列的更新方式,遵循如下规则:

  1. 对于非Map类型:列指定为_update_columns_ 时直接更新,允许更新为默认值。
  2. 对于Map类型:
    • partial_update_enable_merge_map = true 时:
      • 对有旧值的key进行更新。
      • 对于新key则直接写入,未更新的旧key保留原值;
    • partial_update_enable_merge_map = false 时,为直接替换value。

使用举例

下面列举了一个包含map类型,且设置 partial_update_enable_merge_map = false 的场景。
通过下面的举例,可以看到_update_columns_字段的基本用法,以及map类型在更新时会直接替换value而不是根据键key来更新。

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注:UNIQUE KEY 不支持 Nullable
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;

-- 可以不显示的设置,session 级别 enable_unique_partial_update 默认值为 1
SET enable_unique_partial_update = 1; 

-- 不指定 _update_columns_,自动解析填充 _update_columns_ 为'k,c1,c2,m1,a1'
-- 等价于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'] │
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘

-- 主动指定 _update_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'] │
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘

-- 主动指定 _update_columns_,不在_update_columns_中的列,就算有数据也不会更新
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'] │
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘

-- 不指定 _update_columns_,自动解析填充 _update_columns_ 为'k,c1,c3,m1'
-- 等价于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'] │
└───┴────┴──────┴─────┴──────┴──────────┴───────────┘

-- 当主动指定 _update_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'] │
└───┴────┴──────┴─────┴────┴──────────┴───────────────────┘

-- 更灵活的用法:行级别指定 _update_columns_
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'] │
└───┴─────┴─────┴─────┴────┴──────────┴───────────────────┘

下面列举了一个包含map类型,且设置 partial_update_enable_merge_map = true 的场景。
通过下面的举例,可以看到与上面的区别在于,map类型在更新时会根据键key来更新,对于存在的key进行更新,而对于新增的key,会保留原值并插入新的key-value。

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注:UNIQUE KEY 不支持 Nullable
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;

-- 可以不显示的设置,session 级别 enable_unique_partial_update 默认值为 1
SET enable_unique_partial_update = 1; 

-- 不指定 _update_columns_,自动解析填充 _update_columns_ 为'k,c1,c2,m1,a1'
-- 等价于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'] │
└───┴────┴──────┴──────┴──────┴──────────┴───────────┘

-- 主动指定 _update_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'] │
└───┴────┴──────┴──────┴──────┴─────────────────┴───────────┘

-- 主动指定 _update_columns_,不在_update_columns_中的列,就算有数据也不会更新
-- Attention:map 类型会进行更新
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':20} │ ['world'] │
└───┴────┴──────┴──────┴──────┴─────────────────┴───────────┘

-- 不指定 _update_columns_,自动解析填充 _update_columns_ 为'k,c1,c3,m1'
-- 等价于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':20,'k3':3} │ ['world'] │
└───┴────┴──────┴─────┴──────┴─────────────────────────┴───────────┘

-- 当主动指定 _update_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':20,'k3':3,'k4':4} │ ['hello','world'] │
└───┴────┴──────┴─────┴────┴────────────────────────────────┴───────────────────┘

-- 更灵活的用法:行级别指定 _update_columns_
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':20,'k3':3,'k4':4} │ ['hello','world'] │
└───┴─────┴─────┴─────┴────┴────────────────────────────────┴───────────────────┘