Table creation in HUE for JSON value

[#1]

Hi everyone,

I need to create HUE table separating "|" for insert like following values into table with removing all not required ""

"94112605011"|"DEFAULT"|"FTTH_WEB FAMILY PLUS!PDL&Ec5@1706725800;94112605011;1704047400"|"COUNTER"|"u"|"0E-12"|"2023-12-31 18:30:00.000000"|"2024-01-31 18:30:00.000000"|"0"|"{""data"":{""o"":{""-t"":""ECS_BalanceContextInfo"",""m"":[{""-c"":""57a2"",""-v"":""1"",""a"":[{""-n"":""unitType"",""v"":{""-s"":""VOLUME""}},{""-n"":""offerId"",""v"":{""-s"":""15""}},{""-n"":""offerName"",""v"":{""-s"":""FTTH_WEB FAMILY PLUS""}},{""-n"":""offerQuota"",""v"":{""-s"":""42949672960""}},{""-n"":""quotaThreshold"",""v"":{""-s"":""0""}}]}],""o"":[{""-t"":""ECS_BalanceContextInfoBase"",""m"":[{""-c"":""5136"",""-v"":""1""},{""-c"":""8d18"",""-v"":""2"",""a"":[{""-n"":""subscriptionId"",""v"":{""-s"":""9411260501111152164097540010""}}]}]},{""-t"":""ECS""},{""-t"":""AVS"",""m"":[{""-c"":""e3a0"",""-v"":""1""}]}]}}}"|"{""a"":[""0""]}"|"BYTES"|"0E-12"

i HAVE USED FOLLOWING COMMAND

CREATE TABLE slt.OCS_BALANCE(SUBSCRIBERID STRING,PURSEID STRING,BALANCEID STRING,BALANCETYPE STRING,BALANCEFLAGS STRING,CREDITLIMIT STRING,EFFECTIVEDATE STRING,EXPIRYDATE STRING,PRIORITY STRING,CONTEXTINFO STRING,ROLLOVERAMOUNT STRING,UNITTYPE STRING,THRESHOLDALTERATION STRING)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' WITH SERDEPROPERTIES ('serialization.format'=',', 'field.delim'=',') STORED AS TEXTFILE LOCATION '/temp/OCS_BALANCEVALUE';

Please guide me to create correct table

[#2 · Accepted solution]

@Choolake See if this does the job for you.

...
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.JsonSerde'
WITH SERDEPROPERTIES (
    'separatorChar' = '|',
    'quoteChar' = '"'
)
STORED AS TEXTFILE LOCATION ....

This is a third party serde. You may download it from https://code.google.com/archive/p/hive-json-serde/downloads

[#3]

@Choolake, Did the response assist in resolving your query? If it did, kindly mark the relevant reply as the solution, as it will aid others in locating the answer more easily in the future.