python - 通过 Binary Copy 将包含 PostGis 字段的数据批量加载到 PostgreSQL

Question

总结

我有一个带有 PostgreSQL + PostGis 数据库设置的应用程序，我正在尝试将大量行加载到其中一个表中。经过一些研究，二进制复制似乎是最好的方法，但经过无数次尝试和无休止的调试，我未能实现数据的加载。

此外，我注意到这种特定方法的可用引用资料数量相当少，因此认为提出问题可以帮助 future 的开发人员。

背景

技术

应用程序后端是用 Python 3 编写的。目标数据库是本地托管的 PostgreSQL v11 数据库，扩展名为 PostGIS 2.5.3。psycopg2 适配器用于将后端连接到数据库。PPyGIS 的 ppygis3 端口仅用于较小的替代尝试，如稍后在代码中所示。

数据

尽管出于保密原因我无法共享此类数据的副本，但我可以描述该数据由地理位置(经纬度格式)、时间戳和值组成。此数据量为数百万。

方法

我已经研究了解决这个问题的潜在方法，并且出于我们对性能的考虑，使用二进制格式的 Postgres COPY 命令似乎是最明智的方法。

引用资料:

在寻找引用资料时，从 Mike T 那里找到了这个非常有用的答案。不幸的是，它只涵盖了数字类型: PostgreSQL Documentation on Copy with binary format

AlexImmer's port of PPyGIS and "example" on binary copy with WKB

NBSoftSolutions dissection on binary copy and useful example on copying strings

数据库表

该表的简化模式类似于以下内容:

POINT              TIMESTAMP                      value(Geography)        (timestamp without zone)       (real)

CREATE TABLE testtable (  point GEOGRAPHY(Point),  timestamp TIMESTAMP WITHOUT TIME ZONE,  value REAL);

Code

These are two simplified versions of my attempts at conducting the data loading.

Copy Attempt with BytesIO

The following is the approach I am mainly interested in.

con = psycopg2.connect(user=username, password=password, host="localhost", database="test")
cur = con.cursor()

# ByteIO buffer type
buffer = BytesIO()
buffer.write(pack('!11sii', b'PGCOPY\n\377\r\n\0', 0, 0))

# Dummy values
lat = 40.0
lon = 10.0
date = "2019-01-01 00:00:00"
pointStr = "POINT({} {})".format(lon, lat)
pointWKB = Point(lon, lat).write_ewkb()
value = 555.555

# Number of columns (3)
buffer.write(pack('>h', 3))

# Geographic point
# Attempt with direct encoding of the string format
buffer.write(pack('>i', len(pointStr)))
buffer.write(pointStr.encode())

# Geographic point
# Attempt using the Well-Known Binary encoding hinted by AlexsImmer
#buffer.write(pack('>i', len(pointWKB)))
#buffer.write(pointWKB)

# Timestamp
buffer.write(pack('>i', len(date)))
buffer.write(pack('>s', date.encode()))

# Reading value
buffer.write(pack('>i', 4))
buffer.write(pack('>f', reading))

# Write final file trailer
buffer.write(pack('>h', -1))

buffer.seek(0)

# Perform a bulk upload of data to the table
cur.copy_expert('COPY testtable FROM STDIN WITH BINARY', buffer)

# Also attempted 
#cur.copy_from(buffer, 'testtable', null='None')

con.commit()

cur.close()
con.close()

我猜这些问题要么与我的无知有关，要么与数据库不识别/支持以这种方式插入地理字段有关。然而令人惊讶的是，尝试使用 StringIO 方法进行相同的尝试，如下所示完美无缺。

使用 StringIO 复制尝试

我也尝试过 StringIO 方法，我已经设法使它工作，但它的性能并不令人满意。请注意，在这种情况下，PostGis 地理字段以其简化的字符串形式(即“POINT(Y X)”)传递给数据库。

con = psycopg2.connect(user=username, password=password, host="localhost", database="test")
cur = con.cursor()

# StringIO buffer type
buffer = StringIO()

# Dummy values
lat = 40.0
lon = 10.0
date = "2019-01-01 00:00:00"
point = "POINT({} {})".format(lon, lat)
value = 555.555

buffer.write(point)
buffer.write('\t')
# Timestamp of reading
buffer.write(date)
buffer.write('\t')
# Parameter reading
buffer.write(str(value))
buffer.write('\n')

# Reset offset to byte 0
buffer.seek(0)

cur.copy_from(buffer, "testtable", null='None')

con.commit()

cur.close()
con.close()

尝试使用两种方法(对 WKB 表示点的字符串表示形式进行编码)中的任何一种进行二进制复制会产生以下错误:

psycopg2.errors.InternalError_:遇到无效的字节序标志值。上下文:复制测试表，第 1 行，列地理

预期/理想的结果自然是将数百万行成功加载到数据库中。

我们将不胜感激任何意见和/或指导!

Answer 1

如果您使用二进制模式，则需要提供内部二进制表示。这是值存储在内存和磁盘中的格式。

数据通过类型输入输出函数在内部格式和外部格式之间转换。

现在您可能会想到，geometry 的内部二进制格式是 EWKB(两者的名称中都有“binary”)，我不能责怪您。但事实并非如此——EWKB 是数据的文本表示。这就是您遇到问题的原因。

如果您想使用内部二进制格式，则必须阅读 PostGIS 源代码。我认为您不使用二进制 cooy。我认为这是一个过早的优化。是什么让您认为您的代码比 PostGIS 的类型输入功能更高效？除此之外，如果客户端架构与服务器架构不同，您就会面临危险:您能确定它们以相同的方式表示 8 字节浮点值吗？