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

Question

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

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

我对归一化数据有一个粗略的了解。

首先，以下假设是否正确:将 event_type 存储在单独的表(可能具有外键关系)中(对于存储大小、索引和查询速度)有益吗？

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

有很多字段类型，例如 event_types。

那么其次:有没有办法在插入数据的时候告诉数据库创建和维护这种表？

还有其他策略可以实现这一目标吗？我正在和 Pandas 一起工作。

Answer 1

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

首先，关于常态化问题。是的，您应该始终将 和 归一化为所谓的第三范式 (3NF)。这基本上意味着任何类型的现实世界数据(例如您的 event_type)仅存储一次。 (在某些情况下，您可以稍微放松一下并转到 2NF——通常只有当真实世界的数据需要非常少的存储时，例如 ISO 国家代码、M/F(男性/女性)选择等——但是在大多数其他情况下，3NF 会更好。)

在您的特定情况下，假设您的 event_type 是 char(20) 类型。十个这样的事件及其相应的 int 代码很容易放在一个数据库页面上，通常需要 4kB 的磁盘空间。如果您有 1,000 条事件类型为 char(20) 的日志消息，那么您需要 20kB 来存储该信息，或五个数据库页面。如果您的日志消息中有其他此类项目，则存储减少量会相应增加。 date 或 timestamp 等其他项目可以以其 native 格式(分别为 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 注入(inject)。

以上所有内容显然都需要根据您的具体情况量身定制。