原文鏈接：https://www.gbase.cn/community/post/3870
更多精彩內(nèi)容盡在南大通用GBase技術(shù)社區(qū)悔醋，南大通用致力于成為用戶最信賴的數(shù)據(jù)庫(kù)產(chǎn)品供應(yīng)商。
GaussDB數(shù)據(jù)導(dǎo)出
導(dǎo)出方式對(duì)比介紹
GDS外表Rmote導(dǎo)出示例：
mkdir?-p?/output_data?groupadd?gdsgrp?useradd?-g?gdsgrp?gds_user?chown?-R?gds_user:gdsgrp?/output_data/opt/bin/gds/gds?-d?/output_data?-p?192.168.0.90:5000?-H?10.10.0.1/24?-D?CREATE?FOREIGN?TABLE?foreign_tpcds_reasons?(?r_reason_sk?integer?not?null,?r_reason_id?char(16)?not?null,?r_reason_desc?char(100)?)?SERVER?gsmpp_server?OPTIONS?(LOCATION?'gsfs://192.168.0.90:5000/',?FORMAT?'CSV',ENCODING?'utf8',DELIMITER?E'\x08',?QUOTE?E'\x1b',?NULL?'')?WRITE?ONLY;?INSERT?INTO?foreign_tpcds_reasons?SELECT?*?FROM?reasons;?ps?-ef|grep?gds?gds_user?128954?1?0?15:03???00:00:00?gds?-d?/output_data?-p?192.168.0.90:5000?-D?gds_user?129003?118723?0?15:04?pts/0?00:00:00?grep?gds?kill?-9?128954
GDS外表local導(dǎo)出示例：
mkdir?-p?/output_data?chown?-R?omm:wheel?/output_data?CREATE?FOREIGN?TABLE?foreign_tpcds_reasons?(?r_reason_sk?integer?not?null,?r_reason_id?char(16)?not?null,?r_reason_desc?char(100)?)?SERVER?gsmpp_server?OPTIONS?(LOCATION?'file:///output_data/',?FORMAT?'CSV',ENCODING?'utf8',?DELIMITER?E'\x08',?QUOTE?E'\x1b',?NULL?'')?WRITE?ONLY;?INSERT?INTO?foreign_tpcds_reasons?SELECT?*?FROM?reasons;
gs_dumpall導(dǎo)出示例
導(dǎo)出所有數(shù)據(jù)庫(kù)的公共全局表空間信息和用戶信息（omm用戶為管理員用戶）兽叮，導(dǎo)出文件為文本格式芬骄。
gs_dumpall?-W?Bigdata@123?-U?omm?-f?/home/omm/backup/MPPDB_globals.sql?-p?25308?-g?gs_dumpall[port='25308'][2018-11-14?19:06:24]:?dumpall?operation?successful?gs_dumpall[port='25308'][2018-11-14?19:06:24]:?total?time:?1150?ms
導(dǎo)出所有數(shù)據(jù)庫(kù)全量信息（omm用戶為管理員用戶），導(dǎo)出文件為文本格式鹦聪。執(zhí)行命令后账阻，會(huì)有很長(zhǎng)的打印信息，最終出現(xiàn)total time即代表執(zhí)行成功泽本。
gs_dumpall?-W?Bigdata@123?-U?omm?-f?/home/omm/backup/MPPDB_backup.sql?-p?25308?gs_dumpall[port='25308'][2017-07-21?15:57:31]:?dumpall?operation?successful?gs_dumpall[port='25308'][2017-07-21?15:57:31]:?total?time:?9627?ms
導(dǎo)出所有數(shù)據(jù)庫(kù)定義（omm用戶為管理員用戶）淘太，導(dǎo)出文件為文本格式。
gs_dumpall?-W?Bigdata@123?-U?omm?-f?/home/omm/backup/MPPDB_backup.sql?-p?25308?-s?gs_dumpall[port='25308'][2018-11-14?11:28:14]:?dumpall?operation?successful?gs_dumpall[port='25308'][2018-11-14?11:28:14]:?total?time:?4147?ms
4.2GBase 8a MPP數(shù)據(jù)導(dǎo)入
執(zhí)行sql文件導(dǎo)入數(shù)據(jù)庫(kù)定義
gccli?-ugbase?-pgbase20110531?-Dtestdb?-vvv?-f?<guessdb_out.sql?>>guessdb_out.result??2>guessdb_out.err
注：-D參數(shù)后必須跟gbase集群內(nèi)已經(jīng)存在的庫(kù)规丽，執(zhí)行的guessdb_out.sql文件將按照文件中sql指定的庫(kù)操作蒲牧，跟-D參數(shù)后面的庫(kù)無(wú)關(guān)。
GBase 8a MPP導(dǎo)入文本數(shù)據(jù)
第一步：GaussDB導(dǎo)出的數(shù)據(jù)所在的數(shù)據(jù)服務(wù)器赌莺，需要配置ftp服務(wù)冰抢，并保證GBase 8a MPP集群各節(jié)點(diǎn)可以ftp訪問(wèn)數(shù)據(jù)服務(wù)器數(shù)據(jù)文件。
第二步：整理GuessDB導(dǎo)出的數(shù)據(jù)文件的特征
編碼格式艘狭、 字段分隔符（delimiter）挎扰、 引號(hào)字符（quote）翠订、數(shù)據(jù)文件中空值（null）、逃逸字符（escape）默認(rèn)值雙引號(hào)遵倦、數(shù)據(jù)文件是否包含標(biāo)題行（header）尽超、 導(dǎo)出數(shù)據(jù)文件換行符樣式、日期列的數(shù)據(jù)格式等
第三步：根據(jù)第二步整理出來(lái)的特征骇吭，在GBase 8a MPP中編寫導(dǎo)入數(shù)據(jù)的sql并執(zhí)行橙弱。
語(yǔ)法格式:?
LOAD?DATA?INFILE?'file_list'?INTO?TABLE?[dbname.]tbl_name?[options]?options:?[CHARACTER?SET?charset_name]?[DATA_FORMAT?number?[HAVING?LINES?SEPARATOR]]?[NULL_VALUE?'string']?[FIELDS?[TERMINATED?BY?'string']?[ENCLOSED?BY?'string']?[PRESERVE?BLANKS]?[AUTOFILL]?[LENGTH?'string']?[TABLE_FIELDS?'string']?]?[LINES?[TERMINATED?BY?'string']?]?[MAX_BAD_RECORDS?number]?[DATETIME?FORMAT?format][DATE?FORMAT?format]?[TIMESTAMP?FORMAT?format]?[TIME?FORMAT?format]?[TRACE?number]?[TRACE_PATH?'string']?[NOSPLIT]?[PARALLEL?number]?[MAX_DATA_PROCESSORS?number]?[MIN_CHUNK_SIZE?number]?[SKIP_BAD_FILE?number]?[SET?col_name?=?value[,...]]?[IGNORE?NUM?LINES]?[FILE_FORMAT?format]
加載示例：
?多數(shù)據(jù)文件加載
gbase>?LOAD?DATA?INFILE?'ftp://192.168.0.1/pub/lineitem.tbl，?http://192.168.0.2/lineitem.tbl'?INTO?TABLE?test.lineitem?FIELDS?TERMINATED?BY?'|'?ENCLOSED?BY?'"'?LINES?TERMINATED?BY?'\n';
帶通配符多文件導(dǎo)入語(yǔ)句
gbase>?LOAD?DATA?INFILE?'ftp://192.168.10.114/data/*'?INTO?TABLE?test.t;
?帶列燥狰、行分隔符和包圍符的導(dǎo)入語(yǔ)句
gbase>?LOAD?DATA?INFILE?'ftp://192.168.0.1/pub/lineitem.tbl'?INTO?TABLE?test.lineitem?FIELDS?TERMINATED?BY?'|'?ENCLOSED?BY?'"'?LINES?TERMINATED?BY?'\n'
帶日期格式導(dǎo)入語(yǔ)句
load?data?infile?'ftp://192.168.88.141/load_data/table_fields.tbl'?into?table?test.t?fields?terminated?by?','?table_fields?'i,?vc,?dt?date?"%H:%i:%s?%Y-%m-%d",?dt1?date?"%Y-%m-%d?%H:%i:%s"';
帶自動(dòng)填充導(dǎo)入語(yǔ)句
load?data?infile?'ftp://192.168.88.141/load_data/autofill.tbl'?into?table?test.t?fields?terminated?by?'|'?autofill;
帶常量值導(dǎo)入語(yǔ)句
gbase>?Load?data?infile?'data.tbl'?into?table?t?fields?terminated?by?'|'?set?c='2016-06-06?18:08:08',d='default',e=20.6;
?忽略表頭導(dǎo)入語(yǔ)句
gbase>load?data?infile?‘http://192.168.6.39/test.tbl’?into?table?data_test?fields?terminated?by?‘|’?ignore?3?lines;
?帶Blob數(shù)據(jù)導(dǎo)入
gbase>load?data?infile?‘http://192.168.6.39/test.tbl’?into?table?data_test?fields?terminated?by?‘|’?table_fields?‘a(chǎn),b,c?type_text,d’;?gbase>load?data?infile?‘http://192.168.6.39/test.tbl’?into?table?data_test?fields?terminated?by?‘|’?table_fields?‘a(chǎn),b,c?type_base64,d’;?gbase>Load?data?infile?‘http://192.168.6.39/test.tbl’?into?table?data_test?fields?terminated?by?‘|’?table_fields?‘a(chǎn),b,c?type_url,d’;
原文鏈接：www.gbase.cn/community/p…
更多精彩內(nèi)容盡在南大通用GBase技術(shù)社區(qū)棘脐，南大通用致力于成為用戶最信賴的數(shù)據(jù)庫(kù)產(chǎn)品供應(yīng)商。
?
