NAME
tdsql - run SQL queries against a Teradata data warehouse server
SYNOPSIS
% tdsql [-h] [-H host] [-u user] [-p pass] [-d database] [-c charset] [-m mode] [-o file] [-f format] [-r] [sql] [args]
DESCRIPTION
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.
Command-Line Options
The following optional command-line arguments are accepted:
-h
,--help
-
Display command line help and exit.
-H
host,--hostname=
host-
Connect to this hostname (default localhost).
-u
user,--username=
user-
Connect with this username (default your Unix username).
-p
pass,--password=
pass-
Connect with this password (default is to prompt interactively for one).
-d
database,--database=
database-
Connect initially to the given database (default is not in any database).
-c
charset,--charset=
charset-
Specify character set; must be one of ASCII or UTF8
-m
mode,--mode=
mode-
Specify mode; must be one of ANSI or TERADATA
-o
file,--output=
file-
Write output to this file (default is to write to stdout).
-f
format,--format=
format-
Write output in
text
(tab-separated, the default),csv
(comma-separated),vbar
(pipe-separated),box
pretty-printed boxed format (this is the slowest output format, as all output must be buffered before any output is printed), orvertical
for vertical rows (like MySQL with \G). -r
,--header
-
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.
Initialization File
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
).
INSTALLATION
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:
DBI
Term::ReadKey
Term::ReadLine::Gnu
Text::CSV_XS
It also relies on the DBD::Teradata module available here: http://www.presicient.com/tdatdbd/
That module in turn has these dependencies:
Crypt:ECB
Crypt::Rijndael
Digest::SHA1
EXAMPLES
% 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'
TODO
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.
AUTHOR
Andrew Ho <[email protected]>
SEE ALSO
https://github.com/groupon/tdsql
LICENSE
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.