How to parse Yarn Metrics via REST ( api v49 )json output to Hive JsonSerde Table

[#1]

Trying to load and parse YARN application metrics and store it for performance analysis by using REST api call. Data returned is a single json entry with multiple application entries nested in a complex format. 

Can someone suggest or provide correct DDL how to map all data points to a hive external table since JsonSerde can parse and tabulate it?

Sample json data format: 

{"apps":{"app":[{"id":"application_282828282828_12717","user":"xyz","name":"xyz-4b6bdae2-1a0c-4772-bd8e-0d7454268b82","queue":"root.users.dummy","state":"finished","finalstatus":"succeeded","progress":100.0,"trackingui":"history","trackingurl":"http://dang:8088/proxy/application_282828282828_12717/","diagnostics":"session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0\n","clusterid":282828282828,"applicationtype":"aquaman","applicationtags":"ABC,xyz_20221107070124_2beb5d90-24c7-4b1b-b977-3c9af1397195,userid=dummy","priority":0,"startedtime":1667822485626,"launchtime":1667822485767,"finishedtime":1667822553365,"elapsedtime":67739,"amcontainerlogs":"http://dingdong:8042/node/containerlogs/container_e65_282828282828_12717_01_000001/xyz","amhosthttpaddress":"dingdong:8042","amrpcaddress":"dingdong:46457","masternodeid":"dingdong:8041","allocatedmb":-1,"allocatedvcores":-1,"reservedmb":-1,"reservedvcores":-1,"runningcontainers":-1,"memoryseconds":1264304,"vcoreseconds":79,"queueusagepercentage":0.0,"clusterusagepercentage":0.0,"resourcesecondsmap":{"entry":{"key":"memory-mb","value":"1264304"},"entry":{"key":"vcores","value":"79"}},"preemptedresourcemb":0,"preemptedresourcevcores":0,"numnonamcontainerpreempted":0,"numamcontainerpreempted":0,"preemptedmemoryseconds":0,"preemptedvcoreseconds":0,"preemptedresourcesecondsmap":{},"logaggregationstatus":"succeeded","unmanagedapplication":false,"amnodelabelexpression":"","timeouts":{"timeout":[{"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1}]}},{"id":"application_282828282828_12724","user":"xyz","name":"xyz-94962a3e-d230-4fd0-b68b-01b59dd3299d","queue":"root.users.dummy","state":"finished","finalstatus":"succeeded","progress":100.0,"trackingui":"history","trackingurl":"http://dang:8088/proxy/application_282828282828_12724/","diagnostics":"session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0\n","clusterid":282828282828,"applicationtype":"aquaman","applicationtags":"ZZZ_,xyz_20221107070301_e6f788db-e39c-49b6-97d5-6a02ff994c00,userid=dummy","priority":0,"startedtime":1667822585231,"launchtime":1667822585437,"finishedtime":1667822631435,"elapsedtime":46204,"amcontainerlogs":"http://ding:8042/node/containerlogs/container_e65_282828282828_12724_01_000002/xyz","amhosthttpaddress":"ding:8042","amrpcaddress":"ding:46648","masternodeid":"ding:8041","allocatedmb":-1,"allocatedvcores":-1,"reservedmb":-1,"reservedvcores":-1,"runningcontainers":-1,"memoryseconds":5603339,"vcoreseconds":430,"queueusagepercentage":0.0,"clusterusagepercentage":0.0,"resourcesecondsmap":{"entry":{"key":"memory-mb","value":"5603339"},"entry":{"key":"vcores","value":"430"}},"preemptedresourcemb":0,"preemptedresourcevcores":0,"numnonamcontainerpreempted":0,"numamcontainerpreempted":0,"preemptedmemoryseconds":0,"preemptedvcoreseconds":0,"preemptedresourcesecondsmap":{},"logaggregationstatus":"time_out","unmanagedapplication":false,"amnodelabelexpression":"","timeouts":{"timeout":[{"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1}]}},{"id":"application_282828282828_12736","user":"xyz","name":"xyz-1a9c73ef-2992-40a5-aaad-9f0688bb04f4","queue":"root.users.dummy","state":"finished","finalstatus":"succeeded","progress":100.0,"trackingui":"history","trackingurl":"http://dang:8088/proxy/application_282828282828_12736/","diagnostics":"session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0\n","clusterid":282828282828,"applicationtype":"aquaman","applicationtags":"BLAHBLAH,xyz_20221107070609_8d261352-3efa-46c5-a5a0-8a3cd745d180,userid=dummy","priority":0,"startedtime":1667822771170,"launchtime":1667822773663,"finishedtime":1667822820351,"elapsedtime":49181,"amcontainerlogs":"http://dong:8042/node/containerlogs/container_e65_282828282828_12736_01_000001/xyz","amhosthttpaddress":"dong:8042","amrpcaddress":"dong:34266","masternodeid":"dong:8041","allocatedmb":-1,"allocatedvcores":-1,"reservedmb":-1,"reservedvcores":-1,"runningcontainers":-1,"memoryseconds":1300011,"vcoreseconds":89,"queueusagepercentage":0.0,"clusterusagepercentage":0.0,"resourcesecondsmap":{"entry":{"key":"memory-mb","value":"1300011"},"entry":{"key":"vcores","value":"89"}},"preemptedresourcemb":0,"preemptedresourcevcores":0,"numnonamcontainerpreempted":0,"numamcontainerpreempted":0,"preemptedmemoryseconds":0,"preemptedvcoreseconds":0,"preemptedresourcesecondsmap":{},"logaggregationstatus":"succeeded","unmanagedapplication":false,"amnodelabelexpression":"","timeouts":{"timeout":[{"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1}]}},{"id":"application_282828282828_12735","user":"xyz","name":"xyz-d5f56a0a-9c6b-4651-8f88-6eaff5953777","queue":"root.users.dummy","state":"finished","finalstatus":"succeeded","progress":100.0,"trackingui":"history","trackingurl":"http://dang:8088/proxy/application_282828282828_12735/","diagnostics":"session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0\n","clusterid":282828282828,"applicationtype":"aquaman","applicationtags":"HAHAHA_,xyz_20221107070605_a082d9d8-912f-4278-a2ef-5dfe66089fd7,userid=dummy","priority":0,"startedtime":1667822766897,"launchtime":1667822766999,"finishedtime":1667822796759,"elapsedtime":29862,"amcontainerlogs":"http://dung:8042/node/containerlogs/container_e65_282828282828_12735_01_000001/xyz","amhosthttpaddress":"dung:8042","amrpcaddress":"dung:42765","masternodeid":"dung:8041","allocatedmb":-1,"allocatedvcores":-1,"reservedmb":-1,"reservedvcores":-1,"runningcontainers":-1,"memoryseconds":669695,"vcoreseconds":44,"queueusagepercentage":0.0,"clusterusagepercentage":0.0,"resourcesecondsmap":{"entry":{"key":"memory-mb","value":"669695"},"entry":{"key":"vcores","value":"44"}},"preemptedresourcemb":0,"preemptedresourcevcores":0,"numnonamcontainerpreempted":0,"numamcontainerpreempted":0,"preemptedmemoryseconds":0,"preemptedvcoreseconds":0,"preemptedresourcesecondsmap":{},"logaggregationstatus":"succeeded","unmanagedapplication":false,"amnodelabelexpression":"","timeouts":{"timeout":[{"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1}]}}]}}  

So far, using Json2Hive (nifty tool which generates json serde DDL from data) below DDL was created. Can someone confirm if this structure is correct or if there is a better way to create the table? Once created , how to explode the data entries so that each application will have its own row in the final table. 

create external table user_tables.sample_json_table (
  `apps` struct<
    `app`: array<struct<
        `id`: string,
        `queue`: string,
        `finalstatus`: string,
        `trackingurl`: string,
        `applicationtype`: string,
        `applicationtags`: string,
        `startedtime`: string,
        `launchtime`: string,
        `finishedtime`: string,
        `memoryseconds`: string,
        `vcoreseconds`: string,
        `resourcesecondsmap`: struct<
          `entry`: struct<
            `key`: string,
            `value`: string
          >
        >
      >
    >
  >
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe' 
location '/xyz/location/;

[#2 · Accepted solution]

It appears that the JSON data contains multiple application entries within a single line, presented as struct data. This format makes schema creation challenging. To address this, you can leverage Spark to flatten the schema and store the data in Hive. This enables you to query the data conveniently from either Hive or Spark.

Read the data JSON data 

df = spark.read.json("/user/hive/app_data_sample_data.json")

First, explode the "app" array to separate rows

from pyspark.sql.functions import col, explode, lit, struct
exploded_df = df.select(
    explode(col("apps.app")).alias("app")
)

Flatten and transform the exploded DataFrame

# Flatten and transform the exploded DataFrame
flattened_df = exploded_df.select(
    col("app.id").alias("id"),
    col("app.user").alias("user"),
    col("app.name").alias("name"),
    col("app.queue").alias("queue"),
    col("app.state").alias("state"),
    col("app.finalstatus").alias("finalstatus"),
    col("app.progress").alias("progress"),
    col("app.trackingui").alias("trackingui"),
    col("app.trackingurl").alias("trackingurl"),
    col("app.diagnostics").alias("diagnostics"),
    col("app.clusterid").alias("clusterid"),
    col("app.applicationtype").alias("applicationtype"),
    col("app.applicationtags").alias("applicationtags"),
    col("app.priority").alias("priority"),
    col("app.startedtime").alias("startedtime"),
    col("app.launchtime").alias("launchtime"),
    col("app.finishedtime").alias("finishedtime"),
    col("app.elapsedtime").alias("elapsedtime"),
    col("app.amcontainerlogs").alias("amcontainerlogs"),
    col("app.amhosthttpaddress").alias("amhosthttpaddress"),
    col("app.amrpcaddress").alias("amrpcaddress"),
    col("app.masternodeid").alias("masternodeid"),
    col("app.allocatedmb").alias("allocatedmb"),
    col("app.allocatedvcores").alias("allocatedvcores"),
    col("app.reservedmb").alias("reservedmb"),
    col("app.reservedvcores").alias("reservedvcores"),
    col("app.runningcontainers").alias("runningcontainers"),
    col("app.memoryseconds").alias("memoryseconds"),
    col("app.vcoreseconds").alias("vcoreseconds"),
    col("app.queueusagepercentage").alias("queueusagepercentage"),
    col("app.clusterusagepercentage").alias("clusterusagepercentage"),
    col("app.preemptedresourcemb").alias("preemptedresourcemb"),
    col("app.preemptedresourcevcores").alias("preemptedresourcevcores"),
    col("app.numnonamcontainerpreempted").alias("numnonamcontainerpreempted"),
    col("app.numamcontainerpreempted").alias("numamcontainerpreempted"),
    col("app.preemptedmemoryseconds").alias("preemptedmemoryseconds"),
    col("app.preemptedvcoreseconds").alias("preemptedvcoreseconds"),
    col("app.logaggregationstatus").alias("logaggregationstatus"),
    col("app.unmanagedapplication").alias("unmanagedapplication"),
    col("app.amnodelabelexpression").alias("amnodelabelexpression"),
    struct(
        lit("lifetime").alias("type"),
        lit("unlimited").alias("expirytime"),
        lit(-1).alias("remainingtimeinseconds")
    ).alias("timeouts")
)

Validate the flattened DataFrame

flattened_df.show(truncate=False)

If the data looks good , save the data as table. 

flattened_df.write.mode('overwrite').saveAsTable("app_data")

Query form hive (beeline)

+---------------------------------+----------------+-------------------------------------------+-------------------+-----------------+-----------------------+--------------------+----------------------+----------------------------------------------------+----------------------------------------------------+---------------------+---------------------------+----------------------------------------------------+--------------------+-----------------------+----------------------+------------------------+-----------------------+----------------------------------------------------+-----------------------------+------------------------+------------------------+-----------------------+---------------------------+----------------------+--------------------------+-----------------------------+-------------------------+------------------------+--------------------------------+----------------------------------+-------------------------------+-----------------------------------+--------------------------------------+-----------------------------------+----------------------------------+---------------------------------+--------------------------------+--------------------------------+---------------------------------+----------------------------------------------------+
|           app_data.id           | app_data.user  |               app_data.name               |  app_data.queue   | app_data.state  | app_data.finalstatus  | app_data.progress  | app_data.trackingui  |                app_data.trackingurl                |                app_data.diagnostics                | app_data.clusterid  | app_data.applicationtype  |              app_data.applicationtags              | app_data.priority  | app_data.startedtime  | app_data.launchtime  | app_data.finishedtime  | app_data.elapsedtime  |              app_data.amcontainerlogs              | app_data.amhosthttpaddress  | app_data.amrpcaddress  | app_data.masternodeid  | app_data.allocatedmb  | app_data.allocatedvcores  | app_data.reservedmb  | app_data.reservedvcores  | app_data.runningcontainers  | app_data.memoryseconds  | app_data.vcoreseconds  | app_data.queueusagepercentage  | app_data.clusterusagepercentage  | app_data.preemptedresourcemb  | app_data.preemptedresourcevcores  | app_data.numnonamcontainerpreempted  | app_data.numamcontainerpreempted  | app_data.preemptedmemoryseconds  | app_data.preemptedvcoreseconds  | app_data.logaggregationstatus  | app_data.unmanagedapplication  | app_data.amnodelabelexpression  |                 app_data.timeouts                  |
+---------------------------------+----------------+-------------------------------------------+-------------------+-----------------+-----------------------+--------------------+----------------------+----------------------------------------------------+----------------------------------------------------+---------------------+---------------------------+----------------------------------------------------+--------------------+-----------------------+----------------------+------------------------+-----------------------+----------------------------------------------------+-----------------------------+------------------------+------------------------+-----------------------+---------------------------+----------------------+--------------------------+-----------------------------+-------------------------+------------------------+--------------------------------+----------------------------------+-------------------------------+-----------------------------------+--------------------------------------+-----------------------------------+----------------------------------+---------------------------------+--------------------------------+--------------------------------+---------------------------------+----------------------------------------------------+
| application_282828282828_12717  | xyz            | xyz-4b6bdae2-1a0c-4772-bd8e-0d7454268b82  | root.users.dummy  | finished        | succeeded             | 100.0              | history              | http://dang:8088/proxy/application_282828282828_12717/ | session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0
 | 282828282828        | aquaman                   | ABC,xyz_20221107070124_2beb5d90-24c7-4b1b-b977-3c9af1397195,userid=dummy | 0                  | 1667822485626         | 1667822485767        | 1667822553365          | 67739                 | http://dingdong:8042/node/containerlogs/container_e65_282828282828_12717_01_000001/xyz | dingdong:8042               | dingdong:46457         | dingdong:8041          | -1                    | -1                        | -1                   | -1                       | -1                          | 1264304                 | 79                     | 0.0                            | 0.0                              | 0                             | 0                                 | 0                                    | 0                                 | 0                                | 0                               | succeeded                      | false                          |                                 | {"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1} |
| application_282828282828_12724  | xyz            | xyz-94962a3e-d230-4fd0-b68b-01b59dd3299d  | root.users.dummy  | finished        | succeeded             | 100.0              | history              | http://dang:8088/proxy/application_282828282828_12724/ | session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0
 | 282828282828        | aquaman                   | ZZZ_,xyz_20221107070301_e6f788db-e39c-49b6-97d5-6a02ff994c00,userid=dummy | 0                  | 1667822585231         | 1667822585437        | 1667822631435          | 46204                 | http://ding:8042/node/containerlogs/container_e65_282828282828_12724_01_000002/xyz | ding:8042                   | ding:46648             | ding:8041              | -1                    | -1                        | -1                   | -1                       | -1                          | 5603339                 | 430                    | 0.0                            | 0.0                              | 0                             | 0                                 | 0                                    | 0                                 | 0                                | 0                               | time_out                       | false                          |                                 | {"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1} |
| application_282828282828_12736  | xyz            | xyz-1a9c73ef-2992-40a5-aaad-9f0688bb04f4  | root.users.dummy  | finished        | succeeded             | 100.0              | history              | http://dang:8088/proxy/application_282828282828_12736/ | session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0
 | 282828282828        | aquaman                   | BLAHBLAH,xyz_20221107070609_8d261352-3efa-46c5-a5a0-8a3cd745d180,userid=dummy | 0                  | 1667822771170         | 1667822773663        | 1667822820351          | 49181                 | http://dong:8042/node/containerlogs/container_e65_282828282828_12736_01_000001/xyz | dong:8042                   | dong:34266             | dong:8041              | -1                    | -1                        | -1                   | -1                       | -1                          | 1300011                 | 89                     | 0.0                            | 0.0                              | 0                             | 0                                 | 0                                    | 0                                 | 0                                | 0                               | succeeded                      | false                          |                                 | {"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1} |
| application_282828282828_12735  | xyz            | xyz-d5f56a0a-9c6b-4651-8f88-6eaff5953777  | root.users.dummy  | finished        | succeeded             | 100.0              | history              | http://dang:8088/proxy/application_282828282828_12735/ | session stats:submitteddags=1, successfuldags=1, faileddags=0, killeddags=0
 | 282828282828        | aquaman                   | HAHAHA_,xyz_20221107070605_a082d9d8-912f-4278-a2ef-5dfe66089fd7,userid=dummy | 0                  | 1667822766897         | 1667822766999        | 1667822796759          | 29862                 | http://dung:8042/node/containerlogs/container_e65_282828282828_12735_01_000001/xyz | dung:8042                   | dung:42765             | dung:8041              | -1                    | -1                        | -1                   | -1                       | -1                          | 669695                  | 44                     | 0.0                            | 0.0                              | 0                             | 0                                 | 0                                    | 0                                 | 0                                | 0                               | succeeded                      | false                          |                                 | {"type":"lifetime","expirytime":"unlimited","remainingtimeinseconds":-1} |
+---------------------------------+----------------+-------------------------------------------+-------------------+-----------------+-----------------------+--------------------+----------------------+----------------------------------------------------+----------------------------------------------------+---------------------+---------------------------+----------------------------------------------------+--------------------+-----------------------+----------------------+------------------------+-----------------------+----------------------------------------------------+-----------------------------+------------------------+------------------------+-----------------------+---------------------------+----------------------+--------------------------+-----------------------------+-------------------------+------------------------+--------------------------------+----------------------------------+-------------------------------+-----------------------------------+--------------------------------------+-----------------------------------+----------------------------------+---------------------------------+--------------------------------+--------------------------------+---------------------------------+----------------------------------------------------+