数据导入提示

我目前正在将大量空间数据导入PostgreSQL / PostGIS数据库，并意识到其他人可以从我的经验中学到东西。 大多数建议并非特定于PostgreSQL或PostGIS。

了解基本技术

了解加载批量数据的基本技术。 如果可能，请使用COPY。 如果可能，请使用批处理。 如果没有关闭，则在进行单个INSERT或UPDATE调用之前将自动提交，并且仅在每N ^个调用之后提交一次。 使用自动提交的INSERT和UPDATE作为绝对的最后手段。 不幸的是，后者是JDBC连接的通常默认设置。

另一种标准技术是在加载大量数据之前删除所有索引，然后再重新创建它们。 这并非总是可能的，例如，如果您要更新活动表，但这可能会大大提高性能。

最后，请记住要更新索引统计信息。 在PostgreSQL中，这是VACUUM ANALYZE。

了解你的数据库

PostgreSQL允许将表创建为“ UNLOGGED”。 这意味着如果在上传数据时（或之后）系统崩溃，可能会导致数据丢失，那又如何呢？ 如果发生这种情况，您可能还是要从头开始重新上传。

我尚未进行性能测试（但尚未完成），但请牢记另一个技巧。

知道如何使用ExecutorService进行多线程处理

由于网络延迟和数据库完成呼叫所需的时间，对数据库的每次调用都会有停滞时间。 另一方面，数据库在等待桌面准备和上载每个呼叫时处于空闲状态。 您可以使用多线程上载器来填补该死时间。

ExecutorService可以轻松地将工作分解为有意义的块。 例如，每个线程上载单个表或单个数据文件。 ExecutorService还可让您更加智能地上传数据。

上载到专用架构

如果可能，请上传到专用架构。 这为您以后提供了更大的灵活性。

在PostgreSQL中，通过调用ALTER DATABASE数据库SET SEARCH_PATH = schema1，schema2，schema3并指定public和包含上载数据的模式，这对于大多数用户而言是透明的。 在其他数据库中，一次只能有一个活动模式，以后需要显式指定该模式。

上载未处理的数据

有时候，我发现上传原始数据并在数据库中（带有查询和存储过程）处理原始数据要比在桌面上处理原始数据并上传处理过的数据更快。 对于固定格式的记录尤其如此。 除了性能外，还有其他好处–请参阅以下各项。

跟踪您的来源

几乎没有什么比对数据库中的内容有疑问并且没有办法将其追溯到源头更令人沮丧的了。 （显然，这适用于您从其他位置上传的数据，而不适用于应用程序自身生成的数据。）添加几个字段，例如文件名和记录号，以后可以省去很多麻烦。

计划工作

在我当前的应用程序中，第一步是解压缩并打开每个shapefile（数据文件），获取记录数，然后将其关闭并删除未压缩的文件。 这似乎是没有意义的工作，但它使我可以对上载文件所需的时间进行可靠的估算。 反过来，这使我能够做出明智的决定，以安排工作时间。 例如，一种标准方法是使用优先级队列，在该队列中，当线程可用时，您总是会抓住最昂贵的工作项（例如，要处理的记录数）。 这将导致最快的总体上传速度。

这还有另外两个好处。 首先，它使我可以验证是否可以打开并读取所有shapefile。 在遇到致命问题之前，我不会浪费时间。 其次，它使我可以验证我是否写了所有预期的内容。 如果我在计划时发现了813条记录，但是后来只能在表中找到811行，那就是一个问题。 或更糟糕的是，我在表中发现了815行。

记录一切

每次数据库调用后，我都会写入日志表。 每次出现异常后，我都会写入日志表。 这很昂贵，但比起解析日志文件，查询一两个数据库表要容易得多。 如果批量较大，它也比您想象的要便宜。

我记录时间，shapefile的路径名，读取的记录数和写入的记录数。 异常记录时间，shapefile的路径名和异常消息。 我记录了所有异常消息–“ cause”和“ nextException”（对于SQLExceptions）。

执行质量检查

检查是否已写入所有预期记录，并且没有异常。

检查数据是否自洽。 添加唯一索引。 添加参照完整性约束。 在插入和更新数据时添加索引确实要付出代价，但是如果这些索引是只读表则没有任何不利之处。

检查空值。 检查合理的值，例如人口和年龄永远不能为负。

您通常可以执行特定于数据的检查。 例如，我知道州/省必须在各自国家/地区之内，而城市必须在其各自州/省之内。 （美国某些州要求城市在一个县内，而其他州则允许城市跨越国界。）

如果质量控制失败，有两种可能性。 首先，您的上传可能有问题。 这很容易检查是否记录了每个记录的来源。 其次，数据本身可能是错误的。 无论哪种情况，您都希望尽快知道。

备份您的工作

这应该很容易，但是在QC通过之后立即对这些表执行数据库备份。

锁定数据

仅当表在加载后永远不会或很少被修改时才适用。 （想想像包含有关状态或区号信息的表之类的东西。）

锁定桌子。 您可以同时使用皮带和吊带：从PUBLIC撤消INSERT，UPDATE，TRUNCATE ON TABLE表。 ALTER TABLE表设置为只读。 有些人喜欢VACUUM FREEZE表SET READ ONLY。 如果将数据上传到专用模式，则可以使用速记方式从PUBLIC的SCHEMA模式中的所有表上REVOKE INSERT，UPDATE，TRUNCATE。 您还可以撤消对架构的CREATE权限。

翻译自: https://www.javacodegeeks.com/2015/01/tips-for-importing-data.html