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
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.
Loops are not possible 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.
There is the “.GOTO” command with which you can jump to a certain label (indicated by “.LABEL” ).
There are all well-known conditional statements available:
IF, THEN, ELSE, ENDIF.
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>
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>
Yes, you can do this by executing the command “.OS <linux_command>”.
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:
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.
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: