Step 1: Writing a Hive script.
Command: sudo gedit sample.sql


hive.execution.engine to tez:set hive.execution.engine=tez;
filter, join, aggregation, etc.set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled=true;
employee and employee_details, tables that are stored in a text file. Let's say we will use join to fetch details from both tables.Select a.EmployeeID, a.EmployeeName, b.Address,b.Designation from Employee a
Join Employee_Details b
On a.EmployeeID=b.EmployeeID;
Create Table Employee_ORC (EmployeeID int, EmployeeName varchar(100),Age int)
STORED AS ORC tblproperties("compress.mode"="SNAPPY");
Select * from Employee Insert into Employee_ORC;
Create Table Employee_Details_ORC (EmployeeID int, Address varchar(100)
,Designation Varchar(100),Salary int)
STORED AS ORC tblproperties("compress.mode"="SNAPPY");
Select * from Employee_Details Insert into Employee_Details_ORC;
Select a.EmployeeID, a.EmployeeName, b.Address,b.Designation from Employee_ORC a
Join Employee_Details_ORC b
On a.EmployeeID=b.EmployeeID;
Create Table Employee_Temp(EmloyeeID int, EmployeeName Varchar(100),
Address Varchar(100),State Varchar(100),
City Varchar(100),Zipcode Varchar(100))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
LOAD DATA INPATH '/home/hadoop/hive' INTO TABLE Employee_Temp;
Create Table Employee_Part(EmloyeeID int, EmployeeName Varchar(100),
Address Varchar(100),State Varchar(100),
Zipcode Varchar(100))
PARTITIONED BY (City Varchar(100))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
Insert Overwrite table Employee_Part Partition(City) Select EmployeeID,
EmployeeName,Address,State,City,Zipcode from Emloyee_Temp;
Create Table Employee_Part(EmloyeeID int, EmployeeName Varchar(100),
Address Varchar(100),State Varchar(100),
Zipcode Varchar(100))
PARTITIONED BY (City Varchar(100))
Clustered By (EmployeeID) into 20 Buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
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;
=================================================================
[cloudera@quickstart ~]$ cat test.csv
1,13-10-2010,Low
2,01-10-2012,High
3,01-10-2012,High
4,10-07-2011,Low
==================================================================
[cloudera@quickstart ~]$ hive
hive> create database datedb;
OK
Time taken: 0.566 seconds
==================================================================
hive> use datedb;
OK
Time taken: 0.104 second
==================================================================
hive> create table dateDemo(ID INT, demo_date String,priority STRING) row format delimited fields terminated by ',';
OK
Time taken: 1.413 seconds
==================================================================
hive>load data local inpath 'test.csv' overwrite into table dateDemo;
Loading data to table datedb.datedemo
Table datedb.datedemo stats: [numFiles=1, numRows=0, totalSize=71, rawDataSize=0]
OK
Time taken: 1.383 seconds
==================================================================
hive> select * from dateDemo;
OK
1 13-10-2010 Low
2 01-10-2012 High
3 01-10-2012 High
4 10-07-2011 Low
==================================================================
hive>
>
> select * from dateDemo where demo_date='01-10-2012';
OK
2 01-10-2012 High
3 01-10-2012 High
==================================================================
hive> select ID, cast(to_date(from_unixtime(unix_timestamp(demo_date, 'dd-MM-yyyy'))) as date) from dateDemo;
OK
1 2010-10-13
2 2012-10-01
3 2012-10-01
4 2011-07-10
Time taken: 0.152 seconds, Fetched: 4 row(s)
==================================================================
hive> select ID, from_unixtime(unix_timestamp(demo_date, 'dd-MM-yyyy'),'yyyyMMdd') from dateDemo;
OK
1 20101013
2 20121001
3 20121001
4 20110710
Time taken: 0.093 seconds, Fetched: 4 row(s)
==================================================================