hi all, i want to translate pl/sql into hiveql using thiveinput & thive row.
the query looks like this
WITH SS_AAAA AS
(
SELECT ABCD
,SUBSTR(COLUMNSSS, INSTR((VALUE),'^',1,10) +1, INSTR((VALUE),'^',1,11)-1 - INSTR((VALUE),'^',1,10)) CZZ
,SUBSTR(COLUMNSSS, INSTR((VALUE),'^',1,11) +1, INSTR((VALUE),'^',1,12)-1 - INSTR((VALUE),'^',1,11)) CXX
,SUBSTR(COLUMNSSS, INSTR((VALUE),'^',1,12) +1, INSTR((VALUE),'^',1,13)-1 - INSTR((VALUE),'^',1,12)) CYY
,SEQUENCE
,DATE
FROM BIGTABLEONE
),
DELIMITED AS (SELECT LEVEL SEQ FROM DUAL CONNECT BY LEVEL <= 10)
SELECT ID
,B.SEQ SEQA
,SEQUENCE SEQB
,TRIM(REGEXP_SUBSTR(CZZ, '[^|]+', 1, B.SEQ)) CZZ
,TRIM(REGEXP_SUBSTR(CXX, '[^|]+', 1, B.SEQ)) CXX
,TRIM(REGEXP_SUBSTR(CYY, '[^|]+', 1, B.SEQ)) CYY
,DATE
FROM ABCD INNER JOIN DELIMITED B ON 1=1
WHERE REGEXP_SUBSTR(CZZ, '[^|]+', 1, B.SEQ) IS NOT NULL;
how to split this string in thiveinput?
,SUBSTR(COLUMNSSS, INSTR((VALUE),'^',1,10) +1, INSTR((VALUE),'^',1,11)-1 - INSTR((VALUE),'^',1,10)) CZZ
I already trying:
regexp_extract(COLUMNSSS,'^(?:[^^]*\\^){10}([^^]*)',1) AS CZZ
and
split(COLUMNSSS,"\\^")[10] AS CZZ
