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

Question

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

pythonsqlpostgresqlpandas

7

我想将一个大型日志文件导入（Postgres-)SQL。

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

我对数据规范化有一个初步的理解。

首先，假设如下是正确的：将event_type存储在单独的表中（可能具有外键关系）有利于存储大小、索引和查询速度。

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

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

其次：是否有一种方法可以告诉数据库在插入数据时创建和维护此类表？

有没有其他策略可以实现这一点？我正在使用pandas。

- Cilvic

这是很多情况之一，如果PostgreSQL有 upsert ... returning 就太好了。可惜我们没有。 - Craig Ringer

有可能使用重写规则来实现这一点，但这是一种非常脆弱（容易出错...）的方法。最好先将1to1导入到“暂存”表中，然后再从那里进行插入/更新操作。 - wildplasser

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Accepted Answer

这是一种典型情况，即从先前以日志文件等形式存储的数据开始构建数据库。通常会有解决方案，但速度不会很快。也许你可以编写一个日志消息处理程序来处理消息；如果流量（每秒的消息数）不太大，并且可以忘记将消息写入纯文本文件，则您不会注意到开销。

首先，在规范化问题上。是的，您应该始终进行规范化并达到所谓的第三范式（3NF）。这基本上意味着任何实际世界的数据（例如event_type）仅存储一次。（仅在实际数据需要很少存储的情况下，例如ISO国家代码，M / F（男性/女性）选择等情况下，您可以稍微放松，转而使用2NF，但在大多数其他情况下，3NF会更好。）

在您的特定情况下，假设您的event_type是char（20）类型。带有其对应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注入。

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