At Groupon, we crunch a lot of data. When you run a site that gets billions of requests per day, and need to do analyses that might span activity logs, purchase records, and system data, it adds up to gigabytes of new data per day—and that’s just for the United States! One of the many tools we’ve recently rolled out to mine our data is a Teradata data warehouse system. Teradata offers powerful query capabilities and good performance, and the company has an impressive history—the core product has been constantly developed over nearly three decades.
When we switched to using Teradata, one of the tools that developers and analysts missed was a good interactive command line. Teradata ships with native and web GUI applications, but, its primary interactive Unix command line tool, BTEQ, feels pretty primitive if you’re coming from a MySQL, PostgreSQL, Oracle, or Vertica background. So naturally, our response was to just write the missing tool. While Perl is not usually a common language at Groupon, a mature DBD::Teradata library, an excellent CSV export library, and GNU readline support made it a good choice. A few hours of hacking later, the tdsql command line tool was born.
tdsql makes it easy to run a query directly from the Unix command line, and export the result to a tab-separated or CSV output file. In the simplest form, it runs just like a Unix filter, with SQL going to stdin and tab-separated output going to stdout:
% tdsql < query.sql > results.txt
It also has an interactive mode, which feels similar to the interactive tools that ship with other databases. Here’s a screenshot of tdsql in action:
If you use Teradata and want a convenient Unix command line tool, we hope this tool will be useful for you as well. We’ve published the source code under a BSD license on GitHub: https://github.com/groupon/tdsql
If plucking diamonds of business intelligence out of a mountain of data is your cup of tea, or if crafting your own software tools in whatever language fits the problem is as natural to you as breathing, there are plenty of opportunities to do both as a developer at Groupon!