There is a better, far simpler way to extract data from Teradata than my previous blog entry.
Ignore it. Use this one.
The example example below extracts data from a Teradata table or view into a pipe delimited file via bteq in a Unix shell script.
I had a serious hassle eliminating the first two lines of output - i.e. the column heading and the minuses underlining the heading. Initially a colleague Arturo told me to replace the ".export report file ..." row with ".export data file ..." but this caused the output to have hidden control characters in it, like these marked in red below ... I assume for Teradata-to-Teradata data transfers.
^P^@^N^@-1||Null Value
^W^@^U^@125|BUSINESS|Business
^[^@^Y^@149|INDIVIDUAL|Individual
He then found that the TITLE could be removed using the (TITLE '') phrase highlighted in the script in bold red below.
#!/bin/ksh -x
# Program: td_bteq_extract_better.ksh
# Description: To extract data from TD via bteq without any headings or "report formatting"
# Version: 1.0 gml 19-Jul-2011 Initial version from Arturo Gonzalez
# -----------------------------------------------------------------------------------------
LOGON_STR="prodtd/glourei1,xxxxxx;" # Teradata logon acct
TMP=${HOME}/tmp # extract directory
FILENAME=test2 # output will go in ${FILENAME}2.dat
SEP='|' # pipe separator
DATAFILE=${TMP}/${FILENAME}.dat # extract file
> $DATAFILE # Otherwise if run more than once, it will append records to $DATAFILE
bteq >/dev/null 2>&1 <<EOF
.logon $LOGON_STR
.export report file = $DATAFILE -- .export report file = $DATAFILE will result in column header and header underlining - 2 unwanted rows
.set recordmode off -- not sure what this does - leaving it out seems to make no difference
-- an arbitrary example SQL query
SELECT TRIM(COALESCE(Acct_Type_Id,-99)) || '${SEP}' || -- Without the TRIM, the numerics will be right justified and have leading blanks
TRIM(COALESCE(Acct_Type_Cd,'??')) || '${SEP}' ||
TRIM(COALESCE(Acct_Type_Name,'??')) (TITLE '')
FROM nuc_user_view.acct_type
ORDER BY Acct_Type_id;
.export reset
.logoff
.quit
EOF
No comments:
Post a Comment