Connecting to Hive
Log into the Master node of the EMR cluster, switch to the Hadoop user, and navigate to the Hive directory:
[root@10 ~]# su hadoop[hadoop@10 root]$ cd /usr/local/service/hive
Preparing the Data
Creating a data file (in JSON format):
vim test.data
Compile the following content and save it:
{"name":"Mary","age":12,"course":[{"name":"math","location":"b208"},{"name":"english","location":"b702"}],"grade":[99,98,95]}{"name":"Bob","age":20,"course":[{"name":"music","location":"b108"},{"name":"history","location":"b711"}],"grade":[91,92,93]}
Storing the data file on HDFS:
hadoop fs -put ./test.data /
Creating the Table
Connecting to Hive:
[hadoop@10 hive]$ hive
Creating a table based on the mapping relationship:
hive> CREATE TABLE test (name string, age int, course array<map<string,string>>, grade array<int>) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;
Importing Data
hive>LOAD DATA INPATH '/test.data' into table test;
Verifying the Successful Import of Data
Querying All Data:
hive> select * from test;OKMary 12 [{"name":"math","location":"b208"},{"name":"english","location":"b702"}] [99,98,95]Bob 20 [{"name":"music","location":"b108"},{"name":"history","location":"b711"}] [91,92,93]Time taken: 0.153 seconds, Fetched: 2 row(s)
Querying the first score of each record:
hive> select grade[0] from test;OK9991Time taken: 0.374 seconds, Fetched: 2 row(s)
Querying the name and location of the first course for each record:
hive> select course[0]['name'], course[0]['location'] from test;OKmath b208music b108Time taken: 0.162 seconds, Fetched: 2 row(s)