Fork me on GitHub
#sql
<
2018-01-19
>
seancorfield00:01:14

Note that both create-table-ddl and drop-table-ddl can be passed an options hash map as the last argument and, if your database supports it, you can say {:conditional? true} and the generated SQL will contain an EXISTS test (producing DROP TABLE IF EXISTS fruit and CREATE TABLE IF NOT EXISTS fruit ...). ^ @jisaacs46

Jacob01:01:26

Wow awesome thanks man that helps a ton. @seancorfield

Jacob01:01:33

sorry guys Im coming from a scripting world, and Im really liking Clojure

seancorfield02:01:58

@jisaacs46 Are these two databases within the same DB instance? i.e., if your :user has access to both databases/schemas then you can do it in one SQL statement (with execute!).

seancorfield02:01:13

Or, if the data you're moving can fit in memory, just query it from one DB into a local binding and then insert! it into the other database -- after performing whatever in-memory transformations you need to apply.

seancorfield02:01:40

Ah, I answered before I expanded your code -- so I was being speculative...

seancorfield02:01:50

I can't think of a way to do a single update based on data in-memory... but my comment about a user with privileges on both DBs would be my first optimization -- if that is possible.

Jacob03:01:06

Ok great I’ll try something like that I haven’t been able to get the execute! to work yet. But I’ll give it another try and see if I can get something going. Thank you @seancorfield you have been really helpful. I have a Clojure project connecting to 3 databases and pulling all the data into one database so that I can compare the tables. Clojure and this slack channel has been a life saver thanks again.

seancorfield03:01:23

Feel free to post code and stacktraces or whatever. Happy to help.

seancorfield03:01:29

@jisaacs46 We do a lot of general database analysis and maintenance via a REPL -- a Socket REPL connected into one of our production processes 🙂

Jacob18:01:16

I am wondering if there is something i could do to speed up getting the data from multiple databases using my above snippet. It takes about 3-5 minutes to run. would execute! speed this up?

Jacob19:01:09

Is there a plug-in for Clojure where I can download data from a database into a file in the form of insert statements and or json format?

donaldball19:01:49

spit, slurp, jdbc/select and jdbc/insert! or jdbc/insert-multi! probably get you where you want to go, possibly with judicious use of partition-all

Jacob19:01:19

Also is there a way to copy out millions of rows quickly into a file in clojure I use datagrip and it can copy data to a file really fast I’m wondering if there is something in Clojure that can copy data out quickly.

donaldball20:01:53

If you’re talking millions of rows, you’ll probably want to open a writer yourself, make sure you don’t retain the head of your results seq, and do whatever annoying things you have to do with your db connection to ensure it isn’t buffering the entire resultset.

seancorfield21:01:01

Writing JVM-based tools to replicate what existing specialist SQL tools you already have is pretty much always going to produce something slower.