如何在插入SQL表（Postgres）时高效地规范化数据

Question

我想把一个很大的日志文件导入到（Postgres）SQL里。

有些字符串列的内容重复得很厉害，比如'event_type'这一列就只有10种不同的字符串值。

我对数据规范化有个大概念。

首先，我想确认一下：把'event_type'存到一个单独的表里（可能还要加个外键关系），这样对存储空间、索引和查询速度都有好处，对吗？

为了规范化，我需要先检查原始日志中'event_type'的不同值，然后把这些值插入到'event_types'表里。

像'event_types'这样的字段类型还有很多。

所以第二个问题是：有没有办法让数据库在插入数据时自动创建和维护这样的表？

还有其他的策略可以实现这个吗？我现在在用pandas。

Answer 1

当你开始建立一个数据库，而之前的数据是存储在其他地方，比如日志文件中，这种情况是很常见的。虽然有解决办法，但通常速度不快。你可以写一个日志消息处理程序，来处理接收到的消息；如果每秒钟的消息量不是特别大，你可能不会感觉到处理的延迟，尤其是当你不需要把消息写入一个普通文本文件时。

首先，关于数据规范化的问题。是的，你应该始终进行规范化，最好达到所谓的第三范式（3NF）。这基本上意味着任何现实世界的数据（比如你的事件类型）只需要存储一次。虽然在某些情况下，你可以稍微放宽要求，使用第二范式（2NF），通常只有在现实世界的数据占用空间非常小的情况下，比如一个国家的ISO代码，或者性别选择（男/女）等，但在大多数情况下，第三范式会更好。

以你的具体情况为例，假设你的事件类型是 char(20) 类型。十个这样的事件及其对应的 int 代码可以轻松放在一个数据库页面上，通常是4kB的磁盘空间。如果你有1000条日志消息，每条消息的事件类型是 char(20)，那么你需要20kB来存储这些信息，也就是五个数据库页面。如果你的日志消息中还有其他项目，那么存储空间的节省会更大。像 date 或 timestamp 这样的其他项目可以以其原生格式（分别为4和8字节）存储，这样可以节省空间，提高性能，并增加功能（比如比较日期或查看范围）。

其次，你不能让数据库自动创建这些表，你需要自己来做。但一旦创建了表，就可以用存储过程来解析你的日志消息，并把数据放到正确的表中。

在处理日志消息时，你可以这样做（假设你想在数据库中进行解析，而不是在Python中）：

CREATE FUNCTION ingest_log_message(mess text) RETURNS int AS $$
DECLARE
  parts  text[];
  et_id  int;
  log_id int;
BEGIN
  parts := regexp_split_to_array(mess, ','); -- Whatever your delimiter is

  -- Assuming:
  --   parts[1] is a timestamp
  --   parts[2] is your event_type
  --   parts[3] is the actual message

  -- Get the event_type identifier. If event_type is new, INSERT it, else just get the id.
  -- Do likewise with other log message parts whose unique text is located in a separate table.
  SELECT id INTO et_id
  FROM event_type
  WHERE type_text = quote_literal(parts[2]);
  IF NOT FOUND THEN
    INSERT INTO event_type (type_text)
    VALUES (quote_literal(parts[2]))
    RETURNING id INTO et_id;
  END IF;

  -- Now insert the log message
  INSERT INTO log_message (dt, et, msg)
  VALUES (parts[1]::timestamp, et_id, quote_literal(parts[3]))
  RETURNING id INTO log_id;

  RETURN log_id;
END; $$ LANGUAGE plpgsql STRICT;

你需要的表有：

CREATE TABLE event_type (
  id        serial PRIMARY KEY,
  type_text char(20)
);

和

CREATE TABLE log_message (
  id        serial PRIMARY KEY,
  dt        timestamp,
  et        integer REFERENCES event_type
  msg       text
);

然后你可以像简单的 SELECT 语句一样调用这个函数，它会返回新插入的日志消息的 id：

SELECT * FROM ingest_log_message(the_message);

注意在函数体中使用了 quote_literal() 函数。这个函数有两个重要作用：（1）字符串中的引号会被正确转义（这样像 "isn't" 这样的词不会搞乱命令）；（2）它可以防止恶意生成日志消息的SQL注入攻击。

以上所有内容显然需要根据你的具体情况进行调整。