Load operator is a data consumer operator which works similar to native Teradata Fastload utility. Teradata advises to use TPT Load over native Teradata Fastload utility as it is better optimised for performance over native load.
TPT Load can be used toDefine_Operator_Import: TPT19108 Data Format 'TEXT' requires all 'CHAR/ANSIDATE' schema.
TPT10508: RDBMS error 2636: [tablename] must be empty for Fast Loading.
INTEGER SkipRows=2,
VARCHAR SkipRowsEveryFile='N'
DEFINE JOB IMPORT_EMPLOYEE_DATA Description 'TPT fastload script' ( DEFINE OPERATOR Consumer_Table_Detail TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR TdPid='dbc', VARCHAR UserName='tutorial_user', VARCHAR UserPassWord='tutorial_password', VARCHAR TargetDatabase='tutorial_db', VARCHAR TargetTable='emp_unformatted', VARCHAR LogTable='emp_load_log', VARCHAR DateForm='AnsiDate' ); DEFINE SCHEMA Define_Employee_Schema ( emp_no INTEGER, ename_name VARCHAR(50), hire_date AnsiDate ); DEFINE OPERATOR Producer_File_Detail TYPE DATACONNECTOR PRODUCER SCHEMA Define_Employee_Schema ATTRIBUTES ( VARCHAR DirectoryPath='$HOME/tpt/outfile', VARCHAR FileName='emp_unformatted.out', VARCHAR Format='UNFORMATTED', VARCHAR OpenMode='Read', VARCHAR IndicatorMode='N', VARCHAR DateForm='AnsiDate' ); APPLY ( 'INSERT INTO tutorial_db.emp_unformatted(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);' ) TO OPERATOR (Consumer_Table_Detail[3]) SELECT * FROM OPERATOR (Producer_File_Detail); );
DEFINE JOB Import_Employee_Data Description 'Test fastload script' ( DEFINE OPERATOR Consumer_Table_Detail TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR TdPid='dbc', VARCHAR UserName='tutorial_user', VARCHAR UserPassWord='tutorial_password', VARCHAR TargetTable='dept_delimited', VARCHAR LogTable='dept_pradeep_log', VARCHAR DateForm='AnsiDate', INTEGER MaxSessions=6 ); DEFINE SCHEMA Define_Employee_Schema ( emp_no VARCHAR(11), ename_name VARCHAR(50), hire_date VARCHAR(10) ); DEFINE OPERATOR Producer_File_Detail TYPE DATACONNECTOR PRODUCER SCHEMA Define_Employee_Schema ATTRIBUTES ( VARCHAR DirectoryPath='$HOME/tpt/outfile', VARCHAR FileName='tpt_load_delimited_data_file.out', VARCHAR TextDelimiter='|~|', VARCHAR Format='Delimited', VARCHAR OpenMode='Read', VARCHAR IndicatorMode='N', VARCHAR DateForm='AnsiDate', INTEGER SkipRows=2, VARCHAR SkipRowsEveryFile='N' ); APPLY ( 'INSERT INTO tutorial_db.emp_delimited(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);' ) TO OPERATOR(Consumer_Table_Detail) SELECT * FROM OPERATOR (Producer_File_Detail); );
DEFINE JOB Import_Employee_Data Description 'Test fastload script' ( DEFINE OPERATOR Consumer_Table_Detail TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR TdPid='dbc', VARCHAR UserName='tutorial_user', VARCHAR UserPassWord='tutorial_password', VARCHAR TargetTable='dept_delimited', VARCHAR LogTable='dept_pradeep_log', VARCHAR DateForm='AnsiDate', INTEGER MaxSessions=6 ); DEFINE SCHEMA Define_Employee_Schema ( emp_no VARCHAR(11), ename_name VARCHAR(50), hire_date VARCHAR(10) ); DEFINE OPERATOR Producer_File_Detail TYPE DATACONNECTOR PRODUCER SCHEMA Define_Employee_Schema ATTRIBUTES ( VARCHAR DirectoryPath, VARCHAR FileName, VARCHAR TextDelimiter, VARCHAR Format, VARCHAR OpenMode, VARCHAR IndicatorMode, VARCHAR DateForm ); APPLY ( 'INSERT INTO tutorial_db.emp_unformatted(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);' ) TO OPERATOR(Consumer_Table_Detail) SELECT * FROM OPERATOR ( Producer_File_Detail ATTRIBUTES ( DirectoryPath='$HOME/tpt/outfile', FileName='emp_delimited_src_1.out', TextDelimiter='|~|', Format='Delimited', OpenMode='Read', IndicatorMode='N', DateForm='AnsiDate' ) ) UNION ALL SELECT * FROM OPERATOR ( Producer_File_Detail ATTRIBUTES ( DirectoryPath='$HOME/tpt/outfile', FileName='emp_delimited_src_2.out', TextDelimiter='|~|', Format='Delimited', OpenMode='Read', IndicatorMode='N', DateForm='AnsiDate' ) ); ); Note: UNION ALL can be used in APPLY to load data from multiple source files.
tbuild -f tpt_load_unformatted.tpt
tbuild -f tpt_load_delimited.tpt
tbuild -f tpt_load_delimited_multiple_files.tpt