Hello fellow Clojurians I am new to Clojure and next jdbc (so far loving both) and was hoping to get some help to understand how to use next jdbc. I am currently trying to take ids from one database and inserting them into another. I would like to do this in portions as the table contains potentially millions of ids. As far I have been able to read I should be able to use the option {:fetch-size portion} to take the data in smaller portions at a time. My issue is that I don't understand how I can perform the following on each portion: 1. Get a portion of ids from database-1 2. Pick out relevant columns and perform transformations 3. Put the transformed columns into database-2 4. Take the next portion and do step 1 through 3 again. 5. Keep doing step 4 until there are no more rows in table. Here is my (simplified) attempt:

(def db-1-spec
  {:dbtype   "sqlserver"
   :read-only   false
   :auto-commit false
   :sslmode     "require"
   :dbname   "database"
   :user     "username"
   :password "secret-password"
   :host     "host"})

(def db-2-spec
  {:dbtype   "postgres"
   the rest is the same as above...})

(defonce db-1-ds (jdbc/get-datasource db-1-spec))

(defonce db-2-ds (jdbc/get-datasource db-2-spec))

(def ids (delay
	(plan/select! db-1-ds [:id]
						  ["SELECT * FROM table-ids"] 
						  {:fetch-size 10000})))

(defn insert-ids
  [ids]
  (sql/insert-multi! db-2-ds :target-table
                     [:id :other-info]
                     (transform @ids)))

You need to reduce over a call to plan -- which will essentially stream results from the first database and eagerly store them into the other database.

plan/select! is going to produce all the IDs in one go. No streaming.

Hi Sean, thank you for your response and the jdbc clojure wrappers. Is it correctly understood that the function I give to reduce will be applied to each row in the result set? And if so is it the result of reduce I should insert? I am having a hard time understanding exactly how to plan and reduce in this case.

Yes, essentially.

And it's more efficient if you can avoid realizing a row as Clojure data -- plan lets you select specific columns from the row (in the reducing function) without turning the ResultSet data into a Clojure hash map.

The "simple" way is to insert a row into db2 for each call of the reducing function but that is potentially slow. You want do the inserts in batches (see execute-batch!). But the catch there is that you would need a stateful transducer and there are some subtle issues around using a stateful transducer over a reducible collection that manages resources (which is what the result of plan is).

So you might want to think about reducing over plan and using core.async as a buffer between that ingest process and your transform process and perhaps also between that and your output process (inserting batches into the second DB).

This stuff is definitely non-trivial if you're new to Clojure. There are no "magic bullets" when dealing with extremely large data sets.

It definitely isn't, but you have given me a direction that I can now start to study. Thanks a lot for your help