Saturday, September 1, 2018

how to write Hive script file

Being a Data Warehousing package built on top of Hadoop, Apache Hive is increasingly getting used for data analysis, data mining and predictive modeling. In this post, let’s look at how to run Hive Scripts. In general, we use the scripts to execute a set of statements at once. Hive Scripts are used pretty much in the same way. It will reduce the time and effort we put on to writing and executing each command manually.
Hive Scripts are supported in the Hive 0.10.0 and above versions. As Hive 0.90 version is installed in CDH3, we cannot run Hive Scripts in CDH3. You can try the below steps in CDH4 as it has Hive 0.10.0 version installed in them. Are you aware of how to create a Hive script? If no, click here to gain more clarification.
Master-Hive-Now
Now, let us see how to write the scripts in Hive and run them in CDH4:

Step 1: Writing a Hive script.

To write the Hive Script the file should be saved with .sql extension. Open a terminal in your Cloudera CDH4 distribution and give the following command to create a Hive Script.
Command: sudo gedit sample.sql
Writing a Hive script.
On executing the above command, it will open the file with the list of all the Hive commands that need to be executed.
In this script,  a table will be created, described and data will be loaded and retrieved from the table.
1. Creating the Table in Hive:
Command: create table product ( productid: int, productname: string, price: float, category: string) rows format delimited fields terminated by ‘,’ ;
Here, product is the table name and { productid, productname, price, category} are the columns of this table.
Fields terminated by ‘,’ indicate that the columns in the input file are separated by the symbol ‘,’.
By default the records in the input file are separated by a new line.
2. Describing the Table:
Command: describe product;

3. Loading the Data into the Table.

To load the data into the table first we need to create an input file which contains the records that need to be inserted in the table.
Let us create an input file.
Command: sudo gedit input.txt
Loading the Data into the Table.
Edit the contents in the file as shown in the figure.

Loading the Data into the Table.

4. Retrieving the Data:
To retrieve the data, the select command is used.
Command: Select * from product;
The above command is used to retrieve the value of all the columns present in the table. The script should be like as it is shown in the below image.
Now, we are done with writing the Hive script.  The file sample.sql  can now be saved.

 Retrieving the Data

Step 2: Running the Hive Script

The following is the command to run the Hive script:
Command: hive –f /home/cloudera/sample.sql
Running the Hive Script
While executing the script, make sure that the entire path of the location of the Script file is present.
We can see that all the commands are executed successfully.
Running the Hive Script
This is how Hive scripts are run and executed in CDH4.
Hive is a critical component of Hadoop and your expertise in Hive can land you top-paying Hadoop jobs! Edureka has a specially curated Hadoop course that helps you master concepts such as MapReduce, Yarn, Pig, Hive, HBase, Oozie, Flume and Sqoop. Click on the button below to get started.

How to Improve Hive Query Performance With Hadoop

Apache Hive is a data warehouse built on the top of Hadoop for data analysis, summarization, and querying. Hive provides an SQL-like interface to query data stored in various data sources and file systems.

Use Tez Engine

Apache Tez Engine is an extensible framework for building high-performance batch processing and interactive data processing. It is coordinated by YARN in Hadoop. Tez improved the MapReduce paradigm by increasing the processing speed and maintaining the MapReduce ability to scale to petabytes of data.
Tez engine can be enabled in your environment by setting hive.execution.engine to tez:
set hive.execution.engine=tez;

Use Vectorization

Vectorization improves the performance by fetching 1,024 rows in a single operation instead of fetching single row each time. It improves the performance for operations like filter, join, aggregation, etc.
Vectorization can be enabled in the environment by executing below commands.
set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled=true;

Use ORCFile

Optimized Row Columnar format provides highly efficient ways of storing the hive data by reducing the data storage format by 75% of the original. The ORCFile format is better than the Hive files format when it comes to reading, writing, and processing the data. It uses techniques like predicate push-down, compression, and more to improve the performance of the query.
Consider two tables: 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;
Above query will take a long time, as the table is stored as text. Converting this table into ORCFile format will significantly reduce the query execution time.
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;
ORC supports compressed (ZLIB and Snappy), as well as uncompressed storage.

Use Partitioning

With partitioning, data is stored in separate individual folders on HDFS. Instead of querying the whole dataset, it will query partitioned dataset.

Create Temporary Table and Load Data Into Temporary Table

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 Partitioned Table

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;

Enable Dynamic Hive Partition

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

Import Data From Temporary Table To Partitioned Table

Insert Overwrite table Employee_Part Partition(City) Select EmployeeID,
EmployeeName,Address,State,City,Zipcode from Emloyee_Temp;

Use Bucketing

The Hive table is divided into a number of partitions and is called Hive Partition. Hive Partition is further subdivided into clusters or buckets and is called bucketing or clustering.
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;

Cost-Based Query Optimization

Hive optimizes each query's logical and physical execution plan before submitting for final execution. However, this is not based on the cost of the query during the initial version of Hive.
During later versions of Hive, query has been optimized according to the cost of the query (like which types of join to be performed, how to order joins, the degree of parallelism, etc.).
To use cost-based optimization, set the below parameters at the start of the query.
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;

Summary

Apache Hive is a very powerful tool for analyzing data, and it supports batch and interactive data processing. It is one of the most-used techniques by data analysts and data scientists. It is very important that you know how to improve the performance of query when you are processing petabytes of data.
Now you know how to improve the performance of the Hive query!

Thursday, August 9, 2018

Hive date function

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

[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)

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