I'm aware that 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)?
The Wikipedia page for 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 - it appears that ACID semantics 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 - 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).