Как читать все строки из огромной таблицы?

Ответ 1

Используйте CURSOR в PostgreSQL или пусть JDBC-драйвер справится с этим для вас.

LIMIT и OFFSET будут работать медленно при обработке больших наборов данных.

Ответ 2

Краткая версия - вызов stmt.setFetchSize(50); и conn.setAutoCommit(false); чтобы не читать весь ResultSet в память.

Вот что говорят доктора:

Получение результатов на основе курсора

По умолчанию драйвер собирает все результаты запроса сразу. Это может быть неудобно для больших наборов данных, поэтому драйвер JDBC предоставляет средство для базирования ResultSet на курсоре базы данных и выборки только небольшого числа строк.

Небольшое количество строк кэшируется на клиентской стороне соединения, и при исчерпании следующий блок строк извлекается путем изменения положения курсора.

Замечания:

• ResultSets на основе курсора не может использоваться во всех ситуациях. Существует ряд ограничений, из-за которых драйвер молча возвращается к загрузке всего ResultSet одновременно.

• Соединение с сервером должно осуществляться по протоколу V3. Это значение по умолчанию для (и поддерживается только) версий сервера 7.4 и новее.

• Соединение не должно быть в режиме автоматической фиксации. Бэкэнд закрывает курсоры в конце транзакции, поэтому в режиме автоматической фиксации бэкэнд будет закрывать курсор, прежде чем из него можно будет извлечь что-либо.

• Оператор должен быть создан с типом ResultSet ResultSet.TYPE_FORWARD_ONLY. Это значение по умолчанию, поэтому не нужно переписывать код, чтобы воспользоваться этим преимуществом, но это также означает, что вы не можете прокручивать назад или иным образом перемещаться в ResultSet.-

• Данный запрос должен быть одним оператором, а не несколькими операторами, соединенными точкой с запятой.

Пример 5.2. Установка размера выборки для включения и выключения курсоров.

Переключить код в режим курсора так же просто, как установить размер выборки в операторе соответствующего размера. Установка размера выборки обратно в 0 приведет к кэшированию всех строк (поведение по умолчанию).

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
   System.out.print("a row was returned.");
}
rs.close();

// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
   System.out.print("many rows were returned.");
}
rs.close();

// Close the statement.
st.close();

Ответ 3

Таким образом, выясняется, что суть проблемы заключается в том, что по умолчанию Postgres запускается в режиме "autoCommit", а также ему нужны/используются курсоры, чтобы иметь возможность "пролистывать" данные (например: читать первые 10К результатов, затем next, затем next), однако курсоры могут существовать только внутри транзакции. Таким образом, по умолчанию всегда считываются все строки в ОЗУ, а затем разрешается вашей программе начать обработку "первой строки результата, затем второй" после того, как все это поступило по двум причинам, а не в транзакции (поэтому курсоры не работает), а также размер выборки не был установлен.

Таким образом, средство командной строки psql достигает пакетного ответа (его параметр FETCH_COUNT) для запросов, заключается в том, чтобы "обернуть" свои запросы выбора в краткосрочную транзакцию (если транзакция еще не открыта), чтобы курсоры могли работать. Вы можете сделать что-то подобное также с JDBC:

  static void readLargeQueryInChunksJdbcWay(Connection conn, String originalQuery, int fetchCount, ConsumerWithException<ResultSet, SQLException> consumer) throws SQLException {
    boolean originalAutoCommit = conn.getAutoCommit();
    if (originalAutoCommit) {
      conn.setAutoCommit(false); // start temp transaction
    }
    try (Statement statement = conn.createStatement()) {
      statement.setFetchSize(fetchCount);
      ResultSet rs = statement.executeQuery(originalQuery);
      while (rs.next()) {
        consumer.accept(rs); // or just do you work here
      }
    } finally {
      if (originalAutoCommit) {
        conn.setAutoCommit(true); // reset it, also ends (commits) temp transaction
      }
    }
  }
  @FunctionalInterface
  public interface ConsumerWithException<T, E extends Exception> {
    void accept(T t) throws E;
  }

Это дает преимущество меньшего объема ОЗУ и, по моим результатам, в целом работает быстрее, даже если вам не нужно экономить ОЗУ. Weird. Это также дает преимущество в том, что ваша обработка первой строки "начинается быстрее" (поскольку она обрабатывает страницу за раз).

И здесь, как сделать это "необработанным курсором postgres", вместе с полным демонстрационным кодом, хотя в моих экспериментах казалось, что JDBC выше был немного быстрее по любой причине.

Другим вариантом может быть autoCommit режима autoCommit везде, хотя вам все равно придется всегда вручную указывать fetchSize для каждого нового оператора (или вы можете установить размер выборки по умолчанию в строке URL).

Ответ 4

Я думаю, что ваш вопрос похож на этот поток: JDBC Pagination, который содержит решения для вашей потребности.

В частности, для PostgreSQL вы можете использовать ключевые слова LIMIT и OFFSET в своем запросе: http://www.petefreitag.com/item/451.cfm

PS: В Java-коде я предлагаю вам использовать PreparedStatement вместо простых операторов: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Ответ 5

Я сделал это, как показано ниже. Не лучший способ, я думаю, но он работает:)

    Connection c = DriverManager.getConnection("jdbc:postgresql://....");
    PreparedStatement s = c.prepareStatement("select * from " + tabName + " where id > ? order by id");
    s.setMaxRows(100);
    int lastId = 0;
    for (;;) {
        s.setInt(1, lastId);
        ResultSet rs = s.executeQuery();

        int lastIdBefore = lastId;
        while (rs.next()) {
            lastId = Integer.parseInt(rs.getObject(1).toString());
            // ...
        }

        if (lastIdBefore == lastId) {
            break;
        }
    }

Ответ 6

Наверное, в моем случае проблема была на клиенте, который пытается получить результаты.

Требуется получить .csv со ВСЕМИ результатами.

Я нашел решение, используя

psql -U postgres -d dbname  -c "COPY (SELECT * FROM T) TO STDOUT WITH DELIMITER ','"

(где dbname имя db...) и перенаправление на файл.