gbase 导出_gbase 通过 gccli 命令客户端导出数据到文件

@font-face{font-family:“Times New Roman”;} @font-face{font-family:“宋体”;} @font-face{font-family:“Calibri”;} @font-face{font-family:“幼圆”;} p.MsoNormal{mso-style-name: 正文; mso-style-parent:““; margin:0pt; margin-bottom:.0001pt; mso-pagination:none; text-align:justify; text-justify:inter-ideograph; font-family:Calibri; mso-fareast-font-family: 宋体; mso-bidi-font-family:‘Times New Roman’; font-size:10.5000pt; mso-font-kerning:1.0000pt;} span.msoIns{mso-style-type:export-only; mso-style-name:””; text-decoration:underline; text-underline:single; color:blue;} span.msoDel{mso-style-type:export-only; mso-style-name:””; text-decoration:line-through; color:red;} @page{mso-page-border-surround-header:no; mso-page-border-surround-footer:no;}@page Section0{margin-top:72.0000pt; margin-bottom:72.0000pt; margin-left:90.0000pt; margin-right:90.0000pt; size:595.3000pt 841.9000pt; layout-grid:15.6000pt;} div.Section0{page:Section0;}

示例：

以 gbase 用户身份，登陆集群节点 192.168.10.115

$ gccli –no-defaults -ugbase -pgbase -h192.168.10.115

注意：–no-defaults 参数必须紧跟 gccli 命令，即作为 gccli 命令的第一个参数。

查询结果导出注意事项

注意事项： rmt:‛与 select_syntax 之间不能有空格。

示例：

可正确导出数据的语句： rmt:SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’;

会报语法错误的语句： rmt: SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’; file_path，以及 FIELD_OPTION 中的字段分隔符，字段包围符，转义标 识符等均必须用单引号‚’‛包围，否则无法正常导出数据。

示例：

可正确导出数据的语句： rmt:SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’; 会报语法错误的语句： rmt:SELECT * FROM cust INTO OUTFILE /home/gbase/temp/cust.txt;

远程导出不支持顶层查询是 UNION 类查询。

示例：

rmt:SELECT * FROM cust UNION SELECT * FROM product INTO OUTFILE ‘/home/gbase/temp/product.txt’; ERROR 1149 (42000): (GBA-02SC-1001) SELECT INTO OUTFILE with UNION is not supported.

远程导出的目标文件如果已经在本地存在，则不能通过 select @@error_count 来获得错误数，原因为文件是否存在由客户端判断，而 @@error_count 是服务端变量。

示例：

gbase> create table t(id int);

Query OK, 0 rows affected

gbase> insert into t values(1);

Query OK, 1 row affected

gbase> rmt:select * from t into outfile ‘/home/gbase/t.txt’;

Query OK, 1 row affected

gbase> rmt:select * from t into outfile ‘/home/gbase/t.txt’;

ERROR: File ‘/home/gbase/t.txt’ already exists

gbase> select @@error_count;

+—————+

| @@error_count |

+—————+

| 0 |

+—————+

1 row in set

查询结果导出示例

不指定字段分隔符

示例中用到的表及数据：

DROP TABLE IF EXISTS cust;

CREATE TABLE cust(c_id INT, c_name VARCHAR(20), c_addr VARCHAR(100));

INSERT INTO cust VALUES (1, ‘xiaoming’, ‘Tianjin’);

INSERT INTO cust VALUES (3, ‘qiaorui’, ‘Hebei’);

INSERT INTO cust VALUES (4, ‘tianfei’, ‘Anhui’);

INSERT INTO cust VALUES (2, ‘zhangling’, ‘Hunan’);

示例 1：

不指定字段分隔符，即使用默认字段分隔符‚\t‛。

gbase> rmt:SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’;

Query OK, 4 rows affected

查看导出文件：

$ cat cust.txt 1

xiaoming Tianjin 3 qiaorui Hebei 4 tianfei Anhui 2 zhangling Hunan

指定字段分隔符

示例中用到的表及数据：

DROP TABLE IF EXISTS cust;

CREATE TABLE cust(c_id INT, c_name VARCHAR(20), c_addr VARCHAR(100));

INSERT INTO cust VALUES (1, ‘xiaoming’, ‘Tianjin’);

INSERT INTO cust VALUES (3, ‘qiaorui’, ‘Hebei’);

INSERT INTO cust VALUES (4, ‘tianfei’, ‘Anhui’);

INSERT INTO cust VALUES (2, ‘zhangling’, ‘Hunan’);

示例 1：

指定字段分隔符为‚,

gbase> rmt:SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’ FIELDS TERMINATED BY ‘,’; Query OK, 4 rows affected

查看导出文件：

$ cat cust.txt

1,xiaoming,Tianjin

3,qiaorui,Hebei

4,tianfei,Anhui

2,zhangling,Hunan

示例 2：

指定字段分隔符为‚;

gbase> rmt:SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’ FIELDS TERMINATED BY ‘;’;

Query OK, 4 rows affected

查看导出文件：

$ cat cust.txt

1;xiaoming;Tianjin

3;qiaorui;Hebei

4;tianfei;Anhui

2;zhangling;Hunan

指定字段包围符为‚”‛

示例中用到的表及数据：

DROP TABLE IF EXISTS cust;

CREATE TABLE cust(c_id INT, c_name VARCHAR(20), c_addr VARCHAR(100));

INSERT INTO cust VALUES (1, ‘xiaoming’, ‘Tianjin’);

INSERT INTO cust VALUES (3, ‘qiaorui’, ‘Hebei’);

INSERT INTO cust VALUES (4, ‘tianfei’, ‘Anhui’);

INSERT INTO cust VALUES (2, ‘zhangling’, ‘Hunan’);

gbase> rmt:SELECT * FROM cust INTO OUTFILE ‘/home/gbase/temp/cust.txt’ FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”’;

Query OK, 4 rows affected

查看导出文件：

$ cat cust.txt

“1”;“xiaoming”;“Tianjin”

“3”;“qiaorui”;“Hebei”

“4”;“tianfei”;“Anhui”

“2”;“zhangling”;“Hunan”

指定转义符为“g”

示例中用到的表及数据：

DROP TABLE IF EXISTS product;

CREATE TABLE product (p_id INT, p_name VARCHAR(20), p_desc VARCHAR(100));

INSERT INTO product VALUES (1, ‘qianzi’, ‘qianzi\qianzi’);

INSERT INTO product VALUES (2, ‘bandeng’, ‘ban”deng’);

INSERT INTO product VALUES (4, ‘jiandao’, ‘Hei;bei’);

INSERT INTO product VALUES (3, ‘chazi’, ‘Anh\nui’);

INSERT INTO product VALUES (5, ‘canzhuo’, ‘Hunan’);

gbase> SELECT * FROM product;

+——+———+—————+

| p_id | p_name | p_desc |

+——+———+—————+

| 1 | qianzi | qianzi\qianzi |

| 2 | bandeng | ban”deng |

| 4 | jiandao | Hei;bei |

| 3 | chazi | ui |

| 5 | canzhuo | Hunan |

+——+———+—————+

5 rows in set

gbase> rmt:SELECT * FROM product INTO OUTFILE ‘/home/gbase/temp/product.txt’ FIELDS TERMINATED BY ‘;’ ESCAPED BY ‘g’;

Query OK, 5 rows affected

查看导出文件：

$ cat product.txt

1;qianzi;qianzi\qianzi

2;bandengg;ban”dengg

4;jiandao;Heig;bei

3;chazi;Anhg ui

5;canzhuo;Hunan

在示例中，“\”没有被置为“g”，是因为指定其他字符为转义字符后， “\”不再被认为是特殊字符。 “;”，“\n”均被置为转义符“g”，表示数据，而不是字段分隔符“;” 和行分隔符“\n”。 “g”也被前置转义符“g”，因为“g”被指定为转义符后，被作为特殊字符处理