主页 / dba / 问题 / 310598
Accepted
Charlie Clark
Asked: 2022-04-07 01:06:38 +0800 CST

Postgres 对批量加载转换功能的改进

  • 772

我定期从 httparchive.org 导入数据。数据是一个 MySQL CSV 导出，我使用 pgloader，它处理这个导出的怪癖（\Nfor NULL）等。我还需要做一些额外的处理以进行规范化：

  • 在协议 (http|https) 和主机部分中拆分 url
  • 将字符串日期“Mon DD YYYY”转换为日期对象

目前，我在导入数据时有一些触发器可以执行此操作，但我正在寻找改进方法，特别是查看是否可以并行运行某些步骤。

我有以下用于提取协议和端口的 CTE：

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )

在本地运行这似乎比tsdebug

这适用于选择，但作为更新似乎非常慢。

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )
update urls
set 
protocol = parts[1],
host = parts[2] 
from split

另一种方法，尤其是在处理文本源时，会在 URL 进入 Postgres 之前对其进行拆分。

未压缩的 CSV 为 3526430884 字节，导入大约需要 20 分钟，无需处理。但这与加工相比是两倍多。FWIW 我也尝试过使用外部数据包装器。但是，即使在使用 CSV（空值、编码）解决了各种问题之后，这也会导致内存错误。

在一些帮助下，我设法运行了基准测试并改进了我的触发器。

CREATE OR REPLACE FUNCTION public.extract_protocol()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE elements text [];
BEGIN
elements := regexp_match(NEW.url, '(https|http)://(.+)/');
NEW.protocol = elements[1];
NEW.host = elements[2];
RETURN NEW;
END;
$function$

现在，这比进行后续更新运行得更快，但两者都不是限制因素。现在的瓶颈是在将清理后的数据插入主表时索引的开销。我认为我唯一的选择是权衡插入索引的成本，而不是禁用然后添加它们。

postgresql csv

1 个回答

  1. Best Answer

    您的 UPDATE 语法会生成表urls与split结果的交叉连接。这本质上是表与自身的交叉连接。

    您需要在目标表和源表之间建立某种连接条件。显而易见的选择是表的主键列。

    with split as (
  select pk_column, regexp_match(url, '(https|http)://(.+)/' ) as parts 
  from urls 
)
update urls
  set protocol = s.parts[1],
      host = s.parts[2] 
from split s 
where urls.pk_column = s.pk_column --<< here

    我认为您尝试通过使用 CTE 来避免两次评估正则表达式会使事情变得更慢，而不是更快。我预计将表与自身连接的开销远大于两次评估正则表达式的开销。

    所以我认为，你也应该尝试：

    update urls
  set protocol = (regexp_match(url, '(https|http)://(.+)/' ))[1]
      host = (regexp_match(url, '(https|http)://(.+)/' ))[2]
    • 1

相关问题