<p>The <a href="https://en.wikipedia.org/wiki/Apache_Hive#HiveQL" rel="nofollow noreferrer">Wikipedia page for HiveQL</a> provides a brief summary of the differences:</p>
<blockquote>
<p>While based on SQL, HiveQL does not strictly follow the full SQL-92 standard. HiveQL offers extensions not in SQL, including multitable inserts and create table as select, but only offers basic support for indexes. Also, HiveQL lacks support for transactions and materialized views, and only limited subquery support. Support for insert, update, and delete with full ACID functionality was made available with release 0.14.</p>
<p>Internally, a compiler translates HiveQL statements into a directed acyclic graph of MapReduce, Tez, or Spark jobs, which are submitted to Hadoop for execution.</p>
</blockquote>
<p>One of the biggest limitations is with transactions, <a href="https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-Limitations" rel="nofollow noreferrer">as documented in their wiki</a> - it appears that <a href="https://en.wikipedia.org/wiki/ACID" rel="nofollow noreferrer">ACID semantics</a> have been added recently, so the support is not quite as mature as it would be in a typical RDBMS like MySQL.</p>
<p>Hortonworks have also published a <a href="http://hortonworks.com/wp-content/uploads/2016/05/Hortonworks.CheatSheet.SQLtoHive.pdf" rel="nofollow noreferrer">cheat sheet for converting SQL to HiveQL</a> - it looks pretty useful for determining the differences between Hive and a normal RDBMS, although it does appear to be slightly outdated (it talks about v0.10 and v0.11, when the latest release as of March 2017 is 2.1.1).</p>

CC BY-SA 3.0
2017-03-01T16:18:17.333Z
2017-03-01T16:39:19.017Z
2020-06-18T08:28:10.803Z
Aurora0001
<p>I'm aware that <a href="https://en.wikipedia.org/wiki/Apache_Hive" rel="nofollow noreferrer">Apache Hive</a> provides SQL-like interface to query stored data. So I would like to ask, what are the main limitation of Hive-based SQL-like compared to other relational SQL query languages (such as MySQL)?</p>

CC BY-SA 3.0
2017-03-01T16:18:25.943Z
Doesn't seem like this is a devops related question, as its more about a specific big data application
Adam Berry
<sql><hive>
What are HiveQL limitations compared to SQL?
kenorb
# What are HiveQL limitations compared to SQL?

(Asked by: username_0 on 2017-03-01)

I'm aware that [Apache Hive](https://en.wikipedia.org/wiki/Apache_Hive) provides SQL-like interface to query stored data. So I would like to ask, what are the main limitation of Hive-based SQL-like compared to other relational SQL query languages (such as MySQL)?

**Tags:** sql, hive

> **Comments:**
> **username_1**: Doesn't seem like this is a devops related question, as its more about a specific big data application  

---

## 1 answers

### Answer by username_2 on 2017-03-01. Score: 4 | Accepted answer

The [Wikipedia page for HiveQL](https://en.wikipedia.org/wiki/Apache_Hive#HiveQL) provides a brief summary of the differences:

> While based on SQL, HiveQL does not strictly follow the full SQL-92 standard. HiveQL offers extensions not in SQL, including multitable inserts and create table as select, but only offers basic support for indexes. Also, HiveQL lacks support for transactions and materialized views, and only limited subquery support. Support for insert, update, and delete with full ACID functionality was made available with release 0.14.
>
> Internally, a compiler translates HiveQL statements into a directed acyclic graph of MapReduce, Tez, or Spark jobs, which are submitted to Hadoop for execution.

One of the biggest limitations is with transactions, [as documented in their wiki](https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-Limitations) - it appears that [ACID semantics](https://en.wikipedia.org/wiki/ACID) have been added recently, so the support is not quite as mature as it would be in a typical RDBMS like MySQL.

Hortonworks have also published a [cheat sheet for converting SQL to HiveQL](http://hortonworks.com/wp-content/uploads/2016/05/Hortonworks.CheatSheet.SQLtoHive.pdf) - it looks pretty useful for determining the differences between Hive and a normal RDBMS, although it does appear to be slightly outdated (it talks about v0.10 and v0.11, when the latest release as of March 2017 is 2.1.1).


---

