Teradata BTEQ | You Need Efficiency? Dump Your ETL Tool | DWHPRO

How to Start Using Teradata BTEQ

Teradata BTEQ is the first tool from Teradata for executing SQL queries. You can execute queries, export data, and import data.

Teradata BTEQ also offers more possibilities than Teradata SQL Assistant and should, therefore, be one of your basic tools as a Teradata developer.

BTEQ can be used interactively or to execute batch scripts.

ETL-Tools versus BTEQ

BTEQ can be a good alternative to ETL tools. Especially if they are not used properly.

In more than 20 years of experience with Teradata and ETL tools, I always notice that ETL tools are used incorrectly.

Data lineage with ETL Tools? Usually not given. For performance reasons, this is often “interrupted”.

Often deadly is the mixture of an ETL tool and logic in BTEQ. A little transformation here, a little transformation thereā€¦and it's impossible to understand what exactly happens in the ETL process.

It is also more difficult to find specialists for a specific ETL tool, while BTEQ provides a simple scripting language. Have you ever tried to find an Ab Initio specialist? Then you know what I mean.

Simple tasks that can be programmed in BTEQ in a few minutes often require many times the time in ETL tools. Why? I often ask myself this question, because ETL tools were actually created to make the programming of the ETL process easier.

In short, after more than 20 years of experience with Teradata and ETL tools, I have to say, unfortunately:

I haven't seen any of my customers use this tool (be it Informatica, Datastage, Ab Initio, Talend, etc.) in the right way and therefore make life easier.

I don't mean to say that ETL tools are generally bad. Not at all. But developers without the right knowledge and training can quickly undo the benefits of these tools.

So I can only recommend that you take a look at BTEQ, which is a good choice for many tasks.

Frequently Asked Questions about Teradata BTEQ

How is data imported and exported with BTEQ?

BTEQ is not a bulk load utility like Fastload, Multiload, or Fast Export. Data is exported or imported row by row. BTEQ import and export are therefore not suitable for large amounts of data.

Can Teradata BTEQ perform loops?

Loops are not possible in BTEQ.

Can I define my own variables in BTEQ?

Variables cannot be defined. A common workaround is to use Linux parameters in the batch scripts and replace them before the script is forwarded to BTEQ.

What types of conditional logic does BTEQ handle?

There is the “.GOTO” command with which you can jump to a certain label (indicated by “.LABEL” ).

Which methods for conditional statements and branches exist in BTEQ?

There are all well-known conditional statements available:
IF, THEN, ELSE, ENDIF.

Can I determine how many rows are affected by an INSERT, UPDATE or DELETE?

BTEQ returns the result in the following variables: INSERTCOUNT, UPDATECOUNT, ACTIVITYCOUNT These can then be used in a “.GOTO” command, for example:

.IF UPDATECOUNT = 0 THEN .GOTO <NowRowsUpdated>

How is error handling done in a Teradata BTEQ script?

There is a variable ERRORCODE, which is set after each executed statement. This ERRORCODE can be used for different actions:

— Terminate the script and return error code 255 to the client
.IF ERRORCODE <> 0 THEN .QUIT 255
— If the step was successful goto a certain label
.IF ERRORCODE <> 0 THEN .GOTO <NextStep>

Is there a possibility to execute Linux commands from BTEQ?

Yes, you can do this by executing the command “.OS <linux_command>”.

Can I run another BTEQ from within BTEQ?

Yes, with the command “.RUN <other_bteq>”.

Teradata BTEQ Export

BTEQ can export data in various formats. Here are the 4 most important ones:

  • EXPORT DATA:
    Exports data in record mode.
    You can use this format to exchange data between Fastload, Fast export, Multiload, and BTEQ.
  • EXPORT REPORT:
    The data is exported as a report in a readable format and contains the columns as headers. This is the default mode.
  • EXPORT INDICDATA:
    NULL values are marked in this format
  • EXPORT DIF:
    Creates a file in DIF (Data Interchange Format) format, which can be read on a Windows system.

A Teradata BTEQ Export Example

This example BTEQ export writes the table Customer in the record mode into a text file:

.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
.EXPORT DATA FILE = c:\temp\dwhpro.txt
SELECT * FROM Customer;
.EXPORT RESET
.QUIT
.LOGOFF

This example BTEQ export writes the table Customer in the report mode into a text file, which is readable by users. It includes column headers:

.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
.EXPORT REPORT FILE = c:\temp\dwhpro.txt
SELECT * FROM Customer;
.EXPORT RESET
.QUIT
.LOGOFF

This example BTEQ export writes the table Customer in the report mode into a text file, but removes the column headers using the TITLE command:

.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
.SET TITLEDASHES OFF 
.EXPORT REPORT FILE = c:\temp\dwhpro.txt
SELECT FirstName (TITLE ''), LastName (TITLE '')
FROM Customer;
.EXPORT RESET
.QUIT
.LOGOFF

To create a text file that uses a comma as separator you can do the following:

.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
.SET TITLEDASHES OFF 
.EXPORT REPORT FILE = c:\temp\dwhpro.txt
SELECT FirstName|| ',' || LastName (TITLE '')
FROM Customer;
.EXPORT RESET
.QUIT
.LOGOFF

A Teradata BTEQ Import Example

A text file in record mode can be easily imported with BTEQ. The command .REPEAT means that all lines should be read in:

.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
.IMPORT DATE FILE = c:\temp\dwhpro.txt
.REPEAT *
USING (FirstName VARCHAR(200), LastName VARCHAR(200))
INSERT INTO Customer VALUES (:FirstName,:LastName);
.QUIT
.LOGOFF

A comma-separated text file can be imported like this. It is necessary to define all columns as VARCHAR when importing CSV files:

.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
.IMPORT VARTEXT FILE = c:\temp\dwhpro.txt
.REPEAT *
USING (FirstName VARCHAR(200), LastName VARCHAR(200))
INSERT INTO Customer VALUES (:FirstName,:LastName);
.QUIT
.LOGOFF

Teradata BTEQ Sessions

Multiple sessions are like a number of users (but with the same user credentials). Each of these sessions belongs to the same user and can run one or more sequential transactions:

.SET SESSIONS 100
.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
   <...>
.LOGOFF

The use of multiple sessions can improve the performance of a BTEQ script.

Multiple sessions only make sense for data access via PI or USI.
Typically, using multiple sessions in BTEQ imports can make sense, but not in BTEQ exports.

You can read more details here:

Give Me 5 Minutes, I'll Give You The Truth About Teradata Sessions And Performance!

Teradata BTEQ Fast Path Insert

If several INSERT statements are made in the same target table and both the source and target tables have the same primary index, the following technique should be used, since this avoids the transaction log when writing to an empty table.

teradata-bteq-fast-path
Teradata Fast Path INSERT

All that is necessary: Place the semicolon at the beginning of the lines (“;INSERT INTO”) as shown in the following example

.SET SESSIONS 100
.LOGON SYSTEM1/DWHPRO,DWHPRO_PWD
INSERT INTO CUSTOMER SELECT * FROM Customer1
;INSERT INTO CUSTOMER SELECT * FROM Customer2;
.LOGOFF

Here is a comparison of how the Slow Path INSERT works when the comma is not at the beginning of the line, or the tables do not have the same primary index:

teradata-bteq-slow-path
Teradata Slow Path INSERT
TPT Teradata – The Teradata Parallel Transporter
The Fundamentals of FastLoading on Teradata
Roland Wenzlofsky
 

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

>