REAL ESTATE ANALYSIS WITH HIVE

Hive Use Case – Real Estate Data Analysis

Due to an industry, real estate activity is outlined as any economic dealings associated with the acquisition, sale, owner-operation or lease of property. This in addition includes income-generating residential properties, like flat, buildings and single-room rentals.

Real estate services are not enclosed within the sector. Also, samples of real estate services embrace brokerages, property management, appraisers, investment property analysts and different consultants.

All analysts, working with Big Data are using Hive or some other tool to query dataset and get results with ease. Although other querying languages exists, Hive gives us a variety of new features when compared to traditional approaches.

So,On demand of accelerating consumers in real estate field, filtered series of information was collected and handed over to data analyst team.

                                              SOLUTION OF REAL ESTATE USE CASE

create table real1(street string,city string,zip int,state string,beds int,baths int,sq_ft int,type string,sale_date string,price int,lalitude string,longitude string)

    row format delimited

                fields terminated by',';

                load data local inpath '/home/cloudera/hadoopData/real state.csv' into table real1;

1. Problem Statement:

          City wise list all the Villa which is not less than ten thousand.

   

            select city from real where ((price >10000) and (type=='villa')) group by city;

2.         Problem Statement:

In GALT city which residential type has more than 800sq__ft. Display their respective details street,sq__ft,sale_date,city.

    

                select street,sq_ft,sale_date,city from real1 where ((city=='GALT') AND (sq_ft>800));

               

 3.  Problem Statement:

            Which is the cheapest Villa in CA. name the city,street and price for the Villa.

             select city,street,price from real1 where type=='villa' order by price limit 1;

4. Problem Statement:

List top 5 residency details which lie in the budget of 60000-120000, an area more than 1450, sold after 17th may, min bedroom 3 and, min bathroom 2.

               

           select street,city,zip,state,beds,baths,sq_ft,type,price from real1 where((price>=60000) AND (price<=120000) AND (sq_ft>1450) AND (beds>3) AND(baths>2)) order by price limit 5;

 Here only one house address  is selected under above condition.

5.

     Problem Statement:

separate list of residential apartments with more than 2 beds. Also include columns in following order City,Baths,Sq_feet,Price,flat_type,Beds respectively.

  create table intrestedBhk(city STRING,baths INT,sq_ft INT,price INT)

                     partitioned by(type STRING,beds INT)

                                 row format delimited

                                 fields terminated by ','

                                 stored as textfile;

INSERT INTO TABLE intrestedBhk partition(type,beds) select city,baths,sq_ft,price,type,beds from real1 where (beds>2 and type='Residential');

                        To  view  separated list of residential apartment click the given link

https://drive.google.com/open?id=1SiUdrWoi7h5pzXBQ_Id3S3n-kn0zkNYv