如何在插入SQL表时有效地规范化数据(Postgres)

Question

我想导入一个大的日志文件到(Postgres-)SQL

某些字符串列非常重复，例如列'event_type'有10个不同的字符串值中的一个。

我对规范化数据有一个大致的了解。

首先，假设:将event_type存储在单独的表(可能带有外键关系)中是有益的(对于存储大小和索引和查询速度)?

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

有许多字段类型，如event_types。

其次:是否有一种方法告诉数据库在插入数据时创建和维护这种表?

是否有其他策略来实现这一点?我在和熊猫一起工作。

Answer 1

这是从迄今为止以其他方式存储的数据(例如在日志文件中)开始构建数据库时的典型情况。像往常一样，有一个解决方案，但它不是一个很快的解决方案。也许你可以编写一个日志消息处理程序来处理传入的消息;如果通量(消息/秒)不是太大，您将不会注意到开销，特别是如果您可以忘记将消息写入平面文本文件。

首先是，关于规范化的问题。是的，你应该将其归一化为所谓的第三范式(3NF)。这基本上意味着任何类型的真实数据(比如event_type)都只存储一次。(在某些情况下，您可以稍微放松一下，使用2NF -通常只有在实际数据需要很少的存储空间时，例如ISO国家代码，M/F(男/女)选择等-但在大多数其他情况下，3NF会更好。)

在您的特定情况下，假设您的event_type是char(20)类型。10个这样的事件及其相应的int代码很容易放在一个数据库页面上，通常是4kB的磁盘空间。如果您有1,000条日志消息，event_type作为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注入。