Oracle数据到MaxCompute乱码问题详解

简介: 集成Oracle数据到MaxCompute,乱码问题分析;为什么,在oracle数据不乱码,集成到MaxCompute就乱码了? 问题在哪里?

1.1     乱码现象

DataWorks的数据离线集成(DataX)集成Oracle数据到MaxCompute的数据有乱码,但是看源库不是乱码,这是什么原因?

现象:【Oracle;工具:plsql-dev

image.png

MaxCompute;工具:DataWorks

select OP_USER from test.mdtsb where

uuid='161A45E75BC88040E053441074848040';

image.png

1.2     问题分析

使用两个 oracle 函数:

DUMP; CONVERT;

http://docs.oracle.com/database/122/SQLRF/DUMP.htm#SQLRF00635

http://docs.oracle.com/database/122/SQLRF/CONVERT.htm#SQLRF00620

为了避免转码正确是因为刚好乱码的列的真实字符集与操作系统字符集相同而导致显示正常,选择了使用 linux 环境,客户端字符集设置为 UTF8,使用 SQLplus 进行查询分析问题。

1.客户端环境:

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.AL32UTF8

 

2.使用 dump 函数把文本对应的编码输出出来:

SQL> selectdump(OP_USER,1016) from test.mdtsb where

uuid='161A45E75BC88040E053441074848040' ;

DUMP(OP_USER,1016)

----------------------------------------------------

Typ=1Len=6CharacterSet=AL32UTF8: c0,ee,be,b0,ea,bb


这个函数输出的信息有三个

  1. 长度是 6 字节
  2. 字符集是 AL32UTF8
  3. 字符编码是 c0,ee,be,b0,ea,bb

通过这些信息,我们猜测原来的字符集可能是 GBK 或者 GB18030GB2312 这几个字符集的编码。因为三个汉字,如果是 UTF8 的话他们的编码大部分都是一个汉字对应三字节。而 GBK字符集对应的编码是双字节。

3.查看c0,ee,be,b0,ea,bb 对应的 GBK 字符

http://www.qqxiuzi.cn/zh/hanzi-gbk-bianma.php

c0,ee

image.png

be,b0

image.png

ea,bb

image.png

所以,可以认定这个字符编码属于 GBK 类。

4.对应汉子的 GBK 类编码

http://www.qqxiuzi.cn/bianma/zifuji.php

GB2312 编码:C0EE

BIG5 编码:A7F5

GBK 编码:C0EE

GB18030 编码:C0EE

Unicode编码:674E

GB2312 编码:BEB0

BIG5 编码:B4BA

GBK 编码:BEB0

GB18030 编码:BEB0

Unicode编码:666F

GB2312 编码:EABB

BIG5 编码:A9FE

GBK 编码:EABB

GB18030 编码:EABB

Unicode编码:660A

可以看到,这三个汉字可能的编码是:GB2312GB18030GBK

5.使用 convert 函数转换字符串编码为 AL32UTF8

SQL> selectconvert(OP_USER,'AL32UTF8','ZHS16GBK') from test.mdtsb where

uuid='161A45E75BC88040E053441074848040' ;

CONVERT(OP_USER,' AL32UTF8','ZHS16GBK')

-------------------------------------------------------

李景昊

这个结果验证了我们的猜测,这个字符集应该是 GBK 类的字符集,但是因为 GBK GB18030GB2312 并不是完全父子集关系,所以,这个只能是其中的一种。只不过 GBK 字符集从当前角度来看,更为常用。

SQL> selectconvert(OP_USER,'AL32UTF8','ZHS16GBK') from test.mdtsb where

uuid='161A45E75BC88040E053441074848040' ;

CONVERT(OP_USER,' AL32UTF8','ZHS16CGB231280')

-------------------------------------------------------

李景昊

SQL> selectconvert(OP_USER,'AL32UTF8','ZHS16GBK') from test.mdtsb where

uuid='161A45E75BC88040E053441074848040' ;

CONVERT(OP_USER,' AL32UTF8','ZHS32GB18030')

-------------------------------------------------------

李景昊

6.查询这三个汉字对应的UTF8 的字符串编码

网址:http://www.qqxiuzi.cn/bianma/Unicode-UTF.php

Unicode编码:0000674E

UTF8 编码:E69D8E

UTF16 编码:FEFF674E

UTF32 编码:0000FEFF0000674E

Unicode编码:0000666F

UTF8 编码:E699AF

UTF16 编码:FEFF666F

UTF32 编码:0000FEFF0000666F

Unicode编码:0000660A

UTF8 编码:E6988A

UTF16 编码:FEFF660A

UTF32 编码:0000FEFF0000660A

所以,如果原来字符串编码是 UTF8,理论上我们通过 dump 函数获取的字符串编码应该为:e6,9d,8e,e6,99,af,e6,98,8a

1.3     问题解决

通过 convert 函数转换:

SQL> selectdump(convert(OP_USER,'AL32UTF8','ZHS16GBK'),1016) from test.mdtsb

where uuid='161A45E75BC88040E053441074848040' ;

DUMP(CONVERT(OP_USER,' AL32UTF8','ZHS16GBK'),1016)

-------------------------------------------------------

Typ=1Len=9CharacterSet=AL32UTF8: e6,9d,8e,e6,99,af,e6,98,8a

这个结果验证了我们之前的查询结果,UTF8 字符集显示的编码应该是:

e6,9d,8e,e6,99,af,e6,98,8a

结论:

Oracle 数据库存储的该列的实际编码是GBK 类的字符集,因为与客户端环境的字符编码相同,所以,刚好能显示出来。但是因为 Oracle 存储的字符集设置为 UTF8,实际上存储的字符集应该是 UTF8。所以导致标注的编码和实际的编码不一致-乱码。

应对:

在数据同步任务的对应的列上通过测试字符集获取到的真实字符集,使用 convert 函数(convert(OP_USER,'AL32UTF8','ZHS16GBK'))进行转换,转换到 ODPS 后的数据就不会再是乱码。

例如:SELECTconvert(KEY1,'ZHS16GBK','UTF8') FROM MATDOC;

MaxCompute;工具:DataWorks

select OP_USER from MaxCompute.MDTSB where uuid='161A45E75BC88040E053441074848040';

 image.png

1.4     目标端识别方法

其实在MaxCompute也有相关的函数可以对字符编码进行分析和转换,只是我们在MaxCompute只能存储一种字符集“UTF-8”,所以,最好是在源端或者在传输过程中转换编码为UTF-8。下面两个函数是MaxCompute的字符编码相关函数。

·       ENCODE

命令格式

binary encode(string <str>, string )

命令说明

str按照charset格式进行编码。

参数说明

str:必填。STRING类型。待重新编码的字符串。

charset:必填。STRING类型。编码格式。取值范围为:UTF-8UTF-16UTF-16LEUTF-16BEISO-8859-1US-ASCII

返回值说明

返回BINARY类型。任一输入参数为NULL时,返回结果为NULL

示例

示例1:将字符串abc按照UTF-8格式编码。命令示例如下。

select encode("abc", "UTF-8");

--返回abc

 

·       IS_ENCODING

命令格式

boolean is_encoding(string <str>, string , string )

命令说明

判断输入的字符串str是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。也可以用于判断输入是否为乱码,通常您可以将from_encoding设为UTF-8to_encoding设为GBK

参数说明

str:必填。STRING类型。空字符串可以被认为属于任何字符集。

from_encodingto_encoding:必填。STRING类型,源及目标字符集。

返回值说明

返回BOOLEAN类型。如果str能够成功转换,则返回True,否则返回False。如果任一输入参数为NULL,则返回NULL

示例

示例1:判断字符测试或測試是否可以从utf-8字符集转为gbk字符集。命令示例如下。

select is_encoding('测试', 'utf-8', 'gbk');

--返回true

select is_encoding('測試', 'utf-8', 'gbk');

--返回true

https://help.aliyun.com/document_detail/48973.htm?spm=a2c4g.11186623.0.0.626e753e9Ypwbf#section-qdj-kyz-vdb

1.5     附录

Convert函数

image.png

Purpose

CONVERT converts a character string from one character set to another. The datatype of the returned value is VARCHAR2.

  • The char argument      is the value to be converted. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
  • The dest_char_set argument is the name of the character set to which char is converted.
  • The source_char_set argument is the name of the character set in which char is stored in the database. The default      value is the database character set.

Both the destination and source character set arguments can be either literals or columns containing the name of the character set.

For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

Examples

The following example illustrates character set conversion by converting a Latin-1 string to ASCII. The result is the same as importing the same string from a WE8ISO8859P1 database to a US7ASCII database.

SELECTCONVERT('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')

  FROM DUAL;

CONVERT('ÄÊÍÓØABCDE'

---------------------

A E I ? ? A B C D E ?

'US7ASCII'是当前oracle数据库的字符集,'WE8ISO8859P1'是被转换后的字符集】

Common character sets include:

· US7ASCII: US 7-bit ASCII character set

· WE8DEC: West European 8-bit character set

· F7DEC: DEC French 7-bit character set

· WE8EBCDIC500: IBM West European EBCDIC Code Page 500

· WE8ISO8859P1: ISO 8859-1 West European 8-bit character set

· UTF8: Unicode 4.0 UTF-8 Universal character set, CESU-8 compliant

· AL32UTF8: Unicode 4.0 UTF-8 Universal character set

 

参考文档:

http://www.3lian.com/edu/2013/07-16/81217.html

http://docs.oracle.com/database/122/SQLRF/DUMP.htm#SQLRF00635

http://docs.oracle.com/database/122/SQLRF/CONVERT.htm#SQLRF00620


相关实践学习
简单用户画像分析
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景,如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
24天前
|
分布式计算 DataWorks IDE
MaxCompute数据问题之忽略脏数据如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
37 0
|
24天前
|
SQL 存储 分布式计算
MaxCompute问题之下载数据如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
31 0
|
24天前
|
分布式计算 关系型数据库 MySQL
MaxCompute问题之数据归属分区如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
27 0
|
24天前
|
分布式计算 Cloud Native MaxCompute
MaxCompute数据问题之没有访问权限如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
25 0
|
4天前
|
SQL Oracle 关系型数据库
Oracle insert数据时字符串中有‘单引号问题
Oracle insert数据时字符串中有‘单引号问题
|
4天前
|
分布式计算 DataWorks 关系型数据库
DataWorks报错问题之dataworks同步rds数据到maxcompute时报错如何解决
DataWorks是阿里云提供的一站式大数据开发与管理平台,支持数据集成、数据开发、数据治理等功能;在本汇总中,我们梳理了DataWorks产品在使用过程中经常遇到的问题及解答,以助用户在数据处理和分析工作中提高效率,降低难度。
|
7天前
|
存储 监控 大数据
数据仓库(11)什么是大数据治理,数据治理的范围是哪些
什么是数据治理,数据治理包含哪些方面?大数据时代的到来,给了我们很多的机遇,也有很多的挑战。最基础的调整也是大数据的计算和管理,数据治理是一个特别重要的大数据基础,他保证着数据能否被最好的应用,保证着数据的安全,治理等。那么数据治理到底能治什么,怎么治?
30 0
|
24天前
|
JSON 分布式计算 MaxCompute
MaxCompute问题之创建数据集失败如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
22 0
|
24天前
|
存储 分布式计算 DataWorks
MaxCompute数据问题之数据不一致如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
25 0
|
24天前
|
存储 分布式计算 DataWorks
MaxCompute数据之数据不一致如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
19 0

相关产品

  • 云原生大数据计算服务 MaxCompute
  • 推荐镜像

    更多