HiveQL vs SQL

Scenario/Feature	HiveQL	SQL	Remarks
Default Join	"equi" join	Inner join	"equi" join - the only entries that are returned are the ones where the condition is true and returns no null values
Join syntax	LEFT OUTER JOIN RIGHT OUTER JOIN	LEFT JOIN RIGHT JOIN
Largest table last	Hive attempts to perform a map-side join where it loads the first table into memory and reads the second table in as normal input to the map function		When writing queries, try to facilitate this as much as possible and order the tables used in the join so that the largest table is last.
Data Type	No interval types
	All queries must reference a table	'dual' or table-less queries supported
	No session-scoped temp tables
	No 'IN' predicate
	No 'FIND' string search function for producing the offset to a match
	No find/replace string functions for plain strings (i.e. not regex)
	No regular UNION, INTERSECT, or MINUS operators
	Null values are treated differently than empty string, and are exported differently.  IE, empty strings are exported as '\n' and nulls are exported as nulls		This isn't unique to Hive but still annoying when exporting data from Hive into another system.
	No hierarchical/self-referencing querying		Most distributed computing solutions can't do this, but it can be very handy.
	No Update or Delete statements
	No cost-based explain plans.		Running explain plans generally just shows the path of accessing data.  Useful to some degree but it would be great if it was more advanced in that it could help the user understand which steps are causing the biggest slowdowns
	Hive Does not support the ability to run a query that select from tables in more than one database	It is possible
	Hive does not support sub-queries such as those connected by IN/EXISTS in the WHERE clause
	Hive does not support the truncation of data from a table
	No inequality join
	group_concat () is missing in Hive QL		it is available with Impala

Hive vs Pig

Scenario/Feature	Pig	Hive	Remark
Utilizing SQL experience	Pig Latin’s syntax is Data flow oriented	HiveQL syntax is very similar to SQL
Query Optimization	Developer has some control	Developer has no say in query optimization. Hive Optimizer is final authority.
Coding Style	Verbose	SQL like
General Usage	Scheduled jobs to crunch massive data ETL like jobs	Adhoc queries
Thought process	Think in terms of Flow chart	Think in terms of SQL like declarative style
Underlying platform	Hadoop, Dryad	Hadoop
Connection to external world	Integrated with Hadoop streaming which make Pig accessible to other languages	Get connected to external world using Thrift server (e.g. JDBC). Easy to integrate existing BI tools
Temporary table concept	No such requirement	For complex task, for hive you have to manually to create temporary table to store intermediate data,
Queries in case of complex data structure	Queries involving complex data structure are easier to write. Pig has Tuple and Bag data types.	Queries involving complex data structure are difficult to write
Meta data support	Pig has no metadata support, (or it is optional, in future it may integrate hcatalog).	Hive has tables' metadata stored in relational database
Ease of writing code	Writing UDF in pig much is easier	Writing UDF in hive is not easy.	My opinion ( derived from support to complex data structure)
Streaming of data	Pig allows one to load data and user code at any point in the pipeline. This is can be particularly important if the data is a streaming data, for example data from satellites or instruments.	Hive, which is RDBMS based, needs the data to be first imported (or loaded) and after that it can be worked upon. So if you were using Hive on streaming data, you would have to keep filling buckets (or files) and use hive on each filled bucket, while using other buckets to keep storing the newly arriving data.
Suitability for parallelization	Pig is well suited to parallelization and so it has an edge for systems where the datasets are huge, i.e. in systems where throughput has higher precedence than latency (the time to get any particular datum of result)
Who is faster	Pig is faster in the data import.	Hive is faster is execution
Handling of Skewed data	Pig has a special join mode (skew-join) which users can use to query over data whose join skew distribution in data is not even. It samples the data and uses that information to distribute the load evenly. Pig order-by command also similarly samples the data first. (Pig 'order by' statement does global sorting of data in a scalable fashion (multiple map/reduce tasks))	Hive sort-by sorts within each reduce task	If your data is not evenly distributed (e.g. across join or sort keys), this can greatly affect the runtime of the query- few of the tasks can get much larger share of the processing.

Apache Hive Architecture Diagram

While studying about internals of Apache Hive, I realized that everyone is using same set of illustrations (from here & here) to explain Hive architecture.  To understand the internals of Hive, I drew my own diagram. I am sharing the same here, if you like it share it.

BPEL PM 11g Performance Tuning - Final installment

This is last installment of of Oracle Fusion Middleware BPEL PM 11g Performance Tuning. This installments covers appendixes of the book.

Your comments will make this book useful and valuable.

   http://www.scribd.com/doc/224164824/BPEL-PM-11g-Performance-Tuning-Appendies"  style="text-decoration: underline;" >BPEL PM 11g Performance Tuning- Appendies

BPEL PM 11g Performance Tuning - 8

This is chapter eight of Oracle Fusion Middleware BPEL PM 11g Performance Tuning. This chapter covers L&P test design and load test selection.

Your comments will make this book useful and valuable.

   http://www.scribd.com/doc/224164845/BPEL-PM-11g-Performance-Tuning-8"  style="text-decoration: underline;" >BPEL PM 11g Performance Tuning-8

BPEL PM 11g Performance Tuning - 7

This is seventh chapter of Oracle Fusion Middleware BPEL PM 11g Performance Tuning. This chapter covers data collection aspects.

Your comments will make this book useful and valuable.

   http://www.scribd.com/doc/224164835/BPEL-PM-11g-Performance-Tuning-7"  style="text-decoration: underline;" >BPEL PM 11g Performance Tuning-7

Packt is celebrating 'International #DayAgainstDRM on May 6th 2014

Packt Publishing is offering all its DRM-free content at $10 for 24 hours only on May 6th – that’s all 2000+ eBooks and Videos at http://bit.ly/1q6bpha

Go guys and grab some books and videos which will be helpful in your career.