sampa : colin’s blog

A couple of years ago, I used Groovy a decent amount for database ETL (Extraction, Transformation, and Loading) work with an ongoing project. This weekend I had occasion to do more of the same sort of work and see how the Groovy experience has changed…

Basically in the project two years ago, we versioned the DB schema along with the source code. So for every milestone and regular release of the product, there was an Ant-driven Groovy script which would suck in the data in the old format, slice and dice it a bit, and write it back out (usually updating the schema appropriately in the process). (I also used scripts done in Java and Spring+iBatis when that made more sense).

Using Groovy in that old project was overall a positive experience, but I have to say that it was incredibly frustrating w/respect to error reporting. There were a number of bugs in the language parser, along with ambiguities in the language. This was combined with useless error messages. The net result was that it was incredibly easy to make an innocent syntax error and get back a completely useless error message. It was also pretty easy to type what should have been perfectly legal constructs and get back a non-informative error.

This weekend I needed to make a script which would go into the database for the Spring Framework support forums, now running on vBulletin, look for out-of-date (bad) phpBB-format links to other posts (we used to run phpBB), and then do some queries to match those up to the proper new posts in vBulletin, to output to a file. Using Groovy for this task was a really pleasant experience. The SQL support is still there, with closures usable to handle iterating over results. Built-in regex support makes string matching and parameter extration trivial, and now, a syntax error generally gives a much clearer error message.

So I’ll definitely be using Groovy again for future DB data slicing and dicing. It’s really quick to whip out this kind of code in it. If you’ve never thought of using Groovy for ETL type work, give it a go. Of course it’s also pretty nice language for any general Java scripting at this point.