- SQL is the oldest data analysis option among the three and its ability to update itself in line with growing user expectations make it relevant even today.
- Sql is Stuctured query language which is mainly used for OLTP that is day to day operations. SQL doesn’t support petabytes or terabytes of data as Hive or Pig can. It’s very much in use and the support of its procedures, Triggers, Functions made it very useful in IT world Scenario.
- SQL is declarative and Pig is procedural to a large extent.
- SQL is a general purpose database language that has extensively been used for both transactional and analytical queries.
- Hive is a Dataware house system for Hadoop that facilitates easydata summarisation ,adhoc queries,and analysis of large datasets stored in Hadoop compatible Filesystems.
- Hive provides a mechanism to query the data using Sql like lamguage called as HIVE QL or HQL.
- Hive enables developers not familiar with MapReduce to write data queries that are translated into MapReduce jobs in Hadoop.
- Hive is built with an analytical focus.
- Hive support only structured data and have a distributed data warehouse.
- Hive use separate query language called HQL goes beyond standard SQL
- Supports SELECT, JOIN, GROUP BY, etc
- Hive limitations (Compared to SQL Languages)
- No support for Update or Delete.
- No support for inserting single rows.
- Limited number of Built in functions
- Not all Standard SQL is supported.
- An abstraction over the complexity of MapReduce programming, the Pig platform includes an execution environment and a scripting language (Pig Latin) used to analyzeHadoop data sets.
- Its compiler translates Pig Latin into sequences of MapReduce programs
- Pig can process both structured and unstructured data. It is a data flow language and environment for exploring very large datasets.
- Pig is a data flow language, invented at Yahoo. Its has different semantics than Hive and Sql.
- Pig also has functions like Filter by, Group,Order and just like Hive can have UDFs. Its little bit cumbersome for anyone to understand Pig as compared to Hive because Pig is like Scripting language where as Hive is Sql which we more fond of.