tdsql - run SQL queries against a Teradata data warehouse server
% tdsql [-h] [-H host] [-u user] [-p pass] [-d database] [-c charset] [-m mode] [-o file] [-f format] [-r] [sql] [args]
Teradata ships with very little Unix command line support other than the BTEQ tool. In particular, it lacks an interactive command prompt tool (like mysql for MySQL, psql for PostgreSQL, or vsql for Vertica). The tdsql program aims to fill that gap.
This program runs an SQL query against the Teradata data warehouse, and then sends the output of the query either to stdout, or to an output file. The SQL can be provided on the command line, to stdin, or interactively via a command prompt that supports readline command line editing. Database credentials can be provided on the command line, or in the initialization file ~/.tdsqlrc.
The following optional command-line arguments are accepted:
Display command line help and exit.
Connect to this hostname (default localhost).
Connect with this username (default your Unix username).
Connect with this password (default is to prompt interactively for one).
Connect initially to the given database (default is not in any database).
Specify character set; must be one of ASCII or UTF8
Specify mode; must be one of ANSI or TERADATA
Write output to this file (default is to write to stdout).
Write output in
text(tab-separated, the default),
boxpretty-printed boxed format (this is the slowest output format, as all output must be buffered before any output is printed), or
verticalfor vertical rows (like MySQL with \G).
Include a header row with text, csv, or vbar output. If a format other than text, csv, or vbar is specified, the header argument is ignored.
The last optional command line arguments are the SQL to run, as a single string; and any arguments to pass to the SQL, if the SQL contains question mark (
?) positional placeholders. The default is to prompt for SQL interactively using a command line prompt, if stdin is a tty, or read stdin as verbatim SQL, if stdin is not a tty.
This script looks for a file
~/.tdsqlrc which is a file in YAML format that has the following structure (all contents optional):
hostname: I<host> username: I<user> password: I<pass> database: I<database> charset: I<charset> mode: I<mode>
If the file is present, then any provided values are used as the default values for hostname, username, etc. This means you can avoid needing to continually repeat these from the command line. Note that if you record your password here, you should make sure the file has safe permissions (
chmod 600 ~/.tdsqlrc).
You can install this software simply by moving the bin/tdsql program into your PATH (for example, in /usr/local/bin), and the man page in share/man/man1/tdsql.1 into your MANPATH (for example, in /usr/local/share/man/man1).
This software requires Perl (it is tested with Perl 5.12.3), and the following CPAN modules:
It also relies on the DBD::Teradata module available here: http://www.presicient.com/tdatdbd/
That module in turn has these dependencies:
% tdsql -H td -u johndoe % echo 'SELECT col1, col2 FROM db1.table1' | tdsql -H td -u johndoe % tdsql -o output.txt 'SELECT col1, col2 FROM db1.table1' % tdsql -f box 'SELECT col1, col2 FROM db1.table1 WHERE col1 = ?' 'val1'
- Box format should not need to buffer all rows before starting display.
- Recover from database connection timeouts.
- Basic interaction should still work even without Term::ReadLine::Gnu.
- Fall back to using stty if Term::ReadKey is not installed.
Andrew Ho <firstname.lastname@example.org>
Copyright (c) 2012-2014, Groupon, Inc. All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of GROUPON nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.