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 refer to below link:
http://etl-sql.com/teradata-sql-assistant-import-data/

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)

Refer to below link:
https://etl-sql.com/teradata-parallel-transporter-tpt-basics-example-and-syntax/

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

You can also try our online utility to generate Import Scripts for Teradata tables.
Teradata Import Script Generator

Leave a Comment

Your email address will not be published. Required fields are marked *