hive_serde_isse.sql

Table :
====================
CREATE EXTERNAL TABLE tweets (  createddate string, 
 geolocation string, 
 tweetmessage string,  
 user_name struct<geoenabled:boolean, id:int, name:string, screenname:string, userlocation:string>
 )ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 'gs://iwinner-data/json_data';


Query :
======================
SELECT DISTINCT tweetmessage, user_name.name, createddate FROM default.tweets WHERE user_name.name = 'Hortonworks' ORDER BY createddate;


Jars:
===========
gs://iwinner-data/jars/json-serde-1.3-jar-with-dependencies.jar
gs://iwinner-data/jars/hive-hcatalog-core-0.13.1.jar
add jar gs://iwinner-data/jars/hive-hcatalog-core-0.13.1.jar
add jar gs://iwinner-data/jars/json-serde-1.3-jar-with-dependencies.jar
add jar gs://iwinner-data/jars/json-serde-1.3.8-jar-with-dependencies.jar;
add jar gs://iwinner-data/jars/hive-serde-2.3.0.jar;

add jar gs://iwinner-data/jars/hive-serde-2.3.8.jar,gs://iwinner-data/jars/hive-common-3.1.2.jar;

add jar gs://iwinner-data/jars/hive-serde-3.1.2.jar;
add jar gs://iwinner-data/jars/hive-common-3.1.2.jar;
add jar gs://iwinner-data/jars/hive-common-3.1.2.jar;

Data:
================
{ "user_name": {    "userlocation": "California, Santa Clara",    "id": 222222,    "name": "Hortonworks",    "screenname": "hortonworks",    "geoenabled": true  },  "tweetmessage": "Learn more about #Spark in #HDP 2.4 with @Hortonworks founder @acmurthy in this video overview http://bit.ly/1gOyr9w  #hadoop",  "createddate": "2015-07-24T16:30:33",  "geolocation": "in"}
{ "user_name": {    "userlocation": "California, Santa Clara",    "id": 222222,    "name": "Hortonworks",    "screenname": "hortonworks",    "geoenabled": true  },  "tweetmessage": "Learn more about #Spark in #HDP 2.5 with @Hortonworks founder @acmurthy in this video overview http://bit.ly/1gOyr9w  #hadoop",  "createddate": "2016-07-24T16:30:33",  "geolocation": "uk"}
{ "user_name": {    "userlocation": "California, Santa Clara",    "id": 222222,    "name": "Hortonworks",    "screenname": "hortonworks",    "geoenabled": true  },  "tweetmessage": "Learn more about #Spark in #HDP 2.6 with @Hortonworks founder @acmurthy in this video overview http://bit.ly/1gOyr9w  #hadoop",  "createddate": "2017-07-24T16:30:33",  "geolocation": "usa"}
{ "user_name": {    "userlocation": "California, Santa Clara",    "id": 222222,    "name": "Hortonworks",    "screenname": "hortonworks",    "geoenabled": true  },  "tweetmessage": "Learn more about #Spark in #HDP 2.7 with @Hortonworks founder @acmurthy in this video overview http://bit.ly/1gOyr9w  #hadoop",  "createddate": "2018-07-24T16:30:33",  "geolocation": "ml"}