HIVE Basics

After talking to a lot of aspiring big data developers, I realized basic concepts of big data tools still elude most of the people. HIVE is usually one of the starting points to enter big data domain, so I am starting with some basic concepts of HIVE.

External vs Managed Table:

Concept	External	Managed
Definition	External keyword is used while creating table	No external keyword
Location	Location is provided at the time of table creation	HIVE’s default location is used
Deletion	Only metadata gets deleted when table is dropped	Both metadata and data gets deleted when table is dropped
Need	External table indicates that data doesn’t inherently belong to HIVE. It is either a virtual table created on other tool’s data or other file system or someone is sharing that data with you i.e. you don’t own the right to delete it.	Data entirely belongs to HIVE. You can delete it.
Example	You are using logs to analyze the usage patterns of your cluster, but the logs are also needed to check for errors or load and a lot of other things. So you have to keep them, you can’t delete them after use.	While analyzing usage patterns of your cluster, you create a different table from the data by joining it with the logs about the details of jobs run on your cluster.

 Partitioning vs clustering:

Concept	Partitioning	Clustering(Bucketing)
Definition	It’s a technique to divide the records into different parts based on the value of specific column/s	It’s a technique to divide the records into different parts based on the value of specific column/s
Internal process	Each unique value of the specified column/s leads to creation of new folder. All the records containing same value for the column/s are put into corresponding folder.	Works similar to hash map. A hash function is applied to the value of specified column and the record corresponding to the value is put into the corresponding bucket.
Structure	Records are put into different folders according to the value of specific column	Records are put into different files according the value of column
Number of Partitions	Number of folders is equal to number of unique values for the specified column. If partitioning is done on more than one column, then number of folders is equal to total combinations of the column/s	Number of files is given at creation time.
Multiple columns	Partitions can be done on more than one column, e.g. PARTITIONED BY (state, city). Partitioning is done on state and then every state folder will contain various city folders.	Clustering can be done with partitioning. Clustered files will be put into partitioned folders. Clustering is not possible on more than one column.
Pros	As the data is partitioned according to the actual value of column, it greatly reduces the amount of data to be filtered when the querying on the partitioned column.	Number of buckets is decided by user, this prevents having endless folders. Buckets always contain almost equal amount of data.
Cons	If the data is skewed, you may end up having some partitions with a lot of data and some having close to none. If a column has a lot of unique values you may end up with a lot of folders, which affects both file system and processing	Each bucket contains data from more than one value of the column, so lesser benefit than partitioning in terms of data to be filtered.

Here’s a question:

There is a car company sales data by day and there is an ice cream company’s sales data by day. The data is for one month. The column to partition/bucket is date.

What would you use, partitioning or clustering? In which case? Also give the reason.

I hope this helps. Let me know if I missed something or if you have some doubts.

Also let me know if there is any other concept you want to know about.   

HIVE Tricks and Tips

In continuation of my last post, here are some tips and tricks for HIVE users:

Tricks:

1. Run HIVE script from bash:
     1.1  To run HIVE script from bash script, use command "hive"
              hive -e '{HIVE COMMANDS}'

     1.2  To run HIVE in verbose mode (prints progress with query, generally used for logging):
               hive -v -e '{HIVE COMMANDS}'

     1.3  To  pass arguments from bash to hive
               hive -d ARG=$ARG (argument passed from bash) -e '{HIVE COMMANDS}'

     1.4  To pass results from hive to bash
                1.4.1  If you have one or a few values:
                               VAR= hive -e  '{HIVE COMMANDS}'
                 1.4.2 If you have a big table as a result create an output directory
                               hive -d DIR=$DIR -e '
                               INSERT OVERWRITE LOCAL DIRECTORY "${DIR}"
                               {REST OF THE COMMAND}'


2. Implement MINUS operator in HIVE :
        Q1 LEFT OUTER JOIN Q2 ON (Q1.x=Q2.y) WHERE Q2.y IS NULL
        where Q stands for query
        the left outer join's output is all rows even if they don't have mapping in result of Q2, is null                 excludes all the rows that doesn't have a mapping in Q2 (same effect as MINUS operator)

Tip :

Use group by and CASE as much as possible: If you have multiple queries that run on same table try converting them into one query using group by or CASE statements. For e.g. lets say for an app, you want to find out how many users have sessions less than an hour, one hour to 3 hours, more than 3 hours, based on their location, use CASE for creating the 3 buckets and use group by to do it on the basis of location.

Hope you find these useful. If you have some tips and tricks like this you can share via comments. If you are having some problems with HIVE and want some tricks, do ask.

Thanks for reading :)

HIVE FACTS

HIVE is a big data tool used widely. Its also misunderstood widely.

Here are some facts about HIVE:

1. HIVE is a datawarehouse:
 HIVE is a data warehouse not a database. It gives you the capability to put your data (your big data, usually historical data) into files and then analyze it, slice and dice it, drill down, roll up; basically anything except manipulating it.


2. HIVE works in batch mode:
HIVE queries are similar to SQL but their processing is much different than SQL. HIVE queries get converted into MapReduce jobs. It means it takes atleast the amount of time it takes to create a job to run you query (unless its select *).

3. HIVE is not RDBMS: 

HIVE has a query language very similar to SQL but that doesn't mean it acts as RDBMS. It lacks many of the important features of RDBMS, like:

• Transactions: HIVE doesn't have transactions. It means it has no guarantee that your queries will be atomic, so they can fail at any point leaving your system in inconsistent state.
• No locks: There are no locks on tables. That means it is possible that two queries are manipulating your table (a whole partition or a whole table) simultaneously and hence, giving wrong results.

WARNING: Never use HIVE for transactional purposes

4. HIVE is not SQL:

• Amount of I/O: HIVE doesn't have tables, it only works on files. It can't read a part of your table, it'll always read the whole file. It doesn't matter whether you want to read one record, or a hundred, the amount of data read is same. 
• Processing time: If you read the whole "table", processing time is almost none. All it does is read the whole file and return it according to table's schema. But the processing time goes on increasing with each clause in the query.

5. HIVE uses HDFS to store data:
It means if you access a large table (there are solutions for that), you have to account the network time too alongwith I/O time.


There are a lot of features lacking in HIVE, but that doesn't mean that HIVE is useless. It just means its uses are different.
If you have some questions or you have some points where HIVE is different (or lacking), do comment here.

To find out more about HIVE, stay tuned... :)