Why BTEQ Offers Better Performance Than Any ETL Tool!

Teradata BTEQ is the oldest available tool from Teradata for executing SQL queries. It can be used to run queries and export and import data.

Teradata BTEQ offers more possibilities than Teradata SQL Assistant and should be one of your essential tools as a Teradata developer. We can use BTEQ interactively or execute batch scripts.

ETL-Tools versus BTEQ

BTEQ is an excellent alternative to ETL tools, primarily as ETL tools are often not used properly, and theoretical advantages such as data lineage are not given. I often notice that ETL tools are used as scheduling tools and not in the way they are designed to be used.

Data lineage with ETL Tools is most times not given. For performance reasons, data lineage often is sacrificed.

Especially the combination of the ETL process using BTEQ and an ETL tool leads to the fact that all advantages of the ETL tool are lost. I know customers who use an ETL tool, e.g., only because they have or see no possibility to load EBCDIC files in Linux.

Also, remember that SQL and Linux developers are a dime a dozen. For ETL tools, the supply of experts can be tight. If we think of Ab Initio, where you can’t find documentation, you are on a minimal market.

Simple tasks can be coded in BTEQ in a few minutes but frequently require much longer when implemented in an ETL tool. This proves that ETL tools do not make development more manageable and efficient, which is one of the main arguments for ETL tools.

To sum it up: in my more than two decades of experience with data warehousing, I have never met a customer who used an ETL tool (whichever one) correctly and was able to achieve benefits.

ETL tools are not generally bad. But developers without the right knowledge and training can quickly undo the potential benefits of these tools.

Therefore I always recommend checking if the Teradata tools like BTEQ, TPT Load, etc., are insufficient for the tasks to be solved.

Frequently Asked Questions about Teradata BTEQ

Teradata BTEQ Export

BTEQ can export data in various formats. Here are the four 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 any Windows system.
  
  

A Teradata BTEQ Export Example

In the below 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

In the following example, BTEQ export writes the table Customer in the report mode into a text file 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

In our last example, BTEQ export writes the table Customer in 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 a separator, we 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, as shown below. 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 several 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.
Using various sessions in BTEQ imports can make sense, but not in BTEQ exports.

You can read more details here:

Teradata BTEQ Fast Path Insert

Suppose 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 bypasses the transaction log when writing to an empty table.

Place a 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

Below figure shows 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: