GaussDB T 支援使用dump和load将資料庫中的資料導出成标準化通用檔案，可使用gaussdb T 的 load導入Oracle、MySQL、SQLserver等資料庫導出的檔案，同時也可以使用dump導出gaussdb T 的資料，然後導入到其他資料庫或者其他gaussdb資料庫。
功能類似于Oracle的sqlldr，同時也可用于第三方工具導入到其他資料庫中。這種通用格式的檔案内容如下:[[email protected] ~]$ more BMSQL_HISTORY.dmp
150001,1,1,6,1,6,2019-12-26 11:15:43.086000,10,jRNtYWMBOaKB
150002,2,1,6,1,6,2019-12-26 11:15:43.091000,10,Wq66ccQqhdv4U3TuX
150003,3,1,6,1,6,2019-12-26 11:15:43.091000,10,oAMMJOA2wxZx
150004,4,1,6,1,6,2019-12-26 11:15:43.091000,10,RPqp4vCRYiHn
導出資料 dump
dump支援将表或者一個查詢的SQL轉存到檔案中，用于遷移和備份，關于dump指令的詳細介紹及參數說明參考：
華為GaussDB T DUMP https://www.modb.pro/db/8480
檢視幫助：SQL> dump -u;
The syntax of data dumper is:
DUMP {TABLE table_name | QUERY "select_query"}
INTO FILE "file_name"
[FILE SIZE 'uint64_file_size']
[{FIELDS | COLUMNS} ENCLOSED BY 'ascii_char' [OPTIONALLY]]
[{FIELDS | COLUMNS} TERMINATED BY 'string']
[{LINES | ROWS} TERMINATED BY 'string']
[CHARSET string]
[ENCRYPT BY 'password'];
導出BMSQL_HISTORY表全部資料：
dump table BMSQL_HISTORY into file
‘BMSQL_HISTORY.dmp’;[[email protected] ~]$ zsql steven/"modb123$"@127.0.0.1:1888 -q
connected.
SQL> desc BMSQL_HISTORY
Name Null? Type
----------------------------------- -------- ------------------------------------
HIST_ID NOT NULL BINARY_INTEGER
H_C_ID BINARY_INTEGER
H_C_D_ID BINARY_INTEGER
H_C_W_ID BINARY_INTEGER
H_D_ID BINARY_INTEGER
H_W_ID BINARY_INTEGER
H_DATE TIMESTAMP(6)
H_AMOUNT NUMBER(6, 2)
H_DATA VARCHAR(24 BYTE)
SQL> select count(*) from BMSQL_HISTORY ;
COUNT(*)
--------------------329908
1 rows fetched.
SQL> dump table BMSQL_HISTORY into file 'BMSQL_HISTORY.dmp';
5000 rows dumped.
10000 rows dumped.
15000 rows dumped.
20000 rows dumped.
25000 rows dumped.
30000 rows dumped.
35000 rows dumped.
40000 rows dumped.
45000 rows dumped.
50000 rows dumped.
55000 rows dumped.
60000 rows dumped.
65000 rows dumped.
70000 rows dumped.
75000 rows dumped.
80000 rows dumped.
85000 rows dumped.
90000 rows dumped.
95000 rows dumped.
100000 rows dumped.
105000 rows dumped.
110000 rows dumped.
115000 rows dumped.
120000 rows dumped.
125000 rows dumped.
130000 rows dumped.
135000 rows dumped.
140000 rows dumped.
145000 rows dumped.
150000 rows dumped.
155000 rows dumped.
160000 rows dumped.
165000 rows dumped.
170000 rows dumped.
175000 rows dumped.
180000 rows dumped.
185000 rows dumped.
190000 rows dumped.
195000 rows dumped.
200000 rows dumped.
205000 rows dumped.
210000 rows dumped.
215000 rows dumped.
220000 rows dumped.
225000 rows dumped.
230000 rows dumped.
235000 rows dumped.
240000 rows dumped.
245000 rows dumped.
250000 rows dumped.
255000 rows dumped.
260000 rows dumped.
265000 rows dumped.
270000 rows dumped.
275000 rows dumped.
280000 rows dumped.
285000 rows dumped.
290000 rows dumped.
295000 rows dumped.
300000 rows dumped.
305000 rows dumped.
310000 rows dumped.
315000 rows dumped.
320000 rows dumped.
325000 rows dumped.
329908 rows dumped.
Dump TABLE successfully:
329908 rows are totally dumped.
檢視導出檔案：[[email protected] ~]$ ls -l BMSQL_HISTORY.dmp
-rw------- 1 omm dbgrp 22851266 Jan 9 18:15 BMSQL_HISTORY.dmp
[[email protected] ~]$ more BMSQL_HISTORY.dmp
150001,1,1,6,1,6,2019-12-26 11:15:43.086000,10,jRNtYWMBOaKB
150002,2,1,6,1,6,2019-12-26 11:15:43.091000,10,Wq66ccQqhdv4U3TuX
150003,3,1,6,1,6,2019-12-26 11:15:43.091000,10,oAMMJOA2wxZx
150004,4,1,6,1,6,2019-12-26 11:15:43.091000,10,RPqp4vCRYiHn
150005,5,1,6,1,6,2019-12-26 11:15:43.091000,10,KF2JEs44N7DQF1Q
150006,6,1,6,1,6,2019-12-26 11:15:43.092000,10,HLYGQlJfcx54Nv
150007,7,1,6,1,6,2019-12-26 11:15:43.092000,10,tYZtZ9MVsxUGr13b
150008,8,1,6,1,6,2019-12-26 11:15:43.092000,10,IVH0kTgcptDKmJA0
150009,9,1,6,1,6,2019-12-26 11:15:43.093000,10,JrZ7xLw8Vrq24SorDXv
......
導出BMSQL_HISTORY表的最新資料：
dump query “select HIST_ID,H_DATE,H_DATA from BMSQL_HISTORY where H_DATE>to_date(‘20191226 112000’,‘yyyymmdd hh24miss’)” into file ‘BMSQL_HISTORY_20191226.dmp’ COLUMNS ENCLOSED BY ‘’’’ COLUMNS TERMINATED BY ‘|’;SQL> select count(*) from BMSQL_HISTORY where H_DATE>to_date('20191226 112000','yyyymmdd hh24miss');
COUNT(*)
--------------------29908
1 rows fetched.
SQL> dump query "select HIST_ID,H_DATE,H_DATA from BMSQL_HISTORY where H_DATE>to_date('20191226 112000','yyyymmdd hh24miss')"
2 into file 'BMSQL_HISTORY_20191226.dmp'
3 COLUMNS ENCLOSED BY ''''
4 COLUMNS TERMINATED BY '|';
5000 rows dumped.
10000 rows dumped.
15000 rows dumped.
20000 rows dumped.
25000 rows dumped.
29908 rows dumped.
Dump QUERY successfully:
29908 rows are totally dumped.
檢視導出檔案：[[email protected] ~]$ ls -l BMSQL_HISTORY_20191226.dmp
-rw------- 1 omm dbgrp 1811899 Jan 9 18:17 BMSQL_HISTORY_20191226.dmp
[[email protected] ~]$ more BMSQL_HISTORY_20191226.dmp
'330328'|'2019-12-26 11:22:09.908000'|'Uw0bMTwQq lt9m9Xe'
'330330'|'2019-12-26 11:22:09.943000'|'XM4gpUz XrQhdWYUiV'
'330332'|'2019-12-26 11:22:09.942000'|'gi2eaJ EXLbfacgXB'
'330338'|'2019-12-26 11:22:10.197000'|'Uw0bMTwQq E2vLqtD'
'330340'|'2019-12-26 11:22:10.218000'|'XM4gpUz XrQhdWYUiV'
'330342'|'2019-12-26 11:22:10.195000'|'gi2eaJ r0pVEx'
'330343'|'2019-12-26 11:22:10.268000'|'XM4gpUz uQmoDnGj'
'330345'|'2019-12-26 11:22:10.268000'|'Uw0bMTwQq PgBomyJ2u'
導入資料 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> create table BMSQL_HISTORY_BAK as select * from BMSQL_HISTORY where 1=2;
Succeed.
SQL> load data infile "BMSQL_HISTORY.dmp" into table BMSQL_HISTORY_BAK;
15365 rows have been committed.
30798 rows have been committed.
46201 rows have been committed.
61483 rows have been committed.
76892 rows have been committed.
92173 rows have been committed.
107466 rows have been committed.
122796 rows have been committed.
138123 rows have been committed.
153400 rows have been committed.
168709 rows have been committed.
183939 rows have been committed.
199248 rows have been committed.
214567 rows have been committed.
229861 rows have been committed.
245158 rows have been committed.
260440 rows have been committed.
275391 rows have been committed.
290436 rows have been committed.
304915 rows have been committed.
318853 rows have been committed.
329908 rows have been committed.
Complete the data load.
totally read rows: 329908
ignored rows: 0
loaded rows: 329908
committed rows: 329908
error rows: 0
skip rows: 0
SQL> select count(*) from BMSQL_HISTORY_BAK;
COUNT(*)
--------------------
329908
1 rows fetched.
導入DUMP QUERY出來的資料：
THREADS指定線程數，NOLOGGING指定插入資料不記錄redo日志和undo日志。SQL> load data infile "BMSQL_HISTORY_20191226.dmp" into table BMSQL_HISTORY_20191216
2 COLUMNS ENCLOSED BY ''''
3 COLUMNS TERMINATED BY '|'
4 THREADS 3
5 NOLOGGING;
12596 rows have been committed.
29908 rows have been committed.
Complete the data load.
totally read rows: 29908
ignored rows: 0
loaded rows: 29908
committed rows: 29908
error rows: 0
skip rows: 0SQL> select count(*) from BMSQL_HISTORY_20191216;
COUNT(*)
--------------------
29908
1 rows fetched.
轉自墨天輪