TPT is short form of Teradata Parallel Transporter. Teradata TPT is a single utility which internally calls other Teradata Utilities depending on the operators specified by user. So now you don’t have to write separate scripts for different utilities for data ingestion or data export. One utility which replaces all Teradata utilities.
If you want to load bulk data into empty table you can write TPT script which invokes FASTLOAD internally and loads the data. Similarly it uses MULTILOAD, TPUMP and of course BTEQ utilities to perform various functions. In TPT for every activity there is an associated operator. Operator is nothing but you can think of role which the component plays. Different operator plays different role.
TPT Operators in Teradata
- BTEQ – DDL Operator
- FASTLOAD – LOAD Operator
- MULTILOAD – UPDATE Operator
- FASTEXPORT – EXPORT Operator
- TPUMP – STREAM Operator
Producer Operator refers to Source eg reading from Data File.
Consumer operator refers to Target eg extracting data into Data File.
Job Steps are execution block of code which applies various operators to producer or consumer.
So let’s start with a simple example where we will read data from a file and load it into target table which is empty now. So before TPT was introduced we used to write FASTLOAD scripts for this scenario. Today we will write TPT script to load the data. So we have a Data File which we want to load and an Empty Target Table.
Teradata TPT Script to load from file to table
Step 1 : Define FILE Schema and Producer Operator to read files
DEFINE SCHEMA SCHEMA_EMP_NAME ( NAME VARCHAR(50), AGE VARCHAR(2) ); DEFINE OPERATOR op_EMP_NAME TYPE DATACONNECTOR PRODUCER SCHEMA SCHEMA_EMP_NAME ATTRIBUTES ( VARCHAR DirectoryPath= '', VARCHAR FileName = 'NAME.CSV', VARCHAR Format = 'Delimited', VARCHAR OpenMode = 'Read', VARCHAR TextDelimiter ='|' );
Perfect. So we have defined the file schema and it consists of only 2 columns Name & Age. Also we have defined a PRODUCER operator specifying file details like File path, Filename, File Delimiter etc. There are more options available for it however to keep it simple we will check only these few options here.
Step 2: Define a DDL operator to drop/create Error tables and Target Table.
DEFINE OPERATOR od_EMP_NAME TYPE DDL ATTRIBUTES ( VARCHAR PrivateLogName = '', VARCHAR LogonMech = '', VARCHAR TdpId = '', VARCHAR UserName = '', VARCHAR UserPassword = '', VARCHAR ErrorList = '3807' );
So now we have defined a DDL operator which will drop error tables if already exists and will re create target table. In our case we need target table to be empty so we can recreate it all the time.
Note: Fill in the details for LogonMech/TDPID/USERNAME/PASSWORD.
Step 3: Define a LOAD operator to load the data file into target table.
DEFINE OPERATOR ol_EMP_NAME TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR LogonMech = '', VARCHAR TdpId = '', VARCHAR UserName = '', VARCHAR UserPassword = '', VARCHAR LogTable = 'EMP_NAME_LG', VARCHAR ErrorTable1 = 'EMP_NAME_ET', VARCHAR ErrorTable2 = 'EMP_NAME_UV', VARCHAR TargetTable = 'EMP_NAME' );
Now we have defined LOAD operator and mentioned Target table and Error tables.
Note: Fill in the details for LogonMech/TDPID/USERNAME/PASSWORD.
Step 4: Define Job Step to execute DDL Operator
STEP stSetup_Tables ( APPLY ('DROP TABLE EMP_NAME_LG;'), ('DROP TABLE EMP_NAME_ET;'), ('DROP TABLE EMP_NAME_UV;'), ('DROP TABLE EMP_NAME;'), ('CREATE TABLE EMP_NAME(NAME VARCHAR(50), AGE VARCHAR(2));') TO OPERATOR (od_EMP_NAME); );
Step 5: Define Job Step to execute LOAD operator
STEP stLOAD_FILE_NAME ( APPLY ('INSERT INTO EMP_NAME (Name,Age) VALUES (:Name,:Age); ') TO OPERATOR (ol_EMP_NAME) SELECT * FROM OPERATOR(op_EMP_NAME); );
Steps Taken to Create a TPT script
- Define FILE Schema and Producer Operator to read files.
- Define a DDL operator to drop/create Error tables and Target Table.
- Define a LOAD operator to load the data file into target table.
- Define Job Step to execute DDL Operator
- Define Job Step to execute LOAD operator
Keeping everything together and giving a JOB name:
DEFINE JOB SAMPLE_FILE_LOAD DESCRIPTION 'Load a Teradata table from a file' ( DEFINE SCHEMA SCHEMA_EMP_NAME ( EMP_NAME VARCHAR(50), AGE VARCHAR(2) ); DEFINE OPERATOR op_EMP_NAME TYPE DATACONNECTOR PRODUCER SCHEMA SCHEMA_EMP_NAME ATTRIBUTES ( VARCHAR DirectoryPath= '', VARCHAR FileName = 'EMP_NAME.CSV', VARCHAR Format = 'Delimited', VARCHAR OpenMode = 'Read', VARCHAR TextDelimiter ='|' ); DEFINE OPERATOR od_EMP_NAME TYPE DDL ATTRIBUTES ( VARCHAR LogonMech = '', VARCHAR TdpId = '', VARCHAR UserName = '', VARCHAR UserPassword = '', VARCHAR ErrorList = '3807' ); DEFINE OPERATOR ol_EMP_NAME TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR LogonMech = '', VARCHAR PrivateLogName = 'load_log', VARCHAR TdpId = '', VARCHAR UserName = '', VARCHAR UserPassword = '', VARCHAR LogTable = 'EMP_NAME_LG', VARCHAR ErrorTable1 = 'EMP_NAME_ET', VARCHAR ErrorTable2 = 'EMP_NAME_UV', VARCHAR TargetTable = 'EMP_NAME' ); STEP stSetup_Tables ( APPLY ('DROP TABLE EMP_NAME_LG;'), ('DROP TABLE EMP_NAME_ET;'), ('DROP TABLE EMP_NAME_UV;'), ('DROP TABLE EMP_NAME;'), ('CREATE TABLE EMP_NAME(EMP_NAME VARCHAR(50), AGE VARCHAR(2));') TO OPERATOR (od_EMP_NAME); ); STEP stLOAD_FILE_NAME ( APPLY ('INSERT INTO EMP_NAME (EMP_NAME,Age) VALUES (:EMP_NAME,:Age); ') TO OPERATOR (ol_EMP_NAME) SELECT * FROM OPERATOR(op_EMP_NAME); ); );
How do I run a Teradata TPT script in UNIX ?
You can run the TPT script via invoking tbuild command and passing the tpt script as parameter to tbuild utility.
tbuild –f TPT_Scriptname.tpt
Whenever we execute TPT there is a .out file created which is like a log file for TPT. You cannot read contents of .out file by cat or more or any other UNIX command if running on UNIX or in Notepad or any other Text Editor.
How to view TPT Log File ?
To view TPT log file use tlogview command and pass logfile as the input parameter.
tlogview –l TPT_Script.out
So we created one basic TPT script and successfully executed it to load data from File into Target Table.
Watch the video to see working of TPT script explained above
If writing a typical TPT script is tedious task for you then you may try using existing TPT templates to quickly write a TPT script. Teradata provides list of templates for all the TPT operators which you can use to quickly create a TPT script. Now, we will see how to export data from a Teradata table using default templates available in tbuild.
Load multiple files using TPT Import
Let me share sample TPT script to load multiple data files into one single Teradata Table using Load operator. The TPT script is simple and is very much similar to our TPT script we shared in earlier posts. The main component which will change is the PRODUCER operator. Here we’ll use 2 new variables :
VARCHAR FileList = ‘YES’,
VARCHAR FileName = @File1
FileList : represent here that we are going to process list of files.
FileName: the Filename which has list of DATA files in it. One in each line.
So we will have following 6 files for this example:
- TPT_Name_LOAD.tpt: This is the main TPT file which will hold code to load DATA FILES into TARGET table.
- TPT_Variables.txt: This is the variable file in which we will define all the parameters used in TPT script.
- FILELIST.TXT: This is the file which will hold name of all the DATA FILES in it.
- emp.csv: First Data File.
- emp1.csv: Second Data File.
- emp2.csv: Third Data File.
Let us see now content of each file:
1) TPT_Name_LOAD.tpt
DEFINE JOB SAMPLE_FILE_LOAD DESCRIPTION 'Load multiple files into one single table in Teradata' ( DEFINE SCHEMA SCHEMA_EMP_NAME ( emp_NAME VARCHAR(50), AGE VARCHAR(500) ); DEFINE OPERATOR op_EMP_NAME DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATACONNECTOR PRODUCER' TYPE DATACONNECTOR PRODUCER SCHEMA SCHEMA_EMP_NAME ATTRIBUTES ( VARCHAR FileList = 'YES', VARCHAR FileName = @File1, VARCHAR Format = 'Delimited', VARCHAR OpenMode = 'Read', VARCHAR TextDelimiter ='|' ); DEFINE OPERATOR od_EMP_NAME DESCRIPTION 'TPT DDL OPERATOR FOR LOGIN DETAILS' TYPE DDL ATTRIBUTES ( VARCHAR PrivateLogName = 'ddl_log', VARCHAR LogonMech = @LogonMech, VARCHAR TdpId = @Tdpid, VARCHAR UserName = @Usrid, VARCHAR UserPassword = @Psswd, VARCHAR ErrorList = '3807' ); DEFINE OPERATOR ol_EMP_NAME DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATACONNECTOR LOAD OPERATOR' TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR LogonMech = @LogonMech, VARCHAR TdpId = @Tdpid, VARCHAR UserName = @Usrid, VARCHAR UserPassword = @Psswd, VARCHAR LogTable = 'EMP_NAME_LG', VARCHAR ErrorTable1 = 'EMP_NAME_ET', VARCHAR ErrorTable2 = 'EMP_NAME_UV', VARCHAR TargetTable = 'EMP_NAME' ); STEP stSetup_Tables ( APPLY ('DROP TABLE EMP_NAME_LG;'), ('DROP TABLE EMP_NAME_ET;'), ('DROP TABLE EMP_NAME_UV;'), ('DROP TABLE EMP_NAME;'), ('CREATE TABLE EMP_NAME(NAME VARCHAR(50), AGE VARCHAR(500));') TO OPERATOR (od_EMP_NAME); ); STEP stLOAD_FILE_NAME ( APPLY ('INSERT INTO EMP_NAME(Name,Age) VALUES(:emp_Name,:Age); ') TO OPERATOR (ol_EMP_NAME) SELECT * FROM OPERATOR(op_EMP_NAME); ); );
2) TPT_Variables.txt
Tdpid='', LogonMech='', Usrid='', Psswd='', File1='FILELIST.TXT'
3) FILELIST.TXT
emp.csv emp1.csv emp2.csv
4) emp.csv
Pat|26
5) emp1.csv
Mark|48
6) emp2.csv
Nitin|29
Place all the 6 files in a directory and run below command:
tbuild -f TPT_Name_LOAD.tpt -v TPT_Variables.txt
Note: Each of the 3 Data Files have 1 record only.
Once the script executes successfully run below command in Teradata:
SELECT * from EMP_NAME;
Output should be 3 rows one from each file.
Teradata Export Script with example
Below is the TPT script content [exp_Customer.tpt].
DEFINE JOB EXPORT_CUSTOMER DESCRIPTION 'Export Customer table to a delimited file' ( DEFINE SCHEMA sch_CUSTOMER FROM TABLE 'DS_TBL_DB.CUSTOMER'; APPLY TO OPERATOR ($FILE_WRITER()[1] ATTR (FileName = 'op_CUSTOMER.dat') ) SELECT * FROM OPERATOR ($EXPORT(sch_CUSTOMER)[1] ATTR ( SelectStmt = 'select * from DS_TBL_DB.CUSTOMER ;' ) ); );
I am using just 2 “OPERATOR” in the TPT script and will use below variable file to pass parameters for corresponding operators. Below is the variable file content [var_Customer.txt].
SourceTdpId = '192.168.100.100' ,SourceUserName = 'dbc' ,SourceUserPassword = 'dbc' ,SourceOpenmode = 'Read' ,FileWriterFormat = 'Delimited' ,FileWriterOpenMode = 'Write' ,FileWriterDirectoryPath= '/root/raj/data/' ,FileWriterTextDelimiter= '#'
You can check out other options available in template file at below location:
/opt/teradata/client/15.00/tbuild/template
*Replace 15.00 with the client version you are using.
To run the TPT script use below command:
tbuild -f exp_Customer.tpt -v var_Customer.txt
Log may look like below:
TDExpress15.0.0.8_Sles10:~/raj # tbuild -f exp_Customer.tpt -v var_Customer.txt Teradata Parallel Transporter Version 15.00.00.00 Job log: /opt/teradata/client/15.00/tbuild/logs/root-5.out Job id is root-5, running on TDExpress15008_Sles10 Teradata Parallel Transporter $FILE_WRITER[1]: TPT19006 Version 15.00.00.00 $FILE_WRITER[1]: TPT19010 Instance 1 directing private log report to 'dtacop-root-20672-1'. $FILE_WRITER[1]: TPT19007 DataConnector Consumer operator Instances: 1 Teradata Parallel Transporter Export Operator Version 15.00.00.00 $EXPORT: private log not specified $FILE_WRITER[1]: TPT19003 ECI operator ID: '$FILE_WRITER-20672' $FILE_WRITER[1]: TPT19222 Operator instance 1 processing file '/root/raj/data/op_CUSTOMER.dat'. $EXPORT: connecting sessions $EXPORT: sending SELECT request $EXPORT: entering End Export Phase $EXPORT: Total Rows Exported: 100000 $FILE_WRITER[1]: TPT19221 Total files processed: 1. $EXPORT: disconnecting sessions $EXPORT: Total processor time used = '0.08 Second(s)' $EXPORT: Start : Sun Jun 30 10:47:51 2019 $EXPORT: End : Sun Jun 30 10:47:58 2019 Job step MAIN_STEP completed successfully Job root completed successfully Job start: Sun Jun 30 10:47:46 2019 Job end: Sun Jun 30 10:47:58 2019
Now on our youtube channel , Chris asked how can we create mutliple file parts while exporting the data from table. This can be really helpful if you are handling huge number of records. This can be done by simply specifying the file writer instances in the script. Just change $FILE_WRITER()[1] to $FILE_WRITER()[2] to create 2 file parts. However this does not mean there always be data in all file parts. It will generate 2 File parts but if data is less you may not see data in all file parts.
Let’s run the script and see the logs.
TDExpress15.0.0.8_Sles10:~/raj # tbuild -f exp_Customer.tpt -v var_Customer.txt Teradata Parallel Transporter Version 15.00.00.00 Job log: /opt/teradata/client/15.00/tbuild/logs/root-6.out Job id is root-6, running on TDExpress15008_Sles10 Teradata Parallel Transporter $FILE_WRITER[1]: TPT19006 Version 15.00.00.00 $FILE_WRITER[1]: TPT19010 Instance 1 directing private log report to 'dtacop-root-21143-1'. $FILE_WRITER[2]: TPT19010 Instance 2 directing private log report to 'dtacop-root-21144-2'. Teradata Parallel Transporter Export Operator Version 15.00.00.00 $EXPORT: private log not specified $FILE_WRITER[1]: TPT19007 DataConnector Consumer operator Instances: 2 $FILE_WRITER[1]: TPT19003 ECI operator ID: '$FILE_WRITER-21143' $FILE_WRITER[1]: TPT19222 Operator instance 1 processing file '/root/raj/data/op_CUSTOMER.dat-1'. $FILE_WRITER[2]: TPT19222 Operator instance 2 processing file '/root/raj/data/op_CUSTOMER.dat-2'. $EXPORT: connecting sessions $EXPORT: sending SELECT request $EXPORT: entering End Export Phase $EXPORT: Total Rows Exported: 100000 $FILE_WRITER[1]: TPT19221 Total files processed: 2. $EXPORT: disconnecting sessions $EXPORT: Total processor time used = '0.08 Second(s)' $EXPORT: Start : Sun Jun 30 10:58:26 2019 $EXPORT: End : Sun Jun 30 10:58:33 2019 Job step MAIN_STEP completed successfully Job root completed successfully Job start: Sun Jun 30 10:58:21 2019 Job end: Sun Jun 30 10:58:33 2019
So in this post we saw how to use default Teradata tbuild templates to write TPT script quickly.
Also we saw sample TPT Export script and how you can change FILE WRITER instances to generate multiple file parts than just 1.
Hope this helps. If any query or question, feel free to leave a comment.
6 ways to load data file into Teradata table
Loading Data files into Teradata table is one of the most common action required in TD datawarehouse environment. In this post, we will discuss about 6 ways of loading data file into teradata table. Also we will talk about pros/cons of each method. We will use below table for all the examples mentioned below:
create multiset table usa_states ( state_id byteint, state_name varchar(100), state_abbr char(2), state_capital varchar(100), state_larg_city varchar(100), state_population int ) primary index (state_id);
We will load following data from file into this table:
stateid~state_name~state_abbr~state_capital~largest_city~population 1~Alabama~AL~Montgomery~Birmingham~4874747 2~Alaska~AK~Juneau~Anchorage~739795 3~Arizona~AZ~Phoenix~Phoenix~7016270 4~Arkansas~AR~Little Rock~Little Rock~3004279 5~California~CA~Sacramento~Los Angeles~39536653 6~Colorado~CO~Denver~Denver~5607154 7~Connecticut~CT~Hartford~Bridgeport~3588184 8~Delaware~DE~Dover~Wilmington~961939 9~Florida~FL~Tallahassee~Jacksonville~20984400 10~Georgia~GA~Atlanta~Atlanta~10429379 11~Hawaii~HI~Honolulu~Honolulu~1427538 12~Idaho~ID~Boise~Boise~1716943 13~Illinois~IL~Springfield~Chicago~12802023 14~Indiana~IN~Indianapolis~Indianapolis~6666818 15~Iowa~IA~Des Moines~Des Moines~3145711 16~Kansas~KS~Topeka~Wichita~2913123 17~Kentucky~KY~Frankfort~Louisville~4454189 18~Louisiana~LA~Baton Rouge~New Orleans~4684333 19~Maine~ME~Augusta~Portland~1335907 20~Maryland~MD~Annapolis~Baltimore~6052177 21~Massachusetts~MA~Boston~Boston~6859819 22~Michigan~MI~Lansing~Detroit~9962311 23~Minnesota~MN~St. Paul~Minneapolis~5576606 24~Mississippi~MS~Jackson~Jackson~2984100 25~Missouri~MO~Jefferson City~Kansas City~6113532 26~Montana~MT~Helena~Billings~1050493 27~Nebraska~NE~Lincoln~Omaha~1920076 28~Nevada~NV~Carson City~Las Vegas~2998039 29~New Hampshire~NH~Concord~Manchester~1342795 30~New Jersey~NJ~Trenton~Newark~9005644 31~New Mexico~NM~Santa Fe~Albuquerque~2088070 32~New York~NY~Albany~New York~19849399 33~North Carolina~NC~Raleigh~Charlotte~10273419 34~ North Dakota~ND~Bismarck~Fargo~755393 35~Ohio~OH~Columbus~Columbus~11658609 36~Oklahoma~OK~Oklahoma City~Oklahoma City~3930864 37~Oregon~OR~Salem~Portland~4142776 38~Pennsylvania~PA~Harrisburg~Philadelphia~12805537 39~Rhode Island~RI~Providence~Providence~1059639 40~South Carolina~SC~Columbia~Charleston~5024369 41~South Dakota~SD~Pierre~Sioux Falls~869666 42~Tennessee~TN~Nashville~Nashville~6715984 43~Texas~TX~Austin~Houston~28304596 44~Utah~UT~Salt Lake City~Salt Lake City~3101833 45~Vermont~VT~Montpelier~Burlington~623657 46~Virginia~VA~Richmond~Virginia Beach~8470020 47~Washington~WA~Olympia~Seattle~7405743 48~West Virginia~WV~Charleston~Charleston~1815857 49~Wisconsin~WI~Madison~Milwaukee~5795483 50~Wyoming~WY~Cheyenne~Cheyenne~579315
Following points to observe before loading this file:
1) File comes with header
2) File is delimited with “~” as seperator
3) There are 50 records in the file.
Let’s start loading this file into table.
SQL Editor
Nowadays most of the SQL editor comes with Import feature to load data file into Teradata table. I have used Teradata Studio, Teradata SQL Assistant, Squirrel, HeidiSQL etc to load data files and it is very simple. Also steps involved to load data files in different SQL editors is also similar. Here I will show you how to use Teradata Studio to load data file.
Teradata Studio:
Once you create the table, go to “Data Source Explorer” on the left panel. Browse to your database and move to your table. Right click on the table name and select “Load Data” option (refer to screenshot below). It is good idea to maximize the “Load Data Wizard” window and browse the data file. Change the delimiter to the one you have in data file. In our case we have Headers, so I have checked “Column Labels in First Row” option. Click on Finish. That is it. Now you can check data in your table.
Similarly, for Teradata SQL Assistant, you can follow these steps:
Importing Data from FLAT FILE to TABLE using TERADATA SQL ASSISTANT is very simple.It is very easy to import data from any flat file to table in TERADATA using TERADATA SQL Assistant. First, you need to write SQL Query in Query Window. INSERT query should be in accordance with the flat file considering the columns mentioned in FLAT FILE in the same order.
If your flat file contains five columns, mention those five columns in the same order in your INSERT CLAUSE.
INSERT INTO TABLENAME (COL1,COL2,COL3,COL4,COL5) VALUES (?,?,?,?,?);
how to import data in teradata sql assistant
Go to FILE –> Import Data. Now when you run the query, it will display the browse window and will ask you to select the file to which you want to use to import data.
Points to consider while importing data from table:
a) Delimiter: Whether you want to import FIXED WIDTH or DELIMITED file. If file is delimited then you need to select the delimiter for the input file. To select the appropriate Delimiter for your output file go to TOOLS–>OPTIONS –> EXPORT/IMPORT –> Set the Delimiter.
b) SKIP HEADER:There are other options available, like Ignore First Record (Skip Header) .So check these options also while importing data.
c) Suitable UTILITY:IMPORT via TERADATA SQL Assistant is not preferable if data is too huge in terms of records counts or row length. As it operates at row level, importing huge volume of data may take long time. For such cases, use FASTLOAD or MULTILOAD utility.
You can watch below video to see how to import data into Teradata using Teradata SQL Assistant Import Feature.
Pros: Extremely user friendly approach.
Cons: Not good for huge file with millions of records.
BTEQ Import
Very common utility to load small data file into Teradata table. Operates at row level. Easy to develop, execute, debug.
.LOGON TDPID/USER, PASS; .IMPORT vartext '~' file = '\PATH_TO_FILE\US_States_data.txt', skip=1; .REPEAT * using state_id (varchar(100)),state_name (varchar(100)),state_abbr (varchar(100)),state_capital (varchar(100)),state_larg_city (varchar(100)),state_population (varchar(100)) INSERT into usa_states("state_id", "state_name", "state_abbr", "state_capital", "state_larg_city", "state_population") values (:state_id, :state_name, :state_abbr, :state_capital, :state_larg_city, :state_population)
Pros: user friendly script. Operates at rows level but faster than typical SQL editor.
Cons: Since it operates at row level hence not good for huge file with millions of records.
Teradata Fastload
Fastload is lightning fast utility to load data files into Teradata table. It operates at the block-level so it takes as many rows as it can push into a block and moves it to various AMP vdisks. Movement of records to respective AMP is done at later stage but it is way faster because it utilise AMP memory for that task. Below is the sample code:
LOGON TDPID/USER, PASS; RECORD 2; BEGIN LOADING usa_states ERRORFILES usa_states_ET, usa_states_UV CHECKPOINT 10; SET RECORD VARTEXT "~"; DEFINE state_id (varchar(100)), state_name (varchar(100)), state_abbr (varchar(100)), state_capital (varchar(100)), state_larg_city (varchar(100)), state_population (varchar(100)), FILE = '\PATH_TO_FILE\US_States_data.txt'; INSERT into usa_states("state_id", "state_name", "state_abbr", "state_capital", "state_larg_city", "state_population") values (:state_id, :state_name, :state_abbr, :state_capital, :state_larg_city, :state_population); END LOADING; LOGOFF;
Pros: Very fast loading of data file. Loads millions of records in seconds.
Cons: Different style of writing Fastload script. So developer should be well-versed with syntax.
Target table must be empty to run fastload.
There must not be constraints like referential integrity of primary key on Target table columns.
Not much of data manipulation is allowed in the INSERT query in FASTLOAD.
Teradata Multiload
Multiload is another utility which can be used to load data file into Teradata. It also works at block level and not at row-level. So it is very quick as well. However it does not require Target table to be empty. It is slightly slower than Fastload.
.LOGTABLE usa_states_log; .LOGON TDPID/USER, PASS; .BEGIN MLOAD TABLES usa_states; .LAYOUT USA; .FIELD state_id * VARCHAR(100); .FIELD state_name * VARCHAR(100); .FIELD state_abbr * VARCHAR(100); .FIELD state_capital * VARCHAR(100); .FIELD state_larg_city * VARCHAR(100); .FIELD state_population * VARCHAR(100); .DML LABEL LoadLabel; INSERT into usa_states("state_id", "state_name", "state_abbr", "state_capital", "state_larg_city", "state_population") values (:state_id, :state_name, :state_abbr, :state_capital, :state_larg_city, :state_population); .IMPORT INFILE '\PATH_TO_FILE\US_States_data.txt' FORMAT VARTEXT '~' FROM 2 LAYOUT USA APPLY LoadLabel; .END MLOAD; LOGOFF;
Pros: Quick loading of data file. Loads millions of records in seconds.
Cons: Different style of writing Multiload script. So developer should be well-versed with syntax.
Teradata Parallel Transporter (TPT)
DEFINE JOB SAMPLE_FILE_LOAD DESCRIPTION 'Load a Teradata table from a file' ( DEFINE SCHEMA SCHEMA_EMP_NAME ( EMP_NAME VARCHAR(50), AGE VARCHAR(2) ); DEFINE OPERATOR op_EMP_NAME TYPE DATACONNECTOR PRODUCER SCHEMA SCHEMA_EMP_NAME ATTRIBUTES ( VARCHAR DirectoryPath= '', VARCHAR FileName = 'EMP_NAME.CSV', VARCHAR Format = 'Delimited', VARCHAR OpenMode = 'Read', VARCHAR TextDelimiter ='|' ); DEFINE OPERATOR od_EMP_NAME TYPE DDL ATTRIBUTES ( VARCHAR LogonMech = '', VARCHAR TdpId = '', VARCHAR UserName = '', VARCHAR UserPassword = '', VARCHAR ErrorList = '3807' ); DEFINE OPERATOR ol_EMP_NAME TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR LogonMech = '', VARCHAR PrivateLogName = 'load_log', VARCHAR TdpId = '', VARCHAR UserName = '', VARCHAR UserPassword = '', VARCHAR LogTable = 'EMP_NAME_LG', VARCHAR ErrorTable1 = 'EMP_NAME_ET', VARCHAR ErrorTable2 = 'EMP_NAME_UV', VARCHAR TargetTable = 'EMP_NAME' ); STEP stSetup_Tables ( APPLY ('DROP TABLE EMP_NAME_LG;'), ('DROP TABLE EMP_NAME_ET;'), ('DROP TABLE EMP_NAME_UV;'), ('DROP TABLE EMP_NAME;'), ('CREATE TABLE EMP_NAME(EMP_NAME VARCHAR(50), AGE VARCHAR(2));') TO OPERATOR (od_EMP_NAME); ); STEP stLOAD_FILE_NAME ( APPLY ('INSERT INTO EMP_NAME (EMP_NAME,Age) VALUES (:EMP_NAME,:Age); ') TO OPERATOR (ol_EMP_NAME) SELECT * FROM OPERATOR(op_EMP_NAME); ); );
Pros & Cons explained earlier.
JDBC Connection to connect to Teradata
If you are java developer, then you can create Java program to connect to Teradata and load data files. Kindly refer to below links for sample Java programs:
https://developer.teradata.com/doc/connectivity/jdbc/reference/current/samplePrograms.html
Leave a Reply