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
Temperature data Processing using hive
.jpg)
we also offer , online and classroom trainings
we support in POC
author: Bharat (sree ram)
contact : 04042026071
--------------------------------------------------------------------------------------------------
input file : temperature.txt
xxxxx1950xxx20xx
xxxxx1950xxx24xx
xxxxx1950xxx21xx
xxxxx1951xxx22xx
xxxxx1951xxx19xx
year starting position in each line : 6th column
length of the year : 4
temperature starting position in each line : 13 column
length of the temperature : 2
based on these clues, first we need to seperate year and temperature from the raw input table.
then we can perform required analytics.
step1.
hive> use halitics;
hive > create table raw(str string);
hive > load data local inpath 'temperature.txt' into table raw;
hive> select * from raw;
o/p --->
xxxxx1950xxx20xx
xxxxx1950xxx24xx
xxxxx1950xxx21xx
xxxxx1951xxx22xx
xxxxx1951xxx19xx
hive> create table tempr(y int, t int)
hive> insert overwrite table tempr
select substr(str,6,4), substr(str,13,2) from raw;
hive> select * from tempr;
o/p--->
1950 20
1950 24
1950 21
1951 22
1951 19
now your data became structured.
step3.
hive> create table result(y int, max int);
hive> insert overwrite table result
select y, max(t) from tempr group by y;
hive> select * from result;
step4.
hive> create table results(y int, max int, min int);
hive> insert overwrite table results
select y, max(t) , min(t) from tempr group by y;
hive> select * from results;
Appending rows of one table to another table.
.jpg)
we also offer , online and classroom trainings
we support in POC
author: Bharat (sree ram)
contact : 04042026071
---------------------------------------------------------------------------------------------------
table s : emp1 and emp2
if you directly use the following statement, table emp1 data will be overriden.
hive> insert overwrite table emp1
select * from emp2;
(we used * means,, the schema is same of emp1 and emp2 tables).
But I want to append rows of emp2 to emp1.
hive> insert overwrite table emp1
select * from (
select * from emp1
union all
select * from emp2) e;
note: in hive union should be used as sub query.
but the above process is bad way.
I want to append only females of emp2 to emp1.
hive> insert overwrite table emp1
select * from (
select * from emp1
union all
select * from emp2 where sex='f' ) e;
but the above two ways are bad ...
because,, for example , emp1 has 10lakh rows, and emp2 has 100 rows.
to append 100 rows of emp2 table the union query has to read 10lakh of emp1 and 100 emp2 rows.
I want to append 100 rows, with out reading 10 lakh rows of emp1....
emp1's backend hdfs directory is...
/user/hive/warehouse/halitics.db/emp1
suppose the file loaded into directory is emp.txt
emp2's backend hdfs directory is..
/user/hive/warehouse/halitics.db/emp2
suppose the file loaded into directory is emp2.txt
now I am going to append emp2 data to emp1.
from the command prompt,
$ hadoop fs -cp /user/hive/warehouse/halitics.db/emp2/emp2.txt /user/hive/warehouse/halitics.db/emp1
now, /halitics.db/emp1 directory has emp.txt and emp2.txt.
this , indirect way of loading and appending into table. But good way when compared with hive unions.
because,, for example , emp1 has 10lakh rows, and emp2 has 100 rows.
to append 100 rows of emp2 table the union query has to read 10lakh of emp1 and 100 emp2 rows.
I want to append 100 rows, with out reading 10 lakh rows of emp1....
emp1's backend hdfs directory is...
/user/hive/warehouse/halitics.db/emp1
suppose the file loaded into directory is emp.txt
emp2's backend hdfs directory is..
/user/hive/warehouse/halitics.db/emp2
suppose the file loaded into directory is emp2.txt
now I am going to append emp2 data to emp1.
from the command prompt,
$ hadoop fs -cp /user/hive/warehouse/halitics.db/emp2/emp2.txt /user/hive/warehouse/halitics.db/emp1
now, /halitics.db/emp1 directory has emp.txt and emp2.txt.
this , indirect way of loading and appending into table. But good way when compared with hive unions.
Subscribe to:
Posts (Atom)
.jpg)
