AnalyticDB PostgreSQL版支持多种数据导入方法,在通过OSS高速并行导入和通过\COPY命令导入数据的过程中,经常因为存在特殊字符导致导入失败。本文将介绍预先处理导入数据中的特殊字符的方法,从而消除特殊字符带来的问题。

更多导入数据到AnalyticDB PostgreSQL版的方案,请参见数据迁移及同步方案综述

OSS高速并行导入

在数据导入过程中,一般是将文件的每行作为一个元组,通过在每行中规定分隔符来分割每一列的数据。下文将介绍分隔符的使用方法和约束,以及每列中遇到特殊符号的处理方法。

分隔符

在创建OSS外部表语法中,您可以通过在FORMAT子句后面指定DELIMITER分隔符,如下:

FORMAT 'TEXT' (DELIMITER ',')
  • 如果FORMAT 'TEXT',则DELIMITER缺省值为'\t'
  • 如果FORMAT 'CSV',则DELIMITER缺省值为','

您也可以自定义DELIMITER,但是创建外部表语法中自定义的DELIMITER必须满足以下约束:

  • 必须是一个ASCII字符,不允许是汉字或者2个以及以上ASCII字符。
  • 不支持'\n''\r'
  • 支持除'\n''\r'之外的其他转义字符,使用时前面加E或者e。
  • 支持前面不加E的转义字符'\t'
  • 如果是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命令详细用法见使用\COPY命令导入本地数据