Hive Queries on exampleJsonTable: JSON Data Type

After following the directions presented in the previous sections to create and map a Hive external table to the exampleJsonTable table in the Oracle NoSQL Database store (either non-secure or secure), the data in the store's table can be queried via the Hive Query Language.

Each sub-section below presents three instances of a given query, one for each of the three possible scenarios: non-secure, secure with password file, or secure with Oracle Wallet. But the query results are shown only once, in edited form.

Type the query from each sub-section below that corresponds to how you have configured your particular environment.

List Each Senator, Ordered By Rank and State

SELECT 
    get_json_object(jsonfield, '$.description') 
        AS description, 
    get_json_object(jsonfield, '$.personal.firstname')
        AS firstname, 
    get_json_object(jsonfield, '$.personal.lastname')
        AS lastname
FROM exampleJsonTable ORDER BY description;

..........
Hadoop job information for Stage-1: number of mappers: 2;
number of reducers: 1
Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 16.29 sec
Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 28.8 sec
..........
Job 0: Map: 2   Cumulative CPU: 34.22 sec
HDFS Read: 16799 HDFS Write: 5490 SUCCESS
Total MapReduce CPU Time Spent: 34 seconds 220 msec
..........
OK

Junior Senator for Alabama       Doug	Jones
Junior Senator for Alaska        Dan     Sullivan
Junior Senator for Arizona       Jeff    Flake
Junior Senator for Arkansas      Tom     Cotton
Junior Senator for California    Kamala  Harris
Junior Senator for Colorado      Cory    Gardner
..........
Senior Senator for Virginia      Mark    Warner
Senior Senator for Washington    Patty   Murray
Senior Senator for West Virginia Joe     Manchin
Senior Senator for Wisconsin     Ron     Johnson
Senior Senator for Wyoming       Michael Enzi

Time taken: 29.342 seconds, Fetched: 100 row(s) 

Note:

In the SELECT query, instead of the exampleJsonTable, you could use any of exampleJsonTable, exampleJsonTablePasswd, or exampleJsonTableWallet.

List Each Senator Who Is An Independent

SELECT get_json_object(jsonfield, '$. personal.firstname’), 
       get_json_object(jsonfield, '$. personal.lastname'), 
       get_json_object(jsonfield, '$.party'),
       get_json_object(jsonfield, '$.description')
FROM exampleJsonTable ORDER BY description;

..........
Hadoop job information for Stage-1: number of mappers: 2;
number of reducers: 1
Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 11.29 sec
Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 19.67 sec
..........
Job 0: Map: 2   Cumulative CPU: 19.67 sec
HDFS Read: 13716 HDFS Write: 301 SUCCESS
Total MapReduce CPU Time Spent: 19 seconds 670 msec
..........
OK

Angus   King    Independent Junior Senator for Maine
Bernard Sanders Independent Junior Senator for Vermont

Time taken: 15.614 seconds, Fetched: 2 row(s)

Note:

In the SELECT query, instead of the exampleJsonTable, you could use any of exampleJsonTable, exampleJsonTablePasswd, or exampleJsonTableWallet.