Transpose column to row

Transpose or Pivot table or convert column to row


create table twit_seq STORED AS ORC tblproperties ("orc.compress"="NONE") as
select t1.consumer_id,t1.text,t1.rk
from
(SELECT consumer_id,text,postcreationtime1,rank() OVER (partition by consumer_id ORDER BY postcreationtime1 desc ) as rk,
 row_number() over() as cnt
FROM dunami_v1)t1  where t1.rk < 6;


create table customer_tweet_info_dnami STORED AS ORC tblproperties ("orc.compress"="NONE") as
SELECT consumer_id ,
   max(CASE WHEN rk = 1 THEN text else NULL END) AS Tweet_1,
   max(CASE WHEN rk = 2 THEN text else NULL END) AS Tweet_2,
   max(CASE WHEN rk = 3 THEN text else NULL END) AS Tweet_3,
   max(CASE WHEN rk = 4 THEN text else NULL END) AS Tweet_4,
   max(CASE WHEN rk = 5 THEN text else NULL END) AS Tweet_5
FROM twit_seq  group by consumer_id;

week and rank function

Calculate weeks

select abs(floor(datediff(timestamp(from_unixtime(UNIX_TIMESTAMP(trim('01/04/16 00:00'),'MM/dd/yy hh:mm'))),postcreationtime1)/7)) as wk
from dunami_twitter_visual_data_v1 limit 100;


Use rank function to get value in descending order

create table twit_seq STORED AS ORC tblproperties ("orc.compress"="NONE") as
select t1.consumer_id,t1.text
from
(SELECT consumer_id,text,postcreationtime1,rank() OVER (partition by consumer_id ORDER BY postcreationtime1 desc ) as rk
FROM dunami_v1)t1  where t1.rk < 6;

Hive Optimization properties

set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
set mapreduce.job.mappers=1000;
set mapreduce.job.reducers=1000;
 SET hive.exec.parallel=true;
-----------------------------------------------------------

set hive.vectorized.groupby.maxentries=10240;
set hive.vectorized.groupby.flush.percent=0.1;
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
set hive.cli.print.header = true;


Working with Tez and getting issue like tez.TezProcessor|: java.lang.OutOfMemoryError: Java heap space

Then set the properties on query level so that container size increase for that particular query.

SET hive.tez.java.opts=-Xmx8192m -Xms4096m

SET hive.tez.container.size=4026

Go through link -
https://discuss.pivotal.io/hc/en-us/articles/226239948-Hive-query-with-TEZ-engine-failed-with-exception-java-lang-OutOfMemoryError-Java-heap-space

Hive query output generate empty files along with other files then use below properties.
set hive.merge.tezfiles=true;
set hive.merge.mapredfiles=true;
set hive.merge.mapfiles=true;
set hive.merge.orcfile.stripe.level=true;

=================================================

Optimization for ORC files

set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.vectorized.execution.reduce.groupby.enabled = true;
set hive.exec.parallel=true;
SET hive.tez.java.opts=-Xmx8192m -Xms4096m;
SET hive.tez.container.size=8192;
set hive.exec.parallel=true;

Copy data from S3 to hdfs

hadoop distcp s3n://<access key>:<secret key>@buket/testhadoop/*/* /user/hadoop_user/test/ 

Date format in hive

Examples -  5/11/2015       12/31/2014      5/11/2015

select optin_date,cell_phone_date,optin_date_stamp from datetest;

select
to_date(from_unixtime(UNIX_TIMESTAMP(trim(Optin_Date_Stamp), 'MM/dd/yyyy'))),
to_date(from_unixtime(UNIX_TIMESTAMP(trim(cell_phone_date), 'MM/dd/yyyy'))) ,
to_date(from_unixtime(UNIX_TIMESTAMP(trim(optin_date_stamp), 'MM/dd/yyyy')))
FROM datetest;

----------------------------------------------------------------------------------------------------------
/*
3/10/2015  3:04:05 AM
4/11/2015  4:05:06 AM
5/13/2015  5:06:07 AM
5/31/2016  5:18:19 PM
4/29/2016  4:17:18 PM
10/20/2015  10:11:12 AM
*/

select timestamp(from_unixtime(UNIX_TIMESTAMP(trim(a), 'MM/dd/yyyy hh:mm:ss'))) FROM datetest2;

---------------------------------------------------------------------------------------------------------
2017-01-03T23:55:33.000Z

select timestamp(from_unixtime(unix_timestamp(regexp_replace(concat(concat(concat(substr(standardtimestamp,1,10),substr(standardtimestamp,12,2)),
substr(standardtimestamp,15,2)),substr(standardtimestamp,18,2)),'-',''),'yyyymmddhhmmss')))  as  standardtimestamp from kdf_sample;

create table datetest(date1 string,date2 string,date3 string,date4 string) row format delimited fields terminated by ','
location '/user/dheerendra.a.yadav/testdata1/';

select
  to_date(from_unixtime(UNIX_TIMESTAMP(trim(date1),'MM/dd/yyyy'))) as date1,
  to_date(from_unixtime(UNIX_TIMESTAMP(trim(date2),'MM/dd/yyyy'))) as date2,
  timestamp(from_unixtime(UNIX_TIMESTAMP(trim(date3),'MM/dd/yyyy hh:mm:ss'))) as date3,
   timestamp(from_unixtime(unix_timestamp(regexp_replace(concat(concat(concat(substr(date4,1,10),substr(date4,12,2)),
   substr(date4,15,2)),substr(date4,18,2)),'-',''),'yyyyMMddhhmmss')))  as  date4
from datetest;

select standardtimestamp,timestamp(from_unixtime(unix_timestamp(regexp_replace(concat(concat(concat(substr(standardtimestamp,1,10),substr(standardtimestamp,12,2)),
substr(standardtimestamp,15,2)),substr(standardtimestamp,18,2)),'-',''),'yyyyMMddhhmmssss')))  as  standardtimestamp from test limit 2

Compare dates with date in date format  and date in date format:


select
sum(case when price_change_date  > from_unixtime(unix_timestamp('2016-12-31' ,'yyyy-MM-dd'), 'yyyy-MM-dd') then 1 else 0 end)  as price_change_date,
sum(case when listing_date  > from_unixtime(unix_timestamp('2016-12-31' ,'yyyy-MM-dd'), 'yyyy-MM-dd') then 1 else 0 end)  as listing_date,
sum(case when reduced_date  > from_unixtime(unix_timestamp('2016-12-31' ,'yyyy-MM-dd'), 'yyyy-MM-dd') then 1 else 0 end)  as reduced_date,
sum(case when distressed_date  > from_unixtime(unix_timestamp('2016-12-31' ,'yyyy-MM-dd'), 'yyyy-MM-dd') then 1 else 0 end)  as distressed_date
from fmdb.Interlinked_FM_DS_UPDT_V3;


When date in timestamp and you want to compare the date format with String format then use below format.

select from_unixtime(UNIX_TIMESTAMP(ins_gmt_ts,'yyyy-MM-dd'),'yyyyMMdd') as date1 from dbname_table_name_base limit 1;

========================================================

When you have interger or decimal value and you need to change in date format.

(1) Change integer or decimal value to string using CAST function.

(2) Convert into unix_timestamp with format e.g 'YYYYMMDD'

(3) Convert into from_unixtime()

(4) Convert into date_format()  in required format e.g 'yyyy-MM-dd'

Example:

 Date in decimal -  birth_dt = 1956324.0

 

Solution:

SELECT date_format(from_unixtime(unix_timestamp(cast(birth_date as string),'YYYYMMDD')),'yyyy-MM-dd') as birth_dt from dummy

Result:

birth_dt = 1956-01-01

Json file parsing

{"shortIp":"38.126.127","os":"Windows","refDomain":"www.cnn.com","standardTimestamp":"2016-12-15T19:58:45.000Z","userAgent":"Mozilla/5.0 (Windows NT 10.0; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0","deviceType":"Personal computer","estid":"CsY3SlcrcMZQT1Uvez0HAg==","browserFamily":"Firefox","mappedEvent":"pview","url":"http://money.cnn.com/2016/12/15/technology/apple-class-action-meal-breaks/index.html?iid=ob_homepage_tech_pool","iabCategories":[{"levelOne":"personal_finance","levelTwo":"financial_news","score":"medium"}],"ip":"38.126.127.126"}

drop table sdf;
create table sdf(
shortIp string
,os string
,refDomain string
,standardTimestamp string
,userAgent string
,deviceType string
,estid string
,browserFamily string
,mappedEvent string
,url string
,iabCategories array<struct<levelOne:string,levelTwo:string,score:string>>
,ip string)ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';
-------------------------------------------------------------------------------

drop table ext_sdf;
create external table ext_sdf(
shortIp string
,os string
,refDomain string
,standardTimestamp string
,userAgent string
,deviceType string
,estid string
,browserFamily string
,mappedEvent string
,url string
,iabCategories array<struct<levelOne:string,levelTwo:string,score:string>>
,ip string)ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 'hdfs://127.0.0.1:9000/sdf';



select shortip
,os,useragent
,refDomain
,standardTimestamp
,userAgent
,deviceType
,estid
,browserFamily
,mappedEvent
,url
,iabcategories.levelone[0]
,iabcategories.levelTwo[0]
,iabcategories.score[0]
,ip
from ext_sdf;

Extract json data in hive

Extract json data in hive

Sample data:--

{"data1":[{"id":"10084673031_10152357071093032","from":{"category":"Computers\/technology","category_list":[{"id":"139823692748565","name":"Computers & Electronics"},{"id":"177721448951559","name":"Workplace & Office"}],"name":"Cisco","id":"10084673031"},"message":"The Network team was lucky enough to speak with First Lieutenant Andrew Marsh about his return and transition to life after Afghanistan. Andrew shares his story on how an online program targeted to veterans matched his technical military skills to those required in a civilian career.","picture":"http:\/\/external.ak.fbcdn.net\/safe_image.php?d=AQDPRCLRHhV0HEaE&w=154&h=154&url=http\u00253A\u00252F\u00252Fimg.youtube.com\u00252Fvi\u00252FKNedz6qq45A\u00252F0.jpg","link":"http:\/\/newsroom.cisco.com\/video-content?type=webcontent&articleId=1281697","name":"Online Program Matches Veterans to Civilian Careers - The Network: Cisco's Technology News Site","caption":"newsroom.cisco.com","description":"Andrew Marsh, First Lieutenant U.S. Marine Corps : I was sent to Afghanistan in January of 2012 and I was an advisor to an Afghan army cornel. so it was my job to advise him on a daily basis. And look at what he was doing communications wise.","icon":"http:\/\/static.ak.fbcdn.net\/rsrc.php\/v2\/yD\/r\/aS8ecmYRys0.gif","privacy":{"value":""},"type":"link","status_type":"shared_story","created_time":"2013-11-11T04:04:51+0000","updated_time":"2013-11-11T04:06:26+0000","likes":{"data":[{"id":"100004689103296","name":"Salvador Lara"},{"id":"1661003721","name":"Chad Allison"},{"id":"1347829998","name":"Lynn Young Dasher"},{"id":"100000750083815","name":"Shannon Vanlandingham"},{"id":"100003220428749","name":"Tom Peters"},{"id":"904540186","name":"Manuel van der Herder"},{"id":"776497928","name":"Luis Chiong"},{"id":"748655637","name":"Dave van Asch"},{"id":"100005256342167","name":"Os Meyti"},{"id":"100004025366069","name":"Denish Malam"},{"id":"1050751758","name":"Jens Mueller"},{"id":"506521037","name":"Jen Nickens"},{"id":"100000723537849","name":"Vanya Ivanova"},{"id":"100000875945917","name":"Danien Harper"},{"id":"100000546265440","name":"Robert Hnatko"},{"id":"656888964","name":"Iman Khayambashi"},{"id":"100000730517023","name":"Debayan Deb"},{"id":"1642383715","name":"Jonathan Schwartz"},{"id":"100000865663563","name":"John Mahoney"},{"id":"100003026212534","name":"Rafael Kireyev"},{"id":"1490631811","name":"Jackie Salvatierra Estrada"},{"id":"776984395","name":"Yuval Ariav"},{"id":"100000592615966","name":"Ingemar Petersson"},{"id":"1391145150","name":"Qassim Zia"},{"id":"606035502793053","name":"Cisco Community"}]}}]}

Jars need to register:--

/Users/dheerendra/work/json-serde-1.1.4-jar-with-dependencies.jar
/Users/dheerendra/work/hive-json-serde-0.2.jar
/Users/dheerendra/work/hive-serdes-1.0-SNAPSHOT.jar
/Users/dheerendra/work/json-serde-1.0.jar

Create table :--

create table json6(data1 array<struct<id:string,message:string,created_time:string,likes:struct<data:array<struct<id:string,name:string>>>>>)ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

Load data in table:--

load data local inpath '/root/working/json_file.txt' overwrite into table json6;

Select data from table:--

select  id1,crt,id_4 from json6 lateral view explode(data1.id)id as id1  lateral view explode(data1.created_time)crtime as crt lateral view explode(data1.likes.data)id3  as id_3 lateral view explode(id_3.name)id4 as id_4;