PostgreSQL, Greenplum ETL 之 - 非法字符(如0x00)过滤、转换(blob2text, bytea2text)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , Greenplum , 0x00 , 空字符 , NUL , 数据清洗 , 规则 , 过滤 , 非法字符


背景

如果输入的数据不合法,那么PostgreSQL数据库会报错,不允许写入。

postgres=# select E'\000';  
ERROR:  invalid byte sequence for encoding "UTF8": 0x00  
LOCATION:  report_invalid_encoding, wchar.c:2017  

报错代码为wchar.c的report_invalid_encoding函数。

实际上这个问题在很多文章中有分析过:

《PostgreSQL WHY ERROR: invalid byte sequence for encoding "UTF8"》

《PostgreSQL 转义、UNICODE、与SQL注入》

《PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异》

最好的解决办法是客户端在写入前检查输入字符的合法性,过滤不合法的字符。例如:

https://www.bbsmax.com/A/kjdwxDy6zN/

http://www.codeweblog.com/sqlserve%E5%88%B0pg%E8%BF%81%E7%A7%BB%E9%94%99%E8%AF%AF-%E6%97%A0%E6%95%88%E7%9A%84%E7%BC%96%E7%A0%81%E5%BA%8F%E5%88%97-utf8-0x00/

在数据库中,有没有方法实现ETL中数据清洗的部分呢?

对于不合法的字符,我们可以使用二进制写入,然后在二进制中过滤不合法字符并转化为字符串。

PostgreSQL 不合法字符清洗

输入为bytea,过滤非法字符,转换为字符串。

1、创建转换函数,将NUL字符转换为空格。

set escape_string_warning=on;  
set standard_conforming_strings=on;  
  
create or replace function blob2text (bytea) returns text as $$  
declare  
  i int;  
  val bytea := $1;  
  res text;  
  sql text;  
  encoding name ;  
begin  
      set escape_string_warning=on;  
      set standard_conforming_strings=on;  
  
      select getdatabaseencoding() into encoding;  
      i := position(bytea '\000' in val);  
      WHILE i > 0 LOOP  
        val := set_byte(val, i-1, 32);  
        i := position(bytea '\000' in val);  
      END LOOP;  
        
      sql := format('select convert_from(encode(%L,''escape'')::bytea, %L)', val, encoding) ;  
      -- raise notice '%', sql;  
      execute sql into res;  
      return res;  
end;  
$$ language plpgsql strict;  

2、测试转换函数,符合要求

postgres=# select blob2text(bytea '你好\000哈哈\000hello');  
NOTICE:  select convert_from(encode(E'\\xc4e3bac320b9feb9fe','escape')::bytea, 'EUC_CN')  
 blob2text   
-----------  
 你好 哈哈  
(1 row)  

Greenplum 不合法字符清洗

输入为bytea,过滤非法字符,转换为字符串。

1、创建转换函数,将NUL字符转换为空格。

set escape_string_warning=on;  
set standard_conforming_strings=on;  
  
create or replace function blob2text (bytea) returns text as $$  
declare  
  i int;  
  val bytea := $1;  
  res text;  
  sql text;  
  encoding bytea ;  
begin  
      set escape_string_warning=on;  
      set standard_conforming_strings=on;  
      select getdatabaseencoding() into encoding;  
  
      i := position(bytea '\000' in val);  
      WHILE i > 0 LOOP  
        val := set_byte(val, i-1, 32);  
        i := position(bytea '\000' in val);  
      END LOOP;  
      sql := $_$select convert(E'$_$||val||$_$', '$_$||encoding||$_$', '$_$||encoding||$_$')$_$;  
      -- raise notice '%', sql;  
      execute sql into res;  
      return res;  
end;  
$$ language plpgsql strict;  

2、测试转换函数,符合要求

postgres=# select blob2text(bytea '你好\000哈哈\000hello');  
NOTICE:  select convert(E'\344\275\240\345\245\275 \345\223\210\345\223\210 hello', 'UTF8', 'UTF8')  
      blob2text        
---------------------  
 你好 哈哈 hello  
(1 row)  

使用规则,自动清洗不合法字符

《数据入库实时转换 - trigger , rule》

1、创建入口表test,创建目标表dest_test。用户将数据写入test,自动进行清洗,并写入目标表。

create table test (id int, info bytea);  
create table dest_test (id int, info text);  
  
  
-- 在入口表创建清洗规则  
create rule r1 as on insert to test do instead insert into dest_test(id, info) values (NEW.id, blob2text(NEW.info));  

2、测试,转换正常,非法字符转换为合法字符,可以写入了。

insert into test values (1, '你好\000哈哈\000hello');  
  
  
db=# select * from test;  
 id | info   
----+------  
(0 rows)  
  
  
db=# select * from dest_test;  
 id |      info         
----+-----------------  
  1 | 你好 哈哈 hello  
(1 row)  

小结

对于非法字符,建议在应用层进行过滤,在数据库中转换,影响写入速度,同时会使得数据库的开销变大。实在是万不得已的行为。

参考

《数据入库实时转换 - trigger , rule》

《PostgreSQL WHY ERROR: invalid byte sequence for encoding "UTF8"》

《PostgreSQL 转义、UNICODE、与SQL注入》

《PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异》

https://www.bbsmax.com/A/kjdwxDy6zN/

http://www.codeweblog.com/sqlserve%E5%88%B0pg%E8%BF%81%E7%A7%BB%E9%94%99%E8%AF%AF-%E6%97%A0%E6%95%88%E7%9A%84%E7%BC%96%E7%A0%81%E5%BA%8F%E5%88%97-utf8-0x00/

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
4天前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
12天前
|
监控 关系型数据库 Java
SpringBoot【集成 01】Druid+Dynamic+Greenplum(实际上用的是PostgreSQL的驱动)及 dbType not support 问题处理(附hikari相关配置)
SpringBoot【集成 01】Druid+Dynamic+Greenplum(实际上用的是PostgreSQL的驱动)及 dbType not support 问题处理(附hikari相关配置)
132 0
|
SQL 关系型数据库 Serverless
【实操系列】 从0~1,基于DMS面向AnalyticDB PostgreSQL的数据ETL链路开发
本文以RDSPG 到 ADBPG 的数据链路作为案例,介绍了如何从0~1,基于DMS进行ETL数据链路开发
【实操系列】 从0~1,基于DMS面向AnalyticDB PostgreSQL的数据ETL链路开发
|
SQL 存储 关系型数据库
【学习资料】第2期PostgreSQL、Greenplum 技术+108个场景结合最佳实践《如来神掌》
大家好,这里是PostgreSQL、Greenplum 《如来神掌》 - 目录 - 珍藏级
【学习资料】第2期PostgreSQL、Greenplum 技术+108个场景结合最佳实践《如来神掌》
|
SQL Oracle 关系型数据库
【学习资料】第1期Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级
大家好,这里是Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级
|
SQL 算法 关系型数据库
分析型数据库(PostgreSQL版)正式推出 Greenplum 6.0 兼容版
信息摘要: 分析型数据库(PostgreSQL版)正式推出 Greenplum 6.0 兼容版。 免费公测中,邀您体验!适用客户: 互联网/游戏/政务/开发者 / 大型零售连锁商超 / 金融保险行业 / 考试认证的机构/版本/规格功能: 分析型数据库 AnalyticDB for PostgreSQL 6.0 公测发布,兼容 Greenplum 6.0 版。
4588 0
|
SQL 算法 关系型数据库
分析型数据库(PostgreSQL版)正式推出 Greenplum 6.0
信息摘要: 分析型数据库(PostgreSQL版)正式推出 Greenplum 6.0。 免费公测中,邀您体验!适用客户: 互联网/游戏/政务/开发者 / 大型零售连锁商超 / 金融保险行业 / 考试认证的机构/版本/规格功能: 分析型数据库 AnalyticDB for PostgreSQL 6.0 公测发布,基于 Greenplum 6.0内核。
1737 0
|
SQL 安全 大数据
使用 Dataworks 实现 AnalyticDB PostgreSQL版上的 ETL 作业调度
DataWorks作为阿里云上广受欢迎的大数据开发调度服务,最近加入了对于AnalyticDB PostgreSQL版(原HybridDB for PostgreSQL,简称ADB PG版)的支持,意味着所有ADB PG版的客户可以获得任务开发、任务依赖关系管理、任务调度、任务运维.
2855 0
|
关系型数据库 数据库 C语言
Greenplum 6已合并到PostgreSQL 9.3版本 - 比上一代GP提升:8倍读,195倍更新、删除 - 另有大量PG新特性
标签 PostgreSQL , Greenplum , 6 , gin , 异步事务 背景 Greenplum 6已合并到PostgreSQL 9.3版本 - 相比5性能提升:读8倍,更新、删除195倍 - 另有大量PG新特性,详见PostgreSQL release notes https://www.postgresql.org/docs/11/static/release.html 例如 1、GIN、SPGIST 索引接口。
7392 0

相关产品

  • 云原生数据库 PolarDB