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

Question

我想把一个大的日志文件导入到（Postgres-）SQL中

某些字符串列非常重复，例如列“event_type”有10个不同字符串值中的1个。在

我对规范化数据有一个粗略的理解。在

首先，假设：将事件类型存储在一个单独的表（可能具有外键关系）中是有益的（对于存储大小、索引和查询速度来说）是否正确？

为了规范化，我必须检查原始日志中event_type的不同值，并将它们插入event_types表中。在

有很多字段类型，如event_类型。在

那么第二：在插入数据时，有没有一种方法可以告诉数据库创建和维护这种表？

有没有其他的策略来实现这一点？我和熊猫一起工作。在

Answer 1

这是一种典型的情况，当开始从迄今为止存储的数据（如日志文件）构建数据库时。有一个解决办法-像往常一样-但它不是一个很快的。也许您可以编写一个日志消息处理程序来处理传入的消息；如果流（messages/second）不是太大，您不会注意到开销，特别是当您可以忘记将消息写入纯文本文件时。在

首先，关于规范化问题。是的，你应该总是规范化和所谓的第三范式（3NF）。这基本上意味着任何类型的真实数据（例如您的事件类型）只存储一次。（有些情况下，您可以稍微放松一下，转到2NF—通常只有在实际数据需要很少存储的情况下，例如ISO国家代码、M/F（男/女）选项等—但在大多数情况下，3NF会更好。）

在您的特定案例中，假设您的event_类型是char(20)类型。十个这样的事件及其对应的int代码很容易就可以放在一个数据库页面上，通常是4kB的磁盘空间。如果您有1000条事件类型为char(20)的日志消息，那么您只需要20kB来存储该信息，或者需要五个数据库页。如果您的日志消息中有其他类似的项目，那么存储减少也相应地变大。其他项目，如date或{}可以其本机格式存储（分别为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;

您需要的表格包括：

^{pr2}$

以及

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）字符串中的引号被正确转义（这样“不是”之类的词不会弄乱命令）；（2）它可以防止恶意生成日志消息的SQL注入。在

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