Maximize Your Teradata Development with BTEQ

Teradata BTEQ is the earliest Teradata tool for executing SQL queries. It allows users to run queries and transfer data through import and export.

Teradata BTEQ provides greater functionality than Teradata SQL Assistant and is an indispensable tool for Teradata developers. BTEQ can be utilized interactively or to run batch scripts.

ETL-Tools versus BTEQ

BTEQ is a superior option to ETL tools, as the latter are typically misused and fail to exploit their theoretical benefits, including data lineage, fully. It is common for ETL tools to be used for scheduling purposes rather than their intended functionality.

Data lineage is often ignored in ETL tools due to performance concerns.

Using both BTEQ and an ETL tool in the ETL process results in losing the latter’s advantages. There are customers who solely opt to use an ETL tool due to their inability to load EBCDIC files in Linux because they don’t know how to use the iconv command.

Remember that SQL and Linux developers are plentiful, whereas finding experts in ETL tools can be challenging. This is particularly true for Ab Initio and similar vendors, which has limited documentation and a niche market.

Coding simple tasks in BTEQ takes only a few minutes, whereas implementing them in an ETL tool often takes much longer. This demonstrates that ETL tools do not necessarily enhance development manageability and efficiency, as is often claimed.

Based on my over twenty years of experience in data warehousing, I have not encountered any customers who have effectively utilized an ETL tool, regardless of the specific tool, resulting in tangible benefits.

ETL tools are generally useful, but untrained developers may negate their potential advantages.

I suggest verifying if Teradata’s tools, such as BTEQ and TPT Load, are inadequate for the required tasks.

Frequently Asked Questions about Teradata BTEQ

Teradata BTEQ Export

BTEQ enables exporting of data in various formats, including four of the most significant 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

BTEQ export writes the Customer table in record mode to a text file:

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

The BTEQ export function writes the “Customer” table in report mode to a user-readable text file with column headers.

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

The BTEQ export function writes the Customer table in report mode to a text file while utilizing the TITLE command to remove column headers:

.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 generate a comma-separated text file, follow these steps:

.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 file in record mode can be imported via BTEQ. The command “REPEAT” directs the reading of all lines.

.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

Import a CSV file by separating the text with commas. Ensure that all columns are defined as VARCHAR during the import process.

.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 analogous to multiple users with identical credentials. Each session is associated with the same user and has the ability to execute one or more consecutive transactions.

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

Utilizing multiple sessions can enhance the efficiency of a BTEQ script.

Multiple sessions are only applicable for data access through PI or USI. While using several sessions in BTEQ imports may be appropriate, it is not recommended for BTEQ exports.

For additional information, please refer to the following detailed report.

Teradata BTEQ Fast Path Insert

When multiple INSERT statements target the same table with identical primary indexes on both source and target tables, it is advisable to use the following technique. This method circumvents the transaction log when writing data 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

The following figure illustrates the contrast between the Slow Path INSERT operation when the comma does not appear at the beginning of a line and when the tables have different primary indexes.

BTEQ FAQ

What is BTEQ in Teradata?

BTEQ, or Basic Teradata Query, is a command-driven utility that allows users to interact with Teradata databases. It is a popular tool for querying, importing, and exporting data within the Teradata environment. BTEQ is designed for batch processing, making it a powerful and flexible tool for executing SQL queries and scripts, generating reports, and automating tasks across different platforms.

What is the difference between Teradata SQL Assistant and BTEQ?

Teradata SQL Assistant and BTEQ are tools for interacting with Teradata databases, but they have distinct features and different purposes. Teradata SQL Assistant is a graphical user interface (GUI) that allows users to write, edit, and execute SQL queries and import and export data. It is primarily designed for ease of use and is more suitable for users who prefer a visual approach to database management.

BTEQ, on the other hand, is a command-line utility that is more suitable for batch processing and automation. It is a more powerful and flexible tool for advanced users who are comfortable working with command-line interfaces. While Teradata SQL Assistant offers a user-friendly experience, BTEQ provides greater control and customization options for executing SQL queries and scripts.

What is a BTEQ command?

A BTEQ command is a text-based instruction to perform a specific action or operation within the BTEQ environment. BTEQ commands can be divided into two categories: SQL commands and BTEQ-specific commands. SQL commands are standard SQL statements that can be executed in BTEQ, such as SELECT, INSERT, UPDATE, and DELETE. BTEQ-specific commands are unique to the BTEQ utility and provide additional functionality, such as controlling the formatting of query results, managing error handling, and setting environment variables.

Some examples of BTEQ-specific commands include:

• .LOGON: Used to log on to a Teradata database
• .LOGOFF: Used to log off from a Teradata database
• .SET: Used to configure BTEQ settings, such as error handling and output formatting
• .EXPORT: Used to export query results to a file

How to run BTEQ in Teradata?

To run BTEQ in Teradata, follow these steps:

1. Open a command prompt or terminal window on your computer.
2. Connect to the Teradata database by entering the BTEQ command followed by your database credentials in the following format: bteq .LOGON your_database/your_username,your_password
3. After successfully logging in, you can start executing SQL commands and BTEQ-specific commands.
4. To execute a command, type the command and press Enter.
5. To execute multiple commands, you can create a script file containing the commands and then run the script in BTEQ using the command: bteq < script_filename
6. To exit BTEQ, type .QUIT and press Enter.

Please see the Teradata BTEQ documentation linked below:

https://docs.teradata.com/r/Basic-Teradata-Query-Reference-17.20/Introduction-to-BTEQ