Hive Notes


 -->TYPES OF TABLES IN HIVE


 ANS: There are two types of tables in Hive ,one is Managed table and second is external table. the difference is , when you drop a table, if it is managed table hive deletes both data and meta data,if it is external table Hive only deletes metadata. If you want to create a external table ,you will use external keyword

-->HIVE UPDATE RECORDS 


ANS : UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

--> IS HIVE CONTAINS PRIMARY KEY

ANS;create table exmp((name string),primary key(name));

--> PARTITIONS
ans:
Hive - Partitioning. Hive organizes tables into partitions. It is a way of dividing a table into related parts based on the values of partitioned columns such as date, city, and department. Using partition, it is easy to query a portion of the data.

--> RC &  ORC 
The ORC File (Optimized Row Columnar) format provides a more efficient way to store relational data
ORC= large block sizes
     colomnar format
     arranges coloumns adjesent within the file for compression&fast action


RCFile (Record Columnar File), the previous Hadoop Big Data storage format on Hive, is being challenged by the smart ORC (Optimized Row Columnar) format. ... The second post, Optimising Hadoop and Big Data with Text and Hive, discussed the case for and against text format.



-->Diff hive queries


 
SORT BY – Data is ordered at each of ‘N’ reducers where the reducers can have overlapping range of data.
ORDER BY- This is similar to the ORDER BY in SQL where total ordering of data takes place by passing it to a single reducer.
DISTRUBUTE BY – It is used to distribute the rows among the reducers. Rows that have the same distribute by columns will go to the same reducer.
CLUSTER BY- It is a combination of DISTRIBUTE BY and SORT BY where each of the N reducers gets non overlapping range of data which is then sorted by those ranges at the respective reducers.


-->Join queries

EMP File
--------------------------------------------
101,rajesh,4000,m,10
102,praveen,6000,m,20
103,rani,5000,f,50
104,kavitha,8000,f,60
105,vignesh,7000,m,10
106,rima,2000,f,20
107,veena,3000,f,20



DEPT File
--------------------------------------------
10,hr,chennai
20,sales,bangalore
30,finance,pune
60,cse,delhi
70,it,kolkatta

Hive Joins

-------------------------------------------
INNER JOIN – Select records that have matching values in both tables.

LEFT JOIN (LEFT OUTER JOIN) – returns all the values from the left table, plus the

matched values from the right table, or NULL in case of no matching join predicate


RIGHT JOIN (RIGHT OUTER JOIN) - returns all the values from the right table,

plus the matched values from the left table, or NULL in case of no matching join

predicate

FULL JOIN (FULL OUTER JOIN) – Selects all records that match either left or right table

records.

LEFT SEMI JOIN - Only returns the records from the left-hand table. Hive doesn’t support

IN subqueries so you can’t do


Practice
--------------

hive> create table emp(id int,ename string,sal int,sex string,dno int)row format
delimited fields terminated by ',';
OK
Time taken: 0.082 seconds

hive> create table dept(dno int,dname string,loc string)row format delimited fields
terminated by ',';
OK
Time taken: 0.057 seconds

hive> load data local inpath 'emp' into table emp;

Loading data to table gnr.emp
Table gnr.emp stats: [numFiles=1, totalSize=145]
OK
Time taken: 0.33 seconds

hive> load data local inpath 'dept' into table dept;

Loading data to table gnr.dept
Table gnr.dept stats: [numFiles=1, totalSize=77]
OK
Time taken: 0.341 seconds

hive> select * from emp;
OK

emp.id emp.ename emp.sal emp.sex emp.dno
101 rajesh 4000 m 10
102 praveen 6000 m 20
103 rani 5000 f 50
104 kavitha 8000 f 60
105 vignesh 7000 m 10
106 rima 2000 f 20
107 veena 3000 f 20
Time taken: 0.103 seconds, Fetched: 7 row(s)

hive> select *from dept;
OK

dept.dno dept.dname dept.loc
10 hr chennai
20 sales bangalore
30 finance pune
60 cse delhi
70 it kolkatta
Time taken: 0.061 seconds, Fetched: 5 row(s)



INNER JOIN ( JOIN )
-------------------------
hive> select e.id,e.ename,e.sal,e.dno,d.dname from emp e INNER JOIN dept d on

(e.dno=d.dno);
                            (OR)
hive> select e.id,e.ename,e.sal,e.dno,d.dname from emp e JOIN dept d on (e.dno=d.dno);

Output
---------
e.id e.ename e.sal e.dno d.dname
101 rajesh 4000 10 hr
102 praveen 6000 20 sales
104 kavitha 8000 60 cse
105 vignesh 7000 10 hr
106 rima 2000 20 sales
107 veena 3000 20 sales
Time taken: 22.296 seconds, Fetched: 6 row(s)

LEFT OUTER JOIN   ( LEFT JOIN )
------------------------------------
hive> select e.id,e.ename,e.sal,e.dno,d.dname from emp e LEFT JOIN dept d on

(e.dno=d.dno);

OUTPUT
----------
e.id e.ename e.sal e.dno d.dname
101 rajesh 4000 10 hr
102 praveen 6000 20 sales
103 rani 5000 50 NULL
104 kavitha 8000 60 cse
105 vignesh 7000 10 hr
106 rima 2000 20 sales
107 veena 3000 20 sales
Time taken: 22.162 seconds, Fetched: 7 row(s)


RIGHT OUTER JOIMN (RIGHT JOIN )
--------------------------------------
hive> select e.id,e.ename,e.sal,e.dno,d.dname from emp e RIGHT OUTER JOIN dept d on

(e.dno=d.dno);

OUTPUT
----------
e.id e.ename e.sal e.dno d.dname
101 rajesh 4000 10 hr
105 vignesh 7000 10 hr
102 praveen 6000 20 sales
106 rima 2000 20 sales
107 veena 3000 20 sales
NULL NULL NULL NULL finance
104 kavitha 8000 60 cse
NULL NULL NULL NULL it
Time taken: 21.649 seconds, Fetched: 8 row(s)

FULL OUTER JOIN  ( FULL JOIN)
-------------------------------------
hive> select e.id,e.ename,e.sal,e.dno,d.dname from emp e FULL OUTER JOIN dept d on

(e.dno=d.dno);

OUTPUT
------------
e.id e.ename e.sal e.dno d.dname
105 vignesh 7000 10 hr
101 rajesh 4000 10 hr
107 veena 3000 20 sales
106 rima 2000 20 sales
102 praveen 6000 20 sales
NULL NULL NULL NULL finance
103 rani 5000 50 NULL
104 kavitha 8000 60 cse
NULL NULL NULL NULL it
Time taken: 21.646 seconds, Fetched: 9 row(s)


LEFT SEMI JOIN
------------------
hive> select * from emp e LEFT SEMI JOIN dept d on (e.dno=d.dno);

e.id e.ename e.sal e.sex e.dno
101 rajesh 4000 m 10
102 praveen 6000 m 20
104 kavitha 8000 f 60
105 vignesh 7000 m 10
106 rima 2000 f 20
107 veena 3000 f 20
Time taken: 22.033 seconds, Fetched: 6 row(s)



hive> select e.* from emp e LEFT OUTER JOIN dept d on( e.dno=d.dno ) where  d.dno is

null;

RIGHT SEMI JOIN
-----------------
hive> select d.* from emp e RIGHT OUTER JOIN dept d on( e.dno=d.dno ) where  e.dno is

null;


what is partition in hive

Hive - Partitioning. ... Hive organizes tables into partitions. It is a way of dividing a table into related parts based on the values of partitioned columns such as date, city, and department. Using partition, it is easy to query a portion of the data.

Advantages
  • Partitioning is used for distributing execution load horizontally.
  • As the data is stored as slices/parts, query response time is faster to process the small part of the data instead of looking for a search in the entire data set.
  • For example, In a large user table where the table is partitioned by country, then selecting users of country ‘IN’ will just scan one directory ‘country=IN’ instead of all the directories.
Limitations
  • Having too many partitions in table creates large number of files and directories in HDFS, which is an overhead to NameNode since it must keep all metadata for the file system in memory only.
  • Partitions may optimize some queries based on Where clauses, but may be less responsive for other important queries on grouping clauses.
  • In Mapreduce processing, Huge number of partitions will lead to huge no of tasks (which will run in separate JVM) in each mapreduce job, thus creates lot of overhead in maintaining JVM start up and tear down. For small files, a separate task will be used for each file. In worst scenarios, the overhead of JVM start up and tear down can exceed the actual processing time.
Example Scenarios
  • Partitioning is used in real-time log files analysis to segregate the records based on time stamp or date value to see the results day wise quickly.
  • Another real-time use is that, Customer/user details are partitioned by country/state or department for fast retrieval of subset data pertaining to some category.
  • Sales records by-product type, country, year and month is another commonly used scenario.

16 what is the differences between the static and dynamic partition


Static Partition in Hive
Insert input data files individually into a partition table is Static Partition Usually when loading files (big files) into Hive tables static partitions are preferred


INSERT INTO TABLE emp_part PARTITION(department="sales") 
SELECT EmployeeID, FirstName,Designation,Salary FROM employee WHERE department='sales';

load data local inpath 'emp_10.txt' into table emp_part partition(department="sales")

Static Partition saves your time in loading data compared to dynamic partition You “statically” add a partition in table and move the file into the partition of the table.
We can alter the partition in static partition


ALTER TABLE emp_part ADD PARTITION (Department='sales')
location '-----';


ALTER TABLE emp DROP IF EXISTS
  PARTITION(Department='sales');
You can get the partition column value form the filename, day of date etc without reading the whole big file. If you want to use Static partition in hive you should set property
set hive.mapred.mode = strict

This property set by default in hive-site.xml Static partition is in Strict Mode You should use where clause to use limit in static partition You can perform Static partition on Hive Manage table or external table.
Dynamic Partition in Hive
single insert to partition table is known as dynamic partition

INSERT INTO TABLE emp_part PARTITION(department) SELECT EmployeeID, FirstName,Designation,Salary,department FROM employee;
Usually dynamic partition load the data from non partitioned table
Dynamic Partition takes more time in loading data compared to static partition
When you have large data stored in a table then Dynamic partition is suitable.
If you want to partition number of column but you don’t know how many columns then also dynamic partition is suitable
Dynamic partition there is no required where clause to use limit. we can’t perform alter on Dynamic partition
You can perform dynamic partition on hive external table and managed table If you want to use Dynamic partition in hive then mode is in nonstrict mode .

Here is hive dynamic partition properties you should allow
1. set hive.exec.dynamic.partition=true
     This enable dynamic partitions, by default it is false.
2. set hive.exec.dynamic.partition.mode=nonstrict
     We are using the dynamic partition without a static
     partition (A table can be partitioned based    
     on multiple columns in hive) in such case we have to           
     enable the non strict mode. In strict mode we can use             
     dynamic partition  only with a Static Partition.
3. set hive.exec.max.dynamic.partitions.pernode=3
     The default value is 100, we have to modify the   
     same according to the possible no of partitions
4. hive.exec.max.created.files=150000
     The default values is 100000 but for larger tables  
     it can exceed the default, so we may have to update the same.

Note:


As we all know, Partition helps in increasing the efficiency when performing a query on a table. Instead of scanning the whole table, it will only scan for the partitioned set and does not scan or operate on the unpartitioned sets, which helps us to provide results in lesser time and the details will be displayed very quickly because of Hive Partition.
Now, let’s assume a condition that there is a huge dataset. At times, even after partitioning on a particular field or fields, the partitioned file size doesn’t match with the actual expectation and remains huge and we want to manage the partition results into different parts. To overcome this problem of partitioning, Hive provides Bucketing concept, which allows user to divide table data sets into more manageable parts.

Thus, Bucketing helps user to maintain parts that are more manageable and user can set the size of the manageable parts or Buckets too.
Hive Bucketing:
-----------------------
Buckets in hive is used in segregating of hive table-data into multiple files or directories. it is used for efficient querying.
Hive partition divides table into number of partitions and these partitions can be further subdivided into more manageable parts known as Buckets or Clusters. The Bucketing concept is based on Hash function, which depends on the type of the bucketing column. Records which are bucketed by the same column will always be saved in the same bucket.
Here, CLUSTERED BY clause is used to divide the table into buckets.
In Hive Partition, each partition will be created as directory. But in Hive Buckets, each bucket will be created as file.
Bucketing can also be done even without partitioning on Hive tables.
Advantages of Bucketing:
Bucketed tables allows much more efficient sampling than the non-bucketed tables. With sampling, we can try out queries on a section of data for testing and debugging purpose when the original data sets are very huge. Here, the user can fix the size of buckets according to the need.
Bucketing concept also provides the flexibility to keep the records in each bucket to be sorted by one or more columns. Since the data files are equal sized parts, map-side joins will be faster on the bucketed tables.
Example:
Let us see how to create and populate bucketed table in the following example, where we try to subdivide a partition table into multiple manageable parts based on a field by using bucketing technique.
Input Dataset to Perform Bucketing Operation:
To perform the bucketing operation on a dataset, we require an input dataset. You can refer to the below link for our input dataset real_state.
Dataset Description:
Column 1 : Street
Column 2 : City
Column 3 : Zip
Column 4 : State
Column 5 : Beds
Column 6 : Baths
Column 7 : Sq_feet
Column 8 : flat_type
Column 9 : Price
Selecting the Database:
Let us begin with the coding part by selecting a database and performing queries on the bucketed table.
Here, we have used a database named ‘bucketing’ which already exists in the Hive metastore database and we will create new tables in the ‘bucketing’ database, to perform Bucketing operation on the newly created tables.
Creating a New Input Table:
Next, we have created a new input table with name ‘input_table’, where we will save the contents of the input dataset.
Load the Input Dataset:
Now, in the below image, we can see that we have loaded the contents of real_state into input_table by using the load command.
Display the Contents of Table input_table to Ensure Whether the Input File has been Loaded Successfully or Not:
By using ‘select’ statement, we can check the contents of the input_table, whether we have successfully loaded the contents or not.
We can see from the above image that we have successfully loaded the contents of the input dataset real_state into input_table.
Set the Below Properties in Hive Command Line Before Proceeding Further for Bucketing Scripts:
To populate the bucketed table, we have to set hive.enforce.bucketing property to ‘true’, so that the Hive knows to create the number of buckets declared in the table definition.
The property hive.enforce.bucketing = true is similar to hive.exec.dynamic.partition = true, in Hive partitioning. By setting this property, we will enable dynamic bucketing while loading data into the Hive table.
The above hive.enforce.bucketing = true property sets the number of reduce tasks to be equal to the number of buckets mentioned in the table definition (Which is ‘4’ in our case) and automatically selects the clustered by column from table definition.
Creating Bucket Table:
From the above image, we can see that we have created a new bucket table with name ‘bucket_table’, which is partitioned by ‘city’ and clustered by ‘street’ field with the bucket size of ‘4’.
Here, we have decomposed Hive Buckets into ‘4’ parts.
Query to Retrieve Data from Bucketed Table:
We can observe from the above image that we are selecting the columns from input_table and inserting it into our bucketed table ‘bucket_table’, which is partitioned by city.
After successfully inserting the contents of input_table to bucket_table, we can see the bucketed output result in the browser and can also download the required city partitioned bucketed files in our local file system.
Browse Directory:
The below screenshot shows the path of the bucket_table with city as ‘ANTELOPE’.
We can observe from the above image, four Buckets have been created in the above Hive warehouse path for every city name.
Now, let’s download the output part file result by selecting the download option.
We can observe from the above image, the part file is successfully downloaded in the local file system downloads directory.
It is clear that Bucketing is most suitable for sampling purpose and adds some optimization to the query performance.
We can also execute the above output in command line by copy pasting the same or different part file path, according to the user’s requirement.
Hence, from the above execution steps, we can observe that we have created a structure to decompose the data into more manageable parts or equal parts using Bucketing technique in Hive.
We hope this post has been helpful in understanding the concept of Bucketing in Hive.


Scenario:


Hive Partitioning:
Partition divides large amount of data into multiple slices based on value of a table column(s).
Assume that you are storing information of people in entire world spread across 196+ countries spanning around 500 crores of entries. If you want to query people from a particular country (Vatican city), in absence of partitioning, you have to scan all 500 crores of entries even to fetch thousand entries of a country. If you partition the table based on country, you can fine tune querying process by just checking the data for only one country partition. Hive partition creates a separate directory for a column(s) value.
Pros:
Distribute execution load horizontally Faster execution of queries in case of partition with low volume of data. e.g. Get the population from "Vatican city" returns very fast instead of searching entire population of world. Cons:
Possibility of too many small partition creations - too many directories. Effective for low volume data for a given partition. But some queries like group by on high volume of data still take long time to execute. e.g. Grouping of population of China will take long time compared to grouping of population in Vatican city. Partition is not solving responsiveness problem in case of data skewing towards a particular partition value. Hive Bucketing:
Bucketing decomposes data into more manageable or equal parts.
With partitioning, there is a possibility that you can create multiple small partitions based on column values. If you go for bucketing, you are restricting number of buckets to store the data. This number is defined during table creation scripts.
Pros
Due to equal volumes of data in each partition, joins at Map side will be quicker. Faster query response like partitioning
 Cons
You can define number of buckets during table creation but loading of equal volume of data has to be done manually by programmers.
How will you read and write HDFS files in Hive?
i) TextInputFormat- This class is used to read data in plain text file format.
ii) HiveIgnoreKeyTextOutputFormat- This class is used to write data in plain text file format.
iii) SequenceFileInputFormat- This class is used to read data in hadoop SequenceFile format.
iv) SequenceFileOutputFormat- This class is used to write data in hadoop SequenceFile format.
 what is the difference between order by and sort by in Hive?
  • SORT BY will sort the data within each reducer. You can use any number of reducers for SORT BY operation.
  • ORDER BY will sort all of the data together, which has to pass through one reducer. Thus, ORDER BY in hive uses a single



Comments