Why Hive is not supporting Stored Procedure?

Question

Why hive is not supporting Stored procedure? If its not supporting then how we will handle Sp in Hive? have any alternate solution? (Because we have a already a data base is there in mssql) What about HBASE? Is it support SP?

Answer 1

First of all, Hadoop or Hive is NOT an alternative to your SQL DB. You must never consider either of these 2 to be used as a replacement of your RDBMS.

Hive was developed just to provide warehousing capabilities on top of an existing Hadoop cluster keeping in mind the large base of SQL users, both expert database designers and administrators, as well as casual users who use SQL to extract information from their data warehouses. Although it provides you a SQL like interface, it is not a SQL DB. Hive is most suited for data warehouse applications, where relatively static data is analyzed, fast response times are not required, and when the data is not changing rapidly. Simply put for offline batch processing kind of stuff.

There is nothing like stored procedures in HBase as well. But they have something called as Coprocessor which resembles stored procedures in RDBMS. To find more on Coprocessor you can go here.

And as @zsxwing has said Sqoop is just a data migration tool, nothing more. Once you switch to the NoSQL world you need to be flexible and you need to abide by the NoSQL rules.

If you could elaborate your use case a bit, maybe we can help you better.

---

In response to your comment :

Yes Facebook uses Hadoop and Hive and other related tool extensively. Infact Hive was developed at Facebook. But These are not the only things. Wherever they have OLTP and full transactional need, they still depend on RDBMS. One example is their Timeline feature, which uses MySQL. They have a gigantic(and awesome) pipeline which consists of a lot of things and not just Hadoop and Hive. See the picture below.

Answer 2

Hive and Hbase are not support stored procedure. However, Hive plans to support Sp (HIVE-3087) in the future. HBase has no plan about supporting Sp since it only focuses on being a Storage and more like NoSQL.

Hive UDF could implement some function of stored procedure, though it's not enough.