BTEQ Features
- It is utility, which import and export data from multiple tables to multiple files
- It does not run in phases, it runs according to the system process
- It is not an automatic Restart table, it supports arithmetic, mathematical, etc. expression
- It supports conditional programming branching arid looping
- It performs error handily but not error capturing
- It supports select, insert, delete etc all operations[DDL,DML DCL etc]
- It load duplicate record also even the table is having SI, RI, etc
- It does not bother about the connotations, indexes, and triggers, etc. on the tables.
- It supports formatting and reporting commands etc
Working with BTEQ Interactive Mode
Minimize the window
Right-click on the window properties
Save properties for future windows with the same title
LOGON 127.0.0.1/Vinaya[Enter key press]
Password: Vinayaka[Press on entering key]
Select * From Dept;
O S<OPERATING SYSTEM COMMAND>
- Ex: CLEARING THE SCREEN
- WINDOWS
.OS CLS
- UNIX
.OS Clear
Remark ’Vinay Here’[Remark is like ’c’ language primt]
SHOW ControlTo see current settings
Setting some properties.
A). SET STDETITLES ON
- Select * FROM PARTY:
- Pid 1 pnm Vinay
- Pid 2 pnm Madhu
- .SET SIDETIZES OFF
B). SET FOLDLINE ON
- SELECT*FROM PARTY;
- Pid Pname
- 1 Vinay
- 2 Madhu
- SET FOLDLINE OFF
C). SET WIDTH 300;
D). SET HEADING’ This is tera data class’
E). SET FOOTING data warehousing
- Technologies’
- (one line continuation to another line is-)
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training
F). SET PAGE LENGTH 25;
G).SET page break ON;
Changing the mode from BTET TO ANSI
- .LOGOFF
- .SET SESSION TRANSACTION ANSI
- .LOGON 127.0.0.1/ VINAYAKA
- Password: Vinay
SHOW. VersionsTo see the current version of various components in Teradata
Working with ANSI
A) Select *From dept;
COMMIT;
B)INSERT INTO DEPT VALUES(100,’ marketing’) ;
COMMIT;
C)BEGIN TRANSACTION[press enter key]
[IT Should fail because ANSI Doesn’t support]
Working with Transactions in BTET
- Change To BTET
- Begin Transaction OR BT
Select*From Dept;
---5 Rows
Insert into dept values (100,’marke’);
Select* From Dept;
--6 Rows
Delete From kkk;
--Failed Due To NO Table name kkk
Select* From Dept;
--5Rows
[Because the last statement failed, Transaction failed and the] previous statement are Rollback)
Note
MAX Selectable Rows are one million
MAX Selectable Columns are 2048
MAX Selectable session is 200
BTEQ COMMAND
BTEQ Command Classified in the below 4 types:
a)Session control commands
ABORT, EXTI, Halt EXECUTION, LOGON, LOGOFF, QUIT, Session, charset, session transaction, show versions, etc.
b)File formatting commands
ERROR OUT, EXPORT, HALT EXECUTION, IMPORT, INDIA DATA, OS, QUIET, Repeat, run, etc.
c)Sequence control commands
Abort, error level, Exit, GO TO, quite, IF Then, Label, etc
d)Format control commands
Side titles, fold line, Footing, Heading, etc.
Note:
Refer to the material for all those command explanations
BTEQ IMPORTANT STRUCTURE And export structure
Exporting
.Export Data file=”<Filename>”, limit =n DD=<DD Name>
INDICATE
REPORT
DIFF
LARGE DATA
RESET
IMPORTING
.Importing DATA FILE =”< FILE NAME>” SKIP=N
DD=<DD Name>
INDICDATA
REPORT
VARTEXT’D’
DDelimiter or separator
Export Reset
It Reverses the effect of the previous export command and closes the output on the console/*system apply read lock while loading the data, it releases the locks on target table*/
Ex - Exporting the data in data node[First example in the Browser]
.LOGON 127.0.0.1/Vinayaka, Vinay;
.Export data file=”c:Data-Logparty –Data.TXT”
Select party id as a party – id, party name as a party- income, jdate, organization As a party – loc, party income as a party –income,
From party
.Export reset
.LOGOFF
StartRUNàCMDàBTEQ<C:BTEQExport.Data.Txt
C:Data.LogExportlog.txt
Export the data in variable text format[Common delimiter]
IN The above script Do the below statement changes
Replace the mode data to report
Replace the above statement with the below statement select
CAST(party id As Varchar(10)//’,’//
CAST(party name As Varchar(30)//’,’//
CAST(party Loc As Varchar(30)//’,’//
From party
EXPORTING THE DATA IN FIXED TEXT FORMAT
IN the above script do the below changes
a)Change data to report mode
b)Change the SQL Query to below way
Export Report file =”c:Data-logparty-Repari . txt”, limit=2; Select
CAST(party id As Char(10)//
CAST(party name As Char(30)//
CAST(party Loc As Char(30)//
From party;
EXPORTING THE DATA WITH ETL LOGIL[SECOND SCRIPT IN THE PROJECT]
.LOGON 127.0.0//DBC,DBC;
.SET WIDTH 400;
Database vinayaka;
.Export report file=”c:Data- logparty- Report.Txt” Select
Party Id As party id
CAST(Party name//’-’//party Loc As varchar(17))As Address,
Coast sce(Party income,99999)As income,
CASE
When party income<=30000 then ‘poor’>
WHEN PARTY INCOME<=50000 And party income>30000 Then ‘Avg’ else ’Good’
End AS RATING,
CAST (JNATS As Date)(Format’dd-mm-yyyy’)
AS JDate
CAST(CURRENT- Date AS Varchar(10)As Business- Date,
Sub STR(Organization,I,3)As org-short From party;
.Export Reset /* system apply the Read lock while loading the data, it realize
.LOGOFF
IMPORTING THE DATA IN THE DATA MODE
3rd Script in the project
RUN File c:btEQConn.txt;
Database Vinayaka;
.Import Data File=”C:Data logparty-Data.txt”;
.QUIET ON;/*we want reduce Lock size, only error, and processing information
.Repeat*;/*iteration one my one like for loop*/
Using
I-PID(Integer),
I-PNM(Varchar(30)),
I-Ploc(Varchar (30)),
I-PINS(Integer),
I- JDate(Date),
I-org(Varchar(30))
INSERT INTO party (party id, party name, party Loc, party income, JDate, orgam zation)Vlues(:I-PNM:I-PLOC,:I-PINC,I-Jdate:I-org);
.IF error code<>o Then .Quit:
.LOGOFF
Conn.txt
.Logon 127.0.0.1/DBC, DBC;
Coon.txt:
It is a configuration file, that is user define and configuration settings[user id, password, variables, data base name, etc..]
Note
In the above script, conn.txt is a configuration file
This file is useful when we are moving the script from development to testing, testing to production, etc…
The i/p file should be in data mode[we can not create data file manually]
Importing the data with ETL LOGIC
4TH SCRIPT IN THE PROJECT
/*.RUN File c:BTEQCONN.TXT;*/
.Logon 127.0.0.1/DBC,DBC;
Database vinayaka;
.Import vartext’,’File= “c:BTEQ Data-Vartext.TXT”,sktp=1;
.Quiet on;
.Repeat *;
Using
I- org(Varchar(30),)
I- NUM(Varchar(30),)
I- EName(Varchar(30),)
I- LName(Varchar(30),)
I- MName(Varchar(30),)
I- PLOC(Varchar(30),)
I- PCD(Varchar(30),)
I-JDate (Varchar(30),)
I- PINC(Varchar(30),)
Insert into party2(party id, partyname, email, party code, jdate, party income, party income-INC, Source- system-ID)
Values(:I-org//’-’//: I-NUM,:I-FNAME//:I –WAME//:I-M Name,
‘Tera data vinay @yahoo. co. in’,/*:I-F Name//’gmail.com*/
:I-PCD’
CAST(:I-J data is data)(format ’yy-mm-dd’.)
: I-PINC,
: I-PINC*12/100,
41000/* TO Identify which source system loading the data warehouse*/
.IF ERROR CODE<>0 THEN SELECT error encountered
.Quiet 16;
Select ‘No error’;
.Logoff
WORKING WITH CONDITIONAL PROGRAMMING[SIMPLE BRANCHING AND LOADING]
.Logon 127.0.0.1/DBC,DBC;
Database Vinayaka;
Select*From party;
.IF Error code=o THEN .GOTO Vinay LBL
.Remark ‘No table existed’,
.Label vinay LBL
.IF Activity count>5 Then. Remark
‘Table existed and contained more than 5 Rows’,
.IF Activity count<=5 Then. Remark’ No of rows is less than or equal to five’;
.Logoff
Only BTEQ We do this conditional programming
For In-depth knowledge on Teradata click on:
- Secondary Index in TeraData
- Memory Management in TeraData
- Types of OLAP’s in TeraData
- TPUMP Structure and Process In TeraData
- Teradata Parallel Transporter