我正在尝试从数据库读取数百万行并写入文本文件。
这是我的问题的延续数据库转储到文本文件有副作用
我现在的问题似乎是在程序完成之前不会发生日志记录。我没有懒惰处理的另一个指标是,在程序完成之前根本不会写入文本文件。
根据 IRC 提示,我的问题可能与:result-set-fn
并默认为doall
in the clojure.java.jdbc/query
代码区域。
我尝试用一个替换它for
函数,但仍然发现内存消耗很高,因为它将整个结果集拉入内存。
我怎样才能拥有一个:result-set-fn
这并不像把所有东西都拉进去doall
?如何在程序运行时逐步写入日志文件,而不是在程序运行后转储所有内容-main
执行完毕?
(let [
db-spec local-postgres
sql "select * from public.f_5500_sf "
log-report-interval 1000
fetch-size 100
field-delim "\t"
row-delim "\n"
db-connection (doto ( j/get-connection db-spec) (.setAutoCommit false))
statement (j/prepare-statement db-connection sql :fetch-size fetch-size )
joiner (fn [v] (str (join field-delim v ) row-delim ) )
start (System/currentTimeMillis)
rate-calc (fn [r] (float (/ r (/ ( - (System/currentTimeMillis) start) 100))))
row-count (atom 0)
result-set-fn (fn [rs] (lazy-seq rs))
lazy-results (rest (j/query db-connection [statement] :as-arrays? true :row-fn joiner :result-set-fn result-set-fn))
]; }}}
(.setAutoCommit db-connection false)
(info "Started dbdump session...")
(with-open [^java.io.Writer wrtr (io/writer "output.txt")]
(info "Running query...")
(doseq [row lazy-results]
(.write wrtr row)
))
(info (format "Completed write with %d rows" @row-count))
)
我采取了最近的修复clojure.java.jdbc
通过把[org.clojure/java.jdbc "0.3.0-beta1"]
在我的 project.clj 依赖项列表中。这增强/纠正了:as-arrays? true
的功能clojure.java.jdbc/query
描述here.
我认为这有所帮助,但是我仍然可以覆盖:result-set-fn
to vec
.
通过将所有行逻辑塞进中解决了核心问题:row-fn
。最初的 OutOfMemory 问题与迭代有关j/query
结果集而不是定义具体的:row-fn
.
新的(工作)代码如下:
(defn -main []
(let [; {{{
db-spec local-postgres
source-sql "select * from public.f_5500 "
log-report-interval 1000
fetch-size 1000
row-count (atom 0)
field-delim "\u0001" ; unlikely to be in source feed,
; although i should still check in
; replace-newline below (for when "\t"
; is used especially)
row-delim "\n" ; unless fixed-width, target doesn't
; support non-printable chars for recDelim like
db-connection (doto ( j/get-connection db-spec) (.setAutoCommit false))
statement (j/prepare-statement db-connection source-sql :fetch-size fetch-size :concurrency :read-only)
start (System/currentTimeMillis)
rate-calc (fn [r] (float (/ r (/ ( - (System/currentTimeMillis) start) 100))))
replace-newline (fn [s] (if (string? s) (clojure.string/replace s #"\n" " ") s))
row-fn (fn [v]
(swap! row-count inc)
(when (zero? (mod @row-count log-report-interval))
(info (format "wrote %d rows" @row-count))
(info (format "\trows/s %.2f" (rate-calc @row-count)))
(info (format "\tPercent Mem used %s " (memory-percent-used))))
(str (join field-delim (doall (map #(replace-newline %) v))) row-delim ))
]; }}}
(info "Started database table dump session...")
(with-open [^java.io.Writer wrtr (io/writer "./sql/output.txt")]
(j/query db-connection [statement] :as-arrays? true :row-fn
#(.write wrtr (row-fn %))))
(info (format "\t\t\tCompleted with %d rows" @row-count))
(info (format "\t\t\tCompleted in %s seconds" (float (/ (- (System/currentTimeMillis) start) 1000))))
(info (format "\t\t\tAverage rows/s %.2f" (rate-calc @row-count)))
nil)
)
我尝试过的其他事情(取得了有限的成功)包括音色记录和关闭标准输出;我想知道使用 REPL 是否可以在显示回我的编辑器(vimfirewall)之前缓存结果,并且我不确定这是否占用了大量内存。
另外,我在内存空闲周围添加了日志记录部分(.freeMemory (java.lang.Runtime/getRuntime))
。我对 VisualVM 不太熟悉,也不太清楚我的问题出在哪里。
我对现在的工作方式感到满意,感谢大家的帮助。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)