Skip to main content
Choolake
Choolake
Gold ⭐️
March 20, 2024
Solved

Table creation in HUE for JSON value

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

smruti
smrutiAnswer
Gold ⭐️⭐️⭐️
March 20, 2024

@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

View in thread

2 replies

smruti
Gold ⭐️⭐️⭐️
smrutiAnswer
Gold ⭐️⭐️⭐️
March 20, 2024

@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

Translate
    VidyaSargur
    Community Manager
    VidyaSargur
    Community Manager
    April 1, 2024

    @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. 

    Translate
    Regards, Vidya Sargur, Community Manager Was your question answered? Make sure to mark the answer as the accepted solution. If you find a reply useful, say thanks by clicking on the thumbs up button.
      Terms & ConditionsAccessibility statement