tdsql, a Teradata command line interface

at August 8th, 2012

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:

tdsql screenshot

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!

No Tags


4 thoughts on “tdsql, a Teradata command line interface

  1. Just out of curiosity, what was wrong with bteq? Bteq can handle exporting delimited result sets to flat files. it also has an interactive mode builtin.

    by Ivan Levchenko on December 3, 2012 at 4:03 am
  2. It doesn't support the concept of standard in and standard out in a very clean way. Output from bteq is cluttered with login and logout information. This gets in the way of interacting with the OS in a straightforward manner.

    by Craig on May 9, 2014 at 12:51 pm
  3. Note that this doesn't appear to support more current versions of Teradata than v12. As of July 2014, Teradata is on v15. I was trying to access v13 which did not accept the deprecated v12 logon supported through the DBD posted at http://www.presicient.com/ which hasn't been updated since 2008.

    by sixoseven on July 2, 2014 at 1:24 pm
  4. […] looking for a simple Command line (Terminal) tool for Teradata SQL.  This is an option, this article shows the TDSQL tool in use and has the GITHUB Link to […]

    pingback by Teradata Command Line Tool for Linux | Geek Must Have!! on March 17, 2016 at 10:15 am

Leave a Reply

Your email address will not be published. Required fields are marked *