云数据库 HybridDB for PostgreSQL 目前支持多种数据导入的方法:
在数据导入的过程中,经常因为一些特殊字符导致导入失败,本文将介绍用户应该如何去预先处理将要导入数据中的特殊字符,保证导入成功。
其中上述四种导入方法中,从 MySQL 导入和从 PostgreSQL 导入使用的工具,会自动把特殊符号进行转义和包装,用户可以直接使用而不需要过多的设置。这里重点介绍在使用 OSS 高速并行导入和通过 COPY 数据导入过程中遇到特殊符号的处理方法。
OSS 高速并行导入
在数据导入过程中,一般是将文件的每行作为一个元组,通过在每行中规定分隔符来分割每一列的数据,本文首先介绍下分隔符的使用方法和约束,然后介绍在每列中遇到特殊符号的处理方法。
分隔符
在创建 OSS 外部表语法中,用户可以通过在 FORMAT 子句后面指定 DELIMITER 分隔符,如下:
FORMAT 'TEXT' (DELIMITER ',')
如果 FORMAT ‘TEXT’,则 DELIMITER 缺省值为’\t’,如果 FORMAT ‘CSV’,则 DELIMITER 缺省值为’,’。用户也可以自定义 DELIMITER,但是创建外部表语法中用户自定义 DELIMITER 必须满足以下约束:
- 必须是一个ASCII字符,不允许是汉字或者2个以及以上个ASCII字符
- 不允许是”\n”、”\r”
- 支持前面不加E的转义字符”\t”
- 支持除”\n”和”\r”之外的其他转义字符,使用时前面加E或者e
- 如果是TEXT模式,可以设置DELIMITER为OFF,支持单列外部表
为了能够正常的读取数据,用户提供的 OSS 文件的内容必须严格遵守设置的 DELIMITER。
数据中的特殊符号
在数据导入过程中,出现特殊符号的场景可以分为以下几种:
列中存在和 DELIMITER 相同的字符
如果用户使用的是TEXT模式,用户需要在每个 DELIMITER 字符前加 ESCAPE符。ESCAPE 符可以在创建外表时指定如下,缺省值为反斜杠 \。FORMAT 'TEXT' (ESCAPE '\' )
如果用户使用的是 CSV 模式,用户需要在每个 DELIMITER 字符前加双引号 “。
列中存在中文
OSS 外表支持中文数据,但是为了保证显示正确,用户需要在创建外表时设置编码格式如下:
ENCODING 'UTF8'
列中存在 null
用户可以通过设置 null 对应的匹配字符,在导入数据时将对应的字符匹配为 null。CSV 模式下缺省值为不带引号的空值,TEXT 模式下缺省值为 \N。如下,将空格作为 null 的匹配字符,如果该列为空格,则通过 OSS 文件导入的数据该列值为 null。
FORMAT 'text' (null ' ' )
列中存在转义字符
当用户遇见转义字符时,可以在转义字符前增加 ESCAPE 符。ESCAPE 符在创建外表时指定,CSV 模式缺省值为双引号 “,TEXT 模式缺省值为反斜杠 \。用户可以自定义 ESCAPE 为单个字符,如下设置 ESCAPE 为反斜杠:
FORMAT 'csv' (ESCAPE '\' )
当然也可设置 ESCAPE 为 OFF,可以避免所有字符被自动转义。
列中存在单引号或者双引号
如果用户使用 TEXT 模式,需要在单引号或者双引号前面增加 ESCAPE 符,默认为反斜杠 \。
如果用户使用 CSV 模式,使用在单引号或者双引号前面增加 ESCAPE 符,默认为双引号 “,同时在该列前后加双引号 “ 括起来。
COPY 数据导入
用户使用 \COPY 语句导入数据时,分隔符的使用方法和 OSS 高速并行导入时的使用方法一样,而对数据中出现特殊符号的处理方法也和 OSS 高速并行导入相类似,不同的是 COPY 语句和 CREATE EXTERNAL TABLE 语句用法略有不同,COPY 语句详细用法见
文档。