Temperature data(with positive and negative air temperatures) processing with hive

we also offer , online and classroom trainings

we support in POC

author: Bharat (sree ram)

contact :04042026071

_____________________________________________________________________

input file : tempr.txt

xxxxx1950xxx-20xx
xxxxx1950xxx24xx
xxxxx1950xxx-21xx
xxxxx1950xxx10xx
xxxxx1951xxx-22xx
xxxxx1951xxx19xx
xxxxx1951xxx-24xx
xxxxx1951xxx22xx

I want to find out  for each year max temperature  and minimum temperature.

o/p required.

1950    20   -21
1951    22   -24

step1)
hive> use halitics;
 hive> create table  raw(str string);
 hive> load data local inpath 'tempr.txt' into table raw;
step2)
 hive> create table positives(y int, t int);
 hive> create table nagatives live positives;
hive> insert overwrite table positives
           select substr(str,6,4), substr(str,13,2) from raw where substr(str,13,1) != '-';
hive> insert overwrite table negatives
            select substr(str,6,4) , substr(str,13,3) from raw where substr(str,13,1) = '-';
step3)
 hive> create table target(y int, t int);
 hive> insert overwrite table target
             select * from (
                  select * from positives
                       union all
                  select * from negatives ) tab;
 hive>  create table result(y int, max int, min int);
 hive> insert overwrite table result
              select  y, max(t), min(t) from target group by y;
 hive> select * from result;
 o/p -->
     
1950    20   -21
1951    22   -24