How to write in THiveInput

sakura99 — Sat, 16 Nov 2024 00:10:22 GMT

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

topic How to write in THiveInput in Talend Studio