Difference between SQL and HiveQL

1. Structured Query Language (SQL): SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system also known as RDBMS. It is also useful in handling structured data, i.e., data incorporating relations among entities and variables. SQL is a standard language for storing, manipulating, and retrieving data in databases. 

2. Hive Query Language (HiveQL): HiveQL is a query language for Hive to analyze and process structured data in a Meta-store.  It is a mixture of SQL-92, MySQL, and Oracle’s SQL.  It is very much similar to SQL and highly scalable. It reuses familiar concepts from the relational database world, such as tables, rows, columns and schema, to ease learning. Hive supports four file formats those are TEXT FILE, SEQUENCE FILE, ORC and RC FILE (Record Columnar File). 

Differences between SQL and HiveQL is as follows:  

On the basis of	SQL	HiveQL
Update-commands in table structure	UPDATE, DELETE  INSERT,	UPDATE, DELETE  INSERT,
Manages	Relational data	Data Structures
Transaction	Supported	Limited Support Supported
Indexes	Supported	Supported
Data Types	It contain a total of five data types i.e., Integral, floating-point, fixed-point, text and binary strings, temporal	It contains  Boolean, integral, floating-point, fixed-point, timestamp(nanosecond precision) , Date, text and binary strings, temporal, array, map, struct, Union
Functions	Hundreds of built-in functions	Hundreds of built-in functions
Mapreduce	Not Supported	Supported
Multitable inserts in table	Not supported	Supported
Create table…as Select	Not supported	Supported
Select command	Supported	Supported with SORT BY clause for partial ordering and LIMIT to restrict number of rows returned
Joins	Supported	Inner joins, outer joins, semi join, map joins, cross joins
Subqueries	Supported	Only Used in FROM, WHERE, or HAVING clauses
Views	Can be Updated	Read-only i.e. cannot be updated

My Personal Notes arrow_drop_up

Save