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