Difference Between SQL and HiveQL

Difference Between SQL and HiveQL in Tabular Form

SQL and HiveQL Difference

  • The key difference between SQL and HiveQL
  • SQL-Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
  • The query language of Hive is called Hive Query Language(HQL), which is very similar like SQL. Hive is highly scalable. As, it can serve both the purposes, i.e. large data set processing (i.e. Batch query processing) and real-time processing (i.e. Interactive query processing). Hive gets internally gets converted into MapReduce programs.
  • It supports all primitive data types of SQL. You can use predefined functions, or write tailored user-defined functions (UDF) also to accomplish your specific needs.
Difference Between SQL and HiveQL
Difference Between SQL and HiveQL

Comparison Chart

Feature SQL HiveQL
Updates UPDATE, DELETE, INSERT UPDATE, DELETE, INSERT,
Transaction Supported Limited Support Supported
Indexes Supported Supported
Data Types Integral, floating-point, fixed-point, text and binary strings, temporal Boolean, integral, floating-point, fixed-point, text and binary strings, temporal, array, map, struct
Functions Hundreds of built-in functions Hundreds of built-in functions
Multitable inserts Not supported Supported
Create table…..as Select Not supported Supported
Select Supported Supported with SORT BY clause for partial ordering and LIMIT to restrict the number of rows returned
Joins Supported Inner joins, outer joins, semi join, map joins, cross joins
Subqueries Used in any clause Used in FROM, WHERE, or HAVING clauses
Views Updatable Read-only




SQL

  • SQL stands for Structured Query Language.
  • SQL is a language that helps us to work with databases. The database does not understand English or any other language.
  • Just as to create software, we use Java or C#, in a similar way to work with databases, we use SQL.
  • SQL is the standard language of Database and is also pronounced as Sequel by many people
  • SQL itself is a declarative language.
  • SQL deals with structured data and is for RDBMS that is a relational database management
  • SQL support schema for data storage
  • We use SQL when we need frequent modification in records. SQL is used for better performance

HiveQL

  • Hive’s SQL language is known as HiveQL, it is a combination of SQL-92, Oracle’s SQL language, and MySQL.
  • HiveQI provides some improved features from the previous version of SQL standards, like the analytics function from SQL 2003.
  • Some Hive’s’ extension like multi-table inserts, TRANSFORM, MAP, and REDUCE.