YugabyteDB YCQL单语句Upsert实现及时间戳条件更新报错排查

[该问题由用户发布于YugabyteDB Community Slack]

需要实现以下逻辑：

• 记录不存在时执行insert操作
• 记录已存在时执行条件update操作
  需要确认是否可以不单独执行select查询，仅通过单条insert或update语句完成该Upsert操作；如果无法实现单语句Upsert，仅update场景是否支持基于时间戳列的条件更新。

示例DDL #

CREATE TABLE test.mytable (
    h1 text,
    h2 text,
    name text,
    event_ts timestamp,
    event_id text,
    ev_type text,
    payload text,
    payload_full text,
    created_ts timestamp,
    modified_ts timestamp,
    update_ts timestamp,
    PRIMARY KEY ((h1, h2), name, event_ts, event_id)
) WITH CLUSTERING ORDER BY (name ASC, event_ts DESC, event_id ASC)
    AND default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX consumer_event_idx ON test.mytable ((h1, h2), event_ts, event_id, name) INCLUDE (ev_type, payload, created_ts, modified_ts)
    WITH transactions = {'enabled': 'true'};

问题语句与报错 #

ycqlsh:cdp> UPDATE test.mytable set payload = 'payload_updated'
       ... WHERE h1 = '1' and h2 = 'return' and name = 'mymymy' and event_ts = '2022-06-20 11:11:11' and event_id = 'event_id_1'
       ... IF update_ts < totimestamp('2022-06-21 12:12:12');
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid Arguments. Invalid date format
UPDATE test.mytable set payload = 'payload_updated'
WHERE h1 = '1' and h2 = 'return' and name = 'mymymy' and event_ts = '2022-06-20 11:11:11' and event_id = 'event_id_1'
IF update_ts < totimestamp('2022-06-21 12:12:12');
   ^^^^^^^^^^^^^^^
 (ql error -304)"

1. 原语句报错原因 #

totimestamp()函数的作用是将TIMEUUID类型的值转换为TIMESTAMP类型，不支持传入日期字符串作为入参，因此触发了日期格式非法报错。YCQL中TIMESTAMP类型可以直接识别'yyyy-MM-dd HH:mm:ss'格式的字符串字面量，不需要额外函数转换。

2. 基于时间戳的条件更新支持 #

完全支持。你建表时已经开启了事务支持，直接通过轻量事务（LWT）的IF子句即可实现基于时间戳列的条件更新，不需要提前执行select查询。
仅做条件更新（记录不存在则不操作）的修正后语句如下：

UPDATE test.mytable 
SET payload = 'payload_updated',
    update_ts = '2022-06-21 12:12:12',
    modified_ts = totimestamp(now())
WHERE h1 = '1' 
  AND h2 = 'return' 
  AND name = 'mymymy' 
  AND event_ts = '2022-06-20 11:11:11' 
  AND event_id = 'event_id_1'
IF update_ts < '2022-06-21 12:12:12';

说明：当记录不存在时，update_ts值为null，与任何时间戳比较结果都为false，因此不会执行插入操作，仅当记录存在且满足时间戳条件时才会执行更新。

3. 单语句Upsert实现 #

YCQL中UPDATE语句本身自带Upsert语义：只要指定了完整主键，匹配不到记录时会自动创建新行。你只需要在IF条件中增加对update_ts为空的判断，同时将插入需要的所有字段放到SET子句中，即可单语句实现「不存在则插入，存在则按时间条件更新」的逻辑：

UPDATE test.mytable 
SET payload = 'payload_updated',
    payload_full = '对应全量payload值',
    ev_type = '对应事件类型值',
    update_ts = '2022-06-21 12:12:12',
    created_ts = totimestamp(now()),
    modified_ts = totimestamp(now())
WHERE h1 = '1' 
  AND h2 = 'return' 
  AND name = 'mymymy' 
  AND event_ts = '2022-06-20 11:11:11' 
  AND event_id = 'event_id_1'
IF update_ts < '2022-06-21 12:12:12' OR update_ts IS NULL;

注意：该写法在更新已有记录时会覆盖created_ts字段，如果需要created_ts仅在首次插入时写入，可以先执行INSERT ... IF NOT EXISTS语句，返回applied为false时再执行条件UPDATE语句，两次操作均在轻量事务保障下执行，不会出现并发一致性问题。

如果你使用的是YugabyteDB的PostgreSQL兼容接口YSQL，直接使用标准的INSERT ... ON CONFLICT (主键列) DO UPDATE SET ... WHERE语法即可实现更灵活的Upsert逻辑。

内容的提问来源于stack exchange，提问作者dh YB