主页 / dba / 问题 / 177691
Accepted
Mio
Asked: 2017-07-01 07:04:32 +0800 CST

验证 Redshift 记录中的 id 序列一致性

  • 772

我有一个将事件记录到 Redshift 的后端，它为每个事件生成一个唯一的 ID。id 是一个序列号。

我有类似的东西（events表）：

+-------------------------+------+
| created_at              |  id  |
+-------------------------+------+
| 2017-06-30 09:20:47 UTC | 100  |
| 2017-06-30 09:18:31 UTC | 101  |
| 2017-06-30 09:16:19 UTC | 102  |
| 2017-06-30 09:12:08 UTC | 103  |
| 2017-06-30 09:11:59 UTC | 104  |
| 2017-06-30 09:11:15 UTC | 105  |
| 2017-06-30 07:03:41 UTC | 106  |
+-------------------------+------+

我的任务不是每小时运行一次，将很少的记录移动到另一个表 ( deactivated_events)。

我想验证在过去 3 小时内我没有使用id序列丢失任何记录。首先我考虑过使用 generate_series 但这在 Redshift 中不存在。其他人建议制作一个只有 ID 的表，但是用整数填充数据库仍然很痛苦（这里是一个生成 100 万的例子

我想知道最好的方法是不使用 min max 并像这样计数：

WITH merged_events AS
  (SELECT *
   FROM
     (SELECT id, created_at
      FROM events
      UNION 
      SELECT id, created_at
      FROM deactivated_events
     )
   WHERE created_at > GETDATE() - INTERVAL '3 hours'
   ORDER BY id)
SELECT COUNT(*), (max(id) - min(id) + 1) AS diff
FROM merged_events;

PS：奖金，如何找到丢失或重复的记录？

redshift gaps-and-islands

2 个回答

  1. Best Answer

    假设您有这样的设置：

    INSERT INTO events
    (created_at, id)
VALUES
    ('2017-06-30 09:20:47 UTC', 100),
    -- ('2017-06-30 09:18:31 UTC', 101), -- Missing row
    ('2017-06-30 09:16:19 UTC', 102),
    ('2017-06-30 09:12:08 UTC', 103),
    ('2017-06-30 09:11:59 UTC', 104),
    ('2017-06-30 09:11:15 UTC', 105),
    ('2017-06-30 07:03:41 UTC', 106) ;

    和 ...

    INSERT INTO deactivated_events
    (created_at, id)
VALUES
    ('2017-06-30 07:03:41 UTC', 97),
    ('2017-06-30 09:11:15 UTC', 98),
    ('2017-06-30 09:11:15 UTC', 99),
    ('2017-06-30 09:18:31 UTC', 100)     -- Repeated row
    ;

    如果 Redshift 提供Window Functions，您可以使用查询的细微变化来做两件事：

    1. id考虑分组时有重复的s id，发现count > 1
    2. id如果前一行的 不是 1 + 当前行，则认为缺少一行（或更多行！） 。这是通过LAG函数完成的。

    这可以通过以下查询完成

    WITH merged_events AS
(
  SELECT 
      id
  FROM
  (
      SELECT 
          id
      FROM 
          events
      UNION ALL  /* Must be UNION ALL, because we want to find repeated values */
      SELECT 
          id
      FROM 
          deactivated_events
  ) AS q0
  WHERE true -- In practice, created_at > GETDATE() - INTERVAL '3 hours'
)
SELECT 
    id, 
    count(id) > 1 AS repeated_event, 
    ((lag(id) OVER(ORDER BY id)) /* previous id */ + 1) <> id AS previous_event_missing
FROM 
    merged_events
GROUP BY
    id ;

    这将产生：

    编号 | 重复事件 | 先前_事件_缺失
--: | :------------ | ：--------------------
 97 | f | 无效的                  
 98 | f | F                     
 99 | f | F                     
100 | 真           | F                     
102 | f | 真的                     
103 | f | F                     
104 | f | F                     
105 | f | F                     
106 | f | F

    您可以在dbfiddle此处检查整个设置（使用 PostgreSQL 而不是 RedShift）

    注意事项id：如果它实际上来自一个IDENTITY列（或者SEQUENCE它的 Redshift 恰好实现了它，我认为情况并非如此），您可能会有差距。所以，你应该首先通过其他方式保证你的id最初是连续的......

    参考：

    • 2

  2. 我的任务不是每小时运行一次，将很少的记录移动到另一个表 (deactivated_events)。

    这听起来真是个坏主意。相反，我会敦促您创建一个新active的 type列bool。特别是如果您需要合并该表中的事件。

    PS：奖金，如何找到丢失或重复的记录？

    如果您停止从表中删除，那将不再是一个问题。

    另外，请参阅@joanolo 关于序列的注释。在 Redshift上也是如此，IDENTITY COLUMNS

    为确保身份值是唯一的，Amazon Redshift 在创建身份值时会跳过一些值。因此，标识值是顺序的，但不是连续的。

    通常，您不想将数据移出表以对其进行存档。特别是如果你不必这样做。PostgreSQL 10 即将发布。Redshift 很可能会升级到使用它。如果他们这样做，您将拥有价值分区，它将透明和自动地执行此操作。

    • 1

相关问题