How to write a BTEQ Script in Teradata

In most of the Teradata environment I have seen the preferred medium to call BTEQ is shell script. It means that Teradata developer will write a shell script which will invoke BTEQ utility to run SQL commands on Teradata.

In this post, I will show you how to write a sample BTEQ script using Shell and run it on Teradata. To start with, I would like to discuss why we shall pick Shell script to call BTEQ and why not directly the BTEQ script.

BTEQ supports plain ANSI SQL with few TD extension to SQL. This means that PL/SQL constructs like CURSOR , conditional statements, logical statements are not supported or minimal support is only available. Another reason is most of the times, you will prefer having parameters in the SQL. BTEQ does not support dynamic queries as such. So with Shell script, you can pass runtime parameters to BTEQ script.

The first thing you require is connection string to connect to BTEQ. For security purposes, it is recommend to have it in separate file and not write directly inside BTEQ script. So first create a file with login statement in it.

Filename: logonFile.txt . Change "localhost" to Teradata IP or TDPID.

 localhost/dbc,dbc

Now let's create Shell Script which will invoke BTEQ utility to run SQL commands. Add your queries between "label_bteq". Filename: BTEQ_sample.sh

#!/bin/ksh

#You can add
#comments here
#so that other developer can get some info 
#about this script
#Author: ABCDEF Date: 10-July-2019

PATH_TO_FILE=/root/raj
LOGON_STRING=`cat ${PATH_TO_FILE}/logonFile.txt`
START_DT=$1
END_DT=$2
default_db=$3

#call BTEQ utility and run SQL commands
bteq << label_bteq
.logon ${LOGON_STRING}

database ${default_db};

create multiset volatile table vt_calendar as (
select	* from	sys_calendar.calendar) 
with data 
on commit preserve rows;

DROP TABLE store_sales_weekly;
CREATE SET TABLE store_sales_weekly ,NO FALLBACK
     (
      ss_week_seq_sk INTEGER NOT NULL,
      ss_item_sk INTEGER NOT NULL,
      ss_store_sk INTEGER NOT NULL,
      ss_quantity INTEGER,
      ss_wholesale_cost DECIMAL(12,2),
      ss_list_price DECIMAL(12,2),
      ss_sales_price DECIMAL(12,2),
      ss_ext_discount_amt DECIMAL(12,2),
      ss_ext_sales_price DECIMAL(12,2),
      ss_ext_wholesale_cost DECIMAL(12,2),
      ss_ext_list_price DECIMAL(12,2),
      ss_ext_tax DECIMAL(12,2),
      ss_coupon_amt DECIMAL(12,2),
      ss_net_paid DECIMAL(12,2),
      ss_net_paid_inc_tax DECIMAL(12,2),
      ss_net_profit DECIMAL(12,2)
	  )
PRIMARY INDEX ( ss_week_seq_sk );

delete from store_sales_weekly;  

INSERT INTO store_sales_weekly   
SELECT d_week_seq ,ss_item_sk ,ss_store_sk ,
sum(ss_quantity) , 
max(ss_wholesale_cost),
max(ss_list_price) ,
max(ss_sales_price), 
sum(ss_ext_discount_amt) ,
sum(ss_ext_sales_price) ,
sum(ss_ext_wholesale_cost) , 
sum(ss_ext_list_price) ,
sum(ss_ext_tax) ,
sum(ss_coupon_amt) , 
sum(ss_net_paid) ,
sum(ss_net_paid_inc_tax) ,
sum(ss_net_profit) FROM store_sales inner join 
date_dim ON ss_sold_date_sk=d_date_sk AND ss_item_sk is not null 
and ss_store_sk is not null 
WHERE d_date between ${START_DT} and ${END_DT} 
GROUP BY d_week_seq ,ss_item_sk ,ss_store_sk;

label_bteq

Command to run the shell script. Remember we have to pass 3 parameters to shell [$1,$2,$3] used in BTEQ query.

sh -x BTEQ_SAMPLE.sh "'2019-06-01'" "'2019-07-01'" DB_DEFAULT_NAME

So in this post we saw following:

1. How to write a shell script which calls BTEQ utility.
2. How to create a separate file with login credentials and call it in shell script.
3. How to pass shell variables into BTEQ queries.

Hope this helps. In next post, we will discuss couple of options to make this BTEQ script better by adding some failure checks and we will see how we can add basic conditional statements in BTEQ queries.