gbk转utf8方案

[#1]

请问，在cdp7.17 环境，执行gbk转utf8报错，如下图所示,要如何解决？

[#2]

English Translation:

GBK to UTF-8 conversion solution

In a CDP 7.17 environment, I encountered an error when converting GBK to UTF-8, as shown in the image below. How can I resolve this?

@zhouweibo, Welcome to our community! To help you get the best possible answer, I have tagged in our CDP @zzeng @upadhyayk04  @rki_  experts who may be able to assist you further.

[#3]

@zhouweibo  Hi Weibo,

I build an environment by myself, but I can't reproduce your error.

I created a table in this way:

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS upidb;

-- Create table with correct syntax
CREATE TABLE IF NOT EXISTS upidb.gscs_tbl_fultrans_2_db (
    trace_num STRING COMMENT 'Trace number',
    acq_ins_cde STRING COMMENT 'Acquiring institution code',
    fwd_ins_cde STRING COMMENT 'Forwarding institution code',
    acq_trans_cde STRING COMMENT 'Acquiring transaction code',
    iss_trans_cde STRING COMMENT 'Issuing transaction code',
    pri_acct_num STRING COMMENT 'Primary account number',
    resv_fld2 STRING COMMENT 'Reserved field 2 - contains encoded data',
    sett_dt STRING COMMENT 'Settlement date in YYYYMMDD format'
)
COMMENT 'Full transaction table for settlement date partition'
STORED AS PARQUET;

-- Insert sample data with GBK encoded characters to reproduce the encoding issue
INSERT INTO upidb.gscs_tbl_fultrans_2_db VALUES
(
    '123456789012',
    'ACQ001',
    'FWD001',
    'TRANS001',
    'TRANS002',
    '1234567890123456',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789测试中文字符ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
    '20231201'
);

Then ran the SELECT sql, but Can't reproduce error:

SELECT trace_num
      ,acq_ins_cde
      ,fwd_ins_cde
      ,acq_trans_cde
      ,iss_trans_cde
      ,pri_acct_num
      ,trim(SUBSTR(resv_fld2,111,2))
      ,trim(decode(SUBSTR(encode(resv_fld2,'gbk'),113,15),'gbk'))
      ,trim(decode(SUBSTR(encode(resv_fld2,'gbk'),128,40),'gbk'))
      ,trim(decode(SUBSTR(encode(resv_fld2,'gbk'),168,40),'gbk'))
from upidb.gscs_tbl_fultrans_2_db
where sett_dt = '20231201'

Can you please share your DDL?

[#4]

The error stack trace indicates an issue with the hive encode UDF statement.

Please provide the DDL, sample data, and corresponding SQL to reproduce the problem.

[#5]

@zhouweibo 

Did the replies help you resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.