遍历大型外部postgres db,操作行,将输出写入rails postgres db [英] Iterate over large external postgres db, manipulate rows, write output to rails postgres db

查看:62
本文介绍了遍历大型外部postgres db,操作行,将输出写入rails postgres db的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个Postgres数据库,其中包含大约200,000,000行和6列。这些列具有int,date和string,但没有主键,也没有作为主键基础的唯一值。

I've got a postgres DB with around 200,000,000 rows and 6 columns. The columns have int, date, and string, but no primary key and no unique values on which to base a primary key.

此数据库中的记录包含原始数据我需要我的rails postgres模型之一。我想遍历整个外部数据库,对它的每一行进行计算,然后将输出写入我的rails模型。

The records in this DB contain the raw data I need for one of my rails postgres models. I'd like to iterate through the full external DB, perform a calculation on each row of it, and then write the output to my rails model.

我已经连接到数据库或通过ActiveRecord访问记录都没有问题,但是我尝试遍历数据库的所有操作都失败或花费了太长时间。我尝试了以下操作:

I've got no issue connecting to the DB or accessing records through ActiveRecord, but everything I try for iterating over the DB is failing or taking far too long. I've tried the following:


  • ExternalDB.all.each

  • ExternalDB.find_all.each

  • 使用这些说明

  • ExternalDB.all.each
  • ExternalDB.find_all.each
  • Adding an "id" column to ExternalDB using these instructions

我认为答案将是使用SQL进行迭代,但是我什至不知道如何开始。

I think the answer will be to do the iterations using SQL, but I'm not even sure how to start on that.

推荐答案

您将要使用协议级别的游标或带有 DECLARE FETCH 的SQL级游标。

You will want to use a cursor, either a protocol-level one or an SQL-level cursor with DECLARE and FETCH.

很方便地,有人已经为PostgreSQL游标编写了ActiveRecord适配器;参见 ruby​​gems

您可能还会发现这个问题的内容翔实:是否存在使用游标或智能获取的Ruby ORM?

You might also find this question informative: Are there any Ruby ORMs which use cursors or smart fetch?

我还没有检查源代码/文档,以查看Pg gem是否支持PostgreSQL的协议级游标用于批量读取,但是如果已经有工具(如上链接),可能就不值得探索

I haven't checked the source code / docs to see if the Pg gem supports PostgreSQL's protocol-level cursors for batched reads, but if there's already a tool to do it (as linked above) it's probably not worth exploring.

这篇关于遍历大型外部postgres db,操作行,将输出写入rails postgres db的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆