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”被指定为转义符后,被作为特殊字符处理

  
    展开阅读全文