GBase 8a 导入-导出

导入

1、文本导入

1、 原表
gbase> select *from t100w ;
+------+--------+------+------+---------------------+
| id   | num    | k1   | k2   | dt                  |
+------+--------+------+------+---------------------+
|    1 |  25503 | 0M   | IJ56 | 2019-08-12 11:41:16 |
|    2 | 756660 | rx   | bc67 | 2019-08-12 11:41:16 |
|    3 | 876710 | 2m   | tu67 | 2019-08-12 11:41:16 |
|    4 | 279106 | E0   | VWtu | 2019-08-12 11:41:16 |
|    5 | 641631 | At   | rsEF | 2019-08-12 11:41:16 |
|    6 | 584039 | QJ   | VWlm | 2019-08-12 11:41:16 |
|    7 | 541486 | vc   | ijKL | 2019-08-12 11:41:16 |
|    8 | 771751 | 47   | ghLM | 2019-08-12 11:41:16 |
|    9 | 752847 | aQ   | CDno | 2019-08-12 11:41:16 |
|   10 | 913759 | ej   | EFfg | 2019-08-12 11:41:16 |
+------+--------+------+------+---------------------+


2、导出
select *from  t100w  limit 10  into outfile '/tmp/t100w.txt'  fields  terminated  by '|'  enclosed by '"';

cat  /tmp/t100w.txt
"1"|"25503"|"0M"|"IJ56"|"2019-08-12 11:41:16"
"2"|"756660"|"rx"|"bc67"|"2019-08-12 11:41:16"
"3"|"876710"|"2m"|"tu67"|"2019-08-12 11:41:16"
"4"|"279106"|"E0"|"VWtu"|"2019-08-12 11:41:16"
"5"|"641631"|"At"|"rsEF"|"2019-08-12 11:41:16"
"6"|"584039"|"QJ"|"VWlm"|"2019-08-12 11:41:16"
"7"|"541486"|"vc"|"ijKL"|"2019-08-12 11:41:16"
"8"|"771751"|"47"|"ghLM"|"2019-08-12 11:41:16"
"9"|"752847"|"aQ"|"CDno"|"2019-08-12 11:41:16"
"10"|"913759"|"ej"|"EFfg"|"2019-08-12 11:41:16"


3、 建表
CREATE TABLE `t100w` (
  `id` int(11) ,
  `num` int(11) ,
  `k1` char(2) ,
  `k2` char(4) ,
  `dt` datetime 
);

4、 导入
load data infile 'ftp://10.0.0.57/nihao/t100w.txt' into table test.t100w  data_format 3 fields terminated by '|' enclosed by '"';


gbase> select *from t100w ;
+------+--------+------+------+---------------------+
| id   | num    | k1   | k2   | dt                  |
+------+--------+------+------+---------------------+
|    1 |  25503 | 0M   | IJ56 | 2019-08-12 11:41:16 |
|    2 | 756660 | rx   | bc67 | 2019-08-12 11:41:16 |
|    3 | 876710 | 2m   | tu67 | 2019-08-12 11:41:16 |
|    4 | 279106 | E0   | VWtu | 2019-08-12 11:41:16 |
|    5 | 641631 | At   | rsEF | 2019-08-12 11:41:16 |
|    6 | 584039 | QJ   | VWlm | 2019-08-12 11:41:16 |
|    7 | 541486 | vc   | ijKL | 2019-08-12 11:41:16 |
|    8 | 771751 | 47   | ghLM | 2019-08-12 11:41:16 |
|    9 | 752847 | aQ   | CDno | 2019-08-12 11:41:16 |
|   10 | 913759 | ej   | EFfg | 2019-08-12 11:41:16 |
+------+--------+------+------+---------------------+

2、定长导入

1、原数据 vim t1.txt
10011111111980-05-021001@163.comm2013-05-05 09:20:20
10022222221985-08-201002@163.comm2013-05-06 09:20:20
10033333331978-11-201003@163.comf2013-05-07 09:20:20

2、建表(故意把建表字段搞乱、邮箱数据不需要导入)
create  table t1(no int,pwd  varchar(6),sex varchar(1),birth date,loginTime datetime);

3、导入
load data infile  'ftp://10.0.0.57/nihao/t1.txt'  into table test.t1  data_format 4  fields definer '4,6,10,12,1,19'  table_fields 'no,pwd,birth date "%Y-%m-%d",filler,sex,loginTime  date "%Y-%m-%d %T"';

gbase> select *from t1;
+------+--------+------+------------+---------------------+
| no   | pwd    | sex  | birth      | loginTime           |
+------+--------+------+------------+---------------------+
| 1001 | 111111 | m    | 1980-05-02 | 2013-05-05 09:20:20 |
| 1002 | 222222 | m    | 1985-08-20 | 2013-05-06 09:20:20 |
| 1003 | 333333 | f    | 1978-11-20 | 2013-05-07 09:20:20 |
+------+--------+------+------------+---------------------+

导出表结构

su - gbase
/opt/gcluster/server/bin/gcdump  -ugbase -pgbase  -B -R  test >/home/gbase/test.sql

数据导出语法

select * from  t1 into outfile '/home/gbase/new.txt'  fields  terminated  by '|'  enclosed by '"';

select * from  t1 into outfile '/home/gbase/new.t1.GZ'  fields  terminated  by '|'  enclosed by '"'