暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

GBase 8a MPP Cluster 数据导出参数详解示例

三金先生 2023-10-16
90

Options :

[FIELDS [TERMINATED BY ‘string’] [ENCLOSED BY ‘string’] [LENGTH] [ESCAPED BY ] [DOUBLE_ENCLOSED BY] ] [LINES [TERMINATED BY ‘string’] [ STARTING BY ] ] [CHARACTER SET charset_name] [NULL_VALUE 'string'] [OUTFILEMODE BY] [WRITEMODE BY] [FILECOUNT ] [FILESIZE] [WITH HEAD]

l [FIELDS [TERMINATED BY ‘string’] [ [OPTIONALLY] ENCLOSED BY‘单字符’] [DOUBLE_ENCLOSED BY] [LENGTH] [ESCAPED BY ] ]

• TERMINATED BY:字段分隔符,支持多个字符,如果不指定分隔符则默认值“\t”,即TAB键。

• ENCLOSED BY:字段包围符,可以自行指定单个字符为字段包围符,指定多个字符时报错。支持 OPTIONALLY选项,加OPTIONALLY选项时仅对字符串类型起作用,否则对所有字段都起作用。默认为无 字段包围符。

• DOUBLE_ENCLOSED BY:字段包围符自转义,该参数包含上面参数(字段包围符)的所有功能;但额外 的作用是,当转义符为空时,如果某字段满足使用字段包围符的条件,且该字段中还有与字段包围符相同 的字符,则将该字符通过双写的方式自转义。

• LENGTH:在使用定长模式导出时,用于设定字段长度的参数。定长格式数据导出时,设置每个字段的长 度,多个字段之间用逗号分隔,该参数不能和列分隔符以及包围符混用。

• ESCAPED BY :转义标识符,可以自行指定单个字符为转义标识符,指定多个字符时报错,默认为”\”,默 认值在语句中写为:FIELDS ESCAPED BY '\\'。


示例1: select * from test into outfile '/home/gbase/xyz' FIELDS TERMINATED BY '|' optionally ENCLOSED BY '\'' WRITEMODE BY overwrites;

 导出数据:

 库内数据:


 示例2: select * from test into outfile '/home/gbase/xyz' FIELDS TERMINATED BY '|' ENCLOSED BY '\'' WRITEMODE BY overwrites; 



示例3 : select * from test into outfile '/home/gbase/xyz' FIELDS TERMINATED BY '|' optionally

DOUBLE_ENCLOSED BY '\'' WRITEMODE BY overwrites FIELDS ESCAPED BY''; 

导出数据: 

库内数据: 


例4:ENCLOSED BY select * from test into outfile '/home/gbase/xyz' FIELDS TERMINATED BY '|' optionally ENCLOSED BY '\'' WRITEMODE BY overwrites FIELDS ESCAPED BY'';


示例5: select * from test into outfile '/home/gbase/xyz' FIELDS TERMINATED BY '|' optionally ENCLOSED BY '''' WRITEMODE BY overwrites FIELDS ESCAPED BY ''; 

导出数据:

 库内数据: 


示例6: select * from test into outfile '/home/gbase/xyz' FIELDS TERMINATED BY '|' optionally ENCLOSED BY '''' WRITEMODE BY overwrites FIELDS ESCAPED BY'\''

导出数据:


示例7: select * from test into outfile '/home/gbase/fixlen' FIELDS TERMINATED BY '' ENCLOSED BY '' WRITEMODE BY overwrites; 

导出数据:

 库内数据:


 示例8: select * from test into outfile '/home/gbase/fixlen' WRITEMODE BY overwrites; 

导出数据:


示例9: gbase> select * from test into outfile '/home/gbase/fixlen' fields length '5,50' WRITEMODE BY overwrites; 错误信息: ERROR 1149 (42000): Invalid length field, need length 11 for field 'a'.


示例10: select * from test into outfile '/home/gbase/fixlen' fields length '11,2' WRITEMODE BY overwrites;

 导出数据: 

库内数据:

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论