question How to create hive table out of JSON Schema with s dynamic nested array in Support Questions

How to create hive table out of JSON Schema with s dynamic nested array

Maicat — Tue, 09 Jul 2024 19:43:56 GMT

Hi all - I am trying to create a Hive table from nested JSON parquet data. The problem is one object is dynamic and I want to store it as a string since it changes.

Example JSON:

{

"level1": {

"level2": {

"key1": "someString",

"level3": {

"level4": {

"key2": 1234,

"level5": [

{

"this": 1,

"changes": false,

"each": 12345,

"item": 0,

{

"something": 12345,

"new": true,

"here": [

123

]

}

]

}

Here is what I have tried:

CREATE EXTERNAL TABLE IF NOT EXISTS my_table (

level1 STRUCT<,

level2 STRUCT<,

key1 STRING,

level3 STRUCT<,

level4 STRUCT<,

key2 BIGINT,

level5 ARRAY<STRING>>>>>

)

STORED AS PARQUET

LOCATION '/my/json/parquet/'

TBLPROPERTIES ("parquet.compression"="SNAPPY");

This will successfully create the table. I can query down all levels except "level5" falls apart. Is there a way I can cast the array in level5 into a string since it always changes??

FYI I have tried

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe', but this does not mix with parquet format. Please help!

Re: How to create hive table out of JSON Schema with s dynamic nested array

DianaTorres — Tue, 09 Jul 2024 21:26:46 GMT

@Maicat Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive experts @caio_contente @cravani who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Re: How to create hive table out of JSON Schema with s dynamic nested array

asish — Tue, 30 Jul 2024 15:19:20 GMT

@Maicat You can not typecast array to the string. There are 2 ways you can use

1. Select the nth object of the array.

SELECT level5[0] AS first_genre FROM my_table;

WHere 0 is the first object

2. You can flatten it

SELECT column1 FROM my_table LATERAL VIEW explode(level5) genre_table AS level5;