GBase 8a MPP Cluster 查询结果远程导出示例

本示例使用的数据如下：

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'; 
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

3. 指定字段包围符

-- 指定字段包围符为 '"'
gbase> rmt:SELECT * FROM cust INTO OUTFILE '/home/gbase/temp/cust.txt' ENCLOSED BY '"'; 
Query OK, 4 rows affected

查看导出文件

$ cat cust.txt 
"1"	"xiaoming"	"Tianjin"
"3"	"qiaorui"	"Hebei"
"4"	"tianfei"	"Anhui"
"2"	"zhangling"	"Hunan"

4. 指定转义符

-- 示例中所用的表及数据：
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   | Anh
ui |
| 5    | canzhuo | Hunan         |
+------+---------+---------------+
5 rows in set

导出 SQL 语句：
gbase> rmt:SELECT * FROM product INTO OUTFILE '/home/gbase/temp/product.txt' 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 被指定为转义符后，被作为特殊字符处理。