Trafodion Trickle Load 之 odb Load/Extract/Copy命令用法

（注：以下只是初步介绍Trafodion odb工具的几个主要命令的基本用法，更多用法及适用场景请见下载官方文档点击打开链接查看详情）

Load

语法

-l src=[-]file:tgt=table[:map=mapfile][:fs=fieldsep][:rs=recsep][:soe][:skip=linestoskip][:ns=nullstring][:ec=eschar][:sq=stringqualifier][:pc=padchar][:em=embedchar][:errmax=#max_err][:commit=auto|end|#rows|x#rs][:rows=#rowset][:norb][:full][:max=#max_rec][:truncate][:show][:bpc=#][:bpwc=#][:nomark][:parallel=number][:iobuff=#size][:buffsz=#size]][:fieldtrunc=\{0-4}][:pre=\{@sqlfile}|\{[sqlcmd]}][:post=\{@sqlfile}|\{[sqlcmd]}][:ifempty][:direct][:bad=[+]badfile][:tpar=#tables][:maxlen=#bytes][:time][:xmltag=[+]element][:xmlord][:xmldump][:loadcmd=IN|UP|UL]

用例

（1）创建测试表

SQL>create table test_tbl(id int not null, name varchar(10));--- SQL operation complete.SQL>showddl test_tbl;CREATE TABLE TRAFODION.SEABASE.TEST_TBL  (    ID                               INT NO DEFAULT NOT NULL NOT DROPPABLE      SERIALIZED  , NAME                             VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL SERIALIZED  );--- SQL operation complete.

（2）创建测试文件并编辑数据

[centos@cent-1 bin]$ cat test.file1,liu2,zhang3,li4,wu5,feng

（3）Load文件到测试表

[centos@cent-1 bin]$ ./odb64luo -u trafodion -p traf123 -d traf -l src=test.file:tgt=trafodion.seabase.test_tbl\> :fs=\,:rows=5:loadcmd=IN:truncate:parallel=2odb [main(1354)] - Unknow option :fs=,:rows=5:loadcmd=IN:truncate:parallel=2. Ignoredodb [2016-09-26 09:15:51]: starting ODBC connection(s)... (1) 1 2Connected to Trafodion[0.0.0]--- 0 row(s) deleted in 0.687s (prep 0.006s, exec 0.681s, fetch 0.000s/0.000s)[0] 5 records inserted [commit][0] odb version 1.1.0 Load(2) statistics:        [0] Target table: TRAFODION.SEABASE.TEST_TBL        [0] Source: test.file        [0] Pre-loading time: 0.327 s (00:00:00.327)        [0] Loading time: 0.146 s(00:00:00.146)        [0] Total records read: 5        [0] Total records inserted: 5        [0] Total number of columns: 2        [0] Total bytes read: 31        [0] Average input row size: 6.2 B        [0] ODBC row size: 21 B (data) + 16 B (len ind)        [0] Rowset size: 100        [0] Rowset buffer size: 3.61 KiB        [0] Load throughput (real data): 0.207 KiB/s        [0] Load throughput (ODBC): 0.702 KiB/sodb [2016-09-26 09:15:51]: exiting. Session Elapsed time 0.479 seconds (00:00:00.479)

（4）检查Load是否成功

SQL>select * from seabase.test_tbl;ID          NAME----------- ----------          1 liu          2 zhang          3 li          4 wu          5 feng--- 5 row(s) selected.

Extract

语法

-e {src={table|-file}|sql=<customsql>}:tgt=[+]file[:pwhere=where_cond][:fs=fieldsep][:rs=recsep][:sq=stringqualifier][:ec=escape_char][:soe][:ns=nullstring][es=emptystring][:rows=#rowset][:nomark][:binary][:fwc][:max=#max_rec][:trim=[cCvVdt]][:rtrim][:cast][:multi][:efs=string][:parallel=number][:gzip][:gzpar=wb??][:uncommitted][:splitby=column][:pre={@sqlfile}|{[sqlcmd]}[:mpre=\{@sqlfile}|{[sqlcmd]}[:post={@sqlfile}|{[sqlcmd]}][tpar=#tables][:time][:nts][:cols=[-]columns]][:maxlen=#bytes][:xml]

用例

（1）继续使用上述测试表，当前测试表已经加载了5条数据，

SQL>select * from test_tbl;ID          NAME----------- ----------          1 liu          2 zhang          3 li          4 wu          5 feng--- 5 row(s) selected.

（2）Extract上表数据到指定文件

[centos@cent-1 bin]$ ./odb64luo -u trafodion -p traf123 -d traf -e src=trafodion.seabase.test_tbl:tgt=%t_%d%m:rows=m20:sq=\"Connected to Trafodionodb [2016-09-26 09:37:09]: starting ODBC connection(s)... 0[0] 5 records extracted[0] odb version 1.1.0 Extract statistics:        [0] Source: TRAFODION.SEABASE.TEST_TBL        [0] Target: %t_%d%m        [0] Record buffer size: 23 bytes        [0] Rowset size: 911,805        [0] Rowset buffer size: 20,480.00 KiB        [0] Pre-extract time: 0.125 s (00:00:00.125)        [0] Extract time: 0.008 s (00:00:00.008)        [0] Total records extracted: 5 (0.625 krec/s)        [0] Total data bytes written: 41 (5.005 KiB/s)odb [2016-09-26 09:37:09]: exiting. Session Elapsed time 0.138 seconds (00:00:00.138)

（3）查询文件内容

[centos@cent-1 bin]$ cat test_tbl_201609260937091,"liu"2,"zhang"3,"li"4,"wu"5,"feng

Copy

语法

-cp src={table|-file:tgt=schema[.table][pwhere=where_cond][:soe][:nts][:truncate][:rows=#rowset][:nomark][:max=#max_rec][:fwc][:bpwc=#][:parallel=number][errmax=#max_err][:commit=auto|end|#rows|x#rs][:time][:direct][:uncommitted][:norb][:splitby=column][:pre={@sqlfile}|{[sqlcmd]}][:post={@sqlfile}|{[sqlcmd]}][:mpre={@sqlfile}|{[sqlcmd]}][:ifempty][:loaders=#loaders][:tpar=#tables][:cols=[-]columns][sql={[sqlcmd]|@sqlfile|-file}[:bind=auto|char|cdef][tmpre={@sqlfile}|{[sqlcmd]}][seq=field#[,start]]

用例

（1）创建一个表和原测试结构一致

SQL>showddl test_tbl2;CREATE TABLE TRAFODION.SEABASE.TEST_TBL2  (    ID                               INT NO DEFAULT NOT NULL NOT DROPPABLE      SERIALIZED  , NAME                             VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL SERIALIZED  );--- SQL operation complete.

（2）利用COPY命令把原测试表数据复制到新表

[centos@cent-1 bin]$ ./odb64luo -u trafodion:trafodion -p traf123:traf123 -d traf:traf -cp src=trafodion.seabase.test_tbl:tgt=trafodion.seabase.test_tbl2:rows=m2:truncateConnected to Trafodionodb [2016-09-26 09:51:51]: starting ODBC connection(s)... 0 >1 >2Connected to Trafodion[1.0.0]--- 0 row(s) deleted in 0.298s (prep 0.285s, exec 0.013s, fetch 0.000s/0.000s)[1] 5 records copied [commit][0] odb version 1.1.0 Copy statistics:        [0] Source: TRAFODION.SEABASE.TEST_TBL        [0] Target: trafodion.seabase.test_tbl2        [0] Total number of columns: 2        [0] ODBC row size: 23 B (data) + 16 B (len ind)        [0] Rowset size: 53,773        [0] Rowset buffer size: 2,048.00 KiB        [0] Pre-copy time: 0.677 s (00:00:00.677)        [0] Copy time: 0.037 s (00:00:00.037)        [0] Total records copied: 5 (0.135 krec/s)        [0] Copy throughput (ODBC): 0.003 MiB/s  (0.010 GiB/h)                [0] Total/Wait cycles: 1/0                        [0>1] 5 records copied in 0.037 (00:00:00.037 s)                        [0>2] 0 records copied in 0.037 (00:00:00.037 s)odb [2016-09-26 09:51:52]: exiting. Session Elapsed time 0.733 seconds (00:00:00.733)

（3）检查数据是否复制成功

SQL>select * from test_tbl2;ID          NAME----------- ----------          1 liu          2 zhang          3 li          4 wu          5 feng--- 5 row(s) selected.

Transform

语法

Transform是Load的一种，Transform的意思是在Load的时候通过一个mapfile来实现一些功能，比如忽略输入文件的某些行、生产序列号、插入常量、日期格式转换、字符串截取和替换、生成随机数等等。

用例

（1）测试表继续用上述的test_tbl

（2）创建测试Input文件，内容如下

[centos@cent-1 bin]$ cat test_transform.fileaa,00,liu,onebb,11,zhang,twocc,22,li,threedd,33,wu,fouree,44,feng,five

（3）创建map文件，内容如下

[centos@cent-1 bin]$ cat m.map# Map file to load data into trafodion.seabase.test_tblID:seq:1        # Insert into ID starting from 1NAME:2:REPLACE:liu:LIU  # Load field #3 into NAME and replace all liu to LIU

（4）根据map文件加载数据

[centos@cent-1 bin]$ ./odb64luo -u trafion -p traf123 -d traf -l src=test_transform.file:tgt=trafodion.seabase.test_tbl:map=m.map:fs=,:truncateodb [2016-09-26 10:28:49]: starting ODBC connection(s)... 0Connected to Trafodion[0.0.0]--- 5 row(s) deleted in 0.042s (prep 0.001s, exec 0.041s, fetch 0.000s/0.000s)[0] 5 records inserted [commit][0] odb version 1.1.0 Load statistics:        [0] Target table: TRAFODION.SEABASE.TEST_TBL        [0] Source: test_transform.file        [0] Pre-loading time: 0.165 s (00:00:00.165)        [0] Loading time: 0.023 s(00:00:00.023)        [0] Total records read: 5        [0] Total records inserted: 5        [0] Total number of columns: 2        [0] Total bytes read: 75        [0] Average input row size: 15.0 B        [0] ODBC row size: 21 B (data) + 16 B (len ind)        [0] Rowset size: 100        [0] Rowset buffer size: 3.61 KiB        [0] Load throughput (real data): 3.184 KiB/s        [0] Load throughput (ODBC): 4.458 KiB/sodb [2016-09-26 10:28:49]: exiting. Session Elapsed time 0.195 seconds (00:00:00.195)

（5）检查数据是否转换成功

SQL>select * from test_tbl;ID          NAME----------- ----------          1 LIU          2 zhang          3 LIU          4 wu          5 feng--- 5 row(s) selected.