使用pgloader加载含AES密钥（Blob）的UTF-8 CSV至PostgreSQL时遇转换异常

我尝试用pgloader将UTF-8编码、包含Blob数据（实际为AES密钥）的CSV文件加载到PostgreSQL数据库，编写的加载脚本如下：

LOAD CSV
     FROM 'report_info.csv'
     INTO postgresql://username:password@x.x.x.x:5432/db_name
          TARGET TABLE report_info
          ( 
            id,
            status,
            text1 bytea using ( byte-vector-to-bytea :text1),
            text2 bytea using ( byte-vector-to-bytea :text2),
            aes_key bytea using ( byte-vector-to-bytea :aes_key),
            name
          )
               
     WITH skip header = 1,
          fields optionally enclosed by '"',
          fields escaped by double-quote,
          fields terminated by ','

     SET client_encoding to 'utf-8',
          work_mem to '12MB',
          standard_conforming_strings to 'on'; 

执行脚本后出现异常，错误信息如下：

Error:
in: LAMBDA (PGLOADER.SOURCES::ROW)
(PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA :TEXT1)
note: deleting unreachable code
(PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA :AES_KEY)

解决方法 #

错误原因 #

byte-vector-to-bytea函数要求参数是字节向量类型，但从CSV中读取的所有数据都是字符串类型（哪怕是Blob数据，在CSV里也会以Base64或十六进制字符串的形式存储），类型不匹配直接导致了报错。

修正方案1：用pgloader内置的Base64解码函数 #

如果你的Blob数据是Base64编码的，直接替换成base64-decode函数即可：

LOAD CSV
     FROM 'report_info.csv'
     INTO postgresql://username:password@x.x.x.x:5432/db_name
          TARGET TABLE report_info
          ( 
            id,
            status,
            text1 bytea using (base64-decode :text1),
            text2 bytea using (base64-decode :text2),
            aes_key bytea using (base64-decode :aes_key),
            name
          )
               
     WITH skip header = 1,
          fields optionally enclosed by '"',
          fields escaped by double-quote,
          fields terminated by ','

     SET client_encoding to 'utf-8',
          work_mem to '12MB',
          standard_conforming_strings to 'on'; 

修正方案2：调用PostgreSQL的decode函数（更灵活） #

如果数据是十六进制编码，或者需要更灵活的转换规则，可以让PostgreSQL端处理转换，使用pg-expr调用数据库的decode函数：

LOAD CSV
     FROM 'report_info.csv'
     INTO postgresql://username:password@x.x.x.x:5432/db_name
          TARGET TABLE report_info
          ( 
            id,
            status,
            text1 bytea using (pgloader.transforms:pg-expr "decode(?::text, 'base64')" :text1),
            text2 bytea using (pgloader.transforms:pg-expr "decode(?::text, 'base64')" :text2),
            aes_key bytea using (pgloader.transforms:pg-expr "decode(?::text, 'base64')" :aes_key),
            name
          )
               
     WITH skip header = 1,
          fields optionally enclosed by '"',
          fields escaped by double-quote,
          fields terminated by ','

     SET client_encoding to 'utf-8',
          work_mem to '12MB',
          standard_conforming_strings to 'on'; 

• 若Blob数据是十六进制字符串，把'base64'替换为'hex'即可。

额外注意 #

如果CSV中的Blob字段被引号包裹，需要先去掉引号再解码，比如：

text1 bytea using (base64-decode (trim :text1 :both '"'))

内容的提问来源于stack exchange，提问作者javeed hussain