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.

1) 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.

2) 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.

3) 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 accomodate in a block and push 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.

4) 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.

5) Teradata Parallel Transporter (TPT)
Refer to below link:
http://etl-sql.com/teradata-parallel-transporter-tpt-basics-example-and-syntax/

6) 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

One thought on “6 ways to load data file into Teradata table

Leave a Reply

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