更新时间:2024-05-28 GMT+08:00

Upsert数据写入

本章节主要介绍ClickHouse数据写入时数据去重写入功能的SQL基本语法和使用说明。

本章节仅适用于MRS 3.3.0及之后版本。

基本语法

  • 方法一:使用INSERT VALUES方式进行数据写入。

    UPSERT INTO [database_name.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

  • 方法二:使用INSERT SELECT方式进行数据写入。

    UPSERT INTO [database_name.]table [(c1, c2, c3)] SELECT ...

使用示例

  • 建表样例:
    CREATE TABLE default.upsert_tab ON CLUSTER default_cluster
    (    
    `id`     Int32,    
    `pdate`  Date,    
    `name`   String 
    )ENGINE = ReplicatedMergeTree('/clickhouse/tables/default/{shard}/upsert_tab', '{replica}')
    PARTITION BY toYYYYMM(pdate)
    PRIMARY KEY id 
    ORDER BY id 
    SETTINGS index_granularity = 8192;
  • Upsert数据去重写入:
    Upsert into upsert_tab(id, pdate, name) values (1, rand() % 365, 'abc'), (2, rand() % 365, 'bcd'), (1, rand() % 365, 'def');
  • 查询test_upsert表数据
    select * from upsert_tab;
    ┌─id─┬───pdate─┬─name─┐
    │  2   │   1970-06-09│  bcd   │
    │  1   │   1970-11-30│  def   │
    └───┴── ────┴────┘
  • Upsert支持事务

    与其他SQL语法类型一样,upsert语法也支持显式和隐式事务,使用事务前需要进行相应的事务功能开启配置。

注意事项

  • MergeTree和ReplicatedMergeTree建表要指定primary key或order by字段作为去重唯一键。如果未指定主键,只指定了order by建表属性,去重键以order by字段为准。
  • 数据去重的key需要提前在应用中进行sharding计算,保证相同的key会sharding到同一个shard,才能保证后续相同的key字段数据sharding到同一个shard进行数据的精确去重。