时间戳

首页 > 解决方案 > 有没有办法将文本数据加载到 PostgreSQL 中的数据库？

sql - 有没有办法将文本数据加载到 PostgreSQL 中的数据库？

问题描述

我想从一个文本文件（大约 1GB）中提取信息并将其存储在 PostgreSQL 数据库中。文本文件格式如下：

DEBUG, 2017-03-23T10:02:27+00:00, ghtorrent-40 -- ghtorrent.rb:Repo EFForg/https-everywhere exists
DEBUG, 2017-03-24T12:06:23+00:00, ghtorrent-49 -- ghtorrent.rb:Repo Shikanime/print exists
...

我想从每一行中提取“DEBUG”、时间戳、“ghtorrent-40”、“ghtorrent”和“Repo EFForg/https-everywhere exists”并将其存储在数据库中。

我已经使用其他语言如 python (psycopg2) 和 C++ (libpqxx) 完成了它，但是是否可以在 PostgreSQL 本身中编写一个函数来导入整个数据本身。

我目前正在为 PostgreSQL 使用 pgAdmin4 工具。我正在考虑在函数中使用类似 pg_read_file 的东西来读取文件，但一次读取一行并将其插入表中。

标签： sqlpostgresqlpgadmin-4

解决方案

我对大型 XML 文件（130GB 或更大）使用的一种方法是将整个文件上传到一个临时未记录的表中，然后从那里提取我想要的内容。Unlogged tables不是崩溃安全的，但比记录的要快得多，这完全适合临时表的目的；-)

考虑下表..

CREATE UNLOGGED TABLE tmp (raw TEXT);

..您可以使用psql控制台（unix）中的一行导入这个 1GB 文件。

$ cat 1gb_file.txt | psql -d db -c "COPY tmp FROM STDIN"

之后，您只需要应用您的逻辑来查询和提取您想要的信息。根据表的大小，您可以从 a 创建第二个表SELECT，例如：

CREATE TABLE t AS
SELECT 
  trim((string_to_array(raw,','))[1]) AS operation,
  trim((string_to_array(raw,','))[2])::timestamp AS tmst,
  trim((string_to_array(raw,','))[3]) AS txt
FROM tmp
WHERE raw LIKE '%DEBUG%' AND
      raw LIKE '%ghtorrent-40%' AND 
      raw LIKE '%Repo EFForg/https-everywhere exists%'

根据您的逻辑调整string_to_array函数和子句！WHERE或者，您可以将这些多个LIKE操作替换为单个SIMILAR TO.

..您的数据就可以使用了：

SELECT * FROM t;

 operation |        tmst         |                               txt                                
-----------+---------------------+------------------------------------------------------------------
 DEBUG    | 2017-03-23 10:02:27 | ghtorrent-40 -- ghtorrent.rb:Repo EFForg/https-everywhere exists
(1 Zeile)

提取数据后，您可以DROP TABLE tmp;释放一些磁盘空间；）

进一步阅读：COPY, PostgreSQLarray functions和pattern matching

推荐阅读