此處的中文亂碼和mysql的庫(kù)表 編碼 latin utf 無(wú)關(guān)。

直接上案例。

有時(shí)候我們需要自定義一列，有時(shí)是漢字有時(shí)是字母，結(jié)果遇到這種情況了。

說(shuō)實(shí)話看到這真是糟心。這誰(shuí)受得了。

單獨(dú)select 沒(méi)有任何問(wèn)題。

這是怎么回事呢？ 經(jīng)過(guò)一番檢查，發(fā)現(xiàn)有個(gè)地方類(lèi)似與 "境內(nèi)"? as col但是沒(méi)亂碼，

此時(shí)懷疑就是if 函數(shù)起了作用，但是一時(shí)間不知道是為啥。。

經(jīng)過(guò)多方面測(cè)試 concat("境內(nèi)") concat_ws("","境內(nèi)")沒(méi)用，

concat_ws("",arrary("境內(nèi)")) 有用，此時(shí)也不知道如何下手，只有掏出大殺器 explain.

起作用的

Plan optimized by CBO.
""
Vertex dependency in root stage
Map 1 <- Map 3 (BROADCAST_EDGE)
Reducer 2 <- Map 1 (SIMPLE_EDGE)
""
Stage-0
? Fetch Operator
??? limit:-1
??? Stage-1
????? Reducer 2
????? File Output Operator [FS_14]
??????? Select Operator [SEL_13] (rows=105 width=273)
"????????? Output:[""_col0"",""_col1"",""_col2"",""_col3"",""_col4"",""_col5"",""_col6"",""_col7""]"
????????? Group By Operator [GBY_12] (rows=105 width=273)
"??????????? Output:[""_col0"",""_col1"",""_col2"",""_col3"",""_col4""],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4"
????????? <-Map 1 [SIMPLE_EDGE] vectorized
??????????? SHUFFLE [RS_28]
"????????????? PartitionCols:_col0, _col1, _col2, _col3, _col4"
????????????? Group By Operator [GBY_27] (rows=211 width=273)
"??????????????? Output:[""_col0"",""_col1"",""_col2"",""_col3"",""_col4""],keys:_col1, _col2, _col3, _col4, _col5"
??????????????? Map Join Operator [MAPJOIN_26] (rows=211 width=273)
"????????????????? Conds:SEL_25._col0=RS_23._col0(Inner),Output:[""_col1"",""_col2"",""_col3"",""_col4"",""_col5""]"
??????????????? <-Map 3 [BROADCAST_EDGE] vectorized
????????????????? BROADCAST [RS_23]
??????????????????? PartitionCols:_col0
??????????????????? Select Operator [SEL_22] (rows=1 width=736)
"????????????????????? Output:[""_col0"",""_col1"",""_col2"",""_col3""]"
????????????????????? Filter Operator [FIL_21] (rows=1 width=736)
??????????????????????? predicate:bank_code is not null
??????????????????????? TableScan [TS_3] (rows=1 width=736)
"????????????????????????? dwapsdata@dw_conf_ce_bank_dict_v,t1,Tbl:COMPLETE,Col:NONE,Output:[""bank_code"",""bank_name"",""bank_short_name"",""bank_onshore_flag""]"
??????????????? <-Select Operator [SEL_25] (rows=192 width=273)
"??????????????????? Output:[""_col0"",""_col1""]"
??????????????????? Filter Operator [FIL_24] (rows=192 width=273)
????????????????????? predicate:bank_code is not null
????????????????????? TableScan [TS_0] (rows=192 width=273)
"??????????????????????? dwdmdata@dm_ce_f_portrait_credit_line,t,Tbl:COMPLETE,Col:COMPLETE,Output:[""bank_code""]"
""

沒(méi)有作用的

Plan optimized by CBO.
""
Vertex dependency in root stage
Map 1 <- Map 3 (BROADCAST_EDGE)
Reducer 2 <- Map 1 (SIMPLE_EDGE)
""
Stage-0
? Fetch Operator
??? limit:-1
??? Stage-1
????? Reducer 2 vectorized
????? File Output Operator [FS_31]
??????? Select Operator [SEL_30] (rows=105 width=273)
"????????? Output:[""_col0"",""_col1"",""_col2"",""_col3"",""_col4"",""_col5"",""_col6""]"
????????? Group By Operator [GBY_29] (rows=105 width=273)
"??????????? Output:[""_col0"",""_col1"",""_col2"",""_col3"",""_col4""],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4"
????????? <-Map 1 [SIMPLE_EDGE] vectorized
??????????? SHUFFLE [RS_28]
"????????????? PartitionCols:_col0, _col1, _col2, _col3, _col4"
????????????? Group By Operator [GBY_27] (rows=211 width=273)
"??????????????? Output:[""_col0"",""_col1"",""_col2"",""_col3"",""_col4""],keys:_col1, _col2, _col3, _col4, _col5"
??????????????? Map Join Operator [MAPJOIN_26] (rows=211 width=273)
"????????????????? Conds:SEL_25._col0=RS_23._col0(Inner),Output:[""_col1"",""_col2"",""_col3"",""_col4"",""_col5""]"
??????????????? <-Map 3 [BROADCAST_EDGE] vectorized
????????????????? BROADCAST [RS_23]
??????????????????? PartitionCols:_col0
??????????????????? Select Operator [SEL_22] (rows=1 width=736)
"????????????????????? Output:[""_col0"",""_col1"",""_col2"",""_col3""]"
????????????????????? Filter Operator [FIL_21] (rows=1 width=736)
??????????????????????? predicate:bank_code is not null
??????????????????????? TableScan [TS_3] (rows=1 width=736)
"????????????????????????? dwapsdata@dw_conf_ce_bank_dict_v,t1,Tbl:COMPLETE,Col:NONE,Output:[""bank_code"",""bank_name"",""bank_short_name"",""bank_onshore_flag""]"
??????????????? <-Select Operator [SEL_25] (rows=192 width=273)
"??????????????????? Output:[""_col0"",""_col1""]"
??????????????????? Filter Operator [FIL_24] (rows=192 width=273)
????????????????????? predicate:bank_code is not null
????????????????????? TableScan [TS_0] (rows=192 width=273)
"??????????????????????? dwdmdata@dm_ce_f_portrait_credit_line,t,Tbl:COMPLETE,Col:COMPLETE,Output:[""bank_code""]"
""

對(duì)比發(fā)現(xiàn)

vectorzied 這個(gè)單詞一出來(lái)我就知道怎么回事了。

hive decimal bug， nvl(decimal,1)=0_cclovezbf的博客-CSDN博客

這個(gè)b參數(shù)好處沒(méi)體會(huì)到一點(diǎn)，bug到是一堆。

set hive.vectorized.execution.enabled=false; 即可解決中文亂碼問(wèn)題！！！！！！！

其實(shí)還有別的辦法，但是和concat_ws(array(""))一樣比較丑陋，我就不說(shuō)了