Kettle – ERROR: invalid byte sequence for encoding “UTF8”: 0x00

错误信息:
ERROR: invalid byte sequence for encoding “UTF8”: 0x00

问题原因:
PostgreSQL doesn’t support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

Source: http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE

If you need to store the NULL character, you must use a bytea field – which should store anything you want, but won’t support text operations on it.

Given that PostgreSQL doesn’t support it in text values, there’s no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it’s likely going to be easier to do that in preprocessing before you load it.

解决方法:
使用transformation目录下的“Replace in string”或者”Value Mapper”组件替换\u0000为其他字符串即可。

注意使用“Replace in string”之后会新出来一个_01字段,mapping里要选择新字段,否则还会报错。

参考:

https://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0

One thought on “Kettle – ERROR: invalid byte sequence for encoding “UTF8”: 0x00

发表评论