一个Teradata TPT Export的简单样例
1 简述
通过一个tpt导出样例，让大家了解teradata tpt工具导出的基本用法。本样例将test_db测试库中的tbl_001测试表中的数据按照指定条件导出到test_tpt.dat文件中。
2 登录Teradata数据库，创建测试数据库和测试表
|
a. 创建测试库 create database test_db as perm=10E9 spool=10E9;
b. 创建测试表 create multiset table test_db.tbl_001( tid varchar(10), tname varchar(20), start_tm timestamp(0), end_tm timestamp(0) )primary index(tid); |
3 生成测试表的数据
|
insert into test_db.tbl_001 values('1001','name1001','2024-06-20 07:24:01', '2024-06-21 07:24:01'); insert into test_db.tbl_001 values('1002','name1002','2024-06-18 07:24:01', '2024-06-19 07:24:01'); insert into test_db.tbl_001 values('1002','name1002','2024-06-17 07:24:01', '2024-06-19 07:24:01'); |
4 定义通用变量，写入jobvars.txt文件中
|
MyTdpId = 'dbc' MyUserName = 'dbc' MyPassword = 'dbc' PreExportTime='2024-06-18 00:00:00' ExportTime='2024-06-20 00:00:00' |
5 编写export_to_text.tpt脚本
代码如下：
|
USING CHARACTER SET ASCII DEFINE JOB EXPORT_CSV DESCRIPTION 'Export table Test_Db.tbl_001 to a text file named test_tpt.dat' ( DEFINE OPERATOR W_1_o_EXPORT_CSV TYPE DATACONNECTOR CONSUMER SCHEMA * ATTRIBUTES ( VARCHAR FileName, VARCHAR Format, VARCHAR OpenMode, VARCHAR IndicatorMode, VARCHAR TextDelimiter );
DEFINE SCHEMA W_0_s_EXPORT_CSV ( tid VARCHAR(10), tname VARCHAR(20), start_tm VARCHAR(20), end_tm VARCHAR(20) );
DEFINE OPERATOR W_0_o_EXPORT_CSV TYPE EXPORT SCHEMA W_0_s_EXPORT_CSV ATTRIBUTES ( VARCHAR UserName, VARCHAR UserPassword, VARCHAR SelectStmt, VARCHAR TdpId );
APPLY TO OPERATOR ( W_1_o_EXPORT_CSV[1]
ATTRIBUTES ( FileName = '/root/test_tpt.dat', Format = 'DELIMITED', OpenMode = 'Write', IndicatorMode = 'N', TextDelimiter = ',' ) ) SELECT * FROM OPERATOR ( W_0_o_EXPORT_CSV[1]
ATTRIBUTES ( UserName = @MyUserName, UserPassword = @MyPassword, SelectStmt = 'SELECT tid, tname, cast(start_tm as varchar(20)),cast(end_tm as varchar(20)) FROM test_db.tbl_001 where start_tm > '''|| @PreExportTime || ''' and end_tm < ''' || @ExportTime || ''';', TdpId = @MyTdpId ) ); ); |
6 执行导出
|
tbuild -f export_to_text.tpt -v jobvars.txt |
7 查看执行结果
- 点赞
- 收藏
- 关注作者
评论（0）