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
.