首页 > 代码库 > Teradata中fastload使用
Teradata中fastload使用
Teradata Fastload Utility 是teradata数据库中一个基于命令行的快速load大量数据到一个空表的工具。
数据可以从以下途径被load:
1) Disk 或 tape;
2) 网络服务器上的文件；
Teradata Fastload使用多个session来load data，但是每一个job只能load到一个表中。如果要load到多个表中，需要提交多个job。
1. fastload脚本文件
SESSIONS 4; /* optional ,total number of sessions to be allotted for the script */ERRLIMIT 1000; /* optional */LOGON localhost/Teradata_Education,Educate; /* tdpid = vivaldi,caracal... */DROP TABLE stu_fl; /* final target table */DROP TABLE error_1; /* error table ,internal to fast load utility needed to be defined */DROP TABLE error_2; /* error table ,internal to fast load utility needed to be defined */CREATE TABLE stu_fl /* target table definition */( stu_no VARCHAR(5), stu_name VARCHAR(10), age VARCHAR(2))PRIMARY INDEX ( stu_no,stu_name,age );SET RECORD VARTEXT "#"; /* delimiter in the source file */DEFINE /* define the structure of the source file */ stu_no (VARCHAR(5)), stu_name (VARCHAR(10)), age (VARCHAR(2))File=stu_fl.dat; /* source file location */SHOW;BEGIN LOADING stu_flERRORFILES error_1, error_2;INSERT INTO stu_fl /* final insert */(stu_no ,stu_name ,age )VALUES(:stu_no ,:stu_name ,:age );END LOADING;LOGOFF;
2. fastload数据文件
TDExpress14.0.3_Sles10:~/tough/test # cat stu_fl.dat
10000#Tough00001#26
10001#Tough#26
10002#Tough#26
10000#Tough00001#26
10001#Tough#26
10002#Tough#26
3. 测试
TDExpress14.0.3_Sles10:~/tough/test # fastload < stu_fl.fastload =================================================================== = = = FASTLOAD UTILITY VERSION 14.00.00.07 = = PLATFORM LINUX = = = =================================================================== =================================================================== = = = Copyright 1984-2012, Teradata Corporation. = = ALL RIGHTS RESERVED. = = = =================================================================== **** 06:55:47 Processing starting at: Sat Jun 28 06:55:47 2014 0001 SESSIONS 4; /* optional ,total number of sessions to be allotted for the s cript */ **** 06:55:47 FDL4866 SESSIONS command accepted 0002 ERRLIMIT 1000; /* optional */ **** 06:55:47 Error limit set to: 1000 =================================================================== = = = Logon/Connection = = = =================================================================== 0003 LOGON localhost/Teradata_Education, **** 06:55:49 Teradata Database Release: 14.00.03.02**** 06:55:49 Teradata Database Version: 14.00.03.02**** 06:55:49 Number of AMPs available: 2**** 06:55:49 Current CLI or RDBMS allows maximum row size: 64K**** 06:55:49 Character set for this job: ASCII0004 DROP TABLE stu_fl; /* final target table */ **** 06:55:50 Command completed successfully 0005 DROP TABLE error_1; /* error table ,internal to fast load utility needed to be defined */ **** 06:55:50 RDBMS error 3807: Object ‘error_1‘ does not exist. 0006 DROP TABLE error_2; /* error table ,internal to fast load utility needed to be defined */ **** 06:55:50 RDBMS error 3807: Object ‘error_2‘ does not exist.0007 CREATE TABLE stu_fl /* target table definition */ ( stu_no VARCHAR(5), stu_name VARCHAR(10), age VARCHAR(2) ) PRIMARY INDEX ( stu_no,stu_name,age ); **** 06:55:50 Command completed successfully0008 SET RECORD VARTEXT "#"; /* delimiter in the source file */ **** 06:55:50 Now set to read ‘Variable-Length Text‘ records**** 06:55:50 Delimiter character(s) is set to ‘#‘**** 06:55:50 Command completed successfully0009 DEFINE /* define the structure of the source file */ stu_no (VARCHAR(5)), stu_name (VARCHAR(10)), age (VARCHAR(2)) File=stu_fl.dat; /* source file location */ **** 06:55:50 FDL4803 DEFINE statement processed0010 SHOW; FILE = stu_fl.dat STU_NO OFFSET = 0 LEN = 5 VARCHAR STU_NAME OFFSET = 7 LEN = 10 VARCHAR AGE OFFSET = 19 LEN = 2 VARCHAR TOTAL RECORD LENGTH = 230011 BEGIN LOADING stu_fl ERRORFILES error_1, error_2; **** 06:55:50 Number of FastLoad sessions requested = 4**** 06:55:50 Number of FastLoad sessions connected = 2**** 06:55:50 FDL4808 LOGON successful**** 06:55:50 Number of AMPs available: 2**** 06:55:50 BEGIN LOADING COMPLETE =================================================================== = = = Insert Phase = = = =================================================================== 0012 INSERT INTO stu_fl /* final insert */ ( stu_no , stu_name , age ) VALUES ( :stu_no , :stu_name , :age ) ; **** 06:55:50 Number of recs/msg: 2920**** 06:55:50 Starting to send to RDBMS with record 1**** 06:55:50 Sending row 3**** 06:55:50 Finished sending rows to the RDBMS **** 06:55:50 Acquisition Phase statistics: Elapsed time: 00:00:00 (in hh:mm:ss) CPU time: 0 Seconds MB/sec: N/A MB/cpusec: N/A =================================================================== = = = End Loading Phase = = = =================================================================== 0013 END LOADING; **** 06:55:51 END LOADING COMPLETE Total Records Read = 3 Total Error Table 1 = 0 ---- Table has been dropped Total Error Table 2 = 0 ---- Table has been dropped Total Inserts Applied = 3 Total Duplicate Rows = 0 Start: Sat Jun 28 06:55:50 2014 End : Sat Jun 28 06:55:51 2014 **** 06:55:51 Application Phase statistics: Elapsed time: 00:00:01 (in hh:mm:ss) 0014 LOGOFF; =================================================================== = = = Logoff/Disconnect = = = =================================================================== **** 06:55:51 Logging off all sessions**** 06:55:52 Total processor time used = ‘0.36 Seconds‘ . Start : Sat Jun 28 06:55:47 2014 . End : Sat Jun 28 06:55:52 2014 . Highest return code encountered = ‘0‘.**** 06:55:52 FDL4818 FastLoad Terminated
4. 查看数据是否load成功
SELECT * FROM TERADATA_EDUCATION. stu_fl ;
数据已经成功load到表中了。
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。