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

最新推荐文章于 2018-03-28 14:52:47 发布

数据源的港湾

最新推荐文章于 2018-03-28 14:52:47 发布

阅读量1.2k

点赞数 1

分类专栏： QianBase/EsgynDB/Trafodion

本文链接：https://blog.csdn.net/Post_Yuan/article/details/52671874

版权

QianBase/EsgynDB/Trafodion 专栏收录该内容

263 篇文章 3 订阅

订阅专栏

（注：以下只是初步介绍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.file
1,liu
2,zhang
3,li
4,wu
5,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=2
odb [main(1354)] - Unknow option :fs=,:rows=5:loadcmd=IN:truncate:parallel=2. Ignored
odb [2016-09-26 09:15:51]: starting ODBC connection(s)... (1) 1 2
Connected 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/s
odb [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 Trafodion
odb [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_20160926093709
1,"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:truncate
Connected to Trafodion
odb [2016-09-26 09:51:51]: starting ODBC connection(s)... 0 >1 >2
Connected 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.file
aa,00,liu,one
bb,11,zhang,two
cc,22,li,three
dd,33,wu,four
ee,44,feng,five

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

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

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

[centos@cent-1 bin]$ ./odb64luo -u trafodion -p traf123 -d traf -l src=test_transform.file:tgt=trafodion.seabase.test_tbl:map=m.map:fs=,:truncate
odb [2016-09-26 10:28:49]: starting ODBC connection(s)... 0
Connected 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/s
odb [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.