注意,用于导出的外部表一定是Writable,赋权的时候一定要Grant ALL给一个用户,该用户才能使用这个外部表导出数据。
1. 带分隔符导出
可以只为外部表创建一个很长的字段,在select语句中拼接成一个字段:
CREATE Writable EXTERNAL TABLE schema_name.external_table_name
(
exportdata character varying(3000)
)
LOCATION (
'gpfdist://192.168.0.240:10000/temp/my_table_file.txt'
)
FORMAT 'text' (delimiter '^' null '' escape '\\')
ENCODING 'GB18030';
ALTER TABLE schema_name.external_table_name OWNER TO gpadmin;
GRANT ALL ON TABLE schema_name.external_table_name TO a_user;
2. 定长导出
drop external table schema_name.external_table_name;
CREATE Writable EXTERNAL TABLE schema_name.external_table_name
(
field1 character varying(2),
field2 character varying(8),
field3 character varying(15),
field4 character varying(1),
)
LOCATION (
'gpfdist: