GaussDB T 支持使用dump和load将数据库中的数据导出成标准化通用文件，可使用gaussdb T 的 load导入Oracle、MySQL、SQLserver等数据库导出的文件，同时也可以使用dump导出gaussdb T 的数据，然后导入到其他数据库或者其他gaussdb数据库。
功能类似于Oracle的sqlldr，同时也可用于第三方工具导入到其他数据库中。这种通用格式的文件内容如下:[omm@pr7~]$moreBMSQL_HISTORY.dmp
150001,1,1,6,1,6,-12-2611:15:43.086000,10,jRNtYWMBOaKB
150002,2,1,6,1,6,-12-2611:15:43.091000,10,Wq66ccQqhdv4U3TuX
150003,3,1,6,1,6,-12-2611:15:43.091000,10,oAMMJOA2wxZx
150004,4,1,6,1,6,-12-2611:15:43.091000,10,RPqp4vCRYiHn
导出数据 dump
dump支持将表或者一个查询的SQL转存到文件中，用于迁移和备份，关于dump命令的详细介绍及参数说明参考：
华为GaussDB T DUMP https://www.modb.pro/db/8480
查看帮助：SQL>dump-u;
Thesyntaxofdatadumperis:
DUMP{TABLEtable_name|QUERY"select_query"}
INTOFILE"file_name"
[FILESIZE'uint64_file_size']
[{FIELDS|COLUMNS}ENCLOSEDBY'ascii_char'[OPTIONALLY]]
[{FIELDS|COLUMNS}TERMINATEDBY'string']
[{LINES|ROWS}TERMINATEDBY'string']
[CHARSETstring]
[ENCRYPTBY'password'];
导出BMSQL_HISTORY表全部数据：
dump table BMSQL_HISTORY into file
‘BMSQL_HISTORY.dmp’;[omm@pr7~]$zsqlsteven/"modb123$"@127.0.0.1:1888-q
connected.
SQL>descBMSQL_HISTORY
NameNull?Type
-------------------------------------------------------------------------------
HIST_IDNOTNULLBINARY_INTEGER
H_C_IDBINARY_INTEGER
H_C_D_IDBINARY_INTEGER
H_C_W_IDBINARY_INTEGER
H_D_IDBINARY_INTEGER
H_W_IDBINARY_INTEGER
H_DATETIMESTAMP(6)
H_AMOUNTNUMBER(6,2)
H_DATAVARCHAR(24BYTE)
SQL>selectcount(*)fromBMSQL_HISTORY;
COUNT(*)
--------------------329908
1rowsfetched.
SQL>dumptableBMSQL_HISTORYintofile'BMSQL_HISTORY.dmp';
5000rowsdumped.
10000rowsdumped.
15000rowsdumped.
20000rowsdumped.
25000rowsdumped.
30000rowsdumped.
35000rowsdumped.
40000rowsdumped.
45000rowsdumped.
50000rowsdumped.
55000rowsdumped.
60000rowsdumped.
65000rowsdumped.
70000rowsdumped.
75000rowsdumped.
80000rowsdumped.
85000rowsdumped.
90000rowsdumped.
95000rowsdumped.
100000rowsdumped.
105000rowsdumped.
110000rowsdumped.
115000rowsdumped.
120000rowsdumped.
125000rowsdumped.
130000rowsdumped.
135000rowsdumped.
140000rowsdumped.
145000rowsdumped.
150000rowsdumped.
155000rowsdumped.
160000rowsdumped.
165000rowsdumped.
170000rowsdumped.
175000rowsdumped.
180000rowsdumped.
185000rowsdumped.
190000rowsdumped.
195000rowsdumped.
200000rowsdumped.
205000rowsdumped.
210000rowsdumped.
215000rowsdumped.
220000rowsdumped.
225000rowsdumped.
230000rowsdumped.
235000rowsdumped.
240000rowsdumped.
245000rowsdumped.
250000rowsdumped.
255000rowsdumped.
260000rowsdumped.
265000rowsdumped.
270000rowsdumped.
275000rowsdumped.
280000rowsdumped.
285000rowsdumped.
290000rowsdumped.
295000rowsdumped.
300000rowsdumped.
305000rowsdumped.
310000rowsdumped.
315000rowsdumped.
320000rowsdumped.
325000rowsdumped.
329908rowsdumped.
DumpTABLEsuccessfully:
329908rowsaretotallydumped.
查看导出文件：[omm@pr7~]$ls-lBMSQL_HISTORY.dmp
-rw-------1ommdbgrp22851266Jan918:15BMSQL_HISTORY.dmp
[omm@pr7~]$moreBMSQL_HISTORY.dmp
150001,1,1,6,1,6,-12-2611:15:43.086000,10,jRNtYWMBOaKB
150002,2,1,6,1,6,-12-2611:15:43.091000,10,Wq66ccQqhdv4U3TuX
150003,3,1,6,1,6,-12-2611:15:43.091000,10,oAMMJOA2wxZx
150004,4,1,6,1,6,-12-2611:15:43.091000,10,RPqp4vCRYiHn
150005,5,1,6,1,6,-12-2611:15:43.091000,10,KF2JEs44N7DQF1Q
150006,6,1,6,1,6,-12-2611:15:43.092000,10,HLYGQlJfcx54Nv
150007,7,1,6,1,6,-12-2611:15:43.092000,10,tYZtZ9MVsxUGr13b
150008,8,1,6,1,6,-12-2611:15:43.092000,10,IVH0kTgcptDKmJA0
150009,9,1,6,1,6,-12-2611:15:43.093000,10,JrZ7xLw8Vrq24SorDXv
......
导出BMSQL_HISTORY表的最新数据：
dump query “select HIST_ID,H_DATE,H_DATA from BMSQL_HISTORY where H_DATE>to_date(‘1226 112000’,‘yyyymmdd hh24miss’)” into file ‘BMSQL_HISTORY_1226.dmp’ COLUMNS ENCLOSED BY ‘’’’ COLUMNS TERMINATED BY ‘|’;SQL>selectcount(*)fromBMSQL_HISTORYwhereH_DATE>to_date('1226112000','yyyymmddhh24miss');
COUNT(*)
--------------------29908
1rowsfetched.
SQL>dumpquery"selectHIST_ID,H_DATE,H_DATAfromBMSQL_HISTORYwhereH_DATE>to_date('1226112000','yyyymmddhh24miss')"
2intofile'BMSQL_HISTORY_1226.dmp'
3COLUMNSENCLOSEDBY''''
4COLUMNSTERMINATEDBY'|';
5000rowsdumped.
10000rowsdumped.
15000rowsdumped.
20000rowsdumped.
25000rowsdumped.
29908rowsdumped.
DumpQUERYsuccessfully:
29908rowsaretotallydumped.
查看导出文件：[omm@pr7~]$ls-lBMSQL_HISTORY_1226.dmp
-rw-------1ommdbgrp1811899Jan918:17BMSQL_HISTORY_1226.dmp
[omm@pr7~]$moreBMSQL_HISTORY_1226.dmp
'330328'|'-12-2611:22:09.908000'|'Uw0bMTwQqlt9m9Xe'
'330330'|'-12-2611:22:09.943000'|'XM4gpUzXrQhdWYUiV'
'330332'|'-12-2611:22:09.942000'|'gi2eaJEXLbfacgXB'
'330338'|'-12-2611:22:10.197000'|'Uw0bMTwQqE2vLqtD'
'330340'|'-12-2611:22:10.218000'|'XM4gpUzXrQhdWYUiV'
'330342'|'-12-2611:22:10.195000'|'gi2eaJr0pVEx'
'330343'|'-12-2611:22:10.268000'|'XM4gpUzuQmoDnGj'
'330345'|'-12-2611:22:10.268000'|'Uw0bMTwQqPgBomyJ2u'
导入数据 load
在数据库迁移或者数据备份时，需要进行数据导入导出，GaussDB T支持使用“LOAD”命令导入数据。详细参数说明参考：
华为GaussDB T LOAD
https://www.modb.pro/db/8483
导入数据导备份表BMSQL_HISTORY_BAK：
load data infile “BMSQL_HISTORY.dmp” into table BMSQL_HISTORY_BAK;SQL>createtableBMSQL_HISTORY_BAKasselect*fromBMSQL_HISTORYwhere1=2;
Succeed.
SQL>loaddatainfile"BMSQL_HISTORY.dmp"intotableBMSQL_HISTORY_BAK;
15365rowshavebeencommitted.
30798rowshavebeencommitted.
46201rowshavebeencommitted.
61483rowshavebeencommitted.
76892rowshavebeencommitted.
92173rowshavebeencommitted.
107466rowshavebeencommitted.
122796rowshavebeencommitted.
138123rowshavebeencommitted.
153400rowshavebeencommitted.
168709rowshavebeencommitted.
183939rowshavebeencommitted.
199248rowshavebeencommitted.
214567rowshavebeencommitted.
229861rowshavebeencommitted.
245158rowshavebeencommitted.
260440rowshavebeencommitted.
275391rowshavebeencommitted.
290436rowshavebeencommitted.
304915rowshavebeencommitted.
318853rowshavebeencommitted.
329908rowshavebeencommitted.
Completethedataload.
totallyreadrows:329908
ignoredrows:0
loadedrows:329908
committedrows:329908
errorrows:0
skiprows:0
SQL>selectcount(*)fromBMSQL_HISTORY_BAK;
COUNT(*)
--------------------
329908
1rowsfetched.
导入DUMP QUERY出来的数据：
THREADS指定线程数，NOLOGGING指定插入数据不记录redo日志和undo日志。SQL>loaddatainfile"BMSQL_HISTORY_1226.dmp"intotableBMSQL_HISTORY_1216
2COLUMNSENCLOSEDBY''''
3COLUMNSTERMINATEDBY'|'
4THREADS3
5NOLOGGING;
12596rowshavebeencommitted.
29908rowshavebeencommitted.
Completethedataload.
totallyreadrows:29908
ignoredrows:0
loadedrows:29908
committedrows:29908
errorrows:0
skiprows:0SQL>selectcount(*)fromBMSQL_HISTORY_1216;
COUNT(*)
--------------------
29908
1rowsfetched.
转自墨天轮
