Support Questions
Find answers, ask questions, and share your expertise

hive minus query

Below is my table description.

hive> describe dbsize;
OK
db_name string
location string
size_in_mb string
size_in_gb string
date string

# Partition Information
# col_name data_type comment

date string
Time taken: 0.335 seconds, Fetched: 10 row(s)

 

Have to mins the column size_in_mb with different date in where condition.

 

Having sample sql query for rdbms environment.

select DB_NAME, LOCATION, c2-c1 "difference size in MB", c4-c3 "difference size in GB"
from dbsize
(select size_in_mb c1 from dbsize where date='01-23-2020'),
(select size_in_mb c2 from dbsize where date='01-25-2020'),
(select size_in_gb c3 from dbsize where date='01-23-2020'),
(select size_in_gb c4 from dbsize where date='01-25-2020');

 

Need help on hive hql query to mins the columns for two different dates. In same single table.

 

Please advise me.

1 REPLY 1

Output of my table with two dates.

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

**** hdfs://*** 29GB 30233MB 1/23/2020
**** hdfs://*** 8GB 9040MB 1/23/2020
**** hdfs://*** 911GB 933122MB 1/23/2020
**** hdfs://*** 29GB 29795MB 1/23/2020
-(MINS)
**** hdfs://*** 129GB 130233MB 1/24/2020
**** hdfs://*** 18GB 19040MB 1/24/2020
**** hdfs://*** 1911GB 1933122MB 1/24/2020
**** hdfs://*** 129GB 129795MB 1/24/2020

Output I need like this.

**** hdfs://*** 29GB 30233MB
**** hdfs://*** 8GB 9040MB
**** hdfs://*** 911GB 933122MB
**** hdfs://*** 29GB 29795MB

 

I have tired two view creation with join select not working.

 

create view alerts_archive.dbsizevw1 AS
select * from alerts_archive.dbsize where date='Jan-23-2020';

create view alerts_archive.dbsizevw2 AS
select * from alerts_archive.dbsize where date='Jan-24-2020';

select db_name,location, (a.size_in_mb-b.size_in_mb) as variance_MB, (a.size_in_gb-b.size_in_gb) as variance_GB from alerts_archive.dbsizevw2 a
join alerts_archive.dbsizevw1 b;


select (a.size_in_mb-b.size_in_mb) as variance_MB, (a.size_in_gb-b.size_in_gb) as variance_GB from alerts_archive.dbsizevw2 a
join alerts_archive.dbsizevw1 b;

 

 

; ;